- 4 Making a Backup
- 4.1 Example: Making an Uncompressed Backup of InnoDB Tables
- 4.2 Example: Making a Compressed Backup of InnoDB Tables
- 4.3 Example: Making an Incremental Backup of InnoDB Tables
- 4.4 Making a Point-in-Time Backup of InnoDB, MyISAM, and Other Tables
In this example, the options file
/home/pekka/.backup-my.cnf defines the
location of the backup (as described in the previous section).
The options file /home/pekka/.my.cnf defines
the MySQL installation to back up. Running
ibbackup performs the first phase of the
process:
$ ibbackup /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:32:32 ibbackup: Copying log... 101208 15:32:32 ibbackup: Log copied, lsn 32164666892. ibbackup: We wait 1 second before starting copying the data files... 101208 15:32:33 ibbackup: Copying /sqldata/simple/ibdata1 (Antelope file format). 101208 15:32:34 ibbackup: Copying /sqldata/simple/ibdata2 (Antelope file format). 101208 15:32:36 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 101208 15:33:11 ibbackup: Full backup completed!
The backup directory now contains a backup log file and copies of InnoDB data files:
$ ls -l /sqldata-backup total 499728 -rw-r--r-- 1 pekka pekka 158 2010-12-08 15:33 ibbackup_export_variables.txt -rw-r----- 1 pekka pekka 1024 2010-12-08 15:33 ibbackup_logfile -rw-r----- 1 pekka pekka 10485760 2010-12-08 15:32 ibdata1 -rw-r----- 1 pekka pekka 20971520 2010-12-08 15:32 ibdata2 -rw-r----- 1 pekka pekka 480247808 2010-12-08 15:33 ibdata3
Next Steps:
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.LSN_numberApply the log to the uncompressed 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
Add your own comment.