MySQL Enterprise Backup User's Guide (Version 4.0.3)
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 10.1, “Optimizing Backup Performance” and Section 10.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.
--read-threads=
num_threads
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 10.1, “Optimizing Backup Performance” and
Section 10.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.
--process-threads=
num_threads
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 10.1, “Optimizing Backup Performance” and
Section 10.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.
--write-threads=
num_threads
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.
Default: currently 1. This default applies to these kinds of
operations: copy-back
,
extract
, and
backup
. It is
ignored when you use any of the single-file backup options
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 10.1, “Optimizing Backup Performance” and
Section 10.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.
--limit-memory
=MB
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
, 300 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 300 MB limit, the maximum
number of buffers is 18. If additional buffers are required
because you increase the values for
--read-threads
,
--process-threads
,
--write-threads
, and/or
--number-of-buffers
, increase
the --limit-memory
value proportionally.
--sleep
=MS
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).
--no-locking
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.
--page-reread-time=
MS
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
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-binlog
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, “Types of Files in a Backup”, 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.
--skip-relaylog
When working with a slave 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 slave server.
See Table 1.1, “Types of Files in a Backup”, 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 slave, 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.
--log-bin-index
[=PATH
]
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
--relay-log-index
[=PATH
]
Command-Line Format | --relay-log-index=FILENAME |
---|---|
Type | File name |
Default Value | data_dir/host_name-relay-bin.index |
For offline backups of slave 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
--master-info-file
[=PATH
]
Command-Line Format | --master-info-file=FILENAME |
---|---|
Type | File name |
Default Value | data_dir/master.info |
For offline backups of slave servers only: specify the absolute path (including file name and extension) of the information file in which a slave records information about its master, 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
--relaylog-info-file
[=PATH
]
Command-Line Format | --relaylog-info-file=FILENAME |
---|---|
Type | File name |
Default Value | data_dir/relay-log.info |
For offline backups of slave servers only: specify the absolute path (including file name and extension) of the information file in which a slave 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
,
YYYYMMDDHHMMSSYY-MM-DD
,
YYYY-MM-DD
,
YY-MM-DD
, or
HH.MM.SSYYYYMMDDTHHMMSS
, as
designated by the ISO 8601 standard.
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 4.0.1 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.