[MySQL] innobackupex online backup and recovery (full and incremental)

Xtrabackup is an open-source software developed by percona. It is an open-source alternative to innodb Hot Backup Tool ibbackup (charged commercial software. Xtrabackup consists of xtrabackup and innobackupex. xtrabackup is used to back up tables of innodb and xtraDB engines, while innobackupex is used to back up myisam and in

Xtrabackup is an open-source software developed by percona. It is an open-source alternative to innodb Hot Backup Tool ibbackup (charged commercial software. Xtrabackup consists of xtrabackup and innobackupex. xtrabackup is used to back up tables of innodb and xtraDB engines, while innobackupex is used to back up myisam and in

Xtrabackup is an open-source software developed by percona. It is an open-source alternative to innodb Hot Backup Tool ibbackup (charged commercial software. Xtrabackup consists of xtrabackup and innobackupex. xtrabackup is used to back up tables of innodb and xtraDB engines, while innobackupex is used to back up tables of myisam and innodb engines, this article describes how to use the innobackupex tool for full and Incremental backup.

Official Website: http://www.percona.com/docs/wiki/percona-xtrabackup:start

Install

Statement: The following operations should be performed by mysql users.

First, download the source tar package through wget:

wget http://www.percona.com/redir/downloads/XtraBackup/LATEST/source/percona-xtrabackup-2.1.5.tar.gz

Install the dependency package:

yum install cmake gcc gcc-c++ libaio libaio-devel automake autoconf bzr bison libtool ncurses-devel zlib-devel

Decompress tar:

tar -zxvf percona-xtrabackup-2.1.5.tar.gzcd percona-xtrabackup-2.1.5

The utils/build. sh script automatically decompress and compile the appropriate MySQL source code package based on the specified engine version. This is the simplest installation method. When you execute the script without any parameters in the command line, the following prompt appears:

[mysql@epay100 ~/software/percona-xtrabackup-2.1.5 ]$ ./utils/build.shBuild an xtrabackup binary against the specified InnoDB flavor.Usage: build.sh CODEBASEwhere CODEBASE can be one of the following values or aliases:  innodb51         | plugin                build against InnoDB plugin in MySQL 5.1  innodb55         | 5.5                   build against InnoDB in MySQL 5.5  innodb56         | 5.6,xtradb56,         build against InnoDB in MySQL 5.6                   | mariadb100  xtradb51         | xtradb,mariadb51      build against Percona Server with XtraDB 5.1                   | mariadb52,mariadb53  xtradb55         | galera55,mariadb55    build against Percona Server with XtraDB 5.5

Select the corresponding parameters based on the above prompt and the storage engine and version you are using. Because I am using MySQL 5.6, execute the following statement for installation:

./utils/build.sh innodb56

After the preceding statement is successfully executed, the installation is complete. Finally, copy the generated binary file to a custom directory (/home/mysql/admin/bin/percona-xtrabackup-2.1.5 in this example) and put the directory in the environment variable PATH.

cp ./innobackupex /home/mysql/admin/bin/percona-xtrabackup-2.1.5cp ./src/xtrabackup_56 ./src/xbstream /home/mysql/admin/bin/percona-xtrabackup-2.1.5  

Full backup and recovery

Full backup:

Execute the following statement for full backup:

innobackupex --defaults-file=/opt/mysql/my.cnf  --user=root --password=*** /backup/mysql/data

This statement will copy the data file (by my. specify the variable datadir in cnf) to the backup directory (/backup/mysql/data). Note: If -- defaults-file is not specified, the default value is/etc/my. cnf.

After the backup is successful, a timestamp directory will be created under the backup Directory (the directory created in this example is/backup/mysql/data/2013-10-29_09-05-25), where the backup files will be stored.

Recovery:

Before restoration, you must close the database and delete the data files and log files.

innobackupex --defaults-file=/opt/mysql/my.cnf --user=root --password=*** --use-memory=4G --apply-log /backup/mysql/data/2013-10-29_09-05-25innobackupex --defaults-file=/opt/mysql/my.cnf --user=root --password=***  --copy-back /backup/mysql/data/2013-10-29_09-05-25

From what we can see, recovery is divided into two steps, step 1 is apply-log, to speed up, it is generally recommended to set -- use-memory, after this step is complete, the backup files under directory/backup/mysql/data/2013-10-29_09-05-25 are ready.

Step 2 is copy-back, that is, copy the backup file to the original data directory.

After the data is restored, check whether the owner and permissions of the Data Directory are correct.

Incremental backup and recovery

Note: innobackupex Incremental backup is only for engines that support transactions such as InnoDB. For engines such as MyISAM, it is still full backup.

Incremental Backup:

Incremental backup must be based on full backup. Assume that we already have a full backup (/backup/mysql/data/2013-10-29_09-05-25). We need to perform Incremental backup Based on the full backup.

innobackupex --defaults-file=/opt/mysql/my.cnf  --user=root --password=*** --incremental-basedir=/backup/mysql/data/2013-10-29_09-05-25 --incremental /backup/mysql/data

Here, -- incremental-basedir points to the full backup directory, and -- incremental points to the incremental Backup Directory.

After the preceding statement is successfully executed, a time stamp sub-directory (/backup/mysql/data/2013-10-29_09-52-37 in this example) will be created under the directory of -- incremental execution ), all Incremental backup files are stored in this directory.

In the backup directory, the xtrabackup_checkpoints file records the backup information. The full backup information is as follows:

backup_type = full-backupedfrom_lsn = 0to_lsn = 563759005914last_lsn = 563759005914

The Incremental backup information based on the full backup is as follows:

backup_type = incrementalfrom_lsn = 563759005914to_lsn = 574765133284last_lsn = 574765133284

As shown above, the Incremental Backup from_lsn is exactly the same as the full backup to_lsn.
So can we perform Incremental backup on the basis of Incremental backup? The answer is yes. Just execute the directory of -- incremental-basedir For The Last incremental backup, as shown below:

innobackupex --defaults-file=/opt/mysql/my.cnf  --user=root --password=*** --incremental-basedir=/backup/mysql/data/2013-10-29_09-52-37 --incremental /backup/mysql/data

Its xtrabackup_checkpoints records the following backup information:

backup_type = incrementalfrom_lsn = 574765133284to_lsn = 574770200380last_lsn = 574770200950

We can see that the from_lsn of the Incremental Backup starts from the to_lsn of the previous Incremental backup.

Recovery:

Incremental Backup recovery is much more complex than full backup. The first step is to redo the submitted logs in all backup directories, such:

innobackupex --apply-log --redo-only BASE-DIRinnobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1innobackupex --apply-log BASE-DIR --incremental-dir=INCREMENTAL-DIR-2

Where BASE-DIR refers to the full backup directory, INCREMENTAL-DIR-1 refers to the first Incremental backup, INCREMENTAL-DIR-2 refers to the second Incremental backup, and so on.

Note that the Incremental backup in the last step does not have the -- redo-only option! Also, you can use -- use_memory to improve performance.

After the preceding statement is executed successfully, the final data is under the BASE-DIR (full backup directory.

After step 1 is completed, we start Step 2: Roll Back unfinished logs:

innobackupex --apply-log BASE-DIR

After the above execution, the backup files in the BASE-DIR are fully ready, and the last step is to copy:

innobackupex --copy-back BASE-DIR

Similarly, after the copy is complete, check whether the permission of the data directory is correct.

Common Errors and Solutions

Error:

131028 17:45:57  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: NO).innobackupex: Error: Failed to connect to MySQL server as DBD::mysql module is not installed at /home/mysql/admin/bin/percona-xtrabackup-2.1.5/innobackupex line 2913.

Solution:

yum -y install perl-DBD-MySQL.x86_64

Error:

sh: xtrabackup_55: command not foundinnobackupex: Error: no 'mysqld' group in MySQL options at /home/mysql/admin/bin/percona-xtrabackup-2.1.6/innobackupex line 4341.

Solution:

cp xtrabackup_innodb55 xtrabackup_55