Percona-xtrabackup for database backup and disaster recovery

Directory

1. Introduction of tools

2. Tool installation

3. Backup strategy and prepare test data

4. Full backup data

5. Incremental backup Data

6. Disaster recovery

7. Summary

1. Introduction of tools

The Percona-xtrabackup package contains two tools, one is Xtrabackup, the other is Innobackupex,innobackupex is encapsulated by Per, The Xtraback tool is automatically called when the InnoDB table is backed up, so the actual backup of the InnoDB table is xtrabackup the tool, and Xtrabackup can only be backed up by InnoDB tables, a hot spare tool specifically developed for InnoDB, The tables for other engines such as MyISAM are backed up by Innobackupex, and if the full backup plus incremental scenario, the incremental Innobackupex tool is a full backup of the non-InnoDB table and a read lock is requested.

Xtrabackup a backup of the InnoDB table is no longer just a simple copy of the file, but instead uses the new and old LSN (log sequence number) in the InnoDB storage engine layer to identify whether the data page needs to be backed up.

Xtraback Tools for the InnoDB engine perfect support true hot backup, backup good data files and transaction log files due to the existence of InnoDB cache and other factors, so the backup good data and transaction log data is often inconsistent, so, In the case of data recovery, it is necessary to redo the committed transaction in the transaction log and not commit the transaction to undo, which is the preparatory work to do when the data is restored, that is, prepare.

2. Tool installation

System environment:

[Email protected] ~]# Cat/etc/issuecentos release 6.4 (Final) Kernel \ r on an \m[[email protected] software]# uname-r2.6 .32-358.el6.x86_64

Install dependent packages and Percona-xtrabackup:

[Email protected] ~]# yum-y install perl perl-devel libaio libaio-devel perl-time-hires perl-dbd-mysql[[email protected] ~]# RPM-IVH percona-xtrabackup-2.1.9-744.rhel6.x86_64.rpm #安装的2.1.9 version

By the way, Percona's tool set is installed:

[[email protected] ~]# yum-y install Perl-io-socket-ssl #percona-toolkit Dependency Package [[email protected] ~]# RPM-IVH percona-toolk it-2.2.13-1.noarch.rpm

3. Backup strategy and prepare test data

A scenario in which the first full backup plus incremental backup is used. When working with Xtrabackup on InnoDB tables, it is recommended that MySQL enable the "innodb_file_per_table=1" variable so that each table has its own tablespace, otherwise it is difficult to perform single-table backups and restores. It is also suggested that binary log files do not have to be in the same directory as the data files, and you do not want to lose the data when the binary logs are lost together.

Test data:

Mysql> select version (); +------------+| version ()   |+------------+| 5.5.36- log |+------------+1 row in set  (0.00 sec) mysql> show databases;   #创建了一个mydb1数据库 +--------------------+| database            |+--------------------+| information_schema | |  mydb1              | |  mysql              | |  performance_schema | |  test               |+-------- ------------+mysql> select * from mydb1.tb1;  #表中只有一条数据 +----+------+------+|  id | name | age  |+----+------+------+|  1 | tom   |   10 |+----+------+------+

To create a backup data storage directory:

[[email protected] ~]# Mkdir-pv/backup/{fullbackup,incremental} #fullbackup store full backup Data #incremental store incremental backup data

To create a replication user:

mysql> GRANT reload,lock tables,replication CLIENT on * * to ' bkuser ' @ ' localhost ' identified by ' 123456 ';mysql> flus H privileges;

4. Full backup data

[[email protected] ~]# innobackupex --user=bkuser --password=123456 /backup/ fullbackup/#最后出现 "150415 16:30:23  innobackupex: completed ok!" This information indicates that the backup is complete [[email protected] ~]# ls /backup/fullbackup/2015-04-15_16-30-19/backup-my.cnf   mysql                xtrabackup_binary       xtrabackup_logfileibdata1         performance_schema  xtrabackup_binlog_infomydb1           test                 xtrabackup_checkpoints[[email protected] ~]# cat /backup/ fullbackup/2015-04-15_16-30-19/xtrabackup_checkpointsbackup_type = full-backupedfrom_lsn =  0to_lsn = 1644877last_lsn = 1644877compact = 0 

5. Incremental backup Data

Make some data changes first:

Mysql> INSERT into Mydb1.tb1 (name,age) VALUES (' Jack ', ' a ');mysql> SELECT * from TB1; #增加一条数据 +----+------+------+| ID | name |  Age |+----+------+------+| 1 |   Tom |  10 | | 2 |   Jack | |+----+------+------+

Make the first incremental backup:

[[email protected] ~]# innobackupex --user=bkuser --password=123456 -- incremental /backup/incremental/ --incremental-basedir=/backup/fullbackup/2015-04-15_16-30-19/[[ email protected] ~]# ls /backup/incremental/2015-04-15_16-42-00/backup-my.cnf   mydb1               test                      xtrabackup_checkpointsibdata1.delta  mysql                xtrabackup_binary        Xtrabackup_logfileibdata1.meta   performance_schema  xtrabackup_binlog_info[[email  protected] ~]# cat /backup/incremental/2015-04-15_16-42-00/xtrabackup_checkpointsbackup_ type = incrementalfrom_lsn = 1644877   #这是全备时的 "TO_LSN" value to_lsn = 1645178last_lsn = 1645178compact = 0 

Then do the data modification:

Mysql> INSERT into Mydb1.tb1 (name,age) VALUES (' Jason ', ' Max ');mysql> SELECT * from tb1;+----+-------+------+| ID | name |  Age |+----+-------+------+| 1 |   Tom |  10 | | 2 |   Jack |  20 | | 3 |   Jason | |+----+-------+------+

Make a second incremental backup:

[[email protected] ~]# innobackupex --user=bkuser --password=123456 -- Incremental /backup/incremental/ --incremental-basedir=/backup/incremental/2015-04-15_16-42-00/#这里的 " --incremental-basedir "is the directory that points to the first incremental backup [[email protected] ~]# ls /backup/incremental/ 2015-04-15_16-49-07/backup-my.cnf  mydb1                test                     xtrabackup_checkpointsibdata1.delta  mysql                xtrabackup_binary        xtrabackup_logfileibdata1.meta   performance_schema   xtrabackup_binlog_info[[email protected] ~]# cat /backup/incremental/2015-04-15_ 16-49-07/xtrabackup_checkpointsbackup_type = incrementalfrom_lsn = 1645178  #这是第一次增量的 "TO_LSN" value TO_LSN = 1645463LAST_LSN  = 1645463compact = 0

Let's do a data modification again to demonstrate a point-in-time recovery based on the binary log:

Mysql> INSERT into Mydb1.tb1 (name,age) VALUES (' Lucky ', ' Max ');mysql> SELECT * from tb1;+----+-------+------+| ID | name |  Age |+----+-------+------+| 1 |   Tom |  10 | | 2 |   Jack |  20 | | 3 |   Jason |  30 | | 4 |   Lucky | |+----+-------+------+

6. Disaster recovery

6.1. Data destruction

[[Email protected] ~]# service mysqld stopshutting down MySQL. success! [Email protected] ~]# rm-rf/mnt/mydata/data/* #把数据目录数据全部删除

My understanding of Xtrabackup for data recovery:

Xtrabackup Do data recovery and mysqldump such a logical backup work is very different from the recovery, xtrabackup for data recovery needs to consolidate each incremental data backup and full backup data, the consolidation of each incremental backup can only be committed transactions to replay ( Redo), the data recovery of the consolidated backup can only do redo operations, the various increments are merged and then the uncommitted transactions are rolled back (undo), after the incremental backup, the full backup of the "xtrabackup_checkpoints" file "Last_lsn" Should be the value at the time of the last incremental backup, which is the process of preparing for the redo (prepare) before recovering the data.

While really doing data recovery, it is recommended that the full and incremental backup files are copied one copy, to avoid errors caused by the backup file damage.

6.2. Preparation of data Recovery (prepare) work

[Email protected] ~]# Innobackupex--apply-log--redo-only/backup/fullbackup/2015-04-15_16-30-19/#准备全备份的数据 [[Email Protected] ~]# Innobackupex--apply-log--redo-only/backup/fullbackup/2015-04-15_16-30-19/--incremental-dir=/ Backup/incremental/2015-04-15_16-42-00/#准备第一次增量数据 [[email protected] ~]# Innobackupex--apply-log--redo-only/backup /fullbackup/2015-04-15_16-30-19/--incremental-dir=/backup/incremental/2015-04-15_16-49-07/#准备第二次增量数据 [[Email Protected] ~]# Cat/backup/fullbackup/2015-04-15_16-30-19/xtrabackup_checkpointsbackup_type = full-preparedfrom_lsn = 0TO_LSN = 1645463 #这是最后一次增量备份的位置, to which the data has all been merged last_lsn = 1645463compact = 0

6.3. Real Data recovery

[[email protected] ~]# innobackupex --copy-back  /backup/fullbackup/2015-04-15_16-30-19/   #仅一个 "--copy-back" option [[email protected] ~]#  ls /mnt/mydata/data/ibdata1  mydb1  mysql  performance_schema   test[[email protected] ~]# chown -R mysql.mysql /mnt/mydata/data/    #修改恢复数据的属主与属组 [[Email protected] ~]# service mysqld startstarting mysql..  SUCCESS!mysql> SELECT * FROM mydb1.tb1;   #检查数据 +----+-------+------+|  id | name  | age  |+----+-------+------+|  1 |  tom   |   10 | |   2 | jack  |   20 | |   3 | jason |   30 |+----+-------+------+ #lucky信息还没有恢复 

6.4, using binary log files based on point-in-time recovery

To view binary log files and position information at the last incremental backup:

[[email protected] ~]# cat/backup/incremental/2015-04-15_16-49-07/xtrabackup_binlog_infomysql-bin.0000311665 [[ Email protected] ~]# Mysqlbinlog--start-position=1665/var/log/mysql_log/mysql-bin.000031 >/tmp/position.sql# To export the SQL operation after the last incremental backup with the Mysqlbinlog tool

Import data:

Mysql> source/tmp/position.sqlmysql> SELECT * from mydb1.tb1;+----+-------+------+| ID | name |  Age |+----+-------+------+| 1 |   Tom |  10 | | 2 |   Jack |  20 | | 3 |   Jason |  30 | | 4 |   Lucky | |+----+-------+------+ #数据全部恢复

7. Summary

The Innobackupex tool enables full backup of the database in scenarios where there is a hybrid storage engine, and when the InnoDB table is backed up Innobackupex the script internally

Automatically call the Xtrabackup tool for backup operations, the data restore should stop the MySQL instance, empty the data directory, and then do prepare preparation, the second step is the real data restore (--copy-back), Do not forget to modify the data directory permissions after the data is restored to the data catalog.

Of course Innobackupex tools can also be implemented to the Library single table export, export, if the InnoDB table, it is recommended to use the Xtrabackup tool directly, so more intuitive to use the various options for backup.

There is a question:

When doing a full backup, Innobackupex will back up the non-InnoDB table, and when the output is from the Innobackupex backup, it will "150412 23:17:22 innobackupex:starting to lock all tables ... "Lock all tables, don't know if" lock all Tables "here includes the InnoDB table? If it does, that Xtrabackup is not really a hot backup tool.

This article is from the "knowledge needs summary and records" blog, please be sure to keep this source http://zhaochj.blog.51cto.com/368705/1633254

Percona-xtrabackup for database backup and disaster recovery