Setting up MySQL replication

additional database servers against which we can run readonly clients without impacting performance on our production service. The backup process is often the most important use case for this.

a 'warm standby' that we can promote to master status in the event of failure

Getting started with replication of MySQL databases is fairly straightforward, despite the reputation for complexity and the number of questions on mailing lists/IRC. It's also copiously documented in the MySQL manual; perhaps the problem is that there is too much information there.

Setup the master. Ensure that it is writing binary logs, has a server-id and has an account for the replication slave

Setup the slave. Ensure that it has a server-id and has the details about the master and the replication account

Lock the tables on the master

Dump the data, recording the binary log file and position

Unlock the master

Load the dump into the slave

Start slaving using 'Change Master'

I will use 'Master$' to indicate a root shell prompt on the Master server and 'Slave$' to indicate a root shell prompt on the slave; the following lines at the same indentation without the prompt will be output.

Configure the master

Edit your my.cnf:

log-bin = /var/lib/mysql/<hostname>-bin.log
server-id = 1

Don't forget to either restart the server or use 'SET GLOBAL' to ensure that these variables are set in your running instance.

Configure the slave

Do not restart the slave's mysqld or it will attempt to start slaving and you're not ready for that yet.

Lock the Master

You'll need to lock the master to ensure that no client is able to update any table while you are taking the dump of the initial data so that you can be sure of a consistent data set to start with.

Master$ mysql> FLUSH TABLES WITH READ LOCK;

This session needs to be maintained until the mysqldump is complete.

Dump the data

The critical option for mysqldump(1) is --master-data which records the coordinates for the binary log (i.e. the log file and the position in that file). You'll need this for your 'CHANGE MASTER' step later on.

You can manually get the coordinates with:

Master$ SHOW MASTER STATUS

There are a number of other possible options that you might want to look at for mysqldump, but I'm usually covered by:

Verify

In order to verify that slaving is occuring properly, you will want to check the 'Seconds_Behind_Master' variable from SHOW SLAVE STATUS on the slave.

A good further test to convince yourself might be to create a new table or database on the server and see whether it appears on the slave.

Further considerations

Binary Log expiry.

Eventually, and probably sooner than you expect if your database server sees any significant level of activity, your binary logs will begin to take up a huge amount of space. However, your slave is presumably keeping up with the replication fairly well so you probably don't need to keep your binary logs for long. Mysqld will expire them for you with:

expire_logs_days=n

Statement-based replication vs Row-based vs Mixed

Statement based replication was the default initially and it caused problems with autoincrement fields and UUID() statements (in particular). Vaguely recent versions of mysql offer Row Based Replication that is guaranteed safe, and is how other RDBMS products work. Mixed format replication provides the efficiency/compact expressiveness of SBR and the integrity of RBR. binlog_format=MIXED is probably the best approach; for further reading: http://dev.mysql.com/doc/refman/5.5/en/replication-formats.html

If you're using innodb_file_per_table=1, you can slave several databases to different destinations.

If you want to slave databases from different masters, you'll need to run more than one instance of mysql on the slave, because CHANGE MASTER will only take ONE master.

Multi-Master Replication

If you want more than one master in your setup, you have to avoid the problem of conflicting auto_increment values, but it's not terribly difficult.
The easiest way to get this going for an existing database is:

Reinitializing replication for a large database

If your database is large, then dumping it to SQL and loading it into a slave might not be a viable method because it just takes too long. One of the nifty tools provided by Percona is the innobackupex script. While they might not be satisfied "with the architecture, code quality and maintainability, or functionality of innobackupex", it has worked to meet my needs on several critical occasions.

Here's one incantation that created a mysql datadir ready to be started as a replication slave: