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
, andbackup
. 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. Forapply-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
, andbackup
. It is ignored when you use any of the options--incremental-with-redo-log-only
,apply-incremental-backup
,copy-back
, orbackup-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
, andbackup
. It is ignored when you use any of the single-file backup optionslist-image
orvalidate
.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 asmb
orkb
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 abort
warn
abort_and_remove
Specifies the behavior when a backup process encounters a disk-full condition. This option is only for backup operations (
backup
,backup-and-apply-log
, andbackup-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
andbackup-to-image
operations, but not to incremental backups. The option is ignored by thebackup-and-apply-log
operation.Note that backups created with the
--skip-unused-pages
option cannot be restored usingcopy-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 theslave.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.NoteIf 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 ofyears
,months
,hours
, andminutes
. Some examples for option strings in this format include:5years
,2days
,13months
,23hours
, and35minutes
.A date-time format in any of the following forms:
YYMMDD
,YYYYMMDD
,YYMMDDHHMMSS
,YYYYMMDDHHMMSSYY-MM-DD
,YYYY-MM-DD
,YY-MM-DD
, orHH.MM.SSYYYYMMDDTHHMMSS
, as designated by the ISO 8601 standard.
When both the
optimistic-time
and theoptimistic-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 overoptimistic-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 theoptimistic-busy-tables
options are used and they come into conflict on determining which tables are to be “optimistic”,optimistic-busy-tables
takes precedence overoptimistic-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.