前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >【MySQL】通过Binary Log简单实现数据回滚(一)

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

作者头像
joemsu
发布2018-06-21 16:40:05
1.7K0
发布2018-06-21 16:40:05
举报
文章被收录于专栏:皮皮之路

一、前言

对,没错,我又水了好一阵子,深刻反思寄几。前段时间,工作项目上出于对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中添加如下配置:

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

保存后重启MySQL。

进入MySQL Command:

代码语言:javascript
复制
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指定
代码语言:javascript
复制
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中进行查看:

代码语言:javascript
复制
> sudo -u mysql mysqlbinlog /usr/local/mysql/data/mysql-bin.000030

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

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

代码语言:javascript
复制
# 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事件都有一个共同的通用结构,由一个事件标题和事件数据组成:

代码语言:javascript
复制
+===================+
| 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结构:

代码语言:javascript
复制
+=====================================+
| 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 

五、总结

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

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2018-04-24 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 一、前言
  • 二、Binary Log的概念
  • 三、通过shell查看Binary Log
  • 四、binlog的解析原理以及GitHub上的开源解析工具
    • 4.1 binlog的几种格式
      • 4.2 binlog的事件格式
        • 4.3 GitHub上的开源解析binlog工具
        • 五、总结
        相关产品与服务
        云数据库 SQL Server
        腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
        领券
        问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档