7.3 Example: Verifying a Backup

This example continues from where we ended up at the end of the previous example. To verify the backup, we will run the MySQL daemon (mysqld) on the backup data. Then we can execute queries to verify the data.

We prepare the backup for starting the database server on it by applying the log records (in file ibbackup_logfile) to the InnoDB data files (ibdata*).

$ mysqlbackup --apply-log /home/pekka/.my.cnf /backups/2010-12-08_16-18-22
mysqlbackup: Starting mysqlbackup with following arguments:
mysqlbackup --apply-log /home/pekka/.my.cnf /backups/2010-12-08_16-18-22  
mysqlbackup: IMPORTANT: Please check that apply-log run completes successfully.
            At the end of a successful 'apply-log' run mysqlbackup
            prints "mysqlbackup completed OK!".

mysqlbackup: Using ibbackup version 3.5.2 MySQL Enterprise Backup 3.5.2
mysqlbackup: Starting ibbackup binary with args:

./ibbackup --apply-log /backups/2010-12-08_16-18-22/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 /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

101208 16:23:44  ibbackup: ibbackup_logfile's creation parameters:
ibbackup: start lsn 32164666880, end lsn 32164666892,
ibbackup: start checkpoint 32164666892.
InnoDB: Doing recovery: scanned up to log sequence number 32164666892
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 Setting log file size to 0 10485760
Setting log file size to 0 10485760
Setting log file size to 0 10485760
ibbackup: We were able to parse ibbackup_logfile up to
ibbackup: lsn 32164666892.
ibbackup: Last MySQL binlog file position 0 534, file name ./MySQL-bin.000008
ibbackup: The first data file is '/backups/2010-12-08_16-18-22/ibdata1'
ibbackup: and the new created log files are at '/backups/2010-12-08_16-18-22/'
ibbackup: System tablespace file format is Antelope.
101208 16:23:45  ibbackup: Full backup prepared for recovery successfully!

101208 16:23:45 mysqlbackup: mysqlbackup completed OK!

Here is the backup directory after successfully applying the log to the backup. The ibbackup output above shows that data files were rolled forward to log sequence number (lsn) 928223244.

$ ls -l /backups/2010-12-08_16-18-22
total 530464
-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
-rw-r----- 1 pekka pekka  10485760 2010-12-08 16:23 ib_logfile0
-rw-r----- 1 pekka pekka  10485760 2010-12-08 16:23 ib_logfile1
-rw-r----- 1 pekka pekka  10485760 2010-12-08 16:23 ib_logfile2
drwx------ 2 pekka pekka      4096 2010-12-08 16:19 mysql
drwx------ 2 pekka pekka        24 2010-12-08 16:19 test

We are now almost ready to start a server on this backup. The final requirement is to prepare a valid my.cnf file for the backup. We cannot reuse the backup-my.cnf in the backup directory, because it contains only the small subset of parameters required by ibbackup. We must create a my.cnf file by copying these parameters from the backup-my.cnf file, and copying the other parameters from the original my.cnf file (which is /home/pekka/.my.cnf in this example). We make a copy of the original file and append the additional parameters in backup-my.cnf.

$ cat /home/pekka/.my.cnf  /backups/2010-12-08_16-18-22/backup-my.cnf > /backups/2010-12-08_16-18-22/my.cnf

Now we can start a server on the backup, specifying this new my.cnf as the configuration file:

$ mysqld --defaults-file=/backups/2010-12-08_16-18-22/my.cnf
101208 16:23:45 [Warning] Changed limits: max_open_files: 1024  max_connections: 200  table_cache: 407
101208 16:23:45 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: The log file was created by ibbackup --apply-log at
InnoDB: ibbackup 101208 16:23:45
InnoDB: NOTE: the following crash recovery is part of a normal restore.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
101208 16:23:46  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Last MySQL binlog file position 0 534, file name ./MySQL-bin.000008
101208 16:23:46  InnoDB: Started; log sequence number 7 2099896332
101208 16:23:46 [Note] Event Scheduler: Loaded 0 events
101208 16:23:46 [Note] mysqld: ready for connections.