MySQL Replication is the most popular and cost-effective high availability solution. Many of the world's most trafficked web properties like Facebook, Twitter, Zappos and Zynga rely on MySQL Replication to serve millions of users and handle their exponential growth.
1.0 MySQL clustering options
There are several ways of providing High-Availability (HA) & Scalability in MySQL. Options range from providing just HA and not the scalability to others which would provide only scalability and not HA. This document will provide an overview of most of the official and unofficial methods that are used to provide HA and/or scalability for MySQL setups
2.0 Types of MySQL clusters
These are the kind of MySQL clusters:
- Operating System Clustering Solutions (Red Hat Cluster Suite and GFS)
- DRBD & Pacemaker cluster
- MySQL (MASTER-SLAVE) replication.
- MySQL (MASTER-MASTER) replication.
- MySQL Cluster 7.0
3.0 Analysis and configurations
In this section we will cover the introduction to each method mentioned in the list above and also would list down its pros and cons.
3.1 Operating System Clustering Solutions (Active-Passive & pseudo Active-Active)
This is the most basic solution in the overall spectrum of the clustering suites. This is active- passive solution in which only one node serves MySQL services at a time and that node is called Primary node. The other node is in the standby mode and is called Secondary node. The secondary node will only start serving the MySQL services only if the cluster logic detects a failure of the primary node and promotes it to the primary mode migrating all services to it.
Sometimes distinct services can be split into both nodes and both nodes can act as Primary and secondary for each other. This way we can avoid any one server to idle out and for this reason it issometimes referred to “pseudo active-active