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

[MySQL] innobackupex online backup and recovery (full and incremental) Xtrabackup is an open-source software developed by percona. It is the innodb Hot Backup Tool ibbackup (paid commercial software) is an open-source alternative. Xtrabackup consists of xtrabackup and innobackupex. The xtrabackup tool is used to back up innodb and xtra

[MySQL] innobackupex online backup and recovery (full and incremental) Xtrabackup is an open-source software developed by percona. It is the innodb Hot Backup Tool ibbackup (paid commercial software) is an open-source alternative. Xtrabackup consists of xtrabackup and innobackupex. The xtrabackup tool is used to back up innodb and xtra

[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 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:

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) and perform Incremental backup Based on the full table.

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 solution errors:

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

2 floor linwaterbin yesterday

Hey, are you using this backup online? So are we.

Re: u010316792 yesterday

Reply linwaterbinn yes because it is open source

Mchdba on the first floor yesterday

The problem with datadir is often encountered when performing Backup Recovery: please see my record http://blog.csdn.net/mchdba/article/details/12970991

Re: u010316792 yesterday

To reply to mchdban, it is best to specify the location of my. cnf: -- defaults-file =/opt/mysql/my. cnf

Re: u010316792 yesterday

Reply to mchdban because the datadir variable is not set in your default my. cnf file.

Re: mchdba yesterday

The reply u010316792n is correct, but the datadir directory exists in my. cnf by default. It is not recognized if it is not specified.

Re: u010316792 yesterday

To reply to mchdban, manually specify