MySQL Enterprise Backup User's Guide (Version 3.12.5)
These options limit the resources used by the backup process, in order to minimize backup overhead for busy or huge databases, or specify behaviors of the process when encountering resource issues.
--number-of-buffers=
num_buffers
Command-Line Format | --number-of-buffers=NUMBER |
---|---|
Type | Numeric |
Default Value | 14 |
Minimum Value | 1 |
Specifies the number of buffers, each 16MB in size, to use during multithreaded options.
Use a high number for CPU-intensive processing such as backup, particularly when using compression. Use a low number for disk-intensive processing such as restoring a backup. This value should be at least as high as the number of read threads or write threads, depending on the type of operation.
Default: currently 14.
For compression or incremental backup operations, the buffer size is slightly more than 16MB to accommodate the headers.
One additional buffer is used for single-file incremental backup and single-file compressed backup.
Compressed backup, compressed single-file backup, and uncompress apply-log operations require one additional buffer for each process thread.
If you change the number of read, write, and processing threads, you can experiment with changing this value so that it is slightly larger than the total number of threads specified by those other options. See Section 7.1, “Optimizing Backup Performance” and Section 7.2, “Optimizing Restore Performance” for additional advice about recommended combinations of values for this and other performance-related options for various hardware configurations, such as RAID or non-RAID storage devices.
Command-Line Format | --read-threads=NUMBER |
---|---|
Type | Numeric |
Default Value | 1 |
Minimum Value | 1 |
Maximum Value | 15 |
Specifies the number of threads to use for reading data from disk.
Default: currently 1. This default applies to these kinds of
operations:
copy-back
,
extract
, and
backup
. If you
specify a value of 0, it is silently adjusted to 1. The
maximum is 15; if you supply a negative value, it is
silently adjusted to 15. For
apply-log
operations, the number
of read threads is always 1 regardless of this option
setting. See Section 7.1, “Optimizing Backup Performance” and
Section 7.2, “Optimizing Restore Performance” for
advice about recommended combinations of values for
--read-threads
,
--process-threads
, and
--write-threads
for various hardware
configurations, such as RAID or non-RAID storage devices.
Command-Line Format | --process-threads=NUMBER |
---|---|
Type | Numeric |
Default Value | 6 |
Minimum Value | 1 |
Maximum Value | 15 |
Specifies the number of threads to use for processing data, such as compressing or uncompressing backup files.
Default: currently 6. This default applies to these kinds of
operations:
extract
, and
backup
. It is ignored when you
use any of the options
--incremental-with-redo-log-only
,
apply-incremental-backup
,
copy-back
, or
backup-dir-to-image
.
If you specify a value of 0, it is silently adjusted to 1.
The maximum is 15; if you supply a negative value, it is
silently adjusted to 15. For
apply-log
operations, the number of process threads is always 1
regardless of this option setting. See
Section 7.1, “Optimizing Backup Performance” and
Section 7.2, “Optimizing Restore Performance” for
advice about recommended combinations of values for
--read-threads
,
--process-threads
, and
--write-threads
for various hardware
configurations, such as RAID or non-RAID storage devices.
Command-Line Format | --write-threads=NUMBER |
---|---|
Type | Numeric |
Default Value | 1 |
Minimum Value | 1 |
Maximum Value | 15 |
Specifies the number of threads to use for writing data to disk.
Specifies the number of threads to use for writing data to
disk. This option applies to these kinds of operations:
copy-back
,
copy-back-and-apply-log
,
extract
,
backup-to-image
,
backup
, and
backup-and-apply-log
. Multiple
write threads are supported for any write target that is
seekable; --write-threads
is
forced to be 1 only when the write target is non-seekable
(e.g., when the backup is written to
stdout
, to tape, or to cloud storage).
The option is ignored when used with other single-file
backup operations like
list-image
or
validate
If you specify a value of 0, it is silently adjusted to 1.
The maximum is 15; if you supply a negative value, it is
silently adjusted to 15. For
apply-log
operations, the number of write threads is always 0
regardless of this option setting. See
Section 7.1, “Optimizing Backup Performance” and
Section 7.2, “Optimizing Restore Performance” for
advice about recommended combinations of values for
--read-threads
,
--process-threads
, and
--write-threads
for various hardware
configurations, such as RAID or non-RAID storage devices.
Default: 1.
Command-Line Format | --limit-memory=MB |
---|---|
Type | Numeric |
Default Value | 100 for apply-log (without uncompression), 300 for other operations |
Minimum Value | 0 |
Maximum Value | 999999 |
Unit | megabyte |
Specify maximum memory in megabytes that can be used by
mysqlbackup. Formerly applied only to
apply-log
operation, but in MySQL Enterprise Backup 3.8 and higher it applies to all
operations. Do not include any suffixes such as
mb
or kb
in the option
value.
Default: 100 for apply-log
not
used with
--uncompress
, 400
for all operations (in megabytes).
The memory limit specified by this option also caps the
number of 16MB buffers available for multithreaded
processing. For example, with a 400 MB limit, the maximum
number of buffers is 25 (except for a cloud backup, for
which extra memory is needed, and the maximum number of
buffers is 18). If additional buffers are required because
you increased the values for
--read-threads
,
--process-threads
,
--write-threads
, and/or
--number-of-buffers
, increase
the --limit-memory
value proportionally.
Command-Line Format | --sleep=MS |
---|---|
Type | Numeric |
Default Value | 0 |
Unit | millisecond |
Specify the number in milliseconds to sleep after copying a certain amount of data from InnoDB tables. Each block of data is 1024 InnoDB data pages, typically totalling 16MB. This is to limit the CPU and I/O overhead on the database server.
Default: 0 (no voluntary sleeps).
Disables locking during backup of non-InnoDB files, even if a connection is available. Can be used to copy non-InnoDB data with less disruption to normal database processing. There could be inconsistencies in non-InnoDB data if any changes are made while those files are being backed up.
Command-Line Format | --lock-wait-timeout |
---|---|
Introduced | 3.12.4 |
Type | Numeric |
Default Value | 60 |
Minimum Value | 1 |
Unit | second |
Specify the timeout in seconds for the FLUSH TABLES
WITH READ LOCK
statement, which
mysqlbackup issues during the final stage
of a backup to temporarily put the database into a read-only
state. If the timeout is exceeded, the statement is failed
and the lock on the tables is released, so that queries held
up by the lock can then be executed.
mysqlbackup then retries the statement
and continues with the backup. The timeout prevents the case
in which a long query running on the server prevents the
FLUSH TABLES WITH READ LOCK
statement
from finishing, holding up further queries and eventually
bringing down the server. Default is 60. Minimum value is 1.
Command-Line Format | --page-reread-time=MS |
---|---|
Type | Numeric |
Default Value | 100 |
Unit | millisecond |
Interval in milliseconds that mysqlbackup waits before re-reading a page that fails a checksum test. A busy server could be writing a page at the same moment that mysqlbackup is reading it. Can be a floating-point number, such as 0.05 meaning 50 microseconds. Best possible resolution is 1 microsecond, but it could be worse on some platforms. Default is 100 milliseconds (0.1 seconds).
--page-reread-count=
retry_limit
Command-Line Format | --page-reread-count=number |
---|---|
Type | Numeric |
Default Value | 500 |
Maximum number of re-read attempts, when a
page fails a
checksum test. A busy server could be writing a page at the
same moment that mysqlbackup is reading
it. If the same page fails this many checksum tests
consecutively, with a pause based on the
--page-reread-time
option between each attempt, the backup fails. Default is
500.
--on-disk-full={abort|abort_and_remove|warn}
Command-Line Format | --on-disk-full=option |
---|---|
Type | Enumeration |
Default Value | abort |
Valid Values |
|
Specifies the behavior when a backup process encounters a
disk-full condition. This option is only for backup
operations (backup
,
backup-and-apply-log
, and
backup-to-image
).
abort
: Abort backup, without
removing the backup directory. The disk remains full.
abort_and_remove
: Abort backup and
remove the backup directory.
warn
: Write a warning message every
30 seconds and retry backup until disk space becomes
available.
Default: abort
.
Skip unused pages in tablespaces when backing up InnoDB
tables. This option is applicable to the
backup
and
backup-to-image
operations, but not to
incremental
backups. The option is ignored by the
backup-and-apply-log
operation.
Note that backups created with the
--skip-unused-pages
option
cannot be restored using
copy-back-and-apply-log
.
Unused pages are free pages often caused by bulk delete of
data. By skipping the unused pages during backups, this
option can reduce the backup sizes and thus the required
disk space and I/O resources for the operations. However,
subsequent
apply-log
operations on the backups will take more time to complete,
as the unused pages are inserted back into the tables during
the operations.
Skip including the binary log files in the backup during a backup operation, or skip copying the binary log files onto a server during a restore operation.
Binary log files, together with the binary log index file, are included by default for all kinds of online backups (full, incremental, compressed, partial, single-file, etc.). See Table 1.1, “Files in a MySQL Enterprise Backup Output Directory”, for details. Use this option to skip backing up binary logs for the following situations:
If resource or performance issues arise.
If any binary log files are missing on the server you are backing up, in order to avoid mysqlbackup throwing an error for the missing files.
If you are making an incremental backup that is based on
a backup (full or incremental) created using the
--no-locking
option, as
binary log information will then be unavailable to
mysqlbackup in that situation.
The binary log files and the binary log index file, when included in a backup, are always copied into the restored server's data directory during a restore operation; if that is not the behavior you desire, use this option to skip the restoring of the binary log.
When working with a replica server, skip including the relay log files in the backup during a backup operation, or skip copying the relay log files onto a server during a restore operation.
Relay log files, together with the relay log index file and
the master.info
and the
slave.info
files, are included by
default for all kinds of online backups (full, incremental,
compressed, partial, single-file, etc.) of a replica server.
See Section 1.4, “Files that Are Backed Up”, for
details. Use this option to skip backing up relay logs if
resource, performance, or other issues arise.
If a user runs a FLUSH LOGS
statement while backup is in progress for a replica, the
backup process will fail. Use
the--skip-relaylog
option if you expect a
FLUSH
LOGS statement will be run during the backup and
it is not necessary to include the relay logs in the
backup.
The relay log files and the files backed up together with them, when included in a backup, are always copied into the restored server's data directory during a restore operation; if that is not the behavior you desire, use this option to skip the restoring of the relay log.
Skip the final rescan for InnoDB tables that are modified by DDL operations after the database has been read-locked near the end of a backup operation. This potentially shortens the duration for the lock and reduces the backup's impact on the server's normal operation, especially when many tables are being backed up.
This option can cause an incomplete or inconsistent backup
if, during the backup operation, DDL operations are
executed on any InnoDB tables whose
file-per-table
tablespaces are outside the MySQL data directory (i.e.,
any InnoDB
tables created using the DATA DIRECTORY
table option).
The option is ignored for backups using the
--incremental-with-redo-log-only
option and for non-backup operations.
Command-Line Format | --log-bin-index=FILENAME |
---|---|
Type | File name |
Default Value | data_dir/host_name-bin.index |
For MySQL 5.5 and earlier, as well as all offline backups: specify the absolute path (including file name and extension) of the index file on the MySQL server that lists all the used binary log files, if it is different from the default path given below, in order to include the binary log files in the backup.
Default:
.
data_dir
/host_name
-bin.index
Command-Line Format | --relay-log-index=FILENAME |
---|---|
Type | File name |
Default Value | data_dir/host_name-relay-bin.index |
For offline backups of replica servers only: specify the absolute path (including file name and extension) of the index file on the MySQL server that lists all the used relay log files, if it is different from the default path given below, in order to include the relay log files in the backup.
Default:
.
data_dir
/host_name
-relay-bin.index
Command-Line Format | --master-info-file=FILENAME |
---|---|
Type | File name |
Default Value | data_dir/master.info |
For offline backups of replica servers only: specify the absolute path (including file name and extension) of the information file in which a replica records information about its source, if it is different from the default path given below, in order to include the information file in the backup.
Default:
.
data_dir
/master.info
Command-Line Format | --relaylog-info-file=FILENAME |
---|---|
Type | File name |
Default Value | data_dir/relay-log.info |
For offline backups of replica servers only: specify the absolute path (including file name and extension) of the information file in which a replica records information about the relay logs, if it is different from the default path given below, in order to include the information file in the backup.
Default:
.
data_dir
/relay-log.info
Command-Line Format | --optimistic-time=DATE-TIME |
---|---|
Type | String |
Default Value | now |
Perform an optimistic backup with the value specified with the option as the “optimistic time”—a time after which the tables that have not been modified are taken as “inactive tables.” The “inactive tables”are believed to be unlikely to change during the backup process. The inactive tables are backed up in the optimistic phase of the backup, and all other tables are backed up in the normal phase. See Section 4.3.6, “Making an Optimistic Backup” for details on the concept, use cases, and command samples for an optimistic backup.
Accepted formats for specifying the option include:
now
: This includes all tables into
the optimistic phase of the backup process. It is the
default value for the option when no value is
specified.
{Number}{Unit}
: Indicates
the optimistic time as a time at a certain duration
into the past. {Unit}
can
be any one of years
,
months
, hours
,
and minutes
. Some examples for
option strings in this format include:
5years
,
2days
,13months
,
23hours
, and
35minutes
.
A date-time format in any of the following forms:
YYMMDD
,
YYYYMMDD
,
YYMMDDHHMMSS
,
YYYYMMDDHHMMSS
,
YY-MM-DD
,
YYYY-MM-DD
, YY-MM-DD
HH.MM.SS
, or
YYYYMMDDTHHMMSS
(where
T
is the character
T
).
When both the optimistic-time
and the
optimistic-busy-tables
options are used and
they come into conflict on determining which tables are to
be backed up in the optimistic phase,
optimistic-busy-tables
takes precedence
over optimistic-time
.
--optimistic-busy-tables
=REGEXP
Command-Line Format | --optimistic-busy-tables=REGEXP |
---|---|
Type | String |
Perform an optimistic backup, using the regular expression
specified with the option to select tables that will be
skipped in the first phase of an optimistic backup, because
they are likely to be modified during the backup process.
Tables whose fully qualified names (in the form of
database_name
.table_name
)
are matched by the regular expression are taken as
“busy tables”, which will be backed up in the
second or the “normal” phase of the backup.
Tables whose fully qualified names are NOT matched by the
regular expression are taken as “inactive
tables”, which will be backed up in the first or the
“optimistic” phase of the backup. See
Section 4.3.6, “Making an Optimistic Backup” for
details on the concept, use cases, and command samples for
an optimistic backup.
MySQL Enterprise Backup will throw an error if the option is used but no regular expression is supplied with it.
When both the optimistic-time
and the
optimistic-busy-tables
options are used and
they come into conflict on determining which tables are to
be “optimistic”,
optimistic-busy-tables
takes precedence
over optimistic-time
.
(For release 3.12.3 and later) Free the system buffer cache at the end of a backup operation by syncing all data from the buffer cache to the hard disk. Using the option might increase the backup time significantly for systems with slow storage devices and databases with many tables.
Default: Automatic syncing disabled.