MySQL--------基于binlog实现闪回最佳实战

1. 背景

* 为了数据安全，搭建了主从。实时主从备份只能防止硬件问题，比如主库的硬盘损坏。但对于误操作，则无能为力。比如在主库误删一张表，或者一个update语句没有指定where条件，导致全表被更新。当操作被同步到从库上后，则主从都"回天无力"。

* 闪回用于快速恢复由于误操作丢失的数据。在DBA误操作时，可以把数据库恢复到以前某个时间点（或者说某个binlog的某个pos）。比如忘了带where条件的update、delete操作，传统的恢复方式是利用全备+二进制日志前滚进行恢复，相比于传统的全备+增备，flashback显然更为快速、简单。

2. 测试环境

mysql> show variables like 'version';+---------------+------------+| Variable_name | Value      |+---------------+------------+| version       | 5.6.36-log |+---------------+------------+1 row in set (0.03 sec)mysql> show variables like 'datadir';+---------------+--------------------+| Variable_name | Value              |+---------------+--------------------+| datadir       | /data/mysql_data6/ |+---------------+--------------------+1 row in set (0.00 sec)mysql> show variables like 'log_bin';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin       | ON    |+---------------+-------+1 row in set (0.00 sec)mysql> show variables like 'binlog_format';+---------------+-------+| Variable_name | Value |+---------------+-------+| binlog_format | ROW   |+---------------+-------+1 row in set (0.00 sec)mysql> show variables like 'binlog_row_p_w_picpath';+------------------+-------+| Variable_name    | Value |+------------------+-------+| binlog_row_p_w_picpath | FULL  |+------------------+-------+1 row in set (0.00 sec)

3. 闪回实战

　 * 创建数据库与测试表，并插入数据

mysql> create table users(    -> id BIGINT NOT NULL AUTO_INCREMENT,    -> name VARCHAR(255) NOT NULL,    -> sex ENUM('M', 'F') NOT NULL DEFAULT 'M',    -> age INT UNSIGNED NOT NULL DEFAULT '0',    -> PRIMARY KEY (id)    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;Query OK, 0 rows affected (0.04 sec)mysql> insert into users values(null, 'tom', 'M', 25), (null, 'jak', 'F', 32), (null, 'ses', 'M', 45), (null, 'lisea', 'M', 35);Query OK, 4 rows affected (0.13 sec)Records: 4  Duplicates: 0  Warnings: 0mysql> select * from users;+----+-------+-----+-----+| id | name  | sex | age |+----+-------+-----+-----+|  1 | tom   | M   |  25 ||  2 | jak   | F   |  32 ||  3 | ses   | M   |  45 ||  4 | lisea | M   |  35 |+----+-------+-----+-----+4 rows in set (0.00 sec)

* 下载闪回工具binlog2sql［ 由上海美团DBA团队出品 ］

[root@MySQL ~]# git clone https://github.com/danfengcao/binlog2sql.gitInitialized empty Git repository in /root/binlog2sql/.git/remote: Counting objects: 244, done.remote: Total 244 (delta 0), reused 0 (delta 0), pack-reused 244Receiving objects: 100% (244/244), 121.72 KiB | 27 KiB/s, done.Resolving deltas: 100% (124/124), done.

* 安装相关依赖

[root@MySQL ~]# yum install pip　-y[root@MySQL ~]# pip install --upgrade pip[root@MySQL ~]# pip install -r binlog2sql/requirements.txt

* 提前刷新binlog [ 测试中好区分文件 ]

mysql> flush logs;Query OK, 0 rows affected (0.02 sec)

* 查看当前binlog信息

mysql> show master status;+------------+----------+--------------+------------------+-------------------------------------------+| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |+------------+----------+--------------+------------------+-------------------------------------------+| bin.000006 |      191 |              |                  | c7f82640-6b2d-11e7-9316-000c29f0b169:1-22 |+------------+----------+--------------+------------------+-------------------------------------------+1 row in set (0.01 sec)

* 误操作，delete没带where条件

mysql> delete from users;Query OK, 4 rows affected (0.01 sec)

* binlog2sql工具通过文件输出操作信息，定位SQL开始位置与结束位置

　　 可通过--start-datetime与--stop-datetime定位时间

由此得到开始position为239,结束position为483

[root@MySQL ~]# python binlog2sql/binlog2sql/binlog2sql.py  -hlocalhost -P3306 -uroot -p'123' -dmytest -tusers --start-file='bin.000006' DELETE FROM `mytest`.`users` WHERE `age`=25 AND `sex`='M' AND `id`=1 AND `name`='tom' LIMIT 1; #start 239 end 483 time 2017-07-19 01:02:49DELETE FROM `mytest`.`users` WHERE `age`=32 AND `sex`='F' AND `id`=2 AND `name`='jak' LIMIT 1; #start 239 end 483 time 2017-07-19 01:02:49DELETE FROM `mytest`.`users` WHERE `age`=45 AND `sex`='M' AND `id`=3 AND `name`='ses' LIMIT 1; #start 239 end 483 time 2017-07-19 01:02:49DELETE FROM `mytest`.`users` WHERE `age`=35 AND `sex`='M' AND `id`=4 AND `name`='lisea' LIMIT 1; #start 239 end 483 time 2017-07-19 01:02:49

* binlog2sql通过flashback生成回滚SQL

[root@MySQL ~]# python binlog2sql/binlog2sql/binlog2sql.py -hlocalhost -P3306 -uroot -p'123' -dmytest -tusers --start-file='bin.000006' --start-position=239 --stop-position=483 -B > rollback.sql

* 导入回滚SQL [ 导入前检查SQL语句是否正常 ]

[root@MySQL ~]# mysql -hlocalhost -uroot -p'123' < rollback.sql

* 查看

mysql> select * from mytest.users;+----+-------+-----+-----+| id | name  | sex | age |+----+-------+-----+-----+|  1 | tom   | M   |  25 ||  2 | jak   | F   |  32 ||  3 | ses   | M   |  45 ||  4 | lisea | M   |  35 |+----+-------+-----+-----+4 rows in set (0.00 sec)

4. 总结

以需求驱动技术，技术本身没有优略之分，只有业务之分。