Introduction
In MySQL, replication is a process that provides redundancy, prevents data loss, and increases efficiency on data access. It works basically by live-copying a data set, stored in a MySQL database, from a server to another. This configuration is known as master-slave replication. But, better yet than this popular master-slave replication, there is an evolution of it known as master-master replication, which was introduced to solve some of its predecessor’s major issues. This newer configuration enables read/write operations from multiple servers, allowing MySQL to copy data from two or more master nodes. Also, master-master replication is divided in two parts: “All active masters” and “Active and passive masters”.
As with all technological advancements, there are numerous benefits, but sometimes at the cost of some drawbacks. For an easier reading experience, we’ll write some of master-master replication pros and cons on two different lists.
PROS:
- Scale writes and/or reads is easily done by adding new instances.
- Failover and failback are easy to do.
CONS:
- Greater communication latency.
- With all masters acting as active masters, there is a tendency to data drift and data consistency. issues, which could later result in trouble creating a golden image of the data.
Deployment is more difficult than its master-slave replication counterpart.
Worst case scenario
With new technology is not always a bed of roses, and master-master replication is not the exception. Even though it has noticeable benefits, there is a substantial problem that could arrive with this replication, and it is that, if some of the master servers would crash, recovering the state of the data from all active masters would be complicated.
A solution to this problem could be to use one active master and several passives. If the setup is flexible when it comes to failover and updates, then you could still scale reads when you do not need a result with a 100% precision. This is caused by replication delay, and it means you cannot scale writes at all.
Master-slave replication
As mentioned before, this is the simplified version of master-master replication. Sometimes simple is better, so check the pros and cons to make up your mind about it.
PROS:
- Easy to set up.
- To date the most common architecture design when it comes to MySQL.
CONS:
- No automatic failover/failback. You would need to use a third party application.
- Downtime involved.
- Does not offer the possibility to scale writes.
Galera cluster
This is a synchronous multi-master replication database cluster for MySQL. As with the master-master replication configuration, this has master nodes that can read and write on any other node. It also provides certification-based replication through the wsrep API.
PROS:
- Higher availability compared to master-master and master-slave replications.
- Pretty good write scalability with some limitations when it comes to massive data volumes.
- Uses semi-sync replication.
- Uses IST (Incremental State Transfer) to reduce the amount of data transferred once a expelled node joins back a cluster.
- High availability across different DCs.
CONS:
- Write latency is higher compared to common replication configurations.
- Transaction speed equals the slowest node.
- Is susceptible to writes contention during massive updates in the same dataset (i.e. hotspot).
- Bugs are very common because of the complexity / limitations.
Group replication
This last replication method seems similar to Galera cluster, and it even shares the same benefits and disadvantages, but under the hood they have quite different implementation and replication technology, and should be considered carefully. Real load tests should be made on this replication method. This replication enables you to create elastic, highly-available, fault-tolerant replication topologies.
PROS:
- Scales writes more efficiently than Galera but still prone to the same issue with hotspot writes.
- Deliver writes only to quorum, so it should be faster than Galera.
CONS:
- A lot of things under the hood which might be a bit difficult to amateur DB administrators.
- Node reinitialization can be expensive and expelled nodes won’t join quorum automatically.
- Less WAN support.
- Available only for vanilla MySQL.
Conclusion
So there are many replication options for your database, each with its strengths and weaknesses, but as this article points, the most popular and possibly most useful is the master-master replication. Still, part of managing a database is to find out the best configuration for it, and that depends on your needs and those of your system. So use this article as a starting point on MySQL replication, and stay around for more MySQL tips and tricks!
