1. 背景
* 为了数据安全，搭建了主从。实时主从备份只能防止硬件问题，比如主库的硬盘损坏。但对于误操作，则无能为力。比如在主库误删一张表，或者一个update语句没有指定where条件，导致全表被更新。当操作被同步到从库上后，则主从都“回天无力”。
* 闪回用于快速恢复由于误操作丢失的数据。在DBA误操作时，可以把数据库恢复到以前某个时间点（或者说某个binlog的某个pos）。比如忘了带where条件的update、delete操作，传统的恢复方式是利用全备+二进制日志前滚进行恢复，相比于传统的全备+增备，flashback显然更为快速、简单。
2. 测试环境
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
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_image'
;
+
------------------+-------+
| Variable_name | Value |
+
------------------+-------+
| binlog_row_image |
FULL
|
+
------------------+-------+
1 row
in
set
(0.00 sec)
|
3. 闪回实战
* 创建数据库与测试表，并插入数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
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: 0
mysql>
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团队出品 ］
|
1
2
3
4
5
6
|
[root@MySQL ~]
# git clone https://github.com/danfengcao/binlog2sql.git
Initialized empty Git repository
in
/root/binlog2sql/
.git/
remote: Counting objects: 244,
done
.
remote: Total 244 (delta 0), reused 0 (delta 0), pack-reused 244
Receiving objects: 100% (244
/244
), 121.72 KiB | 27 KiB
/s
,
done
.
Resolving deltas: 100% (124
/124
),
done
.
|
* 安装相关依赖
|
1
2
3
|
[root@MySQL ~]
# yum install pip -y
[root@MySQL ~]
# pip install --upgrade pip
[root@MySQL ~]
# pip install -r binlog2sql/requirements.txt
|
* 提前刷新binlog [ 测试中好区分文件 ]
|
1
2
|
mysql> flush logs;
Query OK, 0
rows
affected (0.02 sec)
|
* 查看当前binlog信息
|
1
2
3
4
5
6
7
|
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条件
|
1
2
|
mysql> delete from
users
;
Query OK, 4 rows affected (0.01 sec)
|
* binlog2sql工具通过文件输出操作信息，定位SQL开始位置与结束位置
可通过--start-datetime与--stop-datetime定位时间
由此得到开始position为239,结束position为483
|
1
2
3
4
5
|
[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:49
DELETE
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:49
DELETE
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:49
DELETE
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
|
1
|
[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语句是否正常 ]
|
1
|
[root@MySQL ~]
# mysql -hlocalhost -uroot -p'123' < rollback.sql
|
* 查看
|
1
2
3
4
5
6
7
8
9
10
|
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. 总结
以需求驱动技术，技术本身没有优略之分，只有业务之分。
本文转自asd1123509133 51CTO博客，原文链接：http://blog.51cto.com/lisea/1949859，如需转载请自行联系原作者
