8.2 Example: 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 ibbackup with the --compress and --include options:

$ ibbackup --compress --include '.*\.(alex|blob).*' /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: '.*\.(alex|blob).*'

ibbackup: System tablespace file format is Barracuda.
ibbackup: Found checkpoint at lsn 2666737471.
ibbackup: Starting log scan from lsn 2666737152.
101208 17:17:57  ibbackup: Copying log...
101208 17:17:57  ibbackup: Log copied, lsn 2666737471.
ibbackup: We wait 1 second before starting copying the data files...
101208 17:17:58  ibbackup: Copying /sqldata/mts/ibdata1 (Barracuda file format).
101208 17:17:59  ibbackup: Copying /sqldata/mts/ibdata2 (Barracuda file format).
101208 17:17:59  ibbackup: Copying /sqldata/mts/ibdata3 (Barracuda file format).
101208 17:18:01  ibbackup: Copying /sqldata/mts/test/alex1.ibd (Antelope file format).
101208 17:18:03  ibbackup: Copying /sqldata/mts/test/alex2.ibd (Antelope file format).
101208 17:18:03  ibbackup: Copying /sqldata/mts/test/alex3.ibd (Antelope file format).
101208 17:18:03  ibbackup: Copying /sqldata/mts/test/blobt3.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

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

101208 17:18:04  ibbackup: 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