Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "the method of innobackupex online backup and recovery". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Background introduction:

1. What is Xtrabackup

Xtrabackup is a data backup tool for InnoDB, supports online hot backup (backup does not affect data read and write), and is a good substitute for commercial backup tool InnoDB Hotbackup.

Xtrabackup has two main tools: xtrabackup and innobackupex

1. Xtrabackup can only back up InnoDB and XtraDB data tables, but not MyISAM data tables.

2. Innobackupex is modified by referring to InnoDBHotbackup's innoback script. Innobackupex is a perl script encapsulation that encapsulates xtrabackup. It is mainly for the convenience of backing up the tables of the InnoDB and MyISAM engines at the same time, but you need to add a read lock when processing myisam. And added some options to use. For example, slave-info can record some information needed by slave after backup and recovery. According to this information, it is convenient to use backup to redo slave.

Xtrabackup, an open source software developed by percona, is an open source alternative to innodb hot backup tool ibbackup (commercial software for a fee). Xtrabackup consists of xtrabackup and innobackupex, where the xtrabackup tool is used to back up the tables of the innodb and xtraDB engines, and the innobackupex tool is used to back up the tables of the myisam and innodb engines. This article describes how to use the innobackupex tool to do full and incremental backups.

Complete and its recovery:

Complete:

Execute the following statement to complete:

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

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

After the backup is successful, a timestamp directory is created under the backup directory (the directory created in this example is / backup/mysql/data/2013-10-29room09-05-25), and the backup files are stored in this directory.

Restore:

Before restoring, 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-299-05-25

Innobackupex-- defaults-file=/opt/mysql/my.cnf-- user=root-- password=***-- copy-back / backup/mysql/data/2013-10-299-05-25

From what can be seen, recovery is divided into two steps, the first step is apply-log, in order to speed up, it is generally recommended to set-- use-memory, after this step is completed, the backup files under the directory / backup/mysql/data/2013-10-29 09-05-25 are ready.

The second step is copy-back, which copies the backup files to the original data directory.

After the restore is complete, be sure to check that the owner and permissions of the data directory are correct.

Incremental backup and its recovery:

Note: innobackupex incremental backup is only for transactional engines such as InnoDB, but it is still complete for engines such as MyISAM.

Incremental backup:

Incremental backup needs to be based on full backup or incremental backup. Suppose we already have a full backup (/ backup/mysql/data/2013-10-29 / 09-05-25), and we need to do incremental backup on this full basis.

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

Where-incremental-basedir points to the full directory and-- incremental points to the directory of incremental backups.

After the above statement is executed successfully, a timestamp subdirectory is created under the directory executed by-- incremental (in this case: / backup/mysql/data/2013-10-29backup 09-52-37), where all files for incremental backups are stored.

In the backup directory, there is a file xtrabackup_checkpoints that records the backup information. The complete information is as follows:

Backup_type = full-backuped

From_lsn = 0

To_lsn = 563759005914

Last_lsn = 563759005914

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

Backup_type = incremental

From_lsn = 563759005914

To_lsn = 574765133284

Last_lsn = 574765133284

As you can see from the above, the from_lsn of the incremental backup is exactly the same as the full to_lsn.

So, can we do incremental backup on the basis of incremental backup? The answer is yes, as long as-- incremental-basedir executes the directory of the last incremental backup, as follows:

Innobackupex-defaults-file=/opt/mysql/my.cnf-user=root-password=***-incremental-basedir=/backup/mysql/data/2013-10-299 09-52-37-incremental / backup/mysql/data

Its xtrabackup_checkpoints records backup information as follows:

Backup_type = incremental

From_lsn = 574765133284

To_lsn = 574770200380

Last_lsn = 574770200950

As you can see, the from_lsn of this incremental backup starts with the to_lsn of the last incremental backup.

Restore: the whole process is divided into three steps, which can be understood as: first, pour the incremental backup data into the full backup, and then use the full backup to restore.

The first step is to redo the committed logs under all backup directories. Note that the last incremental backup does not have-- redo-only, and the data is finally in a full backup, such as:

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

BASE-DIR refers to the full directory, INCREMENTAL-DIR-1 refers to the first incremental backup, INCREMENTAL-DIR-2 refers to the second incremental backup, and so on. One thing to note here is that there is no redo-only option for the incremental backup in the last step! Also, you can use-- use_memory to improve performance.

After the above statement is executed successfully, the final data is under BASE-DIR (that is, full directory).

Step 2: roll back the outstanding logs:

Innobackupex-apply-log BASE-DIR

After the above execution, the backup files in BASE-DIR are fully ready.

The last step: copy:

Innobackupex-copy-back BASE-DIR

Similarly, after the copy is finished, remember to check that the permissions of the data directory are correct.

Common mistakes and their 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 found

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

This is the end of the content of "innobackupex online backup and recovery". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.