mysql如何利用binlog进行数据恢复详解

参数说明

binlog的基本块如下：

# at 417750
#181007 1:50:38 server id 1630000 end_log_pos 417844 CRC32 0x9fc3e3cd Query thread_id=440109962 exec_time=0 error_code=0
SET TIMESTAMP=1538877038/*!*/;
BEGIN

1、# at 417750

指明的当前位置相对文件开始的偏移位置，这个在mysqlbinlog命令中可以作为--start-position的参数

2、#181007 1:50:38 server id 1630000 end_log_pos 417844 CRC32 0x9fc3e3cd Query thread_id=440109962 exec_time=0 error_code=0

181007 1:50:38指明时间为18年10月7号1:50:38，serverid也就是你在配置文件中的配置的，end_log_pos 417844，这个块在417844结束。thread_id执行的线程id，exec_time执行时间，error_code错误码

3、SET TIMESTAMP=1538877038/!/;

BEGIN

具体的执行语句

一行记录产生的日志如下所示

# at 417750
#181010  9:50:38 server id 1630000  end_log_pos 417844 CRC32 0x9fc3e3cd     Query   thread_id=440109962 exec_time=0 error_code=0
SET TIMESTAMP=1539136238/*!*/;
BEGIN
/*!*/;
# at 417844
#181010  9:50:38 server id 1630000  end_log_pos 417930 CRC32 0xce36551b     Table_map: `goods`.`good_info` mapped to number 129411
# at 417930
#181010  9:50:38 server id 1630000  end_log_pos 418030 CRC32 0x5827674a     Update_rows: table id 129411 flags: STMT_END_F
### UPDATE `goods`.`good_info`
### WHERE
###   @1='2018:10:07' /* DATE meta=0 nullable=0 is_null=0 */
###   @2=9033404 /* INT meta=0 nullable=0 is_null=0 */
###   @3=1 /* INT meta=0 nullable=0 is_null=0 */
###   @4=8691108 /* INT meta=0 nullable=0 is_null=0 */
###   @5=9033404 /* INT meta=0 nullable=0 is_null=0 */
###   @6=20 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=1538877024 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### SET
###   @1='2018:10:07' /* DATE meta=0 nullable=0 is_null=0 */
###   @2=9033404 /* INT meta=0 nullable=0 is_null=0 */
###   @3=1 /* INT meta=0 nullable=0 is_null=0 */
###   @4=8691108 /* INT meta=0 nullable=0 is_null=0 */
###   @5=9033404 /* INT meta=0 nullable=0 is_null=0 */
###   @6=21 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=1538877024 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
# at 418030
#181010  9:50:38 server id 1630000  end_log_pos 418061 CRC32 0x468fb30e     Xid = 212760460521
COMMIT/*!*/;
# at 418061

一行记录产生的日志如上所示。以SET TIMESTAMP=1539136238/*!*/;开始，以COMMIT/*!*/;结尾。我们可以根据两个at指明的位置来限定范围。

注意一条记录开始的SET TIMESTAMP之前的# at 417750和结尾的COMMIT之后的# at 418061

利用binlog2sql

binlog2sql官网介绍：从MySQL binlog解析出你要的SQL。根据不同选项，你可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。

基本使用如下：

python binlog2sql.py -hlocalhost -P3306 -udev -p'\*' -d room -t room_info --start-file='mysql-bin.011012' --start-position 129886892 --stop-position 130917280 > rollback.sql

具体的使用我就不讲解了github上讲解的十分清楚，主要看下很多用来筛选的条件，比如起止时间--start-datetime/--stop-datetime，表名限定-t，数据库限定-d，语句限定--sql-type，主要说说我遇到的一些问题。

mysql的binlog模式

这里需要设置为ROW，因为ROW模式有原来的信息，如果可以直接利用binlog2sql反向生成回滚sql，如果是STATEMENT无法生成，需要利用的mysql定时备份的文件再去做回滚

恢复数据的具体操作

因为当时线上执行的是一条update语句，没有唯一键索引的。导致有两千多条记录被更新。语句如下：

update room_info set status=1 where status=2;

mysqlbinlog --no-defaults -v --base64-output='decode-rows' -d room --start-datetime='2018-10-10 9:00:00' --stop-datetime='2018-10-10 10:00:00' mysql-bin.011012>temp.sql

zip temp.zip temp.sql && sz temp.zip 

取回文件在本地用文本工具如vscode分析，里面有正则匹配，根据你改动过的特征，比如我有个房间号888888，这个不应该被修改，你就查看这个房间号的修改记录，ROW模式的语句是Where在前，set在后。利用正则room_id=888888.*show_state=1.*AND show_state=2很快就能匹配到。我当时的语句影响了两千多条记录，你根据找到的语句去找开始的SET TIMESTAMP=1539136238的位置之前的at和结尾的COMMIT之后的at。

python binlog2sql.py -hlocalhost -P3306 -udev -p'*' -d room -t room_info -B --start-file='mysql-bin.011012' --start-position 129886892 --stop-position 130917280 > rollback.sql

另外

因为我这边是一条update影响多条的情况，如果是带唯一键的情况下，影响的只有一条记录，完全没必要这么麻烦，直接利用binlog2sql带上-d和-t参数限定数据库和表，然后利用grep来查找，直接可以得出对应的sql。mysqlbinlog少了一个限定表和限定语句的功能。比如精确到一张表的Delete语句，能减少很多的数据，能快速定位。

总结