MySQL Flashback功能测试
一、出现问题经常会遇到操作数据库误删除数据的问题，对于Oracle可以使用flashback的功能来进行恢复，但是在MySQL就无能为力了,MySQL没有实现这个功能.但是有一些第三方写出了flashback的功能,今天我就测试了一把，以备不时之需！
二、分析问题
通过百度我找到了几个类似的工具，工具的原理是通过分析binlog日志解析出误操作的语句，然后对其反转，如果是delete语句，则反转为insert语句；insert语句反转为delete语句；update语句反向还是转为update语句，也就是update回去。
下面测试的工具为mysqlbinlog,这个版支持MySQL 5.6, 下载路径及原文链接如下：
http://pan.baidu.com/s/1nvGOOIl http://www.cnblogs.com/youge-OneSQL/p/5249736.html
注意事项 1、在指定--start-position时，需要注意包含table_map_event的位置，否则工具无法找到相关表的元数据信息. 2、要开启binlog. 3、 binlog的格式为row. 4、数据库的版本 MySQL 5.6.29 社区版 5、可恢复:insert, update,delete相关的操作．
三、解决问题
1、 首先当然是要下载好工具
2、 查看一下，要操作的表数据。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
#共有六条数据
mysql> select * from t;
+----+---------+
| id | name |
+----+---------+
|1 | xm |
|2 | xmj |
|3 | xuwu |
|4 | chuzan|
|5 | chuzan2 |
|6 | chuzan3 |
+----+---------+
6 rows in set (0.00 sec)
mysql>
3、测试delete后恢复实验过程
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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
#delete t表的数据，模拟误删除
mysql> delete from t;
Query OK, 6 rows affected (0.00 sec)
#同时有可能其它用户也在insert数据，我们恢复的时候不能破坏，出现故障后的数据。
mysql> insert into t(name) values('王五');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t(name) values('赵六');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t;
+----+--------+
| id | name |
+----+--------+
|7 | 王五 |
|8 | 赵六 |
+----+--------+
2 rows in set (0.00 sec)
#发现误操作最好手工flush logs,生成一个新的binlog日志，恢复的时候不至于太大。
mysql> flush logs;
Query OK, 0 rows affected (0.37 sec)
#使用show命令查看binlog，因为刚刚我们切了日志，所以要恢复的操作应该在mysql-bin.000001内。
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 811 |
| mysql-bin.000002 | 120 |
+------------------+-----------+
2 rows in set (0.00 sec)
# 通过命令过滤可以清楚的看到删除语句所在的POS点和时间点
# 通过下面的结果可知，删除语句在POS点192和370之间
# ./mysqlbinlog -v /data/mysql/mysql_3306/logs/mysql-bin.000001|egrep "^#{6}|^#{1} at|^#{3}"
# at 4
#161221 16:30:18 server id 213306end_log_pos 120 CRC32 0x62bcc74c Start: binlog v 4, server v 5.6.29-log created 161221 16:30:18 at startup
# at 120
#161221 16:30:46 server id 213306end_log_pos 192 CRC32 0xe5221fa9 Query thread_id=19 exec_time=0 error_code=0
# at 192
#161221 16:30:46 server id 213306end_log_pos 239 CRC32 0xbdfb7473 Table_map: `test`.`t` mapped to number 77
# at 239
#161221 16:30:46 server id 213306end_log_pos 339 CRC32 0x9cbd343f Delete_rows: table id 77 flags: STMT_END_F
### DELETE FROM `test`.`t`
### WHERE
### @1=1
### @2='xm'
### DELETE FROM `test`.`t`
### WHERE
### @1=2
### @2='xmj'
### DELETE FROM `test`.`t`
### WHERE
### @1=3
### @2='xuwu'
### DELETE FROM `test`.`t`
### WHERE
### @1=4
### @2='chuzan'
### DELETE FROM `test`.`t`
### WHERE
### @1=5
### @2='chuzan2'
### DELETE FROM `test`.`t`
### WHERE
### @1=6
### @2='chuzan3'
# at 339
#161221 16:30:46 server id 213306end_log_pos 370 CRC32 0xfbac7194 Xid = 342
# at 370
#161221 16:30:56 server id 213306end_log_pos 442 CRC32 0x02ad6583 Ignorable thread_id=19 exec_time=0 error_code=0
# at 442
#161221 16:30:56 server id 213306end_log_pos 489 CRC32 0xfe0bbd3c Table_map: `test`.`t` mapped to number 77
# at 489
#161221 16:30:56 server id 213306end_log_pos 536 CRC32 0xb36e3553 Write_rows: table id 77 flags: STMT_END_F
### INSERT INTO `test`.`t`
### SET
### @1=7
### @2='王五'
# at 536
#161221 16:30:56 server id 213306end_log_pos 567 CRC32 0xb3b18f75 Xid = 343
# at 567
#161221 16:31:05 server id 213306end_log_pos 639 CRC32 0xbd7e882f Ignorable thread_id=19 exec_time=0 error_code=0
# at 639
#161221 16:31:05 server id 213306end_log_pos 686 CRC32 0xe4ffc2c1 Table_map: `test`.`t` mapped to number 77
# at 686
#161221 16:31:05 server id 213306end_log_pos 733 CRC32 0xe7628355 Write_rows: table id 77 flags: STMT_END_F
### INSERT INTO `test`.`t`
### SET
### @1=8
### @2='赵六'
# at 733
#161221 16:31:05 server id 213306end_log_pos 764 CRC32 0x4b6d73ec Xid = 344
# at 764
#161221 16:31:23 server id 213306end_log_pos 811 CRC32 0xfe863ca7 Rotate to mysql-bin.000002pos: 4
#
# 加-B 参数可以得到192 和 370 两个POS点的操作，反转后的数据。
# ./mysqlbinlog -B -v --start-position 192 --stop-position 370 /data/mysql/mysql_3306/logs/mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
#161221 16:30:18 server id 213306end_log_pos 120 CRC32 0x62bcc74c Start: binlog v 4, server v 5.6.29-log created 161221 16:30:18 at startup
ROLLBACK/*!*/;
BINLOG '
mj1aWA86QQMAdAAAAHgAAAAAAAQANS42LjI5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACaPVpYEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAUzH
vGI=
'/*!*/;
#161221 16:30:46 server id 213306end_log_pos 239 CRC32 0xbdfb7473 Table_map: `test`.`t` mapped to number 77
#161221 16:30:46 server id 213306end_log_pos 370 CRC32 0xfbac7194 Xid = 342
COMMIT/*!*/;
#161221 16:30:46 server id 213306end_log_pos 339 CRC32 0x9cbd343f Delete_rows: table id 77 flags: STMT_END_F
BINLOG '
tj1aWBM6QQMALwAAAO8AAAAAAE0AAAAAAAEABHRlc3QAAXQAAgMPApYAAHN0+70=
tj1aWB46QQMAZAAAAFMBAAAAAE0AAAAAAAEAAgAC//wBAAAAAnht/AIAAAADeG1q/AMAAAAEeHV3
dfwEAAAABmNodXphbvwFAAAAB2NodXphbjL8BgAAAAdjaHV6YW4zPzS9nA==
'/*!*/;
### INSERT INTO `test`.`t`
### SET
### @1=1
### @2='xm'
### INSERT INTO `test`.`t`
### SET
### @1=2
### @2='xmj'
### INSERT INTO `test`.`t`
### SET
### @1=3
### @2='xuwu'
### INSERT INTO `test`.`t`
### SET
### @1=4
### @2='chuzan'
### INSERT INTO `test`.`t`
### SET
### @1=5
### @2='chuzan2'
### INSERT INTO `test`.`t`
### SET
### @1=6
### @2='chuzan3'
DELIMITER ;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
#
# 最后通过下面命令执行恢复
# ./mysqlbinlog -B -v --start-position 192 --stop-position 370 /data/mysql/mysql_3306/logs/mysql-bin.000001 | \
>mysql -uroot -p123456 -S /data/mysql/mysql_3306/mysql.sock
Warning: Using a password on the command line interface can be insecure.
#
#
# 查看数据，原来删除的数据回来了
# ./login_mysql 3306
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.6.29-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select * from test.t;
+----+---------+
| id | name |
+----+---------+
|1 | xm |
|2 | xmj |
|3 | xuwu |
|4 | chuzan|
|5 | chuzan2 |
|6 | chuzan3 |
|7 | 王五 |
|8 | 赵六 |
+----+---------+
8 rows in set (0.00 sec)
mysql>
4、测试update后恢复实验过程
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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
# 查看update之前的数据
mysql> select * from test.t;
+----+---------+
| id | name |
+----+---------+
|1 | xm |
|2 | xmj |
|3 | xuwu |
|4 | chuzan|
|5 | chuzan2 |
|6 | chuzan3 |
|7 | 王五 |
|8 | 赵六 |
+----+---------+
8 rows in set (0.00 sec)
# 更新数据
mysql> update test.t set name='徐铭江' where name like 'chuzan%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3Changed: 3Warnings: 0
mysql> select * from test.t;
+----+-----------+
| id | name |
+----+-----------+
|1 | xm |
|2 | xmj |
|3 | xuwu |
|4 | 徐铭江 |
|5 | 徐铭江 |
|6 | 徐铭江 |
|7 | 王五 |
|8 | 赵六 |
+----+-----------+
8 rows in set (0.00 sec)
mysql>
# 查看binlog ,下一步好进行恢复。
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 811 |
| mysql-bin.000002 | 631 |
+------------------+-----------+
2 rows in set (0.00 sec)
# 查看binlog 内容，并查看误更新时的起始POS点
# ./mysqlbinlog -v /data/mysql/mysql_3306/logs/mysql-bin.000002|egrep"^#{6}|^#{1} at|^#{3}"
# at 4
#161221 16:31:23 server id 213306end_log_pos 120 CRC32 0x5e5e4653 Start: binlog v 4, server v 5.6.29-log created 161221 16:31:23
# at 120
#161221 16:30:46 server id 213306end_log_pos 188 CRC32 0xf9337095 Query thread_id=20 exec_time=816 error_code=0
# at 188
#161221 16:30:46 server id 213306end_log_pos 235 CRC32 0x4c78f853 Table_map: `test`.`t` mapped to number 77
# at 235
#161221 16:30:46 server id 213306end_log_pos 335 CRC32 0x85c8054b Write_rows: table id 77 flags: STMT_END_F
### INSERT INTO `test`.`t`
### SET
### @1=1
### @2='xm'
### INSERT INTO `test`.`t`
### SET
### @1=2
### @2='xmj'
### INSERT INTO `test`.`t`
### SET
### @1=3
### @2='xuwu'
### INSERT INTO `test`.`t`
### SET
### @1=4
### @2='chuzan'
### INSERT INTO `test`.`t`
### SET
### @1=5
### @2='chuzan2'
### INSERT INTO `test`.`t`
### SET
### @1=6
### @2='chuzan3'
# at 335
#161221 16:30:46 server id 213306end_log_pos 366 CRC32 0xbca9884b Xid = 357
# at 366
#161221 17:02:03 server id 213306end_log_pos 434 CRC32 0x302963f5 Ignorable thread_id=22 exec_time=0 error_code=0
# at 434
#161221 17:02:03 server id 213306end_log_pos 481 CRC32 0xbf370ffa Table_map: `test`.`t` mapped to number 77
# at 481
#161221 17:02:03 server id 213306end_log_pos 600 CRC32 0xee591d93 Update_rows: table id 77 flags: STMT_END_F
### UPDATE `test`.`t`
### WHERE
### @1=4
### @2='chuzan'
### SET
### @1=4
### @2='徐铭江'
### UPDATE `test`.`t`
### WHERE
### @1=5
### @2='chuzan2'
### SET
### @1=5
### @2='徐铭江'
### UPDATE `test`.`t`
### WHERE
### @1=6
### @2='chuzan3'
### SET
### @1=6
### @2='徐铭江'
# at 600
#161221 17:02:03 server id 213306end_log_pos 631 CRC32 0xf84cb2aa Xid = 368
# 使用-B参数查看反转后的SQL
# ./mysqlbinlog -B -v --start-position 434 --stop-position 631 /data/mysql/mysql_3306/logs/mysql-bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
#161221 16:31:23 server id 213306end_log_pos 120 CRC32 0x5e5e4653 Start: binlog v 4, server v 5.6.29-log created 161221 16:31:23
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
2z1aWA86QQMAdAAAAHgAAAABAAQANS42LjI5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAVNG
Xl4=
'/*!*/;
#161221 17:02:03 server id 213306end_log_pos 481 CRC32 0xbf370ffa Table_map: `test`.`t` mapped to number 77
#161221 17:02:03 server id 213306end_log_pos 631 CRC32 0xf84cb2aa Xid = 368
COMMIT/*!*/;
#161221 17:02:03 server id 213306end_log_pos 600 CRC32 0xee591d93 Update_rows: table id 77 flags: STMT_END_F
BINLOG '
C0VaWBM6QQMALwAAAOEBAAAAAE0AAAAAAAEABHRlc3QAAXQAAgMPApYAAPoPN78=
C0VaWB86QQMAdwAAAFgCAAAAAE0AAAAAAAEAAgAC///8BAAAAAnlvpDpk63msZ/8BAAAAAZjaHV6
YW78BQAAAAnlvpDpk63msZ/8BQAAAAdjaHV6YW4y/AYAAAAJ5b6Q6ZOt5rGf/AYAAAAHY2h1emFu
M5MdWe4=
'/*!*/;
### UPDATE `test`.`t`
### WHERE
### @1=4
### @2='徐铭江'
### SET
### @1=4
### @2='chuzan'
### UPDATE `test`.`t`
### WHERE
### @1=5
### @2='徐铭江'
### SET
### @1=5
### @2='chuzan2'
### UPDATE `test`.`t`
### WHERE
### @1=6
### @2='徐铭江'
### SET
### @1=6
### @2='chuzan3'
DELIMITER ;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
#
# 执行恢复操作,也可以生成SQL文件,确认后,再登陆数据库进行恢复.
# ./mysqlbinlog -B -v --start-position 434 --stop-position 631 /data/mysql/mysql_3306/logs/mysql-bin.000002 | \
>mysql -uroot -p123456 -S /data/mysql/mysql_3306/mysql.sock
# ./login_mysql 3306
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.6.29-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
# 数据恢复成功!
mysql> select * from test.t;
+----+---------+
| id | name |
+----+---------+
|1 | xm |
|2 | xmj |
|3 | xuwu |
|4 | chuzan|
|5 | chuzan2 |
|6 | chuzan3 |
|7 | 王五 |
|8 | 赵六 |
+----+---------+
8 rows in set (0.00 sec)
mysql>
5、测试insert后恢复实验过程
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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
# 查看insert之前的数据
mysql> select * from test.t;
+----+---------+
| id | name |
+----+---------+
|1 | xm |
|2 | xmj |
|3 | xuwu |
|4 | chuzan|
|5 | chuzan2 |
|6 | chuzan3 |
|7 | 王五 |
|8 | 赵六 |
+----+---------+
8 rows in set (0.00 sec)
# insert 错误数据
mysql> insert into test.t(name) values('七七八八');
Query OK, 1 row affected (0.00 sec)
# insert 错误数据
mysql> insert into test.t(name) values('二五六');
Query OK, 1 row affected (0.00 sec)
# insert 正确数据，不要恢复
mysql> insert into test.t(name) values('徐武');
Query OK, 1 row affected (0.00 sec)
# insert 正确数据，不要恢复
mysql> insert into test.t(name) values('徐陆');
Query OK, 1 row affected (0.00 sec)
# 查询insert 错误数据后的结果，现不需要'七七八八'和'二五六' 这两条数据。
mysql> select * from test.t;
+----+--------------+
| id | name |
+----+--------------+
|1 | xm |
|2 | xmj |
|3 | xuwu |
|4 | chuzan |
|5 | chuzan2 |
|6 | chuzan3 |
|7 | 王五 |
|8 | 赵六 |
|9 | 七七八八 |
| 10 | 二五六 |
| 11 | 徐武 |
| 12 | 徐陆 |
+----+--------------+
12 rows in set (0.00 sec)
mysql>
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 811 |
| mysql-bin.000002 | 1677 |
+------------------+-----------+
2 rows in set (0.00 sec)
mysql>
# 查看binlog
# ./mysqlbinlog -v /data/mysql/mysql_3306/logs/mysql-bin.000002|egrep"^#{6}|^#{1} at|^#{3}"
# at 865
#161221 17:02:03 server id 213306end_log_pos 896 CRC32 0xb8cb8180 Xid = 378
# at 896
#161221 17:21:44 server id 213306end_log_pos 964 CRC32 0x50a350b3 Ignorable thread_id=26 exec_time=0 error_code=0
# at 964
#161221 17:21:44 server id 213306end_log_pos 1011 CRC32 0x8710413bTable_map: `test`.`t` mapped to number 77
# at 1011
#161221 17:21:44 server id 213306end_log_pos 1064 CRC32 0x2bb90a24Write_rows: table id 77 flags: STMT_END_F
### INSERT INTO `test`.`t`
### SET
### @1=9
### @2='七七八八'
# at 1064
#161221 17:21:44 server id 213306end_log_pos 1095 CRC32 0x2e48ad77Xid = 385
# at 1095
#161221 17:21:56 server id 213306end_log_pos 1163 CRC32 0xbcc87449Ignorable thread_id=26 exec_time=0 error_code=0
# at 1163
#161221 17:21:56 server id 213306end_log_pos 1210 CRC32 0x518a5946Table_map: `test`.`t` mapped to number 77
# at 1210
#161221 17:21:56 server id 213306end_log_pos 1260 CRC32 0x935c3329Write_rows: table id 77 flags: STMT_END_F
### INSERT INTO `test`.`t`
### SET
### @1=10
### @2='二五六'
# at 1260
#161221 17:21:56 server id 213306end_log_pos 1291 CRC32 0xebaa0357Xid = 386
# at 1291
#161221 17:22:12 server id 213306end_log_pos 1359 CRC32 0x337d6793Ignorable thread_id=26 exec_time=0 error_code=0
# at 1359
#161221 17:22:12 server id 213306end_log_pos 1406 CRC32 0x0ae07c99Table_map: `test`.`t` mapped to number 77
# at 1406
#161221 17:22:12 server id 213306end_log_pos 1453 CRC32 0xd4af7b22Write_rows: table id 77 flags: STMT_END_F
### INSERT INTO `test`.`t`
### SET
### @1=11
### @2='徐武'
# at 1453
#161221 17:22:12 server id 213306end_log_pos 1484 CRC32 0xc039e7f0Xid = 387
# at 1484
#161221 17:22:20 server id 213306end_log_pos 1552 CRC32 0x789d671fIgnorable thread_id=26 exec_time=0 error_code=0
# at 1552
#161221 17:22:20 server id 213306end_log_pos 1599 CRC32 0xde45bfafTable_map: `test`.`t` mapped to number 77
# at 1599
#161221 17:22:20 server id 213306end_log_pos 1646 CRC32 0xeb6b1317Write_rows: table id 77 flags: STMT_END_F
### INSERT INTO `test`.`t`
### SET
### @1=12
### @2='徐陆'
# at 1646
#161221 17:22:20 server id 213306end_log_pos 1677 CRC32 0x6902422aXid = 388
# 加-B参数得到反转SQL,仔细看反转SQL是DELETE语句。
# ./mysqlbinlog -B -v --start-position 964 --stop-position 1291 /data/mysql/mysql_3306/logs/mysql-bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
#161221 16:31:23 server id 213306end_log_pos 120 CRC32 0x5e5e4653 Start: binlog v 4, server v 5.6.29-log created 161221 16:31:23
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
2z1aWA86QQMAdAAAAHgAAAABAAQANS42LjI5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAVNG
Xl4=
'/*!*/;
#161221 17:21:44 server id 213306end_log_pos 1011 CRC32 0x8710413bTable_map: `test`.`t` mapped to number 77
#161221 17:21:44 server id 213306end_log_pos 1095 CRC32 0x2e48ad77Xid = 385
COMMIT/*!*/;
#161221 17:21:56 server id 213306end_log_pos 1210 CRC32 0x518a5946Table_map: `test`.`t` mapped to number 77
#161221 17:21:56 server id 213306end_log_pos 1291 CRC32 0xebaa0357Xid = 386
COMMIT/*!*/;
#161221 17:21:56 server id 213306end_log_pos 1260 CRC32 0x935c3329Write_rows: table id 77 flags: STMT_END_F
BINLOG '
tElaWBM6QQMALwAAALoEAAAAAE0AAAAAAAEABHRlc3QAAXQAAgMPApYAAEZZilE=
tElaWCA6QQMAMgAAAOwEAAAAAE0AAAAAAAEAAgAC//wKAAAACeS6jOS6lOWFrSkzXJM=
'/*!*/;
### DELETE FROM `test`.`t`
### WHERE
### @1=10
### @2='二五六'
#161221 17:21:44 server id 213306end_log_pos 1064 CRC32 0x2bb90a24Write_rows: table id 77 flags: STMT_END_F
BINLOG '
qElaWBM6QQMALwAAAPMDAAAAAE0AAAAAAAEABHRlc3QAAXQAAgMPApYAADtBEIc=
qElaWCA6QQMANQAAACgEAAAAAE0AAAAAAAEAAgAC//wJAAAADOS4g+S4g+WFq+WFqyQKuSs=
'/*!*/;
### DELETE FROM `test`.`t`
### WHERE
### @1=9
### @2='七七八八'
DELIMITER ;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
# 找准POS点，直接恢复。
# ./mysqlbinlog -B -v --start-position 964 --stop-position 1291 /data/mysql/mysql_3306/logs/mysql-bin.000002 | \
>mysql -uroot -p123456 -S /data/mysql/mysql_3306/mysql.sock
#
# ./login_mysql 3306
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 5.6.29-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select * from test.t;
+----+---------+
| id | name |
+----+---------+
|1 | xm |
|2 | xmj |
|3 | xuwu |
|4 | chuzan|
|5 | chuzan2 |
|6 | chuzan3 |
|7 | 王五 |
|8 | 赵六 |
| 11 | 徐武 |
| 12 | 徐陆 |
+----+---------+
10 rows in set (0.00 sec)
# 至此insert 语句也恢复成功！
页:
[1]