16.8 Partial Backup and Restore Options

Note

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.

• --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 db_name.table_name) match the regular expression 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.

• --exclude-tables=REGEXP

  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 db_name.table_name) match the regular expression 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.

• --only-known-file-types

  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

• --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	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-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 MySQL Enterprise Backup 4.1.4 and earlier: 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.

    Note

    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 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:

  • 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 any table that is to be restored, or when new_table_name already exists in the target database.

  • For MySQL Enterprise Backup 4.1.4 and earlier: 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.

  • For MySQL Enterprise Backup 4.1.4 and earlier: 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),

  • 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.

Legacy Partial Backup Options

Important

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.

• --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 db_name.table_name, the table is included. The regular expression syntax used is the extended form specified in the POSIX 1003.2 standard. For example, --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.

  Note

  This option does not filter non-InnoDB tables, for which options like --databases and --databases-list-file can be used.

  Important

  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.

  Note

  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 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.

  Note

  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.

  Note

  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.