MySQL Simple? Master-Slave Replication

(Or: How I learnt to stop worrying and love the quirks)

During my time working with MySQL replication, I have learnt a number of things which can either make the process simpler, or far more difficult and problematic. I have tried to distil these pointers into one short post which will hopefully help other people at some point!

There are quite a few advantages of having a slave read-replica – obviously you have an online backup in case the master fails, but also more subtlety – you have a database which you can image with mysqldump and freeze the slave during the process (--single-transaction=TRUE) while not affecting the online master – once the snapshot has finished, the slave will catch up with any changes. As the database gets larger, this feature becomes more and more important.

1) USE MONITORING

So, it turns out that MySQL replication tends to be flaky. Always. It can work for weeks and weeks, and then suddenly one statement/row freaks it out, and it stops. Unless you are proactively monitoring the slave replication, it will break, and you won’t know about it.

I recommend using Nagios (it’s free!), with a handy plugin, check_mysql_health. It does what it says on the tin! There are some quirks on GRANTS required to monitor the various options, especially with regards to slave replication.

2) TO ROW OR NOT TO ROW?

There are 2 (well, 3) types of replication in MySQL.

• STATEMENT – This has been around since the dawn of time
• ROW – The newer form of slave replication – generally considered the safest
• MIXED – Why not have both?

There is one “gotcha” to be aware of – if you are using various isolation modes, e.g. READ_COMMITTED, then you can only use ROW replication. Otherwise, each have their pros/cons, but generally I choose to use ROW as it is the safest form of replication. The MySQL manual provides a useful guide listing the advantages/disadvantages of each form.

3) REPLICATE EVERYTHING

You can configure MySQL to only log certain DBs or tables to the binlog (binlog-do-db), however, be-warned – there be dragons. Unless you are generating HUGE amounts of data, it is much safer let everything log to the binlog.

On the slave side, you can choose to only replicate certain DBs or tables from the replicated binlog (replicate-do-db), but again, this leads to unexpected problems.

There is a useful article that explains some of the unexpected consequences of these options here.

4) SLAVE READ-ONLY

Always make sure that the slave is marked as read-only in the main configuration file with the option read-only=1.

• If it is not, you can (accidentally) make changes to it, and it won’t be in sync with the master
• If you need to fail-over to the slave, then you will need to restart the MySQL process with the read-only option commented out

5) UNIQUE SERVER IDs

Each server in the replication configuration will need to have a unique value set for the server-id value in the main configuration file.

6) EASY DATABASE EXPORT FROM MASTER

When setting up replication, the slave needs to know the position in the binlog which the snapshot was taken from in order to start the replication, and only replicate the required data changes since the snapshot.

This can be done by using the CHANGE MASTER commands in the SQL command prompt on the slave, however, a useful trick is to let the position/log information get incorporated into the dump file from the master, this can be done as follows:

$ mysqldump --user=<username> -p --master-data --all-databases --flush-privileges --default-character-set=utf8 --max_allowed_packet=1G --single-transaction=TRUE --routines > master_backup.sql

Options might change depending on specific configuration of the master, however, the above command would dump all databases and lock the database while doing it (--single-transaction=TRUE) – and add the position/log information to the dump (--master-data).

When restoring the backup to the slave, the master log file name, and position will automatically be set, meaning that assuming the MASTER HOST/PORT and username etc. has been setup (and this wouldn’t normally change), then each time the slave has to be resync’d the process is:

1. Stop slave
2. Restore backup file
3. Start slave

Quick and simple, and no having to copy down log position numbers and making sure they haven’t changed before and after the dumps!

I will be writing a simple step-by-step guide to show the specific options used when setting up replication between MySQL instances – watch this space!