Backup and recovery of Innobackupex

http://blog.itpub.net/15480802/viewspace-1173479/

1 principle

3 Stages: Backup backup– pre-restore Prepare-restore restore

Note: Copying a innodb table file may contain incomplete transactions that require prepare to become consistent

First copy all the InnoDB data files, so that the copied files must be inconsistent, and then each file for the crash recovery processing, eventually achieve the same.

Xtrabackup logs an LSN (log sequence number) at startup, and then copies all of the InnoDB data files so that the copied data files are inconsistent, But Xtrabackup will run a process in the background to record all changes to the redo log file;

The above operation is done by the Xtrabackup binary program (such as XTRABACKUP_55), if the use of Innobackupex script, the steps just completed, Innobackupex will back up the MyISAM table and the. frm file, at which point the consistency of the data will be locked before the table is locked by flush TABLES with the READ Lock command and then the file is copied out, and then the lock is released.

When recovering data, go through prepare (recovery) and restore two steps. At the end of the prepare, the InnoDB table reverts to the point at which the copy InnoDB file ends, which is the point at which the lock table duplicates the MyISAM table, so the final data is consistent. Generally we perform two prepare when recovering, because the second prepare will help us generate the redo log file, which speeds up the MySQL database.

Backup

$ Innobackupex--user=dbuser--password=dbuserpass/path/to/backup-dir/

--Put the database backup in the Backup-dir directory, create a new subdirectory by default,--no-timestamp will skip this function;

Prepare

$ Innobackupex--apply-log/path/to/backup-dir

At this point the data can be used by the program access, you can use the-use-memory option to specify the memory used to speed up the progress, default 100M;

Recovery

$ Innobackupex--copy-back/path/to/backup-dir

Read Datadir/innodb_data_home_dir/innodb_data_file_path and other variables from MY.CNF;

Copy the MyISAM table first, then the InnoDB table, and finally the Logfile;--data-dir directory must be empty

2 Incremental backup

First of all to do, each backup directory has xtrabackup-checkpoints files, the contents are as follows

Backup_type = full-backuped

FROM_LSN = 0

TO_LSN = 1291135

Incremental backup takes TO_LSN as the starting point

$ Innobackupex--incremental/data/backups--incremental-basedir=basedir

The contents of the Xtrabackup-checkpoints file are as follows

Backup_type = Incremental

FROM_LSN = 1291135

TO_LSN = 1352113

Incremental backups can be continued on this basis

The prepare of an incremental backup is somewhat complex, and if a transactional consistency recovery is performed on base backup, it can no longer be used for incremental backup restores, which must specify the -redo-only option;

Innobackupex--apply-log--redo-only Base-dir

Innobackupex--apply-log--redo-only Base-dir--incremental-dir=incremental-dir-1

Innobackupex--apply-log Base-dir--incremental-dir=incremental-dir-2

When all incremental backups are consolidated, start rolling back all uncommitted transactions

Innobackupex--apply-log Base-dir

3 Recovering a single table

Oracle Rman provides the restore datafile, which also has blockrecover for bad blocks, which avoids full-database recovery as much as possible;

Innobackx also provides a similar function that allows the recovery of a single tablespace;

Performing Prepare on backups

$ Innobackupex--apply-log--export/path/to/backup

--export let InnoDB use slow shutdown (full purge + change buffer merge) to ensure that the table space is consistent and import;

For each table, the file list is as follows

/data/backups/mysql/test/export_test.exp

/data/backups/mysql/test/export_test.ibd

The dump,5.6 of the/data/backups/mysql/test/export_test.cfg--innodb data dictionary is not required;

Restore a single table

Mysql> CREATE TABLE mytable (...) Engine=innodb; --Create a table of the same structure

mysql> ALTER TABLE mydatabase.mytable DISCARD tablespace;

Copy the. ibd/.exp/.cfg to the Data directory

mysql> ALTER TABLE mydatabase.mytable IMPORT tablespace;

4 Point-in-time recovery

Each backup directory has a xtrabackup_binlog_info that records the current location of the database when the backup Binlog, which is also the endpoint for database consistency recovery;

$ cat/path/to/backup/xtrabackup_binlog_info

MYSQL-BIN.000003 57

$ Innobackupex--copy-back/path/to/backup

After the restore is complete, you can perform a point-in-time recovery via Mysqlbinlog

$ mysqlbinlog/path/to/datadir/mysql-bin.000003/path/to/datadir/mysql-bin.000004 \

--start-position=57--stop-datetime= "11-12-25 01:00:00" | Mysql-u root–p

5 Performing backups in slave

Be aware of the following two parameters

--slave-info

This option is useful the backing up a replication slave server. It prints the binary log position and name of the master server. It also writes this information to the Xtrabackup_slave_info file as a change MASTER command. A new slave for this master can is set up by starting a slave server in this backup and issuing a change Master command WI Th the binary log position saved in the Xtrabackup_slave_info file.

--safe-slave-backup

Stop slave SQL thread and wait to start backup until Slave_open_temp_tables in SHOW STATUS is zero. If There is no open temporary tables, the backup would take place, otherwise the SQL thread would be started and stopped UN Til there is no open temporary tables. The backup would fail if Slave_open_temp_tables does not become zero after--safe-slave-backup-timeout seconds. The slave SQL thread would be restarted when the backup finishes.

Http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/innobackupex_script.html

http://www.banping.com/2011/07/01/xtrabackup-process-backgroud/

http://www.mysqlperformanceblog.com/2012/01/25/how-to-recover-a-single-innodb-table-from-a-full-backup/

Backup and recovery of Innobackupex