Even though replication is designed to keep your data in sync, circumstances might require you to set up slaves afresh. One such scenario might be severely changing the master data, making replication too expensive. Using a SQL dump to re-initialize the slaves might be too time-consuming, depending on the size of the data set and the power of the slave machines.
In cases where master and slave databases are the same size anyway (meaning, you do not have filters in place to sync data only partially) and if you can afford a downtime on the master database, there is another way of providing slaves with a fresh starting point: copying the master's data files to the slave.
Note
Beware that this approach will lose all data that was changed on the slave alone. So make sure that this is what you want!
To follow along with this recipe you will need privileges to shut down both master and slave MySQL instances and access the data and log directories on both machines. Depending on the size of your database you will have to judge which method of copying will be the best between the machines. If both are part of a local area network, copying via a shared drive or something like FTP will probably be the fastest way. You might, however, need to resort to other means of data transfer like external hard disks or the like, when only limited bandwidth is available.
Moreover, you will need administrative MySQL user accounts on both sides to execute the necessary statements to control replication.
- Open the master's configuration file with a text editor. Locate the line controlling the name of the binlog files. It is located in the
[mysqld]section and will look similar to this:[mysql] ... log-bin=master-bin ...
- Change the value of that setting to a different name. In this example, we will use
log-bin=new-master-bin. This will cause the master MySQL server to start with a new sequence of binlogs upon its next launch, making a convenient starting point for the replication. - Shut down the master database.
- Navigate to the MySQL data directory. The exact location can be found in the MySQL configuration file. Make sure to find both InnoDB data and log locations.
- Optionally, copy data and log files to another location locally on the master. This might be faster than copying via network or USB drives, and allows for a quick restart of the master. If you do this, use this temporary location to copy the data in the next step.
- Copy the data to the slave machine. We recommend a temporary target location on the slave because this allows the slave to continue running for the time the copying takes. Unless you want to bring along all the user accounts and privilege information from the master to the slaves, you should exclude the
mysqlfolder from the data directory. You also need not copy the binary logs (in this example calledmaster-bin.*). - Stop the slave server.
- You can restart the master once the original data and transaction log files have been copied. Verify it starts with a new sequence of binlogs called
new-master-bin.000001. - Write down the names and sizes of InnoDB data and log files you copied to the slave. These will have to be entered into the slave's configuration because, otherwise, InnoDB will not start up. Also, pay attention to an
autoextendoption, which the last of the data files might have attached. Make sure you carry over this option, should it be there on the master. You can also take these values from the master's configuration file, of course. - Replace the original slave data and log files with those of the master. Make sure you keep the
mysqldatabase directory if you decided not to copy it from the master. - Make sure you delete the
master.infofile and any relay-logs from the slave those do not match the current state anymore and would cause trouble when the slave is restarted. - Edit the slave's configuration file to match the names and sizes of the data files you wrote down a minute ago.
- Start the slave server again. It should come up without any problems using the new data files. If not, make sure you got everything right in the config file regarding names and sizes of the data files.
- Re-initialize the replication on the slave. This is rather easy because we altered the master's configuration to log any changes that occurred after the snapshot copy was taken to a new series of binlog files. Fill in the appropriate host name, user, and password values for your master:
slave> CHANGE MASTER TO MASTER_HOST='master', MASTER_USER='repl', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='new-master-bin.000001'; slave> START SLAVE;
As we want the slave to start reading the
new-master-bin.000001file from the beginning, noMASTER_LOG_POShas to be specified. - Verify whether the slave is running with:
The principle of this recipe is very simple: replication needs a common starting point on master and slave. What could be better than a 1:1 copy of the original master's data? As the master is shut down during the process, no more writes can happen. Configuring it to start with a new binlog file on its next start makes it trivial to point the slave to the right position because it is right at the new file's beginning.
If you cannot change the master binlogs' file names, the process is slightly more complicated. First you need to make sure nobody can modify any data for a short period of time. You do so with a FLUSH TABLES WITH READ LOCK; statement. Then issue a SHOW MASTER STATUS; and note the values. Now, without closing the client connection or releasing the lock, shut down the master server. Only if the lock is kept while shutting down the master can you be sure no write operations take place and invalidate the binlog position you just gathered.
Copy the data and transaction log files as described above. The remaining steps are the same, except of course, when you issue the CHANGE MASTER TO on the slave. Here you need to insert the MASTER_LOG_FILE and MASTER_LOG_POS you got from SHOW MASTER STATUS.
The steps described above require you to take down both master and slave databases, albeit not necessarily at the same time. Nevertheless, this might not be an option if you are dealing with a production system that cannot be shut down easily.
In these cases, you have some other options that are, however, not explained in detail here.
If your data and log files are stored on a logical volume managed by LVM, you can use its snapshot feature to conserve the data files' state once you have got the SHOW MASTER STATUS information. As soon as the snapshot has been taken, you can release the lock again and proceed as described above, copying not the most current version but the snapshot files. Be advised, however, that this approach might take a significant hit on the I/O performance of you master!
At the time of writing, an open-source alternative to the commercially available innobackup tool (available from http://www.innodb.com/products/hot-backup/ )is under active development. While being primarily a backup tool that allows backing up InnoDB databases while the server is up and running, the documentation contains a (currently empty) section on setting up a slave from a backup in replication. Experience tells that Percona—the company behind xtrabackup—is very engaged in the MySQL ecosystem and might very well have completed its set of instructions by the time you read this. To check on the current status of the project go to https://launchpad.net/percona-xtrabackup.