MySQL 备份和恢复（三）Xtrabackup实现热备：完全备份+增量备份

影者东升

　　
　　Percona XtraBackup is the world’s only open-source, free MySQL hot backup software that performs non-blocking backups for InnoDB and XtraDB databases. With Percona XtraBackup, you can achieve the following benefits:

• 　　Backups that complete quickly and reliably
  
• 　　Uninterrupted transaction processing during backups
  
• 　　Savings on disk space and network bandwidth
  
• 　　Automatic backup verification
  
• 　　Higher uptime due to faster restore time
  

　　以上摘自官方文档对Xtrabackup的描述。
　　

　　Xtrabackup是Percona公司的开源mysql热备软件，软件自身支持完全备份和增量备份，功能强大，使用简单，且备份结束会自动检查备份的可用性。对于InnoDB和XtraDB可以实现无阻塞的备份。
　　

　　Xtrabackup会在备份目录下自动生成以当前日期和时间为名的目录，目录下包含当前备份所有数据文件和丰富的当前备份时的状态信息。
　　

　　

　　实例演示：
　　xtrabackup的安装，安装包可从官网下载http://www.percona.com/software/percona-xtrabackup，官方提供了二进制和源码包，根据自己需要下载

/* 下载官方的rpm包安装，xtrabackup要依赖perl-DBD-mysql包，不想手动解决依赖关系可以yum localinstall安装 */
[root@console ~]# yum localinstall --nogpgcheck percona-xtrabackup-2.1.4-656.rhel6.x86_64.rpm -y
/* xtrabackup安装后生成的文件不多 */
[root@console ~]# rpm -ql percona-xtrabackup
/usr/bin/innobackupex    /* 备份时用的命令,会根据mysql版本自动调用xtrabackup_{55,56} */
/usr/bin/innobackupex-1.5.1   
/usr/bin/xbcrypt    /* 提供备份过程加密支持 */
/usr/bin/xbstream    /* 支持流式备份 */
/usr/bin/xtrabackup
/usr/bin/xtrabackup_55  / * 这个和下面那个才是备份过程实际调用的备份程序 */
/usr/bin/xtrabackup_56
/usr/share/doc/percona-xtrabackup-2.1.4
/usr/share/doc/percona-xtrabackup-2.1.4/COPYING  /* 文档只有一个软件授权文件，没有man文档，不过--help给出的帮助信息也比较丰富 */

　　备份过程：

/* 在库db1中准备一张表tb1，做效果比较用 */
MariaDB [(none)]> CREATE DATABASE db1;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> USE db1
Database changed
MariaDB [db1]> CREATE TABLE `tb1` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `Name` varchar(30) NOT NULL,   `Gender` enum('F','M','O') NOT NULL,   PRIMARY KEY (`id`) );
Query OK, 0 rows affected (0.22 sec)
MariaDB [db1]> DESC tb1;
+--------+-------------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra      |
+--------+-------------------+------+-----+---------+----------------+
| id   | int(11)       | NO   | PRI | NULL  | auto_increment |
| Name   | varchar(30)     | NO   |   | NULL  |        |
| Gender | enum('F','M','O') | NO   |   | NULL  |        |
+--------+-------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
MariaDB [db1]> INSERT INTO tb1 (Name,Gender) VALUES ('Tom','M'),('Jerry','F');
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0
MariaDB [db1]> SELECT * FROM tb1;
+----+-------+--------+
| id | Name  | Gender |
+----+-------+--------+
|  1 | Tom   | M    |
|  2 | Jerry | F    |
+----+-------+--------+
2 rows in set (0.00 sec)
/* 创建一个用于备份的最小权限的用户 */
MariaDB [(none)]> CREATE USER 'bakuser'@'localhost' IDENTIFIED BY 'backupass';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'bakuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> GRANT RELOAD,LOCK TABLES,REPLICATION CLIENT ON *.* TO 'bakuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

　　

　　现在先做一次完全备份：

/* 全备只需指定用于备份的用户名、密码和备份路径即可，最后出现innobackupex: completed OK! 则代表备份成功 */
[root@node1 ~]# innobackupex --user=bakuser --password=backupass /innobackup/
innobackupex: Backup created in directory '/innobackup/2014-07-31_09-27-36'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 1270
140731 09:27:39  innobackupex: Connection to database server closed
140731 09:27:40  innobackupex: completed OK!
/* 看一下备份都生成了哪些文件,除了数据库文件外还多了一些文件，注意由于我用root登陆系统，备份后的文件属主属组都是root，恢复后要改为运行mysqld进程的用户，不然mysql起不来 */
[root@node1 ~]# cd /innobackup/2014-07-31_09-27-36/
[root@node1 2014-07-31_09-27-36]# ll
total 18476
-rw-r--r--. 1 root root    260 Jul 31 09:27 backup-my.cnf /* 备份命令用到的配置选项信息 */
drwx------. 2 root root   4096 Jul 31 09:27 db1  
drwx------. 2 root root   4096 Jul 31 09:27 hellodb
-rw-r-----. 1 root root 18874368 Jul 31 09:27 ibdata1
drwx------. 2 root root   4096 Jul 31 09:27 mydb
drwxr-xr-x. 2 root root   4096 Jul 31 09:27 mysql
drwxr-xr-x. 2 root root   4096 Jul 31 09:27 performance_schema
drwxr-xr-x. 2 root root   4096 Jul 31 09:27 test
-rw-r--r--. 1 root root     13 Jul 31 09:27 xtrabackup_binary  /* 记录备份过程实际用的备份程序 */
-rw-r--r--. 1 root root     24 Jul 31 09:27 xtrabackup_binlog_info  /* 记录备份时的二进制日志文件和当前的位置 */
-rw-r-----. 1 root root     89 Jul 31 09:27 xtrabackup_checkpoints  /* 记录备份类型、
状态（是否prepared)、是否压缩、备份的LSN(Log Sequence Number日志序列号）范围等信息 */
-rw-r-----. 1 root root   2560 Jul 31 09:27 xtrabackup_logfile  /* xtrabackup自己的日志文件，新版本中不直接可见 */
[root@node1 2014-07-31_09-27-36]# cat backup-my.cnf 
# This MySQL options file was generated by innobackupex.
# The MySQL server
[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=5242880
innodb_fast_checksum=0
innodb_page_size=16384
innodb_log_block_size=512
[root@node1 2014-07-31_09-27-36]# cat xtrabackup_binary 
xtrabackup_55[root@node1 2014-07-31_09-27-36]# 
[root@node1 2014-07-31_09-27-36]# cat xtrabackup_binlog_info 
mysql-bin.0000011270
[root@node1 2014-07-31_09-27-36]# cat xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 1660869
last_lsn = 1660869
compact = 0
[root@node1 2014-07-31_09-27-36]# file xtrabackup_logfile 
xtrabackup_logfile: data

　　

　　全备完成后，对数据库做一些修改后再做一次增备：

/* 第一次增量备份,--incremental 备份类型为增量， --incremental-basedir 指定这次增备是相对哪一个备份做的增量,这里是相对全备  */
[root@node1 innobackup]# innobackupex --user=bakuser --password=backupass --incremental /innobackup/ --incremental-basedir=/innobackup/2014-07-31_09-27-36/
innobackupex: Backup created in directory '/innobackup/2014-07-31_11-51-19'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 1525
140731 11:51:48  innobackupex: Connection to database server closed
140731 11:51:48  innobackupex: completed OK!
/* 做些修改，做第二次增备 */
MariaDB [db1]> DELETE FROM tb1 WHERE Name='Tom';
Query OK, 1 row affected (0.09 sec)
MariaDB [db1]> UPDATE tb1 SET Gender='F' WHERE Name='Dongfang Bubai';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
MariaDB [db1]> SELECT * FROM tb1;
+----+----------------+--------+
| id | Name       | Gender |
+----+----------------+--------+
|  2 | Jerry      | F    |
|  3 | Bob      | M    |
|  4 | Dongfang Bubai | F    |
+----+----------------+--------+
3 rows in set (0.00 sec)
/* incremental-basedir 相对第一次增量的增量 */
[root@node1 innobackup]# innobackupex --user=bakuser --password=backupass --incremental /innobackup/ --incremental-basedir=/innobackup/2014-07-31_11-51-19/
innobackupex: Backup created in directory '/innobackup/2014-07-31_11-59-29'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 1922
140731 11:59:33  innobackupex: Connection to database server closed
140731 11:59:33  innobackupex: completed OK!
/* 第二次增备完成后，再做一些数据修改操作，以模拟实际生产环境数据库突然崩溃场景 */
MariaDB [db1]> INSERT INTO tb1 (Name,Gender) VALUES ('Kangshifu Guamian','O');
Query OK, 1 row affected (0.04 sec)
/* 将二进制日志备份出来一份，生产环境中二进制日志切记不要和数据文件放在一起，
最好不要在同一分区甚至同一物理磁盘, 以免一旦玉石俱焚，悔之晚矣 */
[root@node1 innobackup]# mysql -e 'SHOW MASTER STATUS;'
+------------------+----------+--------------+------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |   2168 |        |          |
+------------------+----------+--------------+------------------+
[root@node1 innobackup]# cp /mydata/data/
aria_log.00000001   hellodb/      ib_logfile1     mysql-bin.000001  node1.bob.org.pid
aria_log_control  ibdata1       mydb/         mysql-bin.index   performance_schema/
db1/        ib_logfile0     mysql/        node1.bob.org.err   test/
[root@node1 innobackup]# cp /mydata/data/mysql-bin.000001 /innobackup/
/* 人工让数据库彻底崩盘 */
[root@node1 innobackup]# service mysqld stop
Shutting down MySQL..                    [  OK  ]
[root@node1 innobackup]# rm -rf /mydata/data/*

　　恢复：
　　xtrabackup的恢复比较特殊，恢复前要先执行prepare过程，prepare主要完成把已提交的事务数据同步到数据文件，未完成的事务则回滚，这是对于只有一个完全备份来说。当还需要结合其他的增备时，则在些过程只需要将已提交的事务同步，未提交的事务则不用回滚，因为在本次备份时未提交的事务可能会在下一备份中已经提交，以避免这种无谓的劳动。

/* 注意 backup_type 和 to_lsn的变化 */
[root@node1 innobackup]# cat 2014-07-31_09-27-36/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 1660869
last_lsn = 1660869
compact = 0 
/* Prepare全备,--apply-log 指定要prepare, --redo-only 只处理已提交事务，未提交的事务则不回滚
 backup_type由full-backuped --> full-prepared */
[root@node1 innobackup]# innobackupex --apply-log --redo-only /innobackup/2014-07-31_09-27-36/
...
140731 15:05:53  innobackupex: completed OK!
[root@node1 innobackup]# cat 2014-07-31_09-27-36/xtrabackup_checkpoints 
backup_type = full-prepared
from_lsn = 0
to_lsn = 1660869
last_lsn = 1660869
compact = 0
/* prepare 增备1， --incremental-dir 增备的路径 */
[root@node1 innobackup]# innobackupex --apply-log --redo-only /innobackup/2014-07-31_09-27-36/ --incremental-dir /innobackup/2014-07-31_11-51-19/
/* 再来看full backup 的 to_lsn 已经 --> 增备1 的 to_lsn了，此时增量1也就退出舞台了 */
[root@node1 innobackup]# cat 2014-07-31_11-51-19/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 1660869
to_lsn = 1661993
last_lsn = 1661993
compact = 0
[root@node1 innobackup]# cat 2014-07-31_09-27-36/xtrabackup_checkpoints 
backup_type = full-prepared
from_lsn = 0
to_lsn = 1661993
last_lsn = 1661993
compact = 0
/* prepare 增备2，增量1已经prepare上去了，所以基准还是全备，--incremental-dir 指向增量2 */
[root@node1 innobackup]# innobackupex --apply-log /innobackup/2014-07-31_09-27-36/ --incremental-dir=/innobackup/2014-07-31_11-59-29/
/* 完事后再瞅一眼，全备的 to_lsn 已经 --> 增量2 的了 */
[root@node1 innobackup]# cat 2014-07-31_09-27-36/xtrabackup_checkpoints 
backup_type = full-prepared
from_lsn = 0
to_lsn = 1664255
last_lsn = 1664255
compact = 0
[root@node1 innobackup]# cat 2014-07-31_11-59-29/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 1661993
to_lsn = 1664255
last_lsn = 1664255
compact = 0

　　至此，恢复的预准备工作已完成，两个增备也完成了历史使命，恢复只要用全备的一个就可以了：

/* 恢复过程灰常简单，恢复过程也不需要启动mysql, 但是备份过程是一定要启动mysql的，因为要以备份用户身份连到Mysql上的嘛 */
[root@node1 innobackup]# innobackupex --copy-back /innobackup/2014-07-31_09-27-36/
/* 现在还不能启动mysql, 对了，属主属组还没改 */
[root@node1 innobackup]# ll /mydata/data/
total 18456
drwxr-xr-x. 2 root root   4096 Jul 31 15:54 db1
drwxr-xr-x. 2 root root   4096 Jul 31 15:54 hellodb
-rw-r--r--. 1 root root 18874368 Jul 31 15:54 ibdata1
drwxr-xr-x. 2 root root   4096 Jul 31 15:54 mydb
drwxr-xr-x. 2 root root   4096 Jul 31 15:54 mysql
drwxr-xr-x. 2 root root   4096 Jul 31 15:54 performance_schema
drwxr-xr-x. 2 root root   4096 Jul 31 15:54 test
[root@node1 innobackup]# chown -R mysql.mysql /mydata/data/*
/* 现在就可以启动了 。。 */
[root@node1 innobackup]# service mysqld start
Starting MySQL.....                    [  OK  ]
/* 正常启动，数据都在 */
MariaDB [db1]> SELECT * FROM tb1;
+----+----------------+--------+
| id | Name       | Gender |
+----+----------------+--------+
|  2 | Jerry      | F    |
|  3 | Bob      | M    |
|  4 | Dongfang Bubai | F    |
+----+----------------+--------+
3 rows in set (0.00 sec)

　　再加上最后的二进制日志恢复，整个恢复过程就全搞颠了

[root@node1 innobackup]# cat /innobackup/2014-07-31_11-59-29/xtrabackup_binlog_info 
mysql-bin.0000011922
[root@node1 innobackup]# mysqlbinlog --start-position=1922 mysql-bin.000001 > 11.sql
[root@node1 innobackup]# mysql < 11.sql 
MariaDB [(none)]> SELECT * FROM db1.tb1;
+----+-------------------+--------+
| id | Name        | Gender |
+----+-------------------+--------+
|  2 | Jerry       | F    |
|  3 | Bob         | M    |
|  4 | Dongfang Bubai  | F    |
|  5 | Kangshifu Guamian | O    |
+----+-------------------+--------+
4 rows in set (0.01 sec)
/* 二进制日志恢复，kangshifu guamian finnaly O 出锅了 */

　　

　　To be continued ...