To save disk space, you can compress backup data files by using
--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
.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
You can use the
--compress option for full
backups, or for incremental backups in combination with the
$ 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
.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
Make a note of the LSN value in the message at the end of
both full and incremental backups, for example,
ibbackup: Was able to parse the log up to lsn
. 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.