一、前言
对,没错,我又水了好一阵子,深刻反思寄几。前段时间,工作项目上出于对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,下面来一一介绍一下:
- STATEMENT
- 从字面上看就是描述的意思。记录了相对于操作的SQL语句,比如在控制台执行了DELETE FROM foo WHERE id = 1
,那么在binlog上就会添加上这条语句。好处很明显:直观。
- 对,总是有个但是~~,但是不保证日志记录的正确性。
- 客户端可能不生成行事件 - 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。
- 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):
- Fixed数据部分
- 6字节:表的id
- 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,上述两个重复一遍,表示更新后的值
- 可变大小:bit来表示在行中的每个字段是否为NULL,1表示为null,0表示不为null,只有在数据部分第二部分的列才会出现在这里。需要占用
这也就是为什么下面提到的几个开源项目里对事件(event)进行转换的时候,出现莫名其妙的对不同字节转化成不同字段。
4.3 GitHub上的开源解析binlog工具
这里简单介绍一下已知的几个解析binlog的项目:
- canal
阿里巴巴mysql数据库binlog的增量订阅&消费组件。在了解binlog能解析出来的内容后,觉得canal做的是真的好,原生的binlog解析出来是没有列名信息、列编码、列类型的,canal在此基础上多加了一层,补全对应的列信息,完善了大众业务理解binlog的基本诉求。事实上是添加列名并不是简单的发送show create table xxx
这么简单,考虑到列可能会被增加、删除等,之前t0时刻消费的列可能会对应不上此时t1时刻的列,中间会出现很多问题。 mysql-binlog-connector-java
前身是open-replicator,在作者不再更新代码后,该作者完全重写了该项目,添加了很多MySQL5.x的新特性。解析结果相对于canal就很原生了。不过也让笔者膜拜。binlog2sql
前面两个都是java语言的项目,这个是python写的,从MySQL binlog解析出你要的SQL。根据不同选项,你可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。算是最接近笔者需求的一个项目,基本上加上项目代码就直接能用,但是笔者的强迫症发作,由于写的项目是java的项目,虽然jython能实现,但是笔者还是想折腾一下其他的,就没采用。o(TωT)o
五、总结
第一部分先记录一下整个操作的过程,第二部分写具体的实现过程。谢谢各位园友观看,如果有描述不对的地方欢迎指正,与大家共同进步!