mysql闪回实战-binlog2sql

--如果想下载相应的word文档的话，则可以点击这里下载。

一：闪回原理

binlog以event形式记录了所有的变更信息，因此我们把需要回滚的event，从后往前回滚回去即可。对于delete操作，我们从binlog提取出delete信息，生成的回滚语句是insert。

真实的闪回场景中，最关键的是能快速筛选出真正需要回滚的SQL。

我们使用开源工具binlog2sql来进行实战演练。

二：环境搭建

2.1 前提条件

MySQL server必须设置以下参数:

[mysqld]

server_id = 1

log_bin = /var/log/mysql/mysql-bin.log

max_binlog_size = 1G

binlog_format = row

binlog_row_image = full

已测试环境

Python 2.7, 3.4+

MySQL 5.6, 5.7

pip 9.0.1

2.2 安装binlog2sql

2.2.1 下载binlog2sql

有两种方式可选。

如果服务器不能联网，则选择第二种方式（先在本地电脑下载安装包，再上传到服务器）。

2.2.1.1 联网安装

shell> git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql

2.2.1.2 下载上传安装包

点击https://download.csdn.net/download/YABIGNSHI/11986031

下载binlog2sql-master.zip压缩包

unzip binlog2sql-master.zip

mv binlog2sql-master binlog2sql

cd binlog2sql

2.2.2 安装binlog2sql

shell> pip install -r requirements.txt

#假如报错：No package pip available，需要安装pip：

方式1:

yum install python-pip，pip install --upgrade pip

方式2：

进入网站https://pypi.python.org/pypi/pip下载

解压tar -xvf 安装包

cd 加压后的目录

python setup.py install

假如执行pip install -r requirements.txt报错：

mportError: No module named pkg_resources

这个问题通常是由于升级到python2.7后执行pip产生的，解决方案是重新在python2.7环境中安装pip。

假如执行pip install -r requirements.txt报错：

Could not find a version that satisfies the requirement pymysql (from versions: )

No matching distribution found for pymysql

需要安装pymysql。

安装步骤可以参考：https://blog.csdn.net/SuperBoy_Liang/article/details/89335906

或http://note.youdao.com/noteshare?id=6171e890e57fac58c46051d62911dc80&sub=701C7745ACA84254A21C4D349367F295

三 相关参数介绍

3.1 mysql连接配置

-h host; -P port; -u user; -p password

3.2 解析模式

--stop-never 持续解析binlog。可选。默认False，同步至执行命令时最新的binlog位置。

-K, --no-primary-key 对INSERT语句去除主键。可选。默认False

-B, --flashback 生成回滚SQL，可解析大文件，不受内存限制。可选。默认False。与stop-never或no-primary-key不能同时添加。

--back-interval -B模式下，每打印一千行回滚SQL，加一句SLEEP多少秒，如不想加SLEEP，请设为0。可选。默认1.0。

3.3 解析范围控制

--start-file 起始解析文件，只需文件名，无需全路径 。必须。

--start-position/--start-pos 起始解析位置。可选。默认为start-file的起始位置。

--stop-file/--end-file 终止解析文件。可选。默认为start-file同一个文件。若解析模式为stop-never，此选项失效。

--stop-position/--end-pos 终止解析位置。可选。默认为stop-file的最末位置；若解析模式为stop-never，此选项失效。

--start-datetime 起始解析时间，格式'%Y-%m-%d %H:%M:%S'。可选。默认不过滤。

--stop-datetime 终止解析时间，格式'%Y-%m-%d %H:%M:%S'。可选。默认不过滤。

3.4 对象过滤

-d, --databases 只解析目标db的sql，多个库用空格隔开，如-d db1 db2。可选。默认为空。

-t, --tables 只解析目标table的sql，多张表用空格隔开，如-t tbl1 tbl2。可选。默认为空。

--only-dml 只解析dml，忽略ddl。可选。默认False。

--sql-type 只解析指定类型，支持INSERT, UPDATE, DELETE。多个类型用空格隔开，如--sql-type INSERT DELETE。可选。默认为增删改都解析。用了此参数但没填任何类型，则三者都不解析。

四 闪回实战

4.1 闪回步骤简单介绍

① 先根据库、表、时间做一次过滤，筛选出真正需要回滚的SQL。

如：

python /download/binlog2sql/binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -tuser --start-file='mysql-bin.000054' --start-datetime='2016-12-26 11:44:00' --stop-datetime='2016-12-26 11:50:00' > /tmp/raw.sql

备注：

--sql-type 只解析指定类型，支持INSERT, UPDATE, DELETE。多个类型用空格隔开，如--sql-type INSERT DELETE。可选。默认为增删改都解析。用了此参数但没填任何类型，则三者都不解析。

② 根据position进一步过滤

上一步会产生相关sql。sql后有相应的起始position.

③ 生成闪回语句

shell> python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -tuser --start-file='mysql-bin.000054'--start-position=257427 --stop-position=504272 -B > /tmp/rollback.sql

④：确认无误后，执行闪回语句

4.2 示例

背景：小明在11:44时误删了test库user表大批的数据，需要紧急回滚。

test库user表原有数据

mysql> select *from user;

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

| id | name | addtime |

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

| 1 | 小赵 | 2013-11-1100:04:33 |

| 2 | 小钱 | 2014-11-1100:04:48 |

| 3 | 小孙 | 2016-11-1120:25:00 |

| 4 | 小李 | 2013-11-1100:00:00 |

.........

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

16384rows inset(0.04sec)

11:44时，user表大批数据被误删除。与此同时，正常业务数据是在继续写入的

mysql> delete from user where addtime>'2014-01-01';

Query OK,16128rows affected(0.18sec)

mysql> select count(*)from user;

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

| count(*) |

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

| 261 |

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

恢复数据步骤：

登录mysql，查看目前的binlog文件

mysql> show master logs;

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

| Log_name | File_size |

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

| mysql-bin.000053 | 168652863 |

| mysql-bin.000054 | 504549 |

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

最新的binlog文件是mysql-bin.000054。我们的目标是筛选出需要回滚的SQL，由于误操作人只知道大致的误操作时间，我们首先根据时间做一次过滤。只需要解析test库user表。(注：如果有多个sql误操作，则生成的binlog可能分布在多个文件，需解析多个文件)

shell> Python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -tuser --start-file='mysql-bin.000054'--start-datetime='2016-12-26 11:44:00' --stop-datetime='2016-12-26 11:50:00' > /tmp/raw.sql

raw.sql输出：

DELETE FROM`test`.`user`WHERE`addtime`='2014-11-11 00:04:48'AND`id`=2AND`name`='小钱'LIMIT1;#start 257427 end 265754 time 2016-12-26 11:44:56

DELETE FROM`test`.`user`WHERE`addtime`='2015-11-11 20:25:00'AND`id`=3AND`name`='小孙'LIMIT1;#start 257427 end 265754 time 2016-12-26 11:44:56

...

DELETE FROM`test`.`user`WHERE`addtime`='2016-12-14 23:09:07'AND`id`=24530AND`name`='tt'LIMIT1;#start 257427 end 504272 time 2016-12-26 11:44:56

INSERT INTO`test`.`user`(`addtime`,`id`,`name`)VALUES('2016-12-10 00:04:33',32722,'小王');#start 504299 end 504522 time 2016-12-26 11:49:42

...

/*

注意：假如执行的时候报错：

Traceback (most recent call last):

File "binlog2sql/binlog2sql.py", line 125, in

binlog2sql.process_binlog()

File "binlog2sql/binlog2sql.py", line 70, in process_binlog

for binlogevent in stream:

File "/usr/lib/python2.6/site-packages/pymysqlreplication/binlogstream.py", line 370, in fetchone

pkt = self._stream_connection._read_packet()

File "/usr/lib/python2.6/site-packages/pymysql/connections.py", line 983, in _read_packet

packet.check_error()

File "/usr/lib/python2.6/site-packages/pymysql/connections.py", line 395, in check_error

err.raise_mysql_exception(self._data)

File "/usr/lib/python2.6/site-packages/pymysql/err.py", line 107, in raise_mysql_exception

raise errorclass(errno, errval)

pymysql.err.InternalError: (1236, u'Misconfigured master - server id was not set')

可以通过set global server_id=N;然后再次执行该语句即可。

*/

根据位置信息，我们确定了误操作sql来自同一个事务，准确位置在257427-504272之间(binlog2sql对于同一个事务会输出同样的start position)。再根据位置过滤，使用 -B 选项生成回滚sql，检查回滚sql是否正确。(注：真实场景下，生成的回滚SQL经常会需要进一步筛选。结合grep、编辑器等)

shell> python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -tuser --start-file='mysql-bin.000054'--start-position=257427 --stop-position=504272 -B > /tmp/rollback.sql

rollback.sql输出：

INSERT INTO`test`.`user`(`addtime`,`id`,`name`)VALUES('2016-12-14 23:09:07',24530,'tt');#start 257427 end 504272 time 2016-12-26 11:44:56

INSERT INTO`test`.`user`(`addtime`,`id`,`name`)VALUES('2016-12-12 00:00:00',24529,'小李');#start 257427 end 504272 time 2016-12-26 11:44:56

...

INSERT INTO`test`.`user`(`addtime`,`id`,`name`)VALUES('2014-11-11 00:04:48',2,'小钱');#start 257427 end 265754 time 2016-12-26 11:44:56

shell> wc -l /tmp/rollback.sql

16128 /tmp/rollback.sql

与业务方确认回滚sql没问题，执行回滚语句。登录mysql，确认回滚成功。

shell> mysql -h127.0.0.1 -P3306 -uadmin -p'admin' < /tmp/rollback.sql

mysql> select count(*)from user;

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

| count(*) |

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

| 16389 |

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

五 注意事项

① 无法回滚truncate table

② 无法回滚DDL

试验发现，若某个表被drop了，则该表在此之前的DML操作也将无法回滚（只能看到相应的建表删表语句，看不到DML语句）

③ 一次删除多条记录的delete sql会被生成N（N=被删除的记录条数）条delete sql（因为binlog日志里就是这样记录的），但是同一批操作里的start和end postion是一样的，不影响恢复。

示例：