Chapter 8 Making a Partial Backup

Table of Contents

8.1 Example: Making an Uncompressed Partial Backup
8.2 Example: Making a Compressed Partial Backup
8.3 Restoring a Single .ibd File
8.4 Backing Up Selected Databases
8.5 Backing Up Files from Different Storage Engines
8.6 Backing Up In-Memory Database Data

When the multiple tablespaces feature is enabled, it is possible to make a partial backup of the InnoDB tables. The multiple tablespaces feature allows storing each InnoDB table in a separate tablespace holding the data and indexes of one table only. A single-table tablespace consists of one autoextending datafile named table_name.ibd in the database directory of the table. 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, ibbackup (v2.0 or higher) can be instructed to make a partial backup excluding some of the per-table datafiles. A partial backup contains the system tablespace and per-table datafiles of those tables that match the include pattern.

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.