Skip navigation links
3.3.4.2 Omitting Unknown Files »
Section Navigation      [Toggle]

3.3.4.1. Backing Up Some or All InnoDB Tables

With its --include option, mysqlbackup can make a backup that includes some InnoDB tables but not others:

  • A partial backup with the --include option always contains the InnoDB system tablespace and all the tables inside it.

  • For the InnoDB tables stored outside the system tablespace, the partial backup includes only those tables whose names match the regular expression specified with the --include option.

This operation requires the tables being left out to be stored in separate table_name.ibd files. To put an InnoDB table outside the system tablespace, create it while the innodb_file_per_table MySQL configuration option is enabled. Each .ibd file holds the data and indexes of one table only.

Those InnoDB tables created with innodb_file_per_table turned off are stored as usual in the InnoDB system tablespace, and cannot be left out of the backup.

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 POSIXextended 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, except when you do partial backups using the --databases option. If you use mysqlbackup with the --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: Because the InnoDB system tablespace holds metadata about InnoDB tables from all databases in an instance, restoring a partial backup on a server that includes other databases could cause the system to lose track of those InnoDB tables in other databases. Always restore partial backups on a fresh MySQL server instance without any other InnoDB tables that you want to preserve.

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.

The --only-innodb and --only-innodb-with-frm options back up InnoDB tables only, skipping those of other storage engines. You might use one of these options for some backup operations based on the following considerations:

  • The InnoDB tables are backed up using the hot backup technique, which does not interfere with database processing.

  • The --compress, --incremental, and --incremental-with-redo-log-only options offer benefits only for InnoDB data.

  • In a busy production environment, InnoDB tables might represent the bulk of your important data because of the importance of high concurrency and crash recovery.

  • In MySQL 5.5 and higher, InnoDB is the default storage engine for new tables.

Example 3.1. Making an Uncompressed Backup of InnoDB Tables

In this example, the options file /home/pekka/.my.cnf defines the MySQL installation to back up. Running mysqlbackup performs the first phase of the process: 

# Back up all InnoDB tables but no .frm files.
$ mysqlbackup --defaults-file=/home/pekka/.my.cnf --only-innodb backup
...many lines of output...
mysqlbackup: Scanned log up to lsn 32164666892.
mysqlbackup: Was able to parse the log up to lsn 32164666892.
mysqlbackup: Maximum page number for a log record 0
101208 15:33:11  mysqlbackup: Full backup completed!

# Back up all InnoDB tables and corresponding .frm files.
$ mysqlbackup --defaults-file=/home/pekka/.my.cnf --only-innodb-with-frm backup
...many lines of output...
mysqlbackup: Scanned log up to lsn 32164666892.
mysqlbackup: Was able to parse the log up to lsn 32164666892.
mysqlbackup: Maximum page number for a log record 0
101208 15:33:11  mysqlbackup: Full backup completed!

The backup directory now contains a backup log file and copies of InnoDB data files. The backup directory from the --only-innodb-with-frm option also includes .frm files for the InnoDB tables.

Next Steps:

  • Make a note of the LSN value in the message at the end of both full and incremental backups, for example, mysqlbackup: Was able to parse the log up to lsn LSN_number. You specify this value when performing incremental backups of changes that occur after this full backup.

  • Apply the log to the uncompressed backup files, so that the full backup is ready to be restored at any time. You can move the backup data to a different server first, to avoid the CPU and I/O overhead of performing this operation on the database server.

  • After applying the log, periodically take incremental backups, which are much faster and smaller than a full backup like this.


Example 3.2. Making an Uncompressed Partial Backup of InnoDB Tables

In this example, we have configured MySQL so that some InnoDB tables have their own tablespaces. We make a partial backup including only those InnoDB tables in test database whose name starts with ib. The contents of the database directory for test database are shown below. The directory contains a MySQL description file (.frm file) for each of the tables (alex1, alex2, alex3, blobt3, ibstest0, ibstest09, ibtest11a, ibtest11b, ibtest11c, and ibtest11d) in the database. Of these 10 tables six (alex1, alex2, alex3, blobt3, ibstest0, ibstest09) are stored in per-table datafiles (.ibd files). 

$ ls /sqldata/mts/test
alex1.frm  alex2.ibd  blobt3.frm    ibstest0.ibd  ibtest11a.frm  ibtest11d.frm
alex1.ibd  alex3.frm  blobt3.ibd    ibtest09.frm  ibtest11b.frm
alex2.frm  alex3.ibd  ibstest0.frm  ibtest09.ibd  ibtest11c.frm

We run the mysqlbackup with the --include option: 

# Back up some InnoDB tables but not any .frm files.
$ mysqlbackup --defaults-file=/home/pekka/.my.cnf --include='test\.ib.*' --only-innodb backup
...many lines of output...
mysqlbackup: Scanned log up to lsn 2666737471.
mysqlbackup: Was able to parse the log up to lsn 2666737471.
mysqlbackup: Maximum page number for a log record 0
101208 17:17:45  mysqlbackup: Full backup completed!

# Back up some InnoDB tables and the .frm files for the backed-up tables only.
$ mysqlbackup --defaults-file=/home/pekka/.my.cnf --include='test\.ib.*' \
  --only-innodb-with-frm=related backup
...many lines of output...
mysqlbackup: Scanned log up to lsn 2666737471.
mysqlbackup: Was able to parse the log up to lsn 2666737471.
mysqlbackup: Maximum page number for a log record 0
101208 17:17:45  mysqlbackup: Full backup completed!

The backup directory contains only backups of ibstest and ibtest09 tables. Other InnoDB tables did not match the include pattern test\.ib.*. Notice, however, that the tables ibtest11a, ibtest11b, ibtest11c, ibtest11d are in the backup even though they are not visible in the directory shown below, because they are stored in the system tablespace (ibdata1 file) which is always included in the backup. 

# With the --only-innodb option:
$ ls /sqldata-backup/test
ibstest0.ibd   ibtest09.ibd

# With the --only-innodb-with-frm=related option:
$ ls /sqldata-backup/test
ibstest0.frm   ibtest09.frm
ibstest0.ibd   ibtest09.ibd

Example 3.3. Making a Compressed Partial Backup

We have configured MySQL so that every InnoDB table has its own tablespace. We make a partial backup including only those InnoDB tables whose name starts with alex or blob. The contents of the database directory for test database is shown below. 

$ ls /sqldata/mts/test
alex1.frm  alex2.ibd  blobt3.frm    ibstest0.ibd  ibtest11a.frm  ibtest11d.frm
alex1.ibd  alex3.frm  blobt3.ibd    ibtest09.frm  ibtest11b.frm
alex2.frm  alex3.ibd  ibstest0.frm  ibtest09.ibd  ibtest11c.frm

We run mysqlbackup with the --compress and --include options: 

$ mysqlbackup --defaults-file=/home/pekka/.my.cnf --compress \
  --include='.*\.(alex|blob).*' --only-innodb backup
...many lines of output...
mysqlbackup: Scanned log up to lsn 2666737471.
mysqlbackup: Was able to parse the log up to lsn 2666737471.
mysqlbackup: Maximum page number for a log record 0

mysqlbackup: Compressed 147 MB of data files to 15 MB (compression 89%).

101208 17:18:04  mysqlbackup: Full backup completed!

The backup directory for the database test is shown below. The .ibz files are compressed per-table datafiles. 

$ ls /sqldata-backup/test
alex1.ibz   alex2.ibz   alex3.ibz   blobt3.ibz

Copyright © 2003, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices