--include-tables=REGEXP

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

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:

--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}]

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:

However, the option has the following limitations:

See also Appendix B, Limitations of MySQL Enterprise Backup for some more minor limitations.

There are two possible values for the option:

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:

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.

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

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

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

--only-innodb-with-frm[={all|related}]

Back up only InnoDB data, log files, and the .frm files associated with the InnoDB tables.

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:

See important discussions here on the limitations with using the --use-tts option.

There are two possible values for the option:

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.