3.3.4.1 Backing Up Data from Different Storage Engines

3.3.4.1.1 Omitting Unknown Files
3.3.4.1.2 Backing Up Only InnoDB Tables

By default, all the files in the data directory are included in the backup, so the backup includes data from all MySQL storage engines, any third-party storage engines, and even any non-database files in that directory. This section explains options you can use to selectively back up or exclude data from particular storage engines.

3.3.4.1.1 Omitting Unknown Files

The --only-known-file-types option of the mysqlbackup command limits the backup to only those files that represent known data files from MySQL or its built-in storage engines, such as .frm, .ibd, .myd, and so on. (See the full list of extensions.) By default, the mysqlbackup command backs up all file extensions within the data directory, which could include files produced by many different storage engines. Use this option to omit the additional data files from other storage engines from the backup, for performance or space reasons.

3.3.4.1.2 Backing Up Only InnoDB Tables

The --only-innodb option backs up InnoDB tables only, skipping those of other storage engines. You might use this option 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 and --incremental 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:

$ 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!

The backup directory now contains a backup log file and copies of InnoDB data files.

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:

$ 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!

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.

$ ls /sqldata-backup/test
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