To facilitate the creation of partial backups, MySQL Enterprise Backup has introduced two new options for partial backup since version 3.10:--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 inMaking a Partial Backup with Legacy Options.
By default, all the files under the database subdirectories 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-tablesor--exclude-tablesoption.Each table is checked against the regular expression specified with the
--include-tablesor--exclude-tablesoption. If the regular expression matches the fully qualified name of the table (in the form ofdb_name.table_name), the table is included or excluded for the backup. The regular expression syntax used is the extended form specified in thePOSIX 1003.2 standard. The options have been implemented with the RE2 regular expression library.Including some or all InnoDB tables, but not other table types. This uses the
--only-innodboption.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-typesoption.Achieving a multiple of selection effects by using a combination of the above mentioned options.
Backing up a selection of InnoDB tables usingtransportable tablespaces (TTS). This uses the
--use-ttsand the--include-tablesor--exclude-tables(or both) options.
For syntax details on all the options involved, seeSection 16.8, “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 the 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_TEMP_BACKUP_DIR --backup-image=$MEB_BACKUPS_DIR/my.mbi \ --include-tables="\.emp" \ backup-to-imageTaking 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_TEMP_BACKUP_DIR --backup-image=$MEB_BACKUPS_DIR/my.mbi \ --exclude-tables="^(mysql|performance_schema)\." \ backup-to-imageTaking 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_TEMP_BACKUP_DIR --backup-image=$MEB_BACKUPS_DIR/my.mbi \ --include-tables="^sales\." --exclude-tables="^sales\.hardware$" \ backup-to-imageTaking a backup of all tables in the“sales reps” database, but excludes the table with the name“euro-asia” (special characters like spaces or dashes are supported by the partial backup options since release 3.12.1):
mysqlbackup \ --host=localhost --user=mysqluser --protocol=TCP --port=3306 \ --backup-dir=$MEB_TEMP_BACKUP_DIR --backup-image=$MEB_BACKUPS_DIR/my.mbi \ --include-tables="^sales reps\." --exclude-tables="^sales reps\.euro-asia" \ backup-to-image Backing up all InnoDB tables, but not.frm files:
mysqlbackup --defaults-file=/home/dbadmin/my.cnf --only-innodb backup-to-imageYou can also makecompressed and other kinds of selective backups by using the appropriate command options.
Making a Partial Backup with the Legacy Options
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-frmoptions.Including certain non-InnoDB tables from selected databases but not others. This operation involves the
--databasesand--databases-list-fileoptions.
For syntax details on all these options, seeLegacy 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
--includeoption 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
--includeoption.
This operation requires the tables being left out to be stored in separatetable_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 withinnodb_file_per_table turned off are stored as usual in the InnoDBsystem tablespace, and cannot be left out of the backup.
For each table with a per-table data file a string of the formdb_name.table_name is checked against the regular expression specified with the--include option. If the regular expression matches the complete stringdb_name.table_name, the table is included in the backup. The regular expression syntax used is the extended form specified in thePOSIX 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 the RE2 regular expression library.
The backup directory produced contains a backup log file and copies of InnoDB data files.
IMPORTANT: Althoughmysqlbackup 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 usemysqlbackup 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 4.23 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 intest database whose name starts withib. The contents of the database directory fortest 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, andibtest11d) 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/testalex1.frm alex2.ibd blobt3.frm ibstest0.ibd ibtest11a.frm ibtest11d.frmalex1.ibd alex3.frm blobt3.ibd ibtest09.frm ibtest11b.frmalex2.frm alex3.ibd ibstest0.frm ibtest09.ibd ibtest11c.frm We run themysqlbackup with the--include option:
# Back up some InnoDB tables but not any .frm files.$ mysqlbackup --defaults-file=/home/dbadmin/my.cnf --include="^test\.ib.*" --only-innodb backup# Contents in the backup directory's subdirectory for the test database:$ ls /sqldata-backup/testibstest0.ibd ibtest09.ibd# Back up some InnoDB tables and the .frm files for the backed-up tables only.$ mysqlbackup --defaults-file=/home/dbadmin/my.cnf --include="^test\.ib.*" \ --only-innodb-with-frm=related backup# Contents in the backup directory's subdirectory for the test database:$ ls /sqldata-backup/testibstest0.frm ibtest09.frmibstest0.ibd ibtest09.ibd The backup directory's subdirectory for thetest database contains only backups ofibstest0 andibtest09 tables, because other InnoDB tables do not match the include pattern^test\.ib.*. The related.frm files are included in the second case. Notice that, however, the tablesibtest11a,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.
Example 4.24 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 withalex orblob. The contents of the database directory fortest database is shown below.
$ ls /sqldata/mts/testalex1.frm alex2.ibd blobt3.frm ibstest0.ibd ibtest11a.frm ibtest11d.frmalex1.ibd alex3.frm blobt3.ibd ibtest09.frm ibtest11b.frmalex2.frm alex3.ibd ibstest0.frm ibtest09.ibd ibtest11c.frm We runmysqlbackup with the--compress and--include options:
$ mysqlbackup --defaults-file=/home/dbadmin/my.cnf --compress \ --include=".*\.(alex|blob).*" --only-innodb backup The backup directory for the databasetest is shown below. The.ibz files are compressed per-table data files.
$ ls /sqldata-backup/testalex1.ibz alex2.ibz alex3.ibz blobt3.ibz The--databases and--databases-list-file options ofmysqlbackup 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.