Documentation Home
MySQL Enterprise Backup 3.8 User's Guide
Download this Manual
PDF (US Ltr) - 0.9Mb
PDF (A4) - 0.9Mb

3.3.3 Making a Compressed Backup

To save disk space, you can compress InnoDB backup data files by using the --compress option of mysqlbackup. 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.

The backup compression feature only applies to InnoDB tables. MySQL 5.5 and higher make InnoDB the default storage engine, because of its high concurrency, reliability, and fast crash recovery. The hot backup and incremental backup features of MySQL Enterprise Backup also apply only to InnoDB tables; For these reasons, Oracle recommends using InnoDB tables for your biggest, busiest, and most important data.

When InnoDB 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.


If there is unused space within an InnoDB tablespace file, the entire file is copied during an uncompressed backup. Do a compressed backup to avoid the storage overhead for this unused space.

You can only use the --compress option for full backups, not for incremental backups.

$ mysqlbackup --defaults-file=/home/pekka/my.cnf --compress backup
...many lines of output...
mysqlbackup: Compressed 488 MB of data files to 53 MB (compression 89%).

101208 15:48:09  mysqlbackup: 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:

  • 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 compressed 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.

User Comments
Sign Up Login You must be logged in to post a comment.