MySQL Slave Initialization Do's and Dont's

July 05, 2009

This assumes you know how to set up a MySQL slave server. Go here if you don't already know and here if you don't care. Everybody needs slaves for their MySQL database instances. A slave copies data-changing DB queries on to another server that attempts to keep up. You can abuse the slaves all you want: run backups, do ad-hoc (non-production) queries, failover, alter, promote... whatever. The wins for having MySQL slaves pretty greatly outweigh the extra costs (hardware, power, Sys Admin time) for most critical databases.

So, how do you set up a new slave? Ideally, you deploy the slave when you deploy the master, but that takes foresight not everybody has. Sometimes, you need to make a new slave if you're migrating the master to new hardware. Ima go over the 2nd case.

You have a master and a slave, but need to make a new master and new slave, right? Well... what do you do? You have a few options:

1. Stop the slave mysqld process and tar+scp or rsync files to the new master.
2. mysqldump the slave and pipe into a new mysqld instance on the new master.
3. Execute "stop slave;" on the slave and tar+scp or rsync the files to the new master.

I tend to use the 3rd method more often than I probably should. Some pros of it are: no slave downtime, as fast as method 1 for large databases (which is faster than method 2), and it's kind of breaking the laws by copying files under a live database so you can feel like a rebel. With all of these methods, you'll need to record the slave status once the slave is no longer writing new data. That's after "stop slave;" or the mysqld process is shutdown cleanly. I say "cleanly" because if you kill -9 mysqld, the master.info file may not be updated to reflect the current position in the bin logs.
So here're the steps for method 3:

1. SLAVE: execute stop slave; show slave statusG" as mysql root on the mysql command line
2. SLAVE: tar zcf - /var/lib/mysql | ssh newmaster "tar zxf -"
3. SLAVE: execute start slave;
4. NEW MASTER: stop mysqld if running, move ~/var/lib/mysql to /var/lib/mysql
5. GOTCHA: if relay logs were stored outside of /var/lib/mysql, remove any and all relay log files
6. GOTCHA: if using InnoDB and copying files from a running slave, delete ib_logfile* from the mysql directory!
7. NEW MASTER: start mysqld and execute CHANGE MASTER TO
MASTER_HOST='current-master',
MASTER_USER='replication-account',
MASTER_PASSWORD='replication-password',
MASTER_LOG_FILE='log-file-from-slave-status',
MASTER_LOG_POS=12345678910;
START SLAVE; as mysql root on the mysql command line

Yeah, so... you gotta be careful about some things. If you copy files from a slave server that writes its relay-log files to a directory other than /var/lib/mysql (/var/run/mysqld is default on RedHatty systems, akshully), be sure you remove the relay-log.info file. Also, if the slaves use the InnoDB storage engine at all, it writes to two (configurable, I think) files named ib_logfile0 and ib_logfile1. If your slave process was running when you copied the directory over, those two files are replayed on startup and can cause the tables to be in a pretty bad state, giving you the error (number 1033) "Incorrect information in file: './database/table.frm'". If you get that error on a slave, try stopping mysqld, removing the InnoDB log files (ib_logfile*) and starting up mysqld again.