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,各位就自行考虑了。

环境版本如下:

> SELECT @@version
+-------------+
| @@version  |
|-------------|
| 5.6.34-log  |
+-------------+
 
> SELECT @@binlog_format;
+-------------------+
| @@binlog_format  |
|-------------------|
| ROW              |
+-------------------+

假设我们的操作都是在一个库里面执行的,MySQL服务器上只跑了这一个hellodb业务的数据库。

如果数据库多的话,还会增大恢复的难度,如下事例(下面的grant操作实例不够明显,但是差不多就是那个操作步骤):

step1  准备一个全量备份:

mysqldump --flush-logs -A > /root/all.sql

step2  手工误操作删除部分数据

> 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停机

/etc/init.d/mysql stop

step4 导出相关的binlog

cd /data/mysql

看下最近的binlog文件,假如我这里看到的是 mysql.0000010 这个文件。

# 先导出一份binlog文件,
mysqlbinlog --base64-output=decode-rows -vv mysql.000010 > /root/1.sql

vi /root/1.sql 找到刚才我们误操作的部分,类似如下(下面被我添加了部分注释):

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 准备恢复的数据

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/dd39dbedc14a644aef267c5cec87c8b7.html