7.2 Example: Backing Up InnoDB and MyISAM Tables

In this example, mysqlbackup takes two arguments: the options file of the MySQL installation to be backed up, and a backup root directory. The last command line argument /backups is the root directory in which mysqlbackup creates the backup directory.

$ mysqlbackup /home/pekka/.my.cnf /backups
mysqlbackup: Starting mysqlbackup with following arguments:
mysqlbackup /home/pekka/.my.cnf /backups  
mysqlbackup: The unique backup id generated for the current backup operation is 12918179022011620 

mysqlbackup: IMPORTANT: Please check that backup run completes successfully.
            At the end of a successful 'backup' run mysqlbackup
            prints "mysqlbackup completed OK!".

mysqlbackup: Created backup directory '/backups/2010-12-08_16-18-22'
mysqlbackup: Using ibbackup version 3.5.2 MySQL Enterprise Backup 3.5.2
mysqlbackup: Using MySQL client version: 5.1.47
mysqlbackup: Checking a connection to MySQL Server with parameters:
mysqlbackup: port=3306, socket=/home/pekka/mysql/MySQL.socket 
mysqlbackup: Using MySQL server version: 5.1.37

101208 16:18:22 mysqlbackup: Starting ibbackup binary with args:

./ibbackup --suspend-at-end /home/pekka/.my.cnf /backups/2010-12-08_16-18-22/backup-my.cnf 
mysqlbackup: Waiting for ibbackup process to suspend
mysqlbackup: Suspend file '/backups/2010-12-08_16-18-22/ibbackup_suspended'
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 /backups/2010-12-08_16-18-22/backup-my.cnf:
innodb_data_home_dir got value /backups/2010-12-08_16-18-22
innodb_data_file_path got value ibdata1:10M;ibdata2:20M;ibdata3:50M:autoextend
datadir got value /backups/2010-12-08_16-18-22
innodb_log_group_home_dir got value /backups/2010-12-08_16-18-22
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 16:18:22  ibbackup: Copying log...
101208 16:18:22  ibbackup: Log copied, lsn 32164666892.
ibbackup: We wait 1 second before starting copying the data files...
101208 16:18:23  ibbackup: Copying /sqldata/simple/ibdata1 (Antelope file format).
101208 16:18:24  ibbackup: Copying /sqldata/simple/ibdata2 (Antelope file format).
101208 16:18:25  ibbackup: Copying /sqldata/simple/ibdata3 (Antelope file format).
ibbackup: Progress in MB: 200 400

ibbackup: You specified the option --suspend-at-end.

101208 16:19:05 mysqlbackup: Continuing after ibbackup has suspended

101208 16:19:05 mysqlbackup: Starting to lock all the tables....

101208 16:19:06 mysqlbackup: All tables are locked and flushed to disk
mysqlbackup: Opening backup source directory '/sqldata/simple'

101208 16:19:06 mysqlbackup: Starting to backup all files in subdirectories of '/sqldata/simple'
mysqlbackup: Backing up the database directory 'mysql'
mysqlbackup: Backing up the database directory 'test'
mysqlbackup: Resuming ibbackup

101208 16:19:05  ibbackup: Suspending the backup procedure to wait
ibbackup: until you delete the marker file /backups/2010-12-08_16-18-22/ibbackup_suspended.
101208 16:19:06  ibbackup: Suspension ends. Continuing the backup procedure.

101208 16:19:06  ibbackup: Copying of the last data file is close to ending...
ibbackup: We still once copy the latest flushed log to ibbackup_logfile.
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 16:19:06  ibbackup: Full backup completed!

101208 16:19:06 mysqlbackup: All tables unlocked
mysqlbackup: All MySQL tables were locked for 0.887 seconds
mysqlbackup: Backup created in directory '/backups/2010-12-08_16-18-22'
mysqlbackup: start_lsn: 32164666880
mysqlbackup: incremental_base_lsn: 32164665879
mysqlbackup: end_lsn: 32164666892

101208 16:19:07 mysqlbackup: mysqlbackup completed OK!

Now we see the backup directory under the BACKUP-ROOT-DIR we specified. The directory name for each new backup is formed from the date and the clock time when the backup run was started, in the local time zone.

$ ls -l /backups
total 72
drwxr-xr-x 4 pekka pekka 4096 2010-03-18 15:34 2009-10-27_16-44-38
drwxr-xr-x 4 pekka pekka 4096 2009-12-14 18:56 2009-12-14_18-56-02
drwxr-xr-x 4 pekka pekka   88 2010-03-25 17:04 2010-03-25_17-03-34
drwxr-xr-x 4 pekka pekka 4096 2010-05-17 18:26 2010-05-17_18-23-52
drwxr-xr-x 4 pekka pekka 4096 2010-10-04 12:14 2010-05-17_18-35-41
drwx------ 4 pekka pekka   80 2010-09-15 13:17 2010-09-15_13-16-19
drwx------ 4 pekka pekka   80 2010-09-16 14:19 2010-09-16_14-17-59
drwx------ 4 pekka pekka   80 2010-09-20 13:37 2010-09-20_13-36-07
drwx------ 4 pekka pekka   80 2010-09-27 12:56 2010-09-27_12-54-57
drwx------ 4 pekka pekka   80 2010-09-30 15:25 2010-09-30_15-24-23
drwx------ 4 pekka pekka 4096 2010-12-08 16:18 2010-10-04_12-14-47
drwx------ 2 pekka pekka    1 2010-12-08 14:57 2010-12-08_14-57-34
drwx------ 4 pekka pekka   80 2010-12-08 15:17 2010-12-08_15-16-42
drwx------ 4 pekka pekka   80 2010-12-08 16:19 2010-12-08_16-18-22

The backup directory contains the backed-up ibdata files and ibbackup_logfile. Its subdirectories mysql, test, test115 are copies of the database directories, and contain copies of .frm, .MYD, and .MYI files:

$ ls -l /backups/2010-12-08_16-18-22
total 499740
-rw-r--r-- 1 pekka pekka       347 2010-12-08 16:18 backup-my.cnf
-rw-r--r-- 1 pekka pekka        18 2010-12-08 16:19 ibbackup_binlog_info
-rw-r--r-- 1 pekka pekka       158 2010-12-08 16:19 ibbackup_export_variables.txt
-rw-r----- 1 pekka pekka      1024 2010-12-08 16:19 ibbackup_logfile
-rw-r----- 1 pekka pekka  10485760 2010-12-08 16:18 ibdata1
-rw-r----- 1 pekka pekka  20971520 2010-12-08 16:18 ibdata2
-rw-r----- 1 pekka pekka 480247808 2010-12-08 16:19 ibdata3
drwx------ 2 pekka pekka      4096 2010-12-08 16:19 mysql
drwx------ 2 pekka pekka        24 2010-12-08 16:19 test