6.1 Setting Up a New Replication Slave

If you use MySQL replication, MySQL Enterprise Backup allows you to set up a slave database without stopping the master, by backing up the master and restoring that backup on a new slave server.

1. Take the backup, transfer it to the slave server, use mysqlbackup with the apply-log option to prepare it, and put the restored backup and the log files in the right directories for the new slave.

2. Edit the my.cnf file of the new slave and put skip-slave-start and event_scheduler=off under the [mysqld] section.

3. Start the new slave mysqld (version >= 5.1). It prints the latest MySQL binary log position the backup knows of.

   …
   InnoDB: Last MySQL binlog file position 0 128760007, file name ./hundin-bin.000006
   …

   While a Last MySQL binlog file position has been displayed, it is NOT necessarily the latest binary log position on the backed up server, as InnoDB does not store binary log position information for any DDL operations or any changes to non-InnoDB tables. Do not use this binary log position to initialize the slave. The next step explains how to find the correct binary log position to use.

4. Look for the file datadir/meta/backup_variables.txt where datadir is the data directory of the new slave. Look into the file to retrieve the latest binary log position and the corresponding log file number stored inside:

   binlog_position=hundin-bin.000006:128760128

5. Use the CHANGE MASTER TO SQL statement and the information you have retrieved in the last step to initialize the slave properly:

   CHANGE MASTER TO
   MASTER_LOG_FILE='hundin-bin.000006',
   MASTER_LOG_POS=128760128;

6. Set the statuses of any events that were copied from the master to SLAVESIDE_DISABLED. For example:

   mysql> UPDATE mysql.event SET status = 'SLAVESIDE_DISABLED';

7. Remove the line skip-slave-start and event_scheduler=off entries you added to the my.cnf file of the slave in step 2. (You can also leave the skip-slave-start entry in, but then you will always need to use the START SLAVE statement to start replication whenever you restart the slave server.)

8. Restart the slave server. Replication starts.