17.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 like --include, 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

    Property Value
    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 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

    Property Value
    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 legacy --include 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 have the following extensions:

    • .ARM: Archive storage engine metadata

    • .ARZ: Archive storage engine data

    • .CSM: CSV storage engine data

    • .CSV: CSV storage engine data

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

  • --only-innodb

    For back up only. Back up only InnoDB data and log files. 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.

    Default: backups include files from all storage engines.

  • --use-tts[={with-minimum-locking|with-full-locking}]

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

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

      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.

Legacy Partial Backup Options

Important

Information in this subsection is only for using the legacy option of --include, 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.

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


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.