I’m a Systems/Software Engineer in the San Francisco Bay Area. I moved from Columbus, Ohio in 2007 after getting a B.S. in Physics from the Ohio State University. I'm married, and we have dogs.

Under my github account (https://github.com/addumb): I open-sourced python-aliyun in 2014, I have an outdated python 2 project starter template at python-example, and I have a pretty handy “sshec2” command and some others in tools.

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
    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.

Creative Commons License
This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 United States License. :wq