【MySQL】通过Binary Log简单实现数据回滚(一)

一、前言

对,没错,我又水了好一阵子,深刻反思寄几。前段时间,工作项目上出于对excel等批量操作可能出现误操作的问题,要求提供一个能够根据操作批次进行数据回滚的能力。在开发的过程中接触到了MySQL的Binary Log,感觉有些收获,记录一下。

二、Binary Log的概念

首先我们要了解一下什么是Binary Log(详情点进去看):

Binary Log(二进制文件),包含了描述数据库更改的“事件”,例如创建表的操作或者改变表的数据。如果采用基于行的日志,它还能包含已经发生更改的语句事件(比如,没有对应行的DELETE事件)。

也就是说你对数据库的操作,包括INSERT、DELETE在内的CRUD,binlog(命令里简称)都会包含进去,那么,如果我们能够解析(因为从binlog的名字可以知道,这是一个二进制文件,不是人类能够阅读的)出它的内容,就可以对执行的语句进行反向操作,对误操作的数据进行恢复。

这也是binlog的目的之一:数据恢复

而binlog的另一个用途就是用于主从复制。我们都知道在现在的大数据背景下,常规的单数据库已经无法满足访问量的需求,于是出现了数据库集群:主数据库进行写操作,从数据库进行读操作,从而降低数据库的访问压力,而为了保证数据库的内容一致,就要用到binlog来保证了,如下图:这里不具体展开。

三、通过shell查看Binary Log

了解了binlog的概念之后,我们来通过shell查看一下binlog。

首先要在my.cnf中添加如下配置:

[mysqld]
log-bin=mysql-bin
binlog-format=ROW #选择row模式
server_id=1 #避免和slave机器重复
log_bin_basename=xxx 可选
log_bin_index=xxx 可选

保存后重启MySQL。

进入MySQL Command:

mysql> show variables like '%log_bin%'; 查看binglog路径
+---------------------------------+---------------------------------------+
| Variable_name                   | Value                                 |
+---------------------------------+---------------------------------------+
| log_bin                         | ON                                    |
| log_bin_basename                | /usr/local/mysql/data/mysql-bin       |
| log_bin_index                   | /usr/local/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                   |
| log_bin_use_v1_row_events       | OFF                                   |
| sql_log_bin                     | ON                                    |
+---------------------------------+---------------------------------------+
  • log_bin:on 表示开启了Binary Log
  • log_bin_basename:binary log的基本文件名,可以在my.cnf指定
  • log_bin_index:binlog文件的索引文件,可以在my.cnf指定
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |   9309624 |
| mysql-bin.000002 |   9008629 |
| mysql-bin.000003 |    229080 |
| mysql-bin.000004 |  15410010 |
| mysql-bin.000005 |       177 |
| mysql-bin.000006 |   5798399 |
| mysql-bin.000007 |       177 |
+------------------+-----------+

显示当前数据库所有的binary log文件和文件大小

知道这些之后,退出MySQL Command,在shell中进行查看:

> sudo -u mysql mysqlbinlog /usr/local/mysql/data/mysql-bin.000030

由于我的/usr/local/mysql/data的在安装MySQL的时候默认只给了mysql用户,所以要加-u切换成mysql。

至此便可以查看到二进制文件中的内容(截取了部分):

# at 1341475
#180416 15:58:45 server id 1  end_log_pos 1341582 CRC32 0x0ca6c030  Table_map: `user-center`.`t_management_entity_role` mapped to number 127
# at 1341582
#180416 15:58:45 server id 1  end_log_pos 1341686 CRC32 0x33552cef  Write_rows: table id 127 flags: STMT_END_F

BINLOG '
tVfUWhMBAAAAawAAAI54FAAAAH8AAAAAAAEADnNoLXVzZXItY2VudGVyABh0X21hbmFnZW1lbnRf
ZW50aXR5X3JvbGUADAMPDw8PDwEPDxIPEhJgADYAYAC0AAMAAwDAAADAAAASADDApgw=
tVfUWh4BAAAAaAAAAPZ4FAAAAH8AAAAAAAEAAgAM//8Q8IkAAAARc3ViX2VtcGxveWVlX2RlcHQG
5qCh5belDXNjaG9vbF93b3JrZXIBMQIBMAN6a2qZn6D7wAN6a2qZn6D7wO8sVTM=
'/*!*/;
# at 1341686
#180416 15:58:45 server id 1  end_log_pos 1341717 CRC32 0x1fdc2123  Xid = 22495
COMMIT/*!*/;
# at 1341717
#180416 16:41:12 server id 1  end_log_pos 1341740 CRC32 0xca0bf05c  Stop
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

看到这里,觉得BINLOG具体里面的还是非人类能够阅读的。想要知道其中的秘密,看来还是要阅读MySQL的开发手册才行。

四、binlog的解析原理以及GitHub上的开源解析工具

4.1 binlog的几种格式

要了解MySQL的解析原理,当然要从头到尾仔细阅读MySQL的开发手册,想了解的可以点这里。里面详细介绍了MySQL的信息。笔者捡其中介绍binlog的部分来简单说一说。

首先要了解binlog的格式,binlog的格式分为三种:STATEMENT,ROW,MIXED,下面来一一介绍一下:

  1. STATEMENT
    • 从字面上看就是描述的意思。记录了相对于操作的SQL语句,比如在控制台执行了DELETE FROM foo WHERE id = 1,那么在binlog上就会添加上这条语句。好处很明显:直观。
    • 对,总是有个但是~~,但是不保证日志记录的正确性
    • 客户端可能不生成行事件
  2. ROW
    • 保证日志记录的正确性
    • DML的改变可能只记录在ROW模式中,不会记录在STATEMENT模式中。每行内容的改变由之前的图像(Before Image,BI)和之后的图像(After Image,AI)组成。BI记录了该行改变前的每列数据,而AI则是改变后的每列数据。有三种类型的log_event:
      • Write_rows_log_event:在表中添加新的一行,还有AI。
      • Update_rows_log_event:修改表中已经存在的行,AI和BI都有。
      • Delete_rows_log_event:删除表中的存在的行,只有BI。
  3. MIXED
    • 保证日志记录的正确性,首先采用STATEMENT记录,如果不能正确记录,则采用ROW模式记录。
    • 增加了处理的难度,要写两种实现。

由上可见,STATEMENT模式是不可用的,因为它不能保证日志的正确性,而MIXED模式会增加代码的复杂度,要考虑到两种情况,增加了代码的工作量,所以实现上采用ROW模式是普遍的做法。

4.2 binlog的事件格式

The Binary Log是阅读的主要内容。里面着重介绍了binlog的消息体格式,事件格式等内容。笔者挑部分说一下。

前面提到对数据库的操作是以event事件的形式以二进制写入binlog的,那么event是什么样的格式呢?所有的event事件都有一个共同的通用结构,由一个事件标题和事件数据组成:

+===================+
| event header      |
+===================+
| event data        |
+===================+

而event header和data的部分在不同的MySQL版本下面有不同的变化。具体表现为:

  • v1:在MySQL 3.23中使用
  • v3:是的没错,没有v2,在MySQL 4.0.2 到 4.1使用
  • v4:在 MySQL 5.0以及以上版本中使用

5.0版本以前的就不介绍了,直接来看v4版本的event结构:

+=====================================+
| event  | timestamp         0 : 4    |
| header +----------------------------+
|        | type_code         4 : 1    |
|        +----------------------------+
|        | server_id         5 : 4    |
|        +----------------------------+
|        | event_length      9 : 4    |
|        +----------------------------+
|        | next_position    13 : 4    |
|        +----------------------------+
|        | flags            17 : 2    |
|        +----------------------------+
|        | extra_headers    19 : x-19 |
+=====================================+
| event  | fixed part        x : y    |
| data   +----------------------------+
|        | variable part              |
+=====================================+
  • 字节为单位
  • header的长度=x 字节
  • data的长度 = (event的长度 - x) 字节
  • fixed 部分的长度 = y字节变量长度。
  • x是由格式描述时间(format description event-FDE)中定义的,目前x是19,即extra_headers是空的
  • y指定的是事件的类型,也是FDE中定义的,相同事件的fixed part 长度相同, 不同事件的长度不同

我们来看一下event data部分的格式,以插入行事件的格式为例(Write_rows_log_event/WRITE_ROWS_EVENT):

  1. Fixed数据部分
    • 6字节:表的id
    • 2字节:留着备用
  2. variable数据部分
    • 打包整数(一种特殊格式的无符号整型,能够存储8字节的整数,表示方法详见这里):表中列的数量。
    • 可变大小:用bit来表示是否每列被使用,一个bit一列,如果N个列,要用INT((N+7)/8)字节
    • 可变大小(针对UPDATE_ROWS_LOG_EVENT),与上面相同,表示的是更新后每列是否被使用
    • 可变大小:零行或多行,截止位置由event头部的event_length决定,每行的格式如下:
      • 可变大小:bit来表示在行中的每个字段是否为NULL,1表示为null,0表示不为null,只有在数据部分第二部分的列才会出现在这里。需要占用INT((N+7)/8)字节
      • 可变大小:行图像,包含所有表格字段的值。 这只会列出使用的表格字段(根据变量数据部分的第二个字段)和非NULL(根据前一个字段)。
      • 针对UPDATE_ROWS_LOG_EVENT,上述两个重复一遍,表示更新后的值

这也就是为什么下面提到的几个开源项目里对事件(event)进行转换的时候,出现莫名其妙的对不同字节转化成不同字段。

4.3 GitHub上的开源解析binlog工具

这里简单介绍一下已知的几个解析binlog的项目:

  1. canal 阿里巴巴mysql数据库binlog的增量订阅&消费组件。在了解binlog能解析出来的内容后,觉得canal做的是真的好,原生的binlog解析出来是没有列名信息、列编码、列类型的,canal在此基础上多加了一层,补全对应的列信息,完善了大众业务理解binlog的基本诉求。事实上是添加列名并不是简单的发送show create table xxx这么简单,考虑到列可能会被增加、删除等,之前t0时刻消费的列可能会对应不上此时t1时刻的列,中间会出现很多问题。
  2. mysql-binlog-connector-java 前身是open-replicator,在作者不再更新代码后,该作者完全重写了该项目,添加了很多MySQL5.x的新特性。解析结果相对于canal就很原生了。不过也让笔者膜拜。
  3. binlog2sql 前面两个都是java语言的项目,这个是python写的,从MySQL binlog解析出你要的SQL。根据不同选项,你可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。算是最接近笔者需求的一个项目,基本上加上项目代码就直接能用,但是笔者的强迫症发作,由于写的项目是java的项目,虽然jython能实现,但是笔者还是想折腾一下其他的,就没采用。o(TωT)o 

五、总结

第一部分先记录一下整个操作的过程,第二部分写具体的实现过程。谢谢各位园友观看,如果有描述不对的地方欢迎指正,与大家共同进步!

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏孔德雨的专栏

MongoDB Mmap 引擎分析

在3.0之前一直使用mmap引擎作为默认存储引擎,本篇从源码角度对mmap引擎作分析,业界一直以来对10gen用mmap实现存储引擎褒贬不一,本文对此不作探讨。

9.1K0
来自专栏有趣的Python

8- vue django restful framework 打造生鲜超市 -商品类别数据展示(下)

Vue+Django REST framework实战 搭建一个前后端分离的生鲜超市网站 Django rtf 完成 商品类别页 vue展示商品列表页数...

2874
来自专栏更流畅、简洁的软件开发方式

【自然框架】元数据的数据库结构的详细说明和示例(二):数据库描述部分

1、Manage_Table(表、视图、存储过程、函数的信息) 字段名 中文名 类型 大小 默认值 说明 TableName 表名 nvarchar 6...

1955
来自专栏Golang语言社区

亲身经历的痛--database/sql: Stmt的使用以及坑

前言 众所周知,golang操作数据库,是通过database/sql包,以及第三方的实现了database/sql/driver接口的数据库驱动包来共同完成的...

49810
来自专栏前端开发

so easy 前端实现多语言

2276
来自专栏社区的朋友们

Node 架构从三层到 N 层,实现代码重用和解耦

三层架构通常意义上是将整个业务应用划分为:控制层、业务逻辑层以及数据访问层,三层架构在 Java Web 项目中很常见,那么这种架构能否运用在 Node 项目中...

1.3K0
来自专栏石瞳禅的互联网实验室

关于Logstash中grok插件的正则表达式例子

近期需要对Nginx产生的日志进行采集,问了下度娘,业内最著名的解决方案非ELK(Elasticsearch, Logstash, Kibana)莫属。

731
来自专栏更流畅、简洁的软件开发方式

100w条记录分页,可以有多快?—— DataReader分页与SQL语句分页的对比测试(在线演示)

  上周看到了两篇关于DataReader分页的帖子,帖子的观点都是可以是用DataReader来分页,而且效率还不错。   根据我的分页经历来看,很难理解Da...

1757
来自专栏皮皮之路

【MySQL】通过Binary Log简单实现数据回滚(一)

35611
来自专栏along的开发之旅

ASP.NET中cs代码页找不到aspx页面中的控件ID

最近在写一个BBS,依照书的葫芦画我的瓢,但是就算这样其中仍然出了不少问题。刚解决如上所述问题。

492

扫码关注云+社区