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:
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:
stop slave; show slave statusG" as mysql root on the mysql command line
tar zcf - /var/lib/mysql | ssh newmaster "tar zxf -"
CHANGE MASTER TOas 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.