To backup a slave database, add the
--slave-info
option to your backup
command.
To restore the backup on a slave server, follow the same steps outlined in Section 6.1, “Setting Up a New Replication Slave”.
Temporary tables on statement-based replication (SBR) slave. MySQL Enterprise Backup does not include temporary tables inside a backup. As a result, for a slave server in a statement-based replication (SBR) or a mixed-based replication setup (see Replication Formats for details), any temporary tables still open at the end of the backup process will be missing in the restored slave server, making the replication state of the slave inconsistent, and any subsequent replicated statements that refer to the temporary tables will fail. To avoid the issue, after the hot backup phase in which mysqlbackup copies all the InnoDB tables, it enters into a loop, in which the following happens:
mysqlbackup waits until all temporary tables have been closed by the SQL slave thread. mysqlbackup tells if that is the case by checking if the variable
Slave_open_temp_tables
has a zero value.After
Slave_open_temp_tables=0
is detected, mysqlbackup stops the SQL slave thread to prevent more changes to the tables on the slave.To avoid the unexpected consequence by a race condition, after the SQL slave thread has been stopped, mysqlbackup checks once more if
Slave_open_temp_tables=0
is still true:If it is true, mysqlbackup exits the loop and finishes the backup by asserting a global read lock and copies all the non-InnoDB tables.
If it is not true, new temporary tables have just been created and opened on the slave. mysqlbackup then restarts the SQL slave thread, so more updates can be made on the slave servers. mysqlbackup then goes back to step 1 of this loop
Besides the exit condition described in step (3) above (which
is, there really are no more open temporary tables and
mysqlbackup is ready to complete the backup),
mysqlbackup will time out after staying in
the above loop for too long to wait for all temporary tables to
be closed. The duration mysqlbackup waits
until it times out is specified by the
--safe-slave-backup-timeout
option.
In addition, mysqlbackup also runs an initial
check at the beginning of a slave backup to see if
Slave_open_temp_tables=0
becomes true within the duration set by
--safe-slave-backup-timeout
. See
description for
--safe-slave-backup-timeout
on details
about the check.
Notice that the above-described issue with temporary tables does
not exist for a row-based replication (RBR) setup, for which
temporary tables are not replicated onto the slave. User who are
certain that SBR is not occurring for the slave can set
--safe-slave-backup-timeout=0
,
which will prevent mysqlbackup from entering
the above-mentioned loop.