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

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

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

Install

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

First, download the source tar package through wget:

[Plain]

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

Install the dependency package:

[Plain]

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

Decompress tar:

[Plain]

Tar-zxvf percona-xtrabackup-2.1.5.tar.gz

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

[Plain]

[Mysql @ epay100 ~ /Software/percona-xtrabackup-2.1.5] $./utils/build. sh

Build an xtrabackup binary against the specified InnoDB flavor.

Usage: build. sh CODEBASE

Where CODEBASE can be one of the following values or aliases:

Innodb51 | plugin build against InnoDB plugin 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 | Route A55, 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:

[Plain]

./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.

[Plain]

Cp./innobackupex/home/mysql/admin/bin/percona-xtrabackup-2.1.5

Cp./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:

[Plain]

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:

[Plain]

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

Innobackupex -- 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.

[Plain]

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:

[Plain]

Backup_type = full-backuped

From_lsn = 0

To_lsns = 563759005914

Last_lsns = 563759005914

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

[Plain]

Backup_type = incremental

From_lsn = 563759005914

To_lsns = 574765133284

Last_lsns = 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:

[Plain]

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:

[Plain]

Backup_type = incremental

From_lsn = 574765133284

To_lsns = 574770200380

Last_lsns = 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:

[Plain]

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

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:

[Plain]

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:

[Plain]

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:

[Plain]

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:

[Plain]

Yum-y install perl-DBD-MySQL.x86_64

BitsCN.com