MySQL Enterprise Backup User's Guide (Version 3.11.1)
To facilitate the creation of partial backups, MySQL Enterprise Backup 3.10
introduces two new options for partial backup:
--include-tables
and
--exclude-tables
. The new options
are 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
the upcoming releases. In the discussions below we assume the
new options are used for partial backups. For reference
purpose, we have included information on the older options at
the end of this section in
Making a Partial Backup with
Legacy Options.
By default, all the files in the data directory are included in the backup, so that the backup includes data from all MySQL storage engines, any third-party storage engines, and even any non-database files in that directory. This section explains options you can use to selectively back up or exclude data.
There are various ways to create different kinds of partial backup with MySQL Enterprise Backup:
Including or excluding specific tables by their names. This
uses the --include-tables
or
--exclude-tables
option.
Each table is checked against the regular expression
specified with the
--include-tables
or
--exclude-tables
option. If the regular expression matches the fully
qualified name of the table (in the form of
db_name.table_name)
, the table is
included or excluded for the backup. The regular expression
syntax used is the extended form specified in the
POSIX 1003.2 standard. The options have
been implemented with Henry Spencer's regular
expression library.
Including some or all InnoDB tables, but not other table
types. This uses the
--only-innodb
option.
Leaving out files that are present in the MySQL data
directory but not actually part of the MySQL instance. This
uses the --only-known-file-types
option.
Achieving a multiple of selection effects by using a combination of the above mentioned options.
Backing up a selection of InnoDB tables using
transportable
tablespaces (TTS). This uses the
--use-tts
and the
--include-tables
or
--exclude-tables
(or both) options.
For syntax details on all the options involved, see Section 5.1.9, “Partial Backup and Restore Options”.
Typically, a partial backup is more difficult to restore than a full backup, because the backup data might not include the necessary interrelated pieces to constitute a complete MySQL instance. In particular, InnoDB tables have internal IDs and other data values that can only be restored to the same instance, not a different MySQL server. Always fully test the recovery procedure for any partial backups to understand the relevant procedures and restrictions.
Because the InnoDB system tablespace holds metadata about InnoDB tables from all databases in an instance, restoring a partial backup on a server that includes other databases could cause the system to lose track of those InnoDB tables in other databases. Always restore partial backups on a fresh MySQL server instance without any other InnoDB tables that you want to preserve.
The following are some command samples for partial backups.
Including all tables with names starting with “emp” into the backup:
$ mysqlbackup \ --host=localhost --user=mysqluser --protocol=TCP --port=3306 \ --backup-dir=$MEB_BACKUPS_DIR/backupdir \ --include-tables='\.emp' \ backup
Taking a backup of all tables except tables from the “mysql” and “performance_schema” databases:
$ mysqlbackup \ --host=localhost --user=mysqluser --protocol=TCP --port=3306 \ --backup-dir=$MEB_BACKUPS_DIR/backupdir \ --exclude-tables='^(mysql|performance_schema)\.' \ backup
Taking a backup of all tables in the “sales” database, but excludes the table with the name “hardware”
$ mysqlbackup \ --host=localhost --user=mysqluser --protocol=TCP --port=3306 \ --backup-dir=$MEB_BACKUPS_DIR/backupdir \ --include-tables='^sales\.' --exclude-tables='^sales\.hardware$' \ backup
Backing up all InnoDB tables, but not .frm
files:
$ mysqlbackup --defaults-file=/home/pekka/.my.cnf --only-innodb backup
You can also make compressed, single-image, and other kinds of selective backups by adding the appropriate options.
Next Steps:
Make a note of the LSN value in the message at the end of
both full and incremental backups, for example,
mysqlbackup: Was able to parse the log up to lsn
. You specify
this value when performing incremental backups of changes
that occur after this full backup.
LSN_number
Apply the log to the uncompressed backup files, so that the full backup is ready to be restored at any time. You can move the backup data to a different server first, to avoid the CPU and I/O overhead of performing this operation on the database server.
After applying the log, periodically take incremental backups, which are much faster and smaller than a full backup like these ones.
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.
MySQL Enterprise Backup can make different kinds of partial backup using the legacy partial-backup options:
Including certain InnoDB tables but not others. This
operation involves the
--include
,
--only-innodb
, and
--only-innodb-with-frm
options.
Including certain non-InnoDB tables from selected databases
but not others. This operation involves the
--databases
and
--databases-list-file
options.
For syntax details on all these options, see Legacy Partial Backup Options.
Typically, a partial backup is more difficult to restore than a full backup, because the backup data might not include the necessary interrelated pieces to constitute a complete MySQL instance. In particular, InnoDB tables have internal IDs and other data values that can only be restored to the same instance, not a different MySQL server. Always fully test the recovery procedure for any partial backups to understand the relevant procedures and restrictions.
With its --include
option,
mysqlbackup can make a backup that includes
some InnoDB tables but not others:
A partial backup with the --include
option
always contains the InnoDB system tablespace and all the
tables inside it.
For the InnoDB tables stored outside the system tablespace,
the partial backup includes only those tables whose names
match the regular expression specified with the
--include
option.
This operation requires the tables being left out to be stored
in separate
files. To put an InnoDB table outside the system tablespace,
create it while the
table_name
.ibdinnodb_file_per_table
MySQL
configuration option is enabled. Each .ibd
file holds the data and indexes of one table only.
Those InnoDB tables created with
innodb_file_per_table
turned off are stored
as usual in the InnoDB
system tablespace,
and cannot be left out of the backup.
For each table with a per-table data file a string of the form
db_name.table_name
is checked against the
regular expression specified with the
--include
option. If the regular
expression matches the complete string
db_name.table_name
, the table is included in
the backup. The regular expression syntax used is the extended
form specified in the POSIX 1003.2 standard.
On Unix-like systems, quote the regular expression appropriately
to prevent interpretation of shell meta-characters. This feature
has been implemented with Henry Spencer's regular
expression library.
The backup directory produced contains a backup log file and copies of InnoDB data files.
IMPORTANT: Although the
mysqlbackup command supports taking partial
backups, be careful when restoring a database from a partial
backup. mysqlbackup copies also the
.frm
files of those tables that are not
included in the backup, except when you do partial backups
using, for example, the --databases
option. If you use mysqlbackup with the
--include
option, before restoring
the database, delete from the backup data the
.frm
files for any tables that are not
included in the backup.
IMPORTANT: Because the InnoDB system tablespace holds metadata about InnoDB tables from all databases in an instance, restoring a partial backup on a server that includes other databases could cause the system to lose track of those InnoDB tables in other databases. Always restore partial backups on a fresh MySQL server instance without any other InnoDB tables that you want to preserve.
The --only-innodb
and
--only-innodb-with-frm
options back
up InnoDB tables only, skipping those of other storage engines.
You might also use them together with the
--include
option to make selective
backup of InnoDB tables while excluding all other files created
by other storage engines.
Example 3.1 Making an Uncompressed Partial Backup of InnoDB Tables
In this example, we have configured MySQL so that some InnoDB
tables have their own tablespaces. We make a partial backup
including only those InnoDB tables in test
database whose name starts with ib
. The
contents of the database directory for test
database are shown below. The directory contains a MySQL
description file (.frm
file) for each of
the tables (alex1
,
alex2
, alex3
,
blobt3
, ibstest0
,
ibstest09
, ibtest11a,
ibtest11b
, ibtest11c
, and
ibtest11d
) in the database. Of these 10
tables six (alex1
,
alex2
, alex3
,
blobt3
, ibstest0
,
ibstest09
) are stored in per-table data
files (.ibd
files).
$ ls /sqldata/mts/test
alex1.frm alex2.ibd blobt3.frm ibstest0.ibd ibtest11a.frm ibtest11d.frm
alex1.ibd alex3.frm blobt3.ibd ibtest09.frm ibtest11b.frm
alex2.frm alex3.ibd ibstest0.frm ibtest09.ibd ibtest11c.frm
We run the mysqlbackup with the
--include
option:
# Back up some InnoDB tables but not any .frm files. $mysqlbackup --defaults-file=/home/pekka/.my.cnf --include='^test\.ib.*' --only-innodb backup
...many lines of output... mysqlbackup: Scanned log up to lsn 2666737471. mysqlbackup: Was able to parse the log up to lsn 2666737471. mysqlbackup: Maximum page number for a log record 0 101208 17:17:45 mysqlbackup: Full backup completed! # Back up some InnoDB tables and the .frm files for the backed-up tables only. $mysqlbackup --defaults-file=/home/pekka/.my.cnf --include='^test\.ib.*' \ --only-innodb-with-frm=related backup
...many lines of output... mysqlbackup: Scanned log up to lsn 2666737471. mysqlbackup: Was able to parse the log up to lsn 2666737471. mysqlbackup: Maximum page number for a log record 0 101208 17:17:45 mysqlbackup: Full backup completed!
The backup directory contains only backups of
ibstest
and ibtest09
tables. Other InnoDB tables did not match the include pattern
^test\.ib.*
. Notice, however, that the
tables ibtest11a
,
ibtest11b
, ibtest11c
,
ibtest11d
are in the backup even though
they are not visible in the directory shown below, because
they are stored in the system tablespace
(ibdata1
file) which is always included in
the backup.
# With the --only-innodb option: $ls /sqldata-backup/test
ibstest0.ibd ibtest09.ibd # With the --only-innodb-with-frm=related option: $ls /sqldata-backup/test
ibstest0.frm ibtest09.frm ibstest0.ibd ibtest09.ibd
Example 3.2 Making a Compressed Partial Backup
We have configured MySQL so that every InnoDB table has its
own tablespace. We make a partial backup including only those
InnoDB tables whose name starts with alex
or blob
. The contents of the database
directory for test
database is shown below.
$ ls /sqldata/mts/test
alex1.frm alex2.ibd blobt3.frm ibstest0.ibd ibtest11a.frm ibtest11d.frm
alex1.ibd alex3.frm blobt3.ibd ibtest09.frm ibtest11b.frm
alex2.frm alex3.ibd ibstest0.frm ibtest09.ibd ibtest11c.frm
We run mysqlbackup with the
--compress
and
--include
options:
$ mysqlbackup --defaults-file=/home/pekka/.my.cnf --compress \
--include='.*\.(alex|blob).*' --only-innodb backup
...many lines of output...
mysqlbackup: Scanned log up to lsn 2666737471.
mysqlbackup: Was able to parse the log up to lsn 2666737471.
mysqlbackup: Maximum page number for a log record 0
mysqlbackup: Compressed 147 MB of data files to 15 MB (compression 89%).
101208 17:18:04 mysqlbackup: Full backup completed!
The backup directory for the database test
is shown below. The .ibz
files are
compressed per-table data files.
$ ls /sqldata-backup/test
alex1.ibz alex2.ibz alex3.ibz blobt3.ibz
The --databases
and
--databases-list-file
options of the
mysqlbackup command let you back up
non-InnoDB tables only from selected databases, rather than
across the entire MySQL instance. (To filter InnoDB tables, use
the --include
option instead.) With
--databases
, you specify a space-separated list
of database names, with the entire list enclosed in double
quotation marks. With --databases-list-file
,
you specify the path of a file containing the list of database
names, one per line.
Some or all of the database names can be qualified with table names, to only back up selected non-InnoDB tables from those databases.
If you specify this option, make sure you include the same set of databases for every backup (especially incremental backups), so that you do not restore out-of-date versions of any databases.