![Page 1: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/1.jpg)
WEBINAR on
MySQL Fabric For HA and Scaling Out
Presented ByAbdul Manaf
![Page 2: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/2.jpg)
OSSCube Corporate Capabilities
![Page 3: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/3.jpg)
Who Are We
• Global Solutions Provider having offices in the US, UK and India.
• We offer complete solutions in the domain of Digital Transformation, IT Modernization and IoT.
• Appraised at CMMI Level 3, we've been amongst the top 100 Great Places to Work® for three consecutive (2014, 2015 & 2016) years.
• We’ve delivered enterprise solutions to the likes of The New York Times, PVR Cinemas, ACER, Intel, CU Solutions, among others.
![Page 4: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/4.jpg)
Digital Transformation
Practice
• PIM (Product Information Management)• WCM (Web Content Management)• DAM (Digital Asset Management)• e-Commerce
Modernization Practice
• Cloud • Enterprise Mobility• Application Modernization
IOT Practice • SMART Track & Trace• Product Lifecycle Management • Big Data - Analytics
© 2015 OSSCube LLC. All rights reserved.
What We Do
![Page 5: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/5.jpg)
Agenda
• Concept of HA
• Concept of Sharding
• MySQL Fabric Basics
• MySQL Fabric for HA
• MySQL Fabric for Sharding
• Some commands
![Page 6: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/6.jpg)
High Availability
![Page 7: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/7.jpg)
High Availability
![Page 8: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/8.jpg)
Availability Calculation
![Page 9: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/9.jpg)
Principles and Causes of loosing HA
Principles of High Availability• Elimination of single points of failure• Reliable crossover. In multi threaded systems, the crossover point
itself tends to become a single point of failure. • Detection of failures as they occur. If the two principles above are
observed, then a user may never see a failure. But the maintenance activity must.
Scheduled and unscheduled downtimeCauses of losing it (unscheduled downtime)• Failures of hosts , Databases / MySQL• Operating system• The hardware • Maintenance activity that may otherwise cause downtime• And many more ……
![Page 10: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/10.jpg)
MySQL HA Solutions
HA is achieved via redundancy
The primary solutions supported by MySQL include:
• MySQL Native Replication• MySQL Cluster• MySQL with DRBD• Galera Cluster• Oracle VM Template for MySQL• MySQL with Solaris Cluster
![Page 11: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/11.jpg)
So what is MySQL Replication?
Replication copies transactions from the master and replays them to the slave
![Page 12: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/12.jpg)
Sharding
![Page 13: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/13.jpg)
Sharding
• Sharding is a type of database partitioning that separates very large databases into smaller, faster, more easily managed parts called data shards.
• The word shard means a small part of a whole.
![Page 14: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/14.jpg)
(Sharding) Tumblrʼs Size and Growth
![Page 15: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/15.jpg)
Why Sharding?
Write scalability
• No other way to scale writes beyond the limits of one machine
• During peak insert times, you'll likely start hitting lag on slaves before your master shows a concurrency problem
Data size
• Working set won't fit in RAM• SSD performance drops as disk fills up• Risk of completely full disk• Operational difficulties: slow backups, longer to spin up new
slaves• Fault isolation: all of your data in one place = single point of
failure affecting all users
![Page 16: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/16.jpg)
Make Sharding Your Last Resort
• Optimize everything else first, and then if the performance still isn’t good enough, it’s time to take a very bitter medicine
• The application developer has to write more code to be able to handle sharding logic
• Operational issues become more difficult (backing up, adding indexes, changing schema).
![Page 17: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/17.jpg)
MySQL FabricAn extensible and easy-to-use framework for
managing a farm of MySQL server supporting high-availability and sharding
![Page 18: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/18.jpg)
MySQL Fabric: What is it?
![Page 19: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/19.jpg)
General Concepts , MySQL Fabric
• Group : collection of mysqld servers
• Global group : Special groups that store updates that must be propagated to all shards
• Node : Running instance of MySQL Fabric
• Shard : Horizontal partition of data in a table
• Primary : Group member that has been designated master
• Secondary : Group member that is read only
![Page 20: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/20.jpg)
High Availability, MySQL Fabric
• Group of MySQL servers
• Primary server
• Secondary servers
• GTID based replication to work
• MySQL 5.6 +
![Page 21: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/21.jpg)
MySQL Fabric , HA
What Fabric Provides in terms of HA
• Failure detection and promotion
• Routing of database
![Page 22: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/22.jpg)
MySQL Fabric , Sharding
• Used to scale-out the database servers by partitioning the data across multiple MySQL Server "groups".
• Group could contain a single MySQL Server or it could be a HA group.
![Page 23: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/23.jpg)
MySQL Fabric , Sharding
![Page 24: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/24.jpg)
Birds-eye View of a Sharded Database
![Page 25: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/25.jpg)
MySQL Fabric: Configuration
![Page 26: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/26.jpg)
MySQL Fabric: Setup and Teardown
![Page 27: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/27.jpg)
MySQL Fabric: Starting and Stopping
![Page 28: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/28.jpg)
Sharding Architecture
![Page 29: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/29.jpg)
MySQL Fabric: Sharding Setup
![Page 30: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/30.jpg)
MySQL Fabric: Create Groups and add Servers
![Page 31: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/31.jpg)
MySQL Fabric: Create Groups and add Servers
![Page 32: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/32.jpg)
MySQL Fabric: Set up Shard Mapping
![Page 33: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/33.jpg)
MySQL Fabric: Add Shards
![Page 34: Using MySQL Fabric for High Availability and Scaling Out](https://reader035.vdocuments.us/reader035/viewer/2022062823/58781ee21a28aba12d8b61e3/html5/thumbnails/34.jpg)
Current Limitations
• Sharding is not completely transparent to the application.
• Auto-increment columns cannot be used as a sharding key
• The MySQL Fabric process itself is not fault-tolerant and must be restarted in the event of it failing
• Because the connectors perform the routing function, which could add extra latency