4.2 Example: Making a Compressed Backup of InnoDB Tables

To save disk space, you can compress backup data files by using the --compress option of ibbackup. Compression lets you keep more sets of backup data on hand, and saves on transmission time when sending the backup data to another server. The downside is extra CPU overhead during the backup itself, and extra time needed during the restore process as the data is uncompressed.

When tablespace files are compressed during backup, they receive the extension .ibz rather than the usual .ibd extension. To avoid wasting CPU cycles without saving additional disk space, --compress does not attempt to compress already-compressed tables that use the Barracuda file format; such tablespace files keep the usual .ibd extension.

You can use the --compress option for full backups, or for incremental backups in combination with the --incremental option.

$ ibbackup --compress /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/simple
innodb_data_file_path got value ibdata1:10M;ibdata2:20M;ibdata3:50M:autoextend
datadir got value /sqldata/simple
innodb_log_group_home_dir got value /sqldata/simple
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

ibbackup: System tablespace file format is Antelope.
ibbackup: Found checkpoint at lsn 32164666892.
ibbackup: Starting log scan from lsn 32164666880.
101208 15:47:39  ibbackup: Copying log...
101208 15:47:39  ibbackup: Log copied, lsn 32164666892.
ibbackup: We wait 1 second before starting copying the data files...
101208 15:47:40  ibbackup: Copying /sqldata/simple/ibdata1 (Antelope file format).
101208 15:47:41  ibbackup: Copying /sqldata/simple/ibdata2 (Antelope file format).
101208 15:47:42  ibbackup: Copying /sqldata/simple/ibdata3 (Antelope file format).
ibbackup: Progress in MB: 200 400
ibbackup: A copied database page was modified at 32164665879.
ibbackup: Scanned log up to lsn 32164666892.
ibbackup: Was able to parse the log up to lsn 32164666892.
ibbackup: Maximum page number for a log record 0

ibbackup: Compressed 488 MB of data files to 53 MB (compression 89%).

101208 15:48:09  ibbackup: Full backup completed!

The backup directory is shown below. Compressed data files have the suffix .ibz. Typically, compression ratios of more than 70% are achieved:

$ ls -l /sqldata-backup
total 54676
-rw-r--r-- 1 pekka pekka      158 2010-12-08 15:48 ibbackup_export_variables.txt
-rw-r----- 1 pekka pekka     1024 2010-12-08 15:48 ibbackup_logfile
-rw-r----- 1 pekka pekka  1095854 2010-12-08 15:47 ibdata1.ibz
-rw-r----- 1 pekka pekka   811625 2010-12-08 15:47 ibdata2.ibz
-rw-r----- 1 pekka pekka 54058462 2010-12-08 15:48 ibdata3.ibz

Next steps: