MySQL Enterprise Backup User's Guide (Version 4.1.4)
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 examples on the
following options, see Section 4.3.5, “Making a Partial Backup”. See also
Section 5.1.4, “Restoring Backups Created with the --use-tts
Option”, on selective
restore of tables from a backup.
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.
mysqlbackup throws an error when the
option is used without a regular expression being supplied
with it.
While mysqlbackup understands the MySQL
convention of quoting the database or the table name (or
both) by backticks (see
Schema Object Names), there is no
need to include the backticks in the regular expression for
--include-tables
.
For a backup operation, mysqlbackup
throws an error when no table matches the regular expression
specified with --include-tables
.
While the option can be used for different kinds of backups,
selective restore is only supported for backups created
using
transportable
tablespace (TTS) (that is, backups created with the
--use-tts
option).
The option can also be used with the
backup-dir-to-image
and
image-to-backup-dir
commands to select tables when creating or unpacking a
backup image.
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.
mysqlbackup throws an error when the
option is used without a regular expression being supplied
with it.
While mysqlbackup understands the MySQL
convention of quoting the database or the table name (or
both) by backticks (see
Schema Object Names), there is no
need to include the backticks in the regular expression for
--exclude-tables
.
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 can also be used with the
backup-dir-to-image
and
image-to-backup-dir
commands to select tables when creating or unpacking a
backup image.
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
Table 1.1, “Types of Files in a Backup” 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, besides the
ibdata*
files, have the following
extensions:
.ARM
: ARCHIVE table metadata
.ARZ
: ARCHIVE table data
.CSM
: CSV table metadata
.CSV
: CSV table 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
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 for
selecting 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:
Backing up and restoring of partitioned tables are
only supported for MySQL 5.7.4 and later. Also,
individual partitions cannot be selectively backed up
or restored. Tables selected by the
--include-tables
and
--exclude-tables
options
are always backed up or restored in full.
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
For partial backups, encrypted InnoDB tables are never included (a warning is issued in the log file whenever an encrypted InnoDB table that matches the table selection criteria has been skipped over).
Cannot be used for incremental backups
Does not include the binary log or the relay log in the backup
See also Appendix B, 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: 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 4.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 Section 5.1.4, “Restoring Backups Created with the --use-tts
Option” for details.
--rename
=“old_table_name
to new_table_name
”
Rename a single table when it is selected by the
--include-tables
or --exclude-tables
option (or
both together) to be restored to a database from a backup
created using the
--use-tts
option.
The table named old_table_name
is
renamed to new_table_name
. Note
that when using the option:
The --include-tables
or
--exclude-tables
option (or both together) must be used in the restore
command for the --rename
option to work, unless there is only one table in the
backup. Also, the
--include-tables
or --exclude-tables
option
(or both together) should specify one and only one
table for restore when
--rename
is used, or the
restore will fail.
old_table_name
and
new_table_name
can be fully
qualified (containing the database names, in the
format of
db_name
.tb_name
)
or not. Regular expressions are not accepted for
old_table_name
and
new_table_name
.
The restore fails if
old_table_name
does not
match with the table specified using the
--include-tables
or
--exclude-tables
option
(or both together), or if
new_table_name
already
exists in the target database.
The requirements listed in
Section 5.1.4, “Restoring Backups Created with the --use-tts
Option” apply.
See Section 5.1.4, “Restoring Backups Created with the --use-tts
Option”, for more information
on selective restores, and for an example of table renaming.
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 examples for these legacy options, see Making a Partial Backup with the Legacy Options.
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.
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.
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 white spaces surrounding the database or table
names, as the white spaces 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 mysqlbackup
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 5.1, “Performing a Restore Operation” for details.