MySQL Enterprise Backup User's Guide (Version 3.11.1)
Since MySQL Enterprise Backup 3.10, the two options
--include-tables
and
--exclude-tables
have been
introduced. These were intended for replacing the older
options of --include
,
--databases
,
--databases-list-file
, and
--only-innodb-with-frm
,
which are incompatible with the new options and will be
deprecated in future releases. For references purpose, we have
included information on the older options at the end of this
section in
Legacy Partial
Backup Options.
To select specific data to be backed up or restored, use the partial backup and restore options described in this section.
For an overview of partial backup and usage information on the following options for partial backup, see Section 3.3.4, “Making a Partial Backup”.
--include-tables
=REGEXP
Command-Line Format | --include-tables=REGEXP |
---|---|
Type | String |
Include for backup or restoration only those tables (both
Innodb and non-Innodb) whose fully qualified names (in the
form of
)
match the regular expression
db_name
.table_name
REGEXP
. The regular expression
syntax used is the extended form specified in the POSIX
1003.2 standard. For example,
--include-tables=^mydb\.t[12]$
matches
the tables t1
and t2
in the database mydb
. On Unix-like
systems, quote the regular expression appropriately to
prevent interpretation of shell meta-characters. Some
limitations apply when using the option to select database
names or file names that contain special characters (spaces,
dashes, periods, etc.); see
this
description in Appendix A, MySQL Enterprise Backup Limitations for details.
mysqlbackup throws an error when the
option is used without a regular expression being supplied
with it.
While the option can be used for different kinds of backups,
selective restore is only supported for backups created
using
transportable
tablespaces (TTS) (that is, backups created with the
--use-tts
option).
The option cannot be used together with the legacy
--include
,
--databases
,
--databases-list-file
, or
--only-innodb-with-frm
option.
When used together with the
--exclude-tables
option,
--include-tables
is applied
first, meaning mysqlbackup first selects
all tables specified by
--include-tables
and then
excludes from the set those tables specified by
--exclude-tables
.
Command-Line Format | --exclude-tables=REGEXP |
---|---|
Type | String |
Exclude for backup or restoration all tables (both Innodb
and non-Innodb) whose fully qualified names (in the form of
)
match the regular expression
db_name
.table_name
REGEXP
. The regular expression
syntax is the extended form specified in the POSIX 1003.2
standard. For example,
--exclude-tables=^mydb\.t[12]$
matches
the tables t1
and t2
in the database mydb
. On Unix-like
systems, quote the regular expression appropriately to
prevent interpretation of shell meta-characters. Some
limitations apply when using the option to select database
names or file names that contain special characters (spaces,
dashes, periods, etc.); see
this
description in Appendix A, MySQL Enterprise Backup Limitations for details.
mysqlbackup throws an error when the
option is used without a regular expression being supplied
with it.
While the option can be used for different kinds of backups,
selective restore is only supported for backups created
using
transportable
tablespaces (TTS) (that is, backups created with the
--use-tts
option).
The option cannot be used together with the
--include
,
--databases
,
--databases-list-file
, or
--only-innodb-with-frm
option.
When used together with the
--include-tables
option,
--include-tables
is applied
first, meaning mysqlbackup first select
all tables specified by
--include-tables
,
and then exclude from the set those tables specified by
--exclude-tables
.
For back up only. By default, all files in the database
subdirectories under the data directory of the server are
included in the backup (see
Section 1.4, “Files that Are Backed Up” for details). If the
--only-known-file-types
option is
specified, mysqlbackup only backs up
those types of files that are data files for MySQL or its
built-in storage engines, which have the following
extensions:
.ARM
: Archive storage engine
metadata
.ARZ
: Archive storage engine data
.CSM
: CSV storage engine data
.CSV
: CSV storage engine data
.frm
: table definitions
.ibd
: InnoDB tablespace created
using the file-per-table mode
.MRG
: Merge storage engine
references to other tables
.MYD
: MyISAM data
.MYI
: MyISAM indexes
.opt
: database configuration
information
.par
: partition definitions
.TRG
: trigger parameters
.TRN
: trigger namespace information
--only-innodb
For back up only. When this option is used, only InnoDB data and log files are included in the backup, and all files created by other storage engines are excluded. Typically used when no connection to mysqld is allowed or when there is no need to copy MyISAM files.
The option is not compatible with the
--slave-info
option.
--use-tts
[={with-minimum-locking|with-full-locking
}]
Command-Line Format | --use-tts[={with-minimum-locking|with-full-locking}] |
---|---|
Type | Enumeration |
Default Value | with-minimum-locking |
Valid Values |
|
Enable selective backup of InnoDB tables using
transportable
tablespaces (TTS). This is to be used in conjunction
with the --include-tables
and
--exclude-tables
options to
select the InnoDB tables to be backed up by regular
expressions. Using
TTS for
backups offers the following advantages:
Backups can be restored to a different server
The system tablespace is not backed up, saving disk space and I/O resources
Data consistency of the tables is managed by MySQL Enterprise Backup
However, the option has the following limitations:
Supports only MySQL version 5.6 and after (as earlier versions of MySQL do not support TTS)
Can only backup tables that are stored in their own individual tablespaces (i.e., tables created with the innodb_file_per_table option enabled)
Non-InnoDB tables are not backed up
Cannot back up partitioned tables
Cannot be used for incremental backups
Does not include the binary log or the relay log in the backup
See also Section A.1, “Limitations of MySQL Enterprise Backup” for some more minor limitations.
There are two possible values for the option:
with-minimum-locking
: Hot copies of
the selected tables are backed up, and the tables are
then locked in read-only mode while the
redo log (with
only the portion containing the relevant changes made
after the hot backup) is being included in the backup.
Any tables created during the locking phase are
ignored.
with-full-locking
: The selected
tables are locked in read-only mode while they are
being backed up. The
redo log is not
included in the backup. Any tables created during the
locking phase are ignored.
Due to a known issue, when creating a backup using TTS for a server containing tables with a mix of the Antelope and Barracuda file formats, do NOT apply full locking on the tables.
Default: back up with minimum locking
To use the --use-tts
option,
extra privileges are required of the user through which
mysqlbackup connects to the server; see
Section 3.1.2, “Grant MySQL Privileges to Backup Administrator” for details.
There are some special requirements for restoring backups
created with the --use-tts
option; see Restoring Backups Created with the --use-tts Option
for details.
Information in this subsection is only for using the legacy
options of --include
,
--databases
,
--databases-list-file
,
and --only-innodb-with-frm
, which
will be deprecated in the upcoming issues. For creating
partial backups, it is strongly recommended that the new
options of --include-tables
and
--exclude-tables
be used instead.
Note that you cannot combine the legacy and the new
partial-backup options in a single command.
Besides the legacy options, some other options are also discussed below, but the information is only for using the options together with the legacy partial-backup options.
For an overview of partial backups and usage information about these legacy options, see Making a Partial Backup with the Legacy Options.
--include
=REGEXP
This option is for filtering InnoDB tables for backup. The
InnoDB tables' fully qualified names are checked against the
regular expression specified by the option. If the REGEXP
matches
,
the table is included. The regular expression syntax used is
the extended form specified in the POSIX 1003.2 standard.
For example, db_name
.table_name
--include=mydb\.t[12]
matches the tables t1
and
t2
in the database
mydb
. mysqlbackup
throws an error when the option is used without a regular
expression being supplied with it.
This option only applies to InnoDB tables created with the
MySQL option
innodb_file_per_table
enabled
(which is the default setting for MySQL 5.6 and after), in
which case the tables are in separate files that can be
included or excluded from the backup. All tables in the
InnoDB system tablespace are always backed up.
When no InnoDB table names match the specified regular expression, an error is thrown with a message indicating there are no matches.
Default: Backs up all InnoDB tables.
This option does not filter non-InnoDB tables, for which
options like --databases
and
--databases-list-file
can be
used.
This option does not filter the .frm
files associated with InnoDB tables, meaning that
regardless of the option’s value, all the
.frm
files for all InnoDB tables are
always backed up unless they are excluded by other
options. Those .frm
files for InnoDB
tables that are not backed up should be deleted before the
database backup is restored. See
Making a Partial Backup with the Legacy Options for details.
--databases
=LIST
Specifies the list of non-InnoDB tables to back up. The argument specifies a space-separated list of database or table names of the following form:
"db_name
[.table_name
]db_name1
[.table_name1
] ...".
If the specified values do not match any database or table, then no non-InnoDB data files are backed up. See Making a Partial Backup with the Legacy Options for details.
By default, all non-InnoDB tables from all databases are backed up.
The option has no filtering effects on the InnoDB data
files (.ibd
files) for the databases
or tables it specifies. To filter InnoDB data files, use
the --include
option instead.
--databases-list-file
=PATH
Specifies the pathname of a file that lists the non-InnoDB
tables to be backed up. The file contains entries for
databases or fully qualified table names separated by
newline or space. The format of the entries is the same as
for the --databases
option:
db_name
[.table_name
]db_name1
[.table_name1
] ...
Remove any whitespaces surrounding the database or table
names, as the whitespaces are not removed automatically.
Begin a line with the #
character to
include a comment. No regular expressions are allowed.
If the specified entries do not match any database or table, then no non-InnoDB data files are backed up.
The option has no filtering effects on the InnoDB data
files (.ibd
files) for the databases
or tables it specifies. To filter InnoDB data files, use
the --include
option instead.
--only-innodb-with-frm
[={all|related}]
Back up only InnoDB data, log files, and the
.frm
files associated with the InnoDB
tables.
--only-innodb-with-frm=all
includes
the .frm
files for all InnoDB
tables in the backup.
--only-innodb-with-frm=related
, in
combination with the
--include
option, copies
only the .frm
files for the
tables that are included in the partial backup.
--only-innodb-with-frm
with no
argument is the same as
--only-innodb-with-frm=related
.
For incremental backups, even only changed
.ibd
files are backed up,
.frm
files associated with
all specified InnoDB tables are
included.
This option saves you having to script the backup step for
InnoDB .frm
files, which you would
normally do while the server is put into a read-only state
by a FLUSH TABLES WITH READ LOCK
statement. The .frm
files are copied
without putting the server into a read-only state, so that
the backup operation is a true
hot backup and does
not interrupt database processing. You must ensure that no
ALTER TABLE
or other DDL
statements change .frm
files for InnoDB
tables while the backup is in progress. If the
mysqlbackup command detects changes to
any relevant .frm
files during the
backup operation, it halts with an error. If it is not
practical to forbid DDL on InnoDB tables during the backup
operation, use the --only-innodb
option
instead and use the traditional method of copying the
.frm
files while the server is locked.
All files created by other storage engines are excluded. Typically used when no connection to mysqld is allowed or when there is no need to copy MyISAM files, for example, when you are sure there are no DDL changes during the backup. See Making a Partial Backup with the Legacy Options for instructions and examples.
The option is not compatible with the
--slave-info
option.
Default: backups include files from all storage engines.
--use-tts
[={with-minimum-locking|with-full-locking
}]
Enable selective backup of InnoDB tables using
transportable
tablespaces (TTS). This is to be used in conjunction
with the --include
option, which selects the InnoDB tables to be backed up by a
regular expression. Using
TTS for
backups offers the following advantages:
Backups can be restored to a different server
The system tablespace is not backed up, saving disk space and I/O resources
Data consistency of the tables is managed by MySQL Enterprise Backup
See important discussions
here on the
limitations with using the
--use-tts
option.
There are two possible values for the option:
with-minimum-locking
: Hot copies of
the selected tables are backed up, and the tables are
then locked in read-only mode while the
redo
log (with only the portion containing the
relevant changes made after the hot backup) is being
included in the backup. Any tables created during the
locking phase are ignored.
with-full-locking
: The selected
tables are locked in read-only mode while they are
being backed up. The
redo log is not
included in the backup. Any tables created during the
locking phase are ignored.
Default: back up with minimum locking
There are some special requirements for restoring backups
created with the --use-tts
option; see the
explanations in
Section 4.2, “Performing a Restore Operation” for details.