ROW 格式的binlog 在MySQL5.6上的数据恢复实验
5.6和5.7版本的MySQL，有个参数binlog_row_image，默认值为FULL，表示记录的是全部的binlog操作日志（仅在binlog_format=ROW时候生效）。此外binlog_row_image还可以是minimal，表示binlog记录的就只是影响后的行。如此一来使用ROW格式就能节约很多的磁盘空间。
因此，我们服务器上就可以直接设置binlog_format=ROW格式了，至于binlog_row_image设置为FULL还是minimal，各位就自行考虑了。
环境版本如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
> SELECT @@version
+-------------+
| @@version |
|-------------|
| 5.6.34-log |
+-------------+
> SELECT @@binlog_format;
+-------------------+
| @@binlog_format |
|-------------------|
| ROW |
+-------------------+
|
假设我们的操作都是在一个库里面执行的，MySQL服务器上只跑了这一个hellodb业务的数据库。
如果数据库多的话，还会增大恢复的难度，如下事例(下面的grant操作实例不够明显，但是差不多就是那个操作步骤)：
step1 准备一个全量备份：
|
1
|
mysqldump --flush-logs -A >
/root/all
.sql
|
step2 手工误操作删除部分数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
> use hellodb;
> delete from scores where `StuID`=8 AND `ID`=14;
# 模拟误操作删了1条用户数据,然后上报需要回滚操作。
此时还有个线程，执行了 grant all on *.* to
'abc'
@
'%'
; 假设这个grant操作的是管理员正常的操作。
> delete from scores where `StuID`=5 AND `ID`=10;
# 模拟再次误操作删了1条用户数据,然后上报需要回滚操作。
........
........
在我们发现操作错了，到汇报这期间，还要很多用户的正常操作，也造成了数据库的一些更新。例如下面这条插入的记录。
........
INSERT INTO students VALUES(100,
'www'
,100,
'F'
,3,5);
........
........
|
step3 mysql停机
|
1
|
/etc/init
.d
/mysql
stop
|
step4 导出相关的binlog
|
1
|
cd
/data/mysql
|
看下最近的binlog文件，假如我这里看到的是 mysql.0000010 这个文件。
|
1
2
|
# 先导出一份binlog文件，
mysqlbinlog --base64-output=decode-rows -vv mysql.000010 >
/root/1
.sql
|
vi /root/1.sql 找到刚才我们误操作的部分，类似如下(下面被我添加了部分注释)：
|
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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
|
BEGIN ---> 这个BEGIN-COMMIT要删除
/*!*/;
# at 662771 ---> 注意这个Postion,回滚要用到
#170116 15:21:31 server id 106 end_log_pos 662826 CRC32 0xc2733cd6 Table_map: `hellodb`.`scores` mapped to number 156
# at 662826
#170116 15:21:31 server id 106 end_log_pos 662873 CRC32 0x0d302d22 Delete_rows: table id 156 flags: STMT_END_F
### DELETE FROM `hellodb`.`scores`
### WHERE
### @1=14 /* INT meta=0 nullable=0 is_null=0 */
### @2=8 /* INT meta=0 nullable=0 is_null=0 */
### @3=4 /* SHORTINT meta=0 nullable=0 is_null=0 */
### @4=57 /* TINYINT meta=0 nullable=1 is_null=0 */
# at 662873
#170116 15:21:31 server id 106 end_log_pos 662904 CRC32 0x7bda6198 Xid = 1136
COMMIT/*!*/;
# at 662904 ---> 这个BEGIN COMMIT要保留,这个是用户的正常操作的sql
#170116 15:21:42 server id 106 end_log_pos 663027 CRC32 0xa7dc153b Query thread_id=10 exec_time=0 error_code=0
SET TIMESTAMP=1484551302/*!*/;
grant all on *.* to
'abc'
@
'%'
/*!*/;
# at 663027
#170116 15:21:49 server id 106 end_log_pos 663102 CRC32 0xa7570f25 Query thread_id=10 exec_time=0 error_code=0
SET TIMESTAMP=1484551309/*!*/;
BEGIN ---> 这个BEGIN-COMMIT要删除
/*!*/;
# at 663102 ---> 注意这个Postion,回滚要用到
#170116 15:21:49 server id 106 end_log_pos 663157 CRC32 0x20b81986 Table_map: `hellodb`.`scores` mapped to number 156
# at 663157
#170116 15:21:49 server id 106 end_log_pos 663204 CRC32 0x26d9f8b8 Delete_rows: table id 156 flags: STMT_END_F
### DELETE FROM `hellodb`.`scores`
### WHERE
### @1=10 /* INT meta=0 nullable=0 is_null=0 */
### @2=5 /* INT meta=0 nullable=0 is_null=0 */
### @3=7 /* SHORTINT meta=0 nullable=0 is_null=0 */
### @4=63 /* TINYINT meta=0 nullable=1 is_null=0 */
# at 663204
#170116 15:21:49 server id 106 end_log_pos 663235 CRC32 0x81f9c1d6 Xid = 1138
COMMIT/*!*/;
# at 663235
#170116 15:22:59 server id 106 end_log_pos 663310 CRC32 0xb3b0508d Query thread_id=10 exec_time=0 error_code=0
SET TIMESTAMP=1484551379/*!*/;
BEGIN ---> 这个BEGIN-COMMIT要保留,这个是用户的正常操作的sql
/*!*/;
# at 663310 ---> 注意这个Postion,回滚要用到
#170116 15:22:59 server id 106 end_log_pos 663373 CRC32 0x17a48bfc Table_map: `hellodb`.`students` mapped to number 152
# at 663373
#170116 15:22:59 server id 106 end_log_pos 663424 CRC32 0x0acbd405 Write_rows: table id 152 flags: STMT_END_F
### INSERT INTO `hellodb`.`students`
### SET
### @1=100 /* INT meta=0 nullable=0 is_null=0 */
### @2='www' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
### @3=100 /* TINYINT meta=0 nullable=0 is_null=0 */
### @4=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @5=3 /* TINYINT meta=0 nullable=1 is_null=0 */
### @6=5 /* INT meta=0 nullable=1 is_null=0 */
# at 663424
#170116 15:22:59 server id 106 end_log_pos 663455 CRC32 0x1f37c970 Xid = 1139
COMMIT/*!*/;
|
step5 准备恢复的数据
|
1
2
3
|
mysqlbinlog mysql.000010 --stop-position=662771 >
/root/22
.sql
# 导出step2中第一个DELETE前的数据
mysqlbinlog mysql.000010 --start-position=662904 --stop-position=663027 >
/root/33
.sql
# 导出step2中这个正常的grant授权操作语句
mysqlbinlog mysql.000010 --start-position=663310 >
/root/44
.sql
# 导出step2中的那个正常的INSERT操作及其后面的全部SQL操作
|
step6 开始恢复数据
|
1
2
3
4
5
|
/etc/init
.d
/mysql
start
mysql <
/root/all
.sql
mysql <
/root/22
.sql
mysql <
/root/33
.sql
mysql <
/root/44
.sql
|
step7 检查恢复后结果
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
> use hellodb;
> SELECT * from students where `StuID`=100 AND `Name`=
'www'
;
+---------+--------+-------+----------+-----------+-------------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
|---------+--------+-------+----------+-----------+-------------|
| 100 | www | 100 | F | 3 | 5 |
+---------+--------+-------+----------+-----------+-------------+
> SELECT * from scores where `StuID`=8 AND `ID`=14;
+------+---------+------------+---------+
| ID | StuID | CourseID | Score |
|------+---------+------------+---------|
| 14 | 8 | 4 | 57 |
+------+---------+------------+---------+
> SELECT * from scores where `StuID`=5 AND `ID`=10;
+------+---------+------------+---------+
| ID | StuID | CourseID | Score |
|------+---------+------------+---------|
| 10 | 5 | 7 | 63 |
+------+---------+------------+---------+
|
可以看到恢复的效果不错。
本文转自 lirulei90 51CTO博客，原文链接：http://blog.51cto.com/lee90/1892434，如需转载请自行联系原作者
