使用XtraBackup工具实现MySQL数据备份及恢复

Xtrabackup是由percona提供的mysql数据库备份工具，支持对Innodb存储引擎的数据库在线热备份（备份时不影响数据读写), 它有以下特点：

(1)备份过程快速、可靠； (2)备份过程不会打断正在执行的事务； (3)能够基于压缩等功能节约磁盘空间和流量； (4)自动实现备份检验； (5)还原速度快；

本文使用的软件包为 percona-xtrabackup-24-2.4.7-2.el7.x86_64.rpm ，最新版的软件可从 http://www.percona.com/software/percona-xtrabackup/ 获得

为解决依赖性关系使用yum源安装

$ yum install -y ./percona-xtrabackup-24-2.4.7-2.el7.x86_64.rpm

本文的备份策略： 全量备份+增量备份+binlog

1. 使用root用户进行一次全量备份

$ cd /var/lib/mysql
$ innobackupex --user=root --host=localhost --password=123456 /data/backup/
...中间省略...
xtrabackup: Transaction log of lsn (310138946) to (310138946) was copied.
170915 20:31:03 completed OK!                                            <-- 显示备份成功

$ ls /data/backup
2017-09-16_08-22-55                                                      <-- 全量备份生成的文件夹

$ cd 2017-09-16_08-22-55/
$ ls
backup-my.cnf  ibdata1  performance_schema  xtrabackup_checkpoints  xtrabackup_logfile
database       mysql    testdb              xtrabackup_info
$

2. testdb库中创建新表tb3并添加数据

$ mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [testdb]> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| tb1              |
| tb2              |
+------------------+
2 rows in set (0.00 sec)

MariaDB [testdb]> create table tb3 (stuid int unsigned primary key,name varchar(200),age tinyint);
Query OK, 0 rows affected (0.07 sec)

MariaDB [testdb]> insert into tb3 values(1,'stu1',22),(2,'stu2',24),(3,'stu3',28);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [testdb]> select * from tb3;
+-------+------+------+
| stuid | name | age  |
+-------+------+------+
|     1 | stu1 |   22 |
|     2 | stu2 |   24 |
|     3 | stu3 |   28 |
+-------+------+------+
3 rows in set (0.00 sec)

3. 进行第一次增量备份

每个InnoDB的页面都会包含一个LSN(日志序列号)信息，每当相关的数据发生改变，相关的页面的LSN就会自动增长。这正是InnoDB表可以进行增量备份的基础，即innobackupex通过备份上次完全备份之后发生改变的页面来实现。增量备份仅能应用于InnoDB或XtraDB表，对于MyISAM表而言，执行增量备份时其实进行的是完全备份。

$ cd /var/lib/mysql
$ innobackupex --user=root --host=localhost --password=123456 --incremental /data/backup --incremental-basedir=/data/backup/2017-09-16_08-22-55

$ ls /data/backup
2017-09-16_08-22-55  
2017-09-16_08-27-37                                                    <-- 增量备份生成的文件夹

$ cat 2017-09-16_08-22-55/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 310144589
last_lsn = 310144589
compact = 0
recover_binlog_info = 0

$ cat 2017-09-16_08-27-37/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 310144589                                                  <-- 日志序列号承接全量备份的
to_lsn = 310148850
last_lsn = 310148850
compact = 0
recover_binlog_info = 0

4. 删除testdb库中tb2

$ mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement

MariaDB [(none)]> use testdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [testdb]> drop table tb2;
MariaDB [testdb]> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| tb1              |
| tb3              |
+------------------+
2 rows in set (0.00 sec)

5. 进行第二次增量备份

$ innobackupex --user=root --host=localhost --password=123456 --incremental /data/backup --incremental-basedir=/data/backup/2017-09-16_08-27-37
...中间省略...
xtrabackup: Transaction log of lsn (310149698) to (310149698) was copied.
170916 08:42:46 completed OK!

$ ls /data/backup
2017-09-16_08-22-55  2017-09-16_08-27-37  2017-09-16_08-42-43

$ cat 2017-09-16_08-42-43/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 310148850                                            <-- 日志序列号承接第一次增量备份的
to_lsn = 310149698
last_lsn = 310149698
compact = 0
recover_binlog_info = 0

6. 删除testdb库中tb1

$ mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use testdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [testdb]> drop table tb1;
Query OK, 0 rows affected (0.03 sec)
MariaDB [testdb]> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| tb3              |
+------------------+
1 row in set (0.00 sec)

7. 备份最近一次增量备份之后的二进制日志

$ cat 2017-09-16_08-42-43/xtrabackup_info 
uuid = f5036e11-9a77-11e7-abfa-00505626a77f
name = 
tool_name = innobackupex
tool_command = --user=root --host=localhost --password=... --incremental /data/backup --incremental-basedir=/data/backup/2017-09-16_08-27-37
tool_version = 2.4.7
ibbackup_version = 2.4.7
server_version = 5.5.52-MariaDB
start_time = 2017-09-16 08:42:43
end_time = 2017-09-16 08:42:46
lock_time = 0
binlog_pos = filename 'master-log.000001', position '828'           <--最后一次增量备份对应二进制日志位置
innodb_from_lsn = 310148850
innodb_to_lsn = 310149698
partial = N
incremental = Y
format = file
compact = N
compressed = N
encrypted = N

$ mysqlbinlog -j 828 /var/lib/mysql/master-log.000001 > /data/backup/2017-09-16_08-42-43-binlog

8 ) 模拟服务器故障并恢复

• 模拟故障并删除数据

$ service mariadb stop
$ rm -rf /var/lib/mysql/*

• prepare增量备份

需要在每个备份(包括完全和各个增量备份)上，将已经提交的事务进行“重放”。“重放”之后，所有的备份数据将合并到完全备份上。 基于所有的备份将未提交的事务进行“回滚”。

$ cd 2017-09-16_08-22-55/
$ innobackupex --apply-log --redo-only ./                  <-- 合并完成之前只重放不回滚
...中间省略...
InnoDB: Shutdown completed; log sequence number 310150212
170916 10:10:24 completed OK!

$ innobackupex --apply-log --redo-only ./ --incremental-dir=/data/backup/2017-09-16_08-27-37
$ cat 2017-09-16_08-22-55/xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 310148850                                          <-- 完全备份的lsn已经合并到第一次增量备份的lsn
last_lsn = 310148850
compact = 0
recover_binlog_info = 0

$ innobackupex --apply-log --redo-only ./ --incremental-dir=/data/backup/2017-09-16_08-42-43

$ innobackupex --apply-log ./                                      <-- 合并完成后进行回滚操作   

• 从完全备份中恢复数据

$ cd 2017-09-16_08-22-55/
$ innobackupex --copy-back ./

$ ls -l /var/lib/mysql
total 40984
drwxr-x--- 2 root root     4096 Sep 16 10:15 database
-rw-r----- 1 root root 18874368 Sep 16 10:15 ibdata1
-rw-r----- 1 root root  5242880 Sep 16 10:15 ib_logfile0
-rw-r----- 1 root root  5242880 Sep 16 10:15 ib_logfile1
-rw-r----- 1 root root 12582912 Sep 16 10:15 ibtmp1
drwxr-x--- 2 root root     4096 Sep 16 10:15 mysql
drwxr-x--- 2 root root     4096 Sep 16 10:15 performance_schema
drwxr-x--- 2 root root     4096 Sep 16 10:15 testdb
-rw-r----- 1 root root       24 Sep 16 10:15 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root      569 Sep 16 10:15 xtrabackup_info

$ chown -R mysql.mysql /var/lib/mysql/*                             <-- 修改数据目录属主属组

• 二进制日志还原

$ service mariadb start
$ mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| database           |
| mysql              |
| performance_schema |
| testdb             |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> use testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed 
MariaDB [testdb]> show tables;                                       <-- 已经恢复到最后一次增量备份的状态
+------------------+
| Tables_in_testdb |
+------------------+
| tb1              |
| tb3              |
+------------------+
2 rows in set (0.00 sec)

MariaDB [testdb]> set @@session.sql_log_bin=OFF;                     <-- 二进制日志重放无需记录日志
Query OK, 0 rows affected (0.01 sec)

MariaDB [testdb]> \. /data/backup/2017-09-16_08-42-43-binlog        <-- 根据故障前备份的二进制日志重放
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Charset changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

MariaDB [testdb]> show tables;                                    <-- 最后一次增量备份后的操作(drop tb2)已经重放
+------------------+
| Tables_in_testdb |
+------------------+
| tb3              |
+------------------+
1 row in set (0.00 sec)

MariaDB [testdb]> set @@session.sql_log_bin=ON;                     <-- 二进制日志记录开启
Query OK, 0 rows affected (0.00 sec)

至此，mysql服务已经实现数据恢复，可以上线；接下来应该立即更新备份即进行一次全量备份。