（点击
上方公众号
，可快速关注）
来源：伯乐在线专栏作者 - 曹单锋
如有好文章投稿，请点击 → 这里了解详情
如需转载，发送「转载」二字查看说明
DBA或开发人员，有时会误删或者误更新数据，如果是线上环境并且影响较大，就需要能快速回滚。传统恢复方法是利用备份重搭实例，再应用去除错误sql后的binlog来恢复数据。此法费时费力，甚至需要停机维护，并不适合快速回滚。也有团队利用LVM快照来缩短恢复时间，但快照的缺点是会影响mysql的性能。
MySQL闪回(flashback)利用binlog直接进行回滚，能快速恢复且不用停机。本文将介绍闪回原理，给出笔者的实战经验，并对现存的闪回工具作比较。
开胃菜
某天，小明因种种原因，误删了大批线上用户表的数据。他急忙找到公司DBA请求帮助，“客服电话已被打爆，大量用户投诉无法登陆，领导非常恼火。请问多久能恢复数据？”DBA一脸懵逼，沉默十秒后，伸出一根手指。“你的意思是一分钟就能恢复？太好了。”小明终于有些放松，露出了一丝笑容。“不，我们中有个人将会离开公司。”DBA沉痛的说道。
勿让悲剧发生，尽早将此文转给公司DBA。
闪回原理
binlog概述
MySQL binlog以event的形式，记录了MySQL server从启用binlog以来所有的变更信息，能够帮助重现这之间的所有变化。MySQL引入binlog主要有两个目的：一是为了主从复制；二是某些备份还原操作后需要重新应用binlog。
有三种可选的binlog格式，各有优缺点：
-
statement：基于SQL语句的模式，binlog数据量小，但是某些语句和函数在复制过程可能导致数据不一致甚至出错；
-
row：基于行的模式，记录的是行的完整变化。很安全，但是binlog会比其他两种模式大很多；
-
mixed：混合模式，根据语句来选用是statement还是row模式；
利用binlog闪回，需要将binlog格式设置为row。row模式下，一条使用innodb的insert会产生如下格式的binlog：
# at 1129
#161225 23:15:38 server id 3773306082 end_log_pos 1197 Query thread_id=1903021 exec_time=0 error_code=0
SET
TIMESTAMP
=
1482678938
/*!*/
;
BEGIN
/*!*/
;
# at 1197
#161225 23:15:38 server id 3773306082 end_log_pos 1245 Table_map: `test`.`user` mapped to number 290
# at 1245
#161225 23:15:38 server id 3773306082 end_log_pos 1352 Write_rows: table id 290 flags: STMT_END_F
BINLOG
'
muJfWBPiFOjgMAAAAN0EAAAAACIBAAAAAAEABHRlc3QABHVzZXIAAwMPEQMeAAAC
muJfWB7iFOjgawAAAEgFAAAAACIBAAAAAAEAAgAD//gBAAAABuWwj+i1tVhK1hH4AgAAAAblsI/p
krFYStYg+AMAAAAG5bCP5a2ZWE/onPgEAAAABuWwj+adjlhNeAD4BQAAAAJ0dFhRYJM=
'
/*!*/
;
# at 1352
#161225 23:15:38 server id 3773306082 end_log_pos 1379 Xid = 5327954
COMMIT
/*!*/
;
闪回原理
既然binlog以event形式记录了所有的变更信息，那么我们把需要回滚的event，从后往前回滚回去即可。
对于单个event的回滚，我们以表test.user来演示原理
mysql
>
show create table
test
.
user
\
G
*
**************************
1.
row *
**************************
Table
:
user
Create
Table
:
CREATE
TABLE
`
user
`
(
`
id
`
int
(
11
)
NOT
NULL
AUTO_INCREMENT
,
`
name
`
varchar
(
10
)
DEFAULT
NULL
,
PRIMARY KEY
(`
id
`)
)
ENGINE
=
InnoDB
AUTO_INCREMENT
=
6
DEFAULT
CHARSET
=
utf8
对于delete操作，我们从binlog提取出delete信息，生成的回滚语句是insert。(注：为了方便解释，我们用binlog2sql将原始binlog转化成了可读SQL)
原始：
DELETE
FROM
`
test
`.`
user
`
WHERE
`
id
`
=
1
AND
`
name
`
=
'小赵'
;
回滚：
INSERT
INTO
`
test
`.`
user
`(`
id
`,
`
name
`)
VALUES
(
1
,
'小赵'
);
对于insert操作，回滚SQL是delete。
原始：
INSERT
INTO
`
test
`.`
user
`(`
id
`,
`
name
`)
VALUES
(
2
,
'小钱'
);
回滚：
DELETE
FROM
`
test
`.`
user
`
WHERE
`
id
`
=
2
AND
`
name
`
=
'小钱'
;
对于update操作，回滚sql应该交换SET和WHERE的值。
原始：
UPDATE
`
test
`.`
user
`
SET
`
id
`
=
3
,
`
name
`
=
'小李'
WHERE
`
id
`
=
3
AND
`
name
`
=
'小孙'
;
回滚：
UPDATE
`
test
`.`
user
`
SET
`
id
`
=
3
,
`
name
`
=
'小孙'
WHERE
`
id
`
=
3
AND
`
name
`
=
'小李'
;
闪回实战
真实的闪回场景中，最关键的是能快速筛选出真正需要回滚的SQL。
我们使用开源工具binlog2sql来进行实战演练。binlog2sql由美团点评DBA团队(上海)出品，多次在线上环境做快速回滚。
首先我们安装binlog2sql：
shell
>
git
clone
https
:
//github.com/danfengcao/binlog2sql.git && cd binlog2sql
shell
>
pip
install
-
r
requirements
.
txt
背景：小明在11:44时误删了test库user表大批的数据，需要紧急回滚。
test
库
user
表原有数据
mysql
>
select *
from
user
;
+----+--------+---------------------+
|
id
|
name
|
addtime
|
+----+--------+---------------------+
|
1
|
小赵
|
2013
-
11
-
11
00
:
04
:
33
|
|
2
|
小钱
|
2014
-
11
-
11
00
:
04
:
48
|
|
3
|
小孙
|
2016
-
11
-
11
20
:
25
:
00
|
|
4
|
小李
|
2013
-
11
-
11
00
:
00
:
00
|
.........
+----+--------+---------------------+
16384
rows
in
set
(
0.04
sec
)
11
:
44
时，
user
表大批数据被误删除。与此同时，正常业务数据是在继续写入的
mysql
>
delete from user where
addtime
>
'2014-01-01'
;
Query
OK
,
16128
rows affected
(
0.18
sec
)
mysql
>
select count
(
*
)
from
user
;
+----------+
|
count
(
*
)
|
+----------+
|
261
|
+----------+
恢复数据步骤：
登录mysql，查看目前的binlog文件
mysql
>
show master
logs
;
+------------------+-----------+
|
