8.1 Example: Making an Uncompressed Partial Backup

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 ibbackup with the --include option.

$ ibbackup --include 'test\.ib.*' /home/pekka/.my.cnf /home/pekka/.backup-my.cnf
ibbackup version 3.5.2 MySQL Enterprise Backup 3.5.2
Copyright (c) 2002, 2010, Oracle and/or its affiliates.
Run 'ibbackup --help' for help and 'ibbackup --version' for version info.

Note: Uses posix_fadvise() for performance optimization.

Contents of /home/pekka/.my.cnf:
innodb_data_home_dir got value /sqldata/mts
innodb_data_file_path got value ibdata1:10M;ibdata2:20M;ibdata3:50M:autoextend
datadir got value /sqldata/mts
innodb_log_group_home_dir got value /sqldata/mts
innodb_log_files_in_group got value 3
innodb_log_file_size got value 10485760

Contents of /home/pekka/.backup-my.cnf:
innodb_data_home_dir got value /sqldata-backup
innodb_data_file_path got value ibdata1:10M;ibdata2:20M;ibdata3:50M:autoextend
datadir got value /sqldata-backup
innodb_log_group_home_dir got value /sqldata-backup
innodb_log_files_in_group got value 3
innodb_log_file_size got value 10485760

Value of include option: 'test\.ib.*'

ibbackup: System tablespace file format is Barracuda.
ibbackup: Found checkpoint at lsn 2666737471.
ibbackup: Starting log scan from lsn 2666737152.
101208 17:17:34  ibbackup: Copying log...
101208 17:17:34  ibbackup: Log copied, lsn 2666737471.
ibbackup: We wait 1 second before starting copying the data files...
101208 17:17:35  ibbackup: Copying /sqldata/mts/ibdata1 (Barracuda file format).
101208 17:17:36  ibbackup: Copying /sqldata/mts/ibdata2 (Barracuda file format).
101208 17:17:37  ibbackup: Copying /sqldata/mts/ibdata3 (Barracuda file format).
101208 17:17:43  ibbackup: Copying /sqldata/mts/test/ibstest0.ibd (Antelope file format).
101208 17:17:45  ibbackup: Copying /sqldata/mts/test/ibtest09.ibd (Antelope file format).
ibbackup: A copied database page was modified at 2666737471.
ibbackup: Scanned log up to lsn 2666737471.
ibbackup: Was able to parse the log up to lsn 2666737471.
ibbackup: Maximum page number for a log record 0
101208 17:17:45  ibbackup: 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