ROW 格式binlog 在MySQL5.6上的数据恢复实验

  • 2019 年 10 月 5 日
  • 筆記

ROW 格式的binlog 在MySQL5.6上的数据恢复实验

5.6和5.7版本的MySQL，有个参数binlog_row_p_w_picpath，默认值为FULL，表示记录的是全部的binlog操作日志（仅在binlog_format=ROW时候生效）。此外binlog_row_p_w_picpath还可以是minimal，表示binlog记录的就只是影响后的行。如此一来使用ROW格式就能节约很多的磁盘空间。

因此，我们服务器上就可以直接设置binlog_format=ROW格式了，至于binlog_row_p_w_picpath设置为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 准备恢复的数据

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 开始恢复数据

/etc/init.d/mysql start   mysql < /root/all.sql   mysql < /root/22.sql  mysql < /root/33.sql  mysql < /root/44.sql

step7 检查恢复后结果

> 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 |  +------+---------+------------+---------+

可以看到恢复的效果不错。

VirMach 便宜 VPS

QNews

QNews