MySQL Enterprise Backup User's Guide (Version 3.12.5)
These options are for backing up database servers that play specific roles in replication, or contain certain kinds of data that require special care in backing up.
When backing up a replica server, this option captures
information needed to set up an identical replica server. It
creates a file meta/ibbackup_slave_info
inside the backup directory, containing a CHANGE
MASTER
statement with the binary log position and
name of the binary log file of the source server. This
information is also printed in the
mysqlbackup output. To set up a new
replica for this source, restore the backup data on another
server, start a replica server on the backup data, and issue
a CHANGE MASTER
command with the binary
log position saved in the
ibbackup_slave_info
file. See
Section 6.1, “Setting Up a New Replica” for
instructions.
Only use this option when backing up a replica server. Its behavior is undefined when used on a source or non-replication server.
This option is not compatible with the
--no-locking
option; using
both options together will make
mysqlbackup throw an error.
This option is not compatible with the
--only-innodb
or
--only-innodb-with-frm
options.
--safe-slave-backup-timeout
=
SECONDS
(For release 3.12.3 and later) For a statement-based
replication (SBR) or a mixed-based replication setup, the
option specifies the time (in seconds)
mysqlbackup will wait for
Slave_open_temp_tables
to become “0” (which is true when no temporary
tables are open) to complete the backup for a replica server
by asserting a global read lock and copies all the
non-InnoDB tables. If the duration of the wait exceeds that
specified with the option, mysqlbackup
times out and throws an error. The wait is for preventing
mysqlbackup from finishing a replica
backup when there are temporary tables still open. See
descriptions in Temporary tables on statement-based replication (SBR) replica for
details on how mysqlbackup deals with
temporary tables on a replica server.
In addition, mysqlbackup also runs an
initial check at the beginning of a replica backup to see if
Slave_open_temp_tables=0
becomes true within the duration set by
--safe-slave-backup-timeout
. If it does
not, mysqlbackup takes it as an early
sign that before the backup is completed, some temporary
tables are likely to remain open after the timeout limit is
exceeded; mysqlbackup then throws an
error, instead of continuing with the backup. When that
happens, you can either restart the backup with a higher
value for --safe-slave-backup-timeout
, or
retry at a time when fewer temporary tables are being used.
Default: 300
Proper setting of this value depends on the use case, and it can vary a lot according to the situation. Setting the value for this option either too high or too low will affect adversely the performance of the backup operation:
Too high: If you need to wait for a long time for there to be no more temporary tables, the chance is that the change rate for your database is quite high, which means the amount of redo log data to be included in the backup will be large and the restore time for the backup will be long. In such a case, it would have been better to have let mysqlbackup timeout and then restart the backup operation, so the tables are copied in their final states. It is therefore not helpful to set a high timeout value for the option. As a very general rule of thumb: even for busy databases that use many contemporary tables, do not set the value to more than an a few hours.
Too low: Setting the wait time value too low would make the backup process time out very easily and when that happens, the process has to be restarted. With a repeating cycle of restarts, the backup might then take a long time to complete, and resources used on the failed backups will be wasted. As a very general rule of thumb, do not set the timeout to below the default value of 300s.
For a row-based replication (RBR) setup, temporary tables
are not replicated onto the replica. Users who are certain
that SBR is not occurring for the replica can set
--safe-slave-backup-timeout=0
,
with which mysqlbackup will not check for
any open temporary tables before finishing the backup.
This option pauses the mysqlbackup
command when the backup procedure is close to ending. It
creates a file called
ibbackup_suspended
in the backup log
group home directory and waits until you delete that file
before proceeding. This option is useful to customize
locking behavior and backup of non-InnoDB files through
custom scripting.
All tables are locked before suspending, putting the
database into a read-only state, unless you turn off locking
with the --no-locking
or
--no-connection
option. The
--only-innodb
and
--only-innodb-with-frm
options
also prevent the locking step. Because locking all tables
could be problematic on a busy server, you might use a
combination of --only-innodb
and
--suspend-at-end
to back up only certain
InnoDB tables.
--exec-when-locked
="utility
arg1
arg2
..."
Command-Line Format | --exec-when-locked="utility arg1 arg2 ..." |
---|---|
Type | String |
You can use this option to run a script that backs up any
information that is not included as part of the usual
backup. For example, with
--exec-when-locked
,
you can use mysqldump to back up tables
from the MEMORY storage engine, which are not on disk.
Set any variable you want to use within your script before
you run mysqlbackup. In the following
example, the BACKUP_DIR
environment
variable is set to point to the current backup directory
(quotes are used for the argument of
--exec-when-locked
,
to prevent premature expansion of the variable
BACKUP_DIR
):
On Unix or Linux systems:
export BACKUP_DIR=path_to_backupdir
mysqlbackup --exec-when-locked="mysqldump mydb t1 > $BACKUP_DIR/t1.sql"other_options
mysqlbackup_command
Or on Windows systems:
set BACKUP_DIR=path_to_backupdir
mysqlbackup --exec-when-locked="mysqldump mydb t1 > %BACKUP_DIR%/t1.sql"other_options
mysqlbackup_command
If the utility cannot be executed or returns a non-zero exit
status, the whole backup process is cancelled. If you also
use the
--suspend-at-end
option, the utility specified by
--exec-when-locked
is executed after the suspension is lifted.