MySQL Enterprise Backup User's Guide (Version 3.11.1)
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.
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.
Edit the my.cnf
file of the new slave and
put skip-slave-start
and
event_scheduler=off
under the
[mysqld]
section.
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.
Look for the file
where datadir
/meta/backup_variables.txt
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:
datadir
binlog_position=hundin-bin.000006:128760128
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;
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';
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.)
Restart the slave server. Replication starts.