Pros and Cons of MySQL replication types and how to run them in the cloud
Types of MySQL replication
MySQL is one of the most popular open source databases in the world. It is very easy to start using MySQL for your application or website. For instance, MySQL is the default option for popular CMS solutions such as WordPress and Magento.
However, it is not trivial making MySQL totally reliable and available in cases of high load. Database replication and clustering can help you achieve this goal.
As MySQL is one of the first open source databases, MySQL replication capabilities have evolved in time. This is the list of the most used replication types for MySQL:
- Master-slave replication
- Master-master replication
- Group replication
- Multi-master cluster (available for MariaDB, which is the fork of MySQL)
Let’s look closer to each of them.
MySQL Master-Slave replication
Master-slave MySQL replication was the very first replication option for MySQL database. It assumes that you have a single Master server that accepts both reads and writes and one or more read-only Slave servers. Data from the master server are asynchronously replicated to Slave servers.
- It is very fast as doesn’t impose any restrictions on performance.
- You can split read and write requests to different servers. For example, all analytics queries can be made on Slave nodes.
- It is not very reliable because of asynchronous replication. It means that some committed on master transactions may be not available on slave if master fails.
- Write requests can hardly be scaled. The only option to scale write requests is to increase compute capacity (RAM and CPU) of the Master node.
- Failover process is manual in a general case. You should take care of promotion replica node to master one.
MySQL Master-Master replication
Master-master MySQL replication has been evolved from master-slave replication and solves its major issues. This type of replication assumes that you have two or more master nodes that can accept both read and write requests. In addition, you can have multiple slave node for each of your masters. The replication between master nodes is asynchronous.
- You have an option to scale write requests not only by increasing the computing capacity of a single master node but via adding additional master nodes.
- Failover semi-automatic because you have multiple master nodes. The chance that all master nodes fail simultaneously is very low. If any of master nodes fail, there is at least one more master node that can handle its requests.
- Due to asynchronous replication between all master nodes, you can lose some transaction in case one of master nodes fail.
- Due to asynchronous replication, you can’t be sure that backups made on each master node contain the same data.
- Failover is still not fully automated in case you need to promote a Slave node to Master one.
MySQL MGR replication
MySQL Group Replication feature is implemented by a MySQL Server plugin and provides replication in a completely new way based on distributed state machine architecture.
Group MySQL Replication allows creating fault-tolerant systems with redundancy that guarantees that even if some of the servers fail (as long as it is not a majority), the cluster still will be available. The unique feature of MGR replication is that it provides you with built-in automatic recovery and conflict resolution.
How MySQL Group Replication can help you?
- Removes the need for manual switching in case of failure of one of the servers.
- Provides fault tolerance.
- Allows you to build a system with the ability to change data on any server.
- Provides automatic reconfiguration.
- Automatic failover in case Master node fails. Servers that belong to the same Group will elect new Master automatically. Therefore, MySQL MGR replication can be considered as a Clustered solution.
- It is synchronous replication in general, so you can be confident that you will not lose committed data in case of Master node fails.
- You can scale both reads and writes by adding new Master and Slave nodes.
- It is doesn’t impose big performance restrictions because it is enough to have only 2 Master nodes for a full-fledged MySQL cluster.
- It is available only for MySQL, but not for its forks: MariaDB and Percona.
- One Group is limited to 9 nodes.
Galera Cluster (Multi-master MySQL replication)
Galera Cluster is a synchronous cluster of databases with several master nodes, based on synchronous replication. Galera performs the role of multi-master and allows you to direct read and write requests to any node at any time. In case one of the individual nodes will be lost, interruption of operations will not occur. Nodes are initialized automatically and there is no need to back up the database manually and copy it to the new node. Galera is a very safe solution because it provides synchronous replication, i.e. there is no visible lag on the slave side, data is not lost when a node is lost.
- It is reliable because guarantees data safety using quorum protocol.
- Galera provides with you with true clustering capabilities, including automatic failover.
- Read requests are very fast and can be scaled efficiently.
- It provides large performance overhead because assumes that all transactions will be committed to at least 3 servers. If you have massive write requests, it may result in performance degradation even for read queries.
- It is only supported for MariaDB and Percona XtraDB, but not supported for MySQL database.
MySQL HA architecture in Hidora
When we decided to implement the support of MySQL high availability on Hidora, we took into account different considerations, including reliability, scalability ease of setup, ease of configuration and monitoring.
As a result, our MySQL architecture includes the following components:
- ProxySQL – a lightweight load balancer for MySQL-powered databases. We spin up 2 nodes of ProxySQL for High Availability considerations. It provides the following features:
- Query caching
- Query routing
- Integration with Orchestrator to support failover
- Orchestrator – is a MySQL high availability and replication management tool that provides different capabilities such as topologies visualization, replication configuration and auditing of replication problems. It is also able to detect master’s failure and provide you with options of manual or automated failover.
- Replicated MySQL/MariaDB nodes
- We support all popular replication options including Master-Slave, Master-Master, Group replication for MySQL, and Galera for MariaDB.
The key feature available on Hidora is automatic clustering and horizontal scaling of MySQL/MariaDB nodes. It means that you can manage these databases via simple UI on PaaS layer, but not only as virtual machines. For example, you can increase the number of Slave nodes just in a click, and they will automatically join your DB cluster. And vice versa, you can safely shrink the cluster and remove unneeded nodes easily.
Moreover, in addition to Hidora’s built-in UI, you can access to Orchestrator control panel to view and monitor MySQL/MariaDB replication process.
How to install MySQL HA solutions on Hidora
How to Enable Automatic Clustering for MySQL Database?
- Click on NEW ENVIRONMENT at the dashboard and choose MySQL database.
- Next, you need to press the Auto-Clustering button in order to see different replication options for MySQL database in the corresponding dropdown list. You can choose from any of the following options:
- Single-Primary Group Replication
- Multi-Primary Group Replication
Your replicated database will be created and configured once you press “Create” button. All the magic will be done behind the scene, so you don’t need to perform any extra actions.
Once you created the database instance, you can log in to the Orchestrator control panel using access credentials from the received email.
Matthieu Robin is CEO at Hidora, an experienced strategic leader, a former system administrator who had managed and configured more environments manually than anyone on the planet and after understanding that it could be done in several clicks established Hidora SA. He regularly speaks at conferences and helps companies to optimize business processes using DevOps. Follow him on Twitter @matthieurobin.