MySQL Enterprise Backup User's Guide (Version 4.0.3)
MySQL Enterprise Backup allows you to set up a replication slave by backing up the master and restoring the backup on a new slave server, without having to stop the master.
For servers NOT using GTID:
Take a full backup of the master and then use, for example,
the copy-back-and-apply-log
command, to restore the backup and the log files to the right
directories on the new slave and prepare the data.
Do not use the --no-locking
option when backing up the server, or you will be unable to
get a proper binary log position in Step 4 below for
initializing the slave.
Edit the my.cnf
file of the new slave and
put skip-slave-start
and
event_scheduler=off
(if the master uses the
Event Scheduler) under
the [mysqld]
section.
Start the new slave mysqld. You see the following in the server's output:
… 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.
For servers using GTIDs (see Setting Up Replication Using GTIDs on how to enable servers to use GTIDs):
Take a full backup of the master and then use, for example,
the copy-back-and-apply-log
command, to restore the backup and the log files to the right
directories on a new GTID-enabled slave and prepare the data.
Edit the my.cnf
file of the new slave and
put skip-slave-start
and
event_scheduler=off
(if the master uses the
Event Scheduler) under
the [mysqld]
section.
Start the new slave server.
Connect to the slave server with the mysql client. Then, execute the following statement to reset the binary log:
mysql> RESET MASTER;
And execute the following statement to stop the binary logging:
mysql> SET sql_log_bin=0;
When a server using the GTID feature is backed up,
mysqlbackup produces a file named
backup_gtid_executed.sql
, which can be
found in the restored data directory of the new slave server.
The file contains a SQL statement that sets the
GTID_PURGED
configuration option on the
slave:
# On a new slave, issue the following command if GTIDs are enabled: SET @@GLOBAL.GTID_PURGED='f65db8e2-0e1a-11e5-a980-080027755380:1-3';
It also contains a commented-out CHANGE MASTER
TO
statement for initializing the slave:
# Use the following command if you want to use the GTID handshake protocol: # CHANGE MASTER TO MASTER_AUTO_POSITION = 1;
Uncomment the command and add any needed connection and
authentication parameters to it (for example,
MASTER_HOST
,
MASTER_USER
,
MASTER_PASSWORD
, and
MASTER_PORT
):
# Use the following command if you want to use the GTID handshake protocol: CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_USER='muser', MASTER_PASSWORD='mpass', MASTER_PORT=18675, MASTER_AUTO_POSITION = 1;
Execute the file with the mysql client
mysql> source /path-to-backup_gtid_executed.sql
/backup_gtid_executed.sql
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 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.
For more information on the GTIDs, see GTID feature.