The easiest way to (re)start MySQL replication

I’ve run mirrored MySQL instances with asynchronous replication from time to time for almost 15 years. The fundamentals haven’t changed much over that time, but one thing that has drastically improved is mysqldump‘s support for properly initializing slaves.

In order for your replication slave to have a complete and uncorrupted database, you need to arrange for it to use exactly the same data as was on the master at some particular instant in time as a starting point. In practice, this means taking out some kind of lock or transaction on the master while a complete copy of the database is made. Then, you need to tell the slave from what point in the master’s binary log to start applying incremental updates.

It used to be that doing all this required a lot of refreshing one’s memory by reading the MySQL manual in order to issue a variety of queries manually. But of course, there’s no reason the steps can’t be scripted, and I was pleased to discover this automation is now nicely packaged as part of mysqldump.

By including –master-data in the following command (to be run on the master), mysqldump will take out locks as necessary to ensure a consistent dump is generated, and automatically append a CHANGE MASTER TO query to the dump file with the associated master binary log coordinates:

$ mysqldump --add-drop-database --master-data -u root -p --databases list your replicated database_names here > /tmp/master-resync.sql

That way, you can simply apply this dump file to a slave server whose replication has broken (for example, due to an extended loss of connectivity) and restart the slave process to be back in business. On the slave:

$ cat /tmp/master-resync.sql | mysql -u root -p
$ mysql -u root -p
mysql> START SLAVE;
Query OK, 0 rows affected (0.01 sec)

Tadaa! Clean restart of MySQL replication without any FLUSH TABLES WITH READ LOCKs or manual copying of binlog coordinates in sight!