Documentation Home
MySQL Enterprise Backup 3.10 User's Guide
Related Documentation Download this Manual
PDF (US Ltr) - 1.0Mb
PDF (A4) - 1.0Mb


5.1.9 Partial Backup Options

Note

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. 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, use the partial backup options described in this section. For an overview of partial backups and usage information on these options, see Section 3.3.4, “Making a Partial Backup”.

  • --include-tables=REGEXP

    Introduced3.10.0
    Command-Line Format--include-tables=REGEXP
    Permitted ValuesTypestring

    Include into the backup 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.

    Some limitations apply when using the option to select database names or file names that contain special characters (spaces, dashes, periods, etc.); see this description in Appendix A, MySQL Enterprise Backup Limitations for details.

    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

    Introduced3.10.0
    Command-Line Format--exclude-tables=REGEXP
    Permitted ValuesTypestring

    Exclude from the backup 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.

    Some limitations apply when using the option to select database names or file names that contain special characters (spaces, dashes, periods, etc.); see this description in Appendix A, MySQL Enterprise Backup Limitations for details.

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

  • --only-known-file-types

    By default, all files in the data directory are included in the backup (see Section 1.4, “Files that Are Backed Up” 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

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

    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.

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

    Command-Line Format--use-tts[={with-minimum-locking|with-full-locking}]
    Permitted ValuesTypeenumeration
    Defaultwith-minimum-locking
    Valid Valueswith-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 to select 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:

    • Supports only MySQL version 5.6 and after (as earlier versions of MySQL do not support TTS)

    • Can only backup tables that are stored in their own individual tablespaces (i.e., tables created with the innodb_file_per_table option enabled)

    • Cannot back up partitioned tables

    • Cannot restore tables selectively from the backup

    • Cannot be used for incremental backups

    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

    To use the --use-tts option, extra privileges are required of the user through which mysqlbackup connects to the server; see Section 3.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 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 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 information about 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.

    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 whitespaces surrounding the database or table names, as the whitespaces 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 the mysqlbackup command 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

    However, the option has the following limitations:

    • Supports only MySQL version 5.6 and after (as earlier versions of MySQL do not support TTS)

    • Can only backup tables that are stored in their own individual tablespaces (i.e., tables created with the innodb_file_per_table option enabled)

    • Cannot back up partitioned tables

    • Cannot restore tables selectively from the backup

    • Cannot be used for incremental backups

    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 4.2, “Performing a Restore Operation” for details.


User Comments
Sign Up Login You must be logged in to post a comment.