3.3.4 Making a Partial Backup Backing Up Data from Different Storage Engines Backing Up Selected Databases

When the multiple tablespaces feature is enabled, you can make a partial backup of the InnoDB tables. The multiple tablespaces feature allows storing each InnoDB table in a separate file, table_name.ibd, holding the data and indexes of one table only. Those InnoDB tables for which the multiple tablespaces feature is not enabled, are stored as usual in the system tablespace defined by the innodb_data_file_path and innodb_data_home_dir parameters in the my.cnf file.

With its --include option, mysqlbackup can make a partial backup including selected per-table datafiles, those whose table names match the include pattern. A partial backup always contains the InnoDB system tablespace and all the tables inside it.

For each table with a per-table data file a string of the form db_name.table_name is checked against the regular expression specified with the --include option. If the regular expression matches the complete string db_name.table_name, the table is included in the backup. The regular expression uses the POSIX extended form. On Unix-like systems, quote the regular expression appropriately to prevent interpretation of shell meta-characters. This feature has been implemented with Henry Spencer's regular expression library.

IMPORTANT: Although the mysqlbackup command 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. If you use mysqlbackup with --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: If no tables match the regular expression pattern specified with the --include option, the backup currently includes all the file-per-table tables. This behavior might change; do not rely on it as part of your backup procedure.