mysql之 innobackupex备份+binlog日志的完全恢复（命令行执行模式）-白红宇

前言：

MySQL的完全恢复，我们可以借助于完整的备份+binlog 来将数据库恢复到故障点。

备份可以是热备与逻辑备份(mysqldump)，只要备份与binlog是完整的，都可以实现完全恢复。

1. 准备实验环境

mysql> select version();

+------------+

| version() |

+------------+

| 5.6.25-log |

+------------+

1 row in set (0.00 sec)

mysql> create database com_rec;

Query OK, 1 row affected (0.00 sec)

mysql> use inc_rec;

Database changed

mysql> create table andy (id int);

Query OK, 0 rows affected (0.08 sec)

mysql> insert into andy values(1),(2);

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

2. 全备

[root@mysql02 full]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=oracle --port=3606 /xtrabackup/full/

xtrabackup: Transaction log of lsn (1662519) to (1662519) was copied.

170609 17:34:34 completed OK!

3. 查看全备生成文件

[root@mysql02 full]# ll /xtrabackup/full/

total 4

drwxr-x---. 6 root root 4096 Jun 9 17:34 2017-06-09_17-34-30

4. 模拟业务新数据

mysql> insert into andy values(3),(4);

Query OK, 2 rows affected (0.14 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

5. 增量备份

-- 创建存放增量备份目录并赋权

[root@mysql02 full]# mkdir -p /xtrabackup/incr/

[root@mysql02 full]# chown -R mysql:mysql /xtrabackup/incr/

[root@mysql02 full]# ll /xtrabackup/

total 8

drwxr-xr-x. 3 mysql mysql 4096 Jun 9 03:53 full

drwxr-xr-x. 2 mysql mysql 4096 Jun 9 04:00 incre

-- 正式开始增量备份

[root@mysql02 full]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=oracle --incremental \

--incremental-basedir=/xtrabackup/full/2017-06-09_17-34-30/ /xtrabackup/incr/

########################################下面是增量备份输出

。。。省略

xtrabackup: Transaction log of lsn (1665808) to (1665808) was copied.

170609 17:36:46 completed OK!

6. 再模拟新业务，该记录在保存在binlog，而不会存在于任何备份，这条记录用于验证完全恢复

mysql> insert into andy values(5);

Query OK, 1 row affected (0.00 sec)

7. 记下操作后的 position 点

mysql> show master status;

+---------------+----------+--------------+------------------+-------------------+

+---------------+----------+--------------+------------------+-------------------+

| binlog.000005 | 1104 | | | |

+---------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

8. 切换binlog日志

mysql> flush logs;

Query OK, 0 rows affected (0.00 sec)

9. 使用binlog events命令来查看我们最后insert的一条记录

mysql> show binlog events in 'binlog.000005';

+---------------+------+-------------+-----------+-------------+-----------------------------------------------+

+---------------+------+-------------+-----------+-------------+-----------------------------------------------+

| binlog.000005 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.25-log, Binlog ver: 4 |

| binlog.000005 | 120 | Query | 1 | 221 | drop database inc_rec |

| binlog.000005 | 221 | Query | 1 | 324 | create database com_rec |

| binlog.000005 | 324 | Query | 1 | 430 | use `com_rec`; create table andy (id int) |

| binlog.000005 | 430 | Query | 1 | 515 | BEGIN |

| binlog.000005 | 515 | Query | 1 | 625 | use `com_rec`; insert into andy values(1),(2) |

| binlog.000005 | 625 | Xid | 1 | 656 | COMMIT /* xid=67 */ |

| binlog.000005 | 656 | Query | 1 | 741 | BEGIN |

| binlog.000005 | 741 | Query | 1 | 851 | use `com_rec`; insert into andy values(3),(4) |

| binlog.000005 | 851 | Xid | 1 | 882 | COMMIT /* xid=81 */ |

| binlog.000005 | 882 | Query | 1 | 967 | BEGIN |

| binlog.000005 | 967 | Query | 1 | 1073 | use `com_rec`; insert into andy values(5) |

| binlog.000005 | 1073 | Xid | 1 | 1104 | COMMIT /* xid=96 */ |

| binlog.000005 | 1104 | Rotate | 1 | 1148 | binlog.000006;pos=4 |

+---------------+------+-------------+-----------+-------------+-----------------------------------------------+

14 rows in set (0.00 sec)

10. 查看binlog的位置与 datadir 的位置，防止 mv datadir时误操作 binlog ，影响恢复（binlog与datadir一定要分开放置）

mysql> show variables like '%log_bin%';

+---------------------------------+------------------------------------+

| Variable_name | Value |

+---------------------------------+------------------------------------+

| log_bin | ON |

| log_bin_basename | /data/mysql/binarylog/binlog |

| sql_log_bin | ON |

+---------------------------------+------------------------------------+

6 rows in set (0.00 sec)

mysql> show variables like '%datadir%';

+---------------+--------------+

| Variable_name | Value |

+---------------+--------------+

| datadir | /data/mysql/ |

+---------------+--------------+

1 row in set (0.00 sec)

11. 恢复

11.1 先做基于全备的apply，注意，此时使用了--redo-only

[root@mysql02 full]# innobackupex --defaults-file=/etc/my.cnf --user=root --apply-log --redo-only /xtrabackup/full/2017-06-09_17-34-30/

170609 04:19:30 completed OK!

11.2 在恢复增量备份集： --此时没有--redo-only，如果有多个增备，仅仅最后一个增备无需指定--redo-only

[root@mysql02 full]# innobackupex --defaults-file=/etc/my.cnf --user=root --apply-log /xtrabackup/full/2017-06-09_17-34-30/ --incremental-dir=/xtrabackup/incr/2017-06-09_17-36-39/

170609 04:24:45 completed OK! #结果出现completed OK表示完全成功

说明： /xtrabackup/full/2017-06-09_17-34-30/ 为全备基目录， incremental-dir 为增量备份目录

12. 关闭要恢复的实例

[root@mysql02 data]# /etc/init.d/mysql stop -p3306

netstat -nltp|grep mysql|grep 3606

13.将原有文件夹重命名到新位置，并创建原文件夹

[root@mysql02 full]# mv /data/mysql /data/mysqlbak

[root@mysql02 full]# mkdir -p /data/mysql

14.执行拷贝恢复的文件到原来的数据位置

[root@mysql02 full]# innobackupex --defaults-file=/etc/my.cnf --user=root --copy-back /xtrabackup/full/2017-06-09_17-34-30/

170609 04:33:06 completed OK! #结果出现completed OK表示完全成功

说明： /xtrabackup/full/2017-06-09_17-34-30/ 为全备基目录

15. 权限修改

[root@mysql02 ~]# mkdir -p /data/mysql/binarylog （说明：这里我binlog在datadir在路径下，所以要单独为binlog创建目录）

-- 将binlog日志mv回原位置（如果binlog不在datadir下，就不用操作）

[root@mysql02 xtrabackup]# ll /data/mysqlbak/binarylog/

total 28

-rw-rw----. 1 mysql mysql 164 Jun 9 06:12 binlog.000001

-rw-rw----. 1 mysql mysql 386 Jun 9 06:14 binlog.000002

-rw-rw----. 1 mysql mysql 143 Jun 9 06:53 binlog.000003

-rw-rw----. 1 mysql mysql 143 Jun 9 07:35 binlog.000004

-rw-rw----. 1 mysql mysql 1148 Jun 9 17:45 binlog.000005

-rw-rw----. 1 mysql mysql 143 Jun 9 17:48 binlog.000006

-rw-rw----. 1 mysql mysql 216 Jun 9 17:45 binlog.index

[root@mysql02 data]# mv /data/mysqlbak/binarylog/* /data/mysql/binarylog/

[root@mysql02 xtrabackup]# ll /data/mysql/binarylog/

total 28

-rw-rw----. 1 mysql mysql 164 Jun 9 06:12 binlog.000001

-rw-rw----. 1 mysql mysql 386 Jun 9 06:14 binlog.000002

-rw-rw----. 1 mysql mysql 143 Jun 9 06:53 binlog.000003

-rw-rw----. 1 mysql mysql 143 Jun 9 07:35 binlog.000004

-rw-rw----. 1 mysql mysql 1148 Jun 9 17:45 binlog.000005

-rw-rw----. 1 mysql mysql 143 Jun 9 17:48 binlog.000006

-rw-rw----. 1 mysql mysql 216 Jun 9 17:45 binlog.index

[root@mysql02 data]# chown -R mysql:mysql /data/mysql

16. 启动恢复后的实例

mysqld_safe --defaults-file=/etc/my.cnf &

17. 登录检查

[root@mysql02 ~]# mysql -uroot -poracle

mysql> use com_rec

mysql> select * from andy;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

| 4 | > 恢复成功，但是没有恢复到最新，缺少 id=5 , 'Inbinlog'记录并没有被恢复。

+------+

18. 使用binlog做完全恢复

[root@mysql02 ~]# cd /xtrabackup/incr/2017-06-09_17-36-39/

--从innobackupex获得binlog的位置

[root@mysql02 2017-06-09_17-36-39]# more xtrabackup_binlog_info

binlog.000005 882

--使用mysqlbinlog 追加的最新

[root@mysql02 2017-06-09_17-36-39]# mysqlbinlog --start-position=882 --stop-position=1104 /data/mysql/binarylog/binlog.000005 | mysql -uroot -poracle

补充：

这样做确实可以，而且row模式的binlog，也可以通过这种方式来执行。但是这样做有几个缺点

a. 如果解析出来的binlog在执行的过程中报错，如何处理？直接加 -f 强制执行吗？

b. 执行中途如何停下来，下次接着跑？比如我想调整一下MySQL的参数（需要重启）后继续跑？

c. 只能单线程执行。而且mysqlbinlog解析再通过管道执行，有比较高的性能开销。

19. 验证，可以看到最后一条记录以及被恢复

mysql> select * from andy;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

| 5 |

+------+

5 rows in set (0.00 sec)

总结

a、对于完全恢复，我们需要使用备份加binlog两者结合的方式来实现

b、在恢复期间，首先使用带read-only的apply-log方式来prepare全备

c、接下来使用带read-only的apply-log方式来prepare增备，仅最后一个增备可以不用read-only

d、停止原有实例，并copy-back后启动恢复后的实例

e、从Innobakcupex备份信息中获取最后的binlog日志及位置信息

f、使用mysqlbinlog方式将日志追加到最新时刻

文章可以转载，必须以链接形式标明出处。

本文转自张冲andy 博客园博客，原文链接： http://www.cnblogs.com/andy6/p/6970837.html

，如需转载请自行联系原作者