MySQL Replication Techniques

MySQL Replication is a feature of the MySQL database that allows you to create a live copy of a database.

Benefits of scaling-out a MySQL database using replication:

* Backup; If one database burns down, all recent data lives on.
* Performance; Reads and Writes can be spread out across multiple machines.
* Robustness; In the event of a problem with one database, you can choose to switch it out.

In this post, I'd like to cover popular MySQL replication setups we use here at SPI, explaining the pros and cons of each one.

Master - Slave

One MySQL master, handling read and writes
One or more MySQL slaves, handling reads only



Pros:

+ Most simple to setup
+ Logically separating reads (slave) from writes (master), translates to improved read throughput. No waiting for locks on reads.

Cons:

+ If Master is down, Slave doesn't automatically take over. There are a few hacks to make this setup work properly, by using DRBD or other home-grown solutions.

Master - Master

Two MySQL masters, each handling both reads and writes.



Pros:

+ Robust. If one database is down, the application can continue functioning normally, with HAProxy (or another MySQL load balancing solution) routing all queries to the other master.
+ Ability to spread-out writes across several machines.
+ Supports scaling outside of a single geographic location.

Cons:

+ More challenging to setup. You have to properly configure /etc/my.cnf, giving each database it's own ID and using different auto-increment offsets.
+ Stateful. If clientA starts a database session with masterA, you have to ensure clientA will continue hitting masterA on future queries. (Simple round robin load balancing doesn't work with a master-master setup)
+ Need to continually monitor replication validity. Can't afford for either one of the masters to fall-back or miss any data.

Circular Replication

Three (or more) MySQL databases, typically all setup as master-master-master, with each node capable of handling reads and writes.



Pros:

+ All benefits of a master-master setup, with improved performance and reliability.

Cons:

+ Initial setup can be tricky if you don't know what you're doing.
+ If replication breaks (i.e. one of the databases stops functioning), the replication chain stops and restarting it requires manual intervention.

Replication on the same machine

Two mysqld instances running on the same machine.

This configuration comes in handy when you need to connect two circular replications together, overcoming MySQL's limitation where each node can only be the slave of one master.

Consider two separate circular replications:

A => B => C => A
D => E => F => D

A is slave of B, B is slave of C and C is slave of A.
D is slave of E, E is slave of F and F is slave of D.

If we need to connect these two circles together, let's say at points A<->D, we will have to setup A as a slave of both B and D. D will become a slave of both A and E.

This is accomplished by running another instance of mysql on machines A and D. The other instance runs on a different port pointing to the same data directory.

Pros:

+ All benefits of circular replication, with better performance, ability to easily support remote geographic locations and slow networks.

Cons:

+ Can be tricky to setup, especially when using InnoDB. Start here.