阿里云-云小站（无限量代金券发放中）
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

浅析MySQL基于ROW格式的二进制日志

189次阅读
没有评论

共计 10394 个字符，预计需要花费 26 分钟才能阅读完成。

上文分析的二进制日志实际上是基于 STATEMENT 格式的，下面我们来看看基于 ROW 格式的二进制日志，毕竟，两者对应的 binlog 事件类型也不一样，同时，很多童鞋反映基于 ROW 格式的二进制日志无法查到原生的 DML 语句，关于这个问题，其实官方也给出了解决方案，下面，将一一揭晓。

关于 MySQL 二进制日志  http://www.linuxidc.com/Linux/2016-08/134341.htm

首先，来几条测试数据 

mysql> set binlog_format=row;
Query OK, 0 rows affected (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test.t1 values(1,'a');
Query OK, 1 row affected (0.00 sec)

mysql> use testDatabase changed

mysql> insert into t1 values(2,'b');
Query OK, 1 row affected (0.00 sec)

mysql> update t1 set name='c' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> delete from t1 where id=1;
Query OK, 1 row affected (0.01 sec) 

首先通过 SHOW BINLOG EVENTS 查看二进制日志中的内容 

mysql> show binlog events in 'mysql-bin.000025';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000025 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.31-log, Binlog ver: 4 |
| mysql-bin.000025 | 120 | Query       |         1 |         188 | BEGIN                                 |
| mysql-bin.000025 | 188 | Table_map   |         1 |         236 | table_id: 79 (test.t1)                |
| mysql-bin.000025 | 236 | Write_rows  |         1 |         278 | table_id: 79 flags: STMT_END_F        |
| mysql-bin.000025 | 278 | Xid         |         1 |         309 | COMMIT /* xid=175 */                  |
| mysql-bin.000025 | 309 | Query       |         1 |         381 | BEGIN                                 |
| mysql-bin.000025 | 381 | Table_map   |         1 |         429 | table_id: 79 (test.t1)                |
| mysql-bin.000025 | 429 | Write_rows  |         1 |         471 | table_id: 79 flags: STMT_END_F        |
| mysql-bin.000025 | 471 | Xid         |         1 |         502 | COMMIT /* xid=183 */                  |
| mysql-bin.000025 | 502 | Query       |         1 |         574 | BEGIN                                 |
| mysql-bin.000025 | 574 | Table_map   |         1 |         622 | table_id: 79 (test.t1)                |
| mysql-bin.000025 | 622 | Update_rows |         1 |         672 | table_id: 79 flags: STMT_END_F        |
| mysql-bin.000025 | 672 | Xid         |         1 |         703 | COMMIT /* xid=184 */                  |
| mysql-bin.000025 | 703 | Query       |         1 |         775 | BEGIN                                 |
| mysql-bin.000025 | 775 | Table_map   |         1 |         823 | table_id: 79 (test.t1)                |
| mysql-bin.000025 | 823 | Delete_rows |         1 |         865 | table_id: 79 flags: STMT_END_F        |
| mysql-bin.000025 | 865 | Xid         |         1 |         896 | COMMIT /* xid=185 */                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
17 rows in set (0.00 sec) 

再来通过 mysqlbinlog 查看

# mysqlbinlog mysql-bin.000025

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160817 10:20:16 server id 1  end_log_pos 120 CRC32 0x5b15ac4f     Start: binlog v 4, server v 5.6.31-log created 160817 10:20:16
# Warning: this binlog is either in use or was not closed properly.
BINLOG '4MmzVw8BAAAAdAAAAHgAAAABAAQANS42LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAU+s
FVs=
'/*!*/;
# at 120
#160817 10:20:22 server id 1  end_log_pos 188 CRC32 0x005847f0     Query    thread_id=12    exec_time=0    error_code=0
SET TIMESTAMP=1471400422/*!*/;
SET @@session.pseudo_thread_id=12/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 188
#160817 10:20:22 server id 1  end_log_pos 236 CRC32 0x2b8d2069     Table_map: `test`.`t1` mapped to number 79
# at 236
#160817 10:20:22 server id 1  end_log_pos 278 CRC32 0xadc98fbc     Write_rows: table id 79 flags: STMT_END_F

BINLOG '5smzVxMBAAAAMAAAAOwAAAAAAE8AAAAAAAEABHRlc3QAAnQxAAIDDwIeAANpII0r
5smzVx4BAAAAKgAAABYBAAAAAE8AAAAAAAEAAgAC//wBAAAAAWG8j8mt
'/*!*/;
# at 278
#160817 10:20:22 server id 1  end_log_pos 309 CRC32 0x552dc682     Xid = 175
COMMIT/*!*/;
# at 309
#160817 10:20:34 server id 1  end_log_pos 381 CRC32 0x17d8173e     Query    thread_id=12    exec_time=0    error_code=0
SET TIMESTAMP=1471400434/*!*/;
BEGIN
/*!*/;
# at 381
#160817 10:20:34 server id 1  end_log_pos 429 CRC32 0x71a27e19     Table_map: `test`.`t1` mapped to number 79
# at 429
#160817 10:20:34 server id 1  end_log_pos 471 CRC32 0xefda98ca     Write_rows: table id 79 flags: STMT_END_F

BINLOG '8smzVxMBAAAAMAAAAK0BAAAAAE8AAAAAAAEABHRlc3QAAnQxAAIDDwIeAAMZfqJx
8smzVx4BAAAAKgAAANcBAAAAAE8AAAAAAAEAAgAC//wCAAAAAWLKmNrv
'/*!*/;
# at 471
#160817 10:20:34 server id 1  end_log_pos 502 CRC32 0x7bed11c4     Xid = 183
COMMIT/*!*/;
# at 502
#160817 10:20:38 server id 1  end_log_pos 574 CRC32 0xd164b750     Query    thread_id=12    exec_time=0    error_code=0
SET TIMESTAMP=1471400438/*!*/;
BEGIN
/*!*/;
# at 574
#160817 10:20:38 server id 1  end_log_pos 622 CRC32 0x9fa3cabc     Table_map: `test`.`t1` mapped to number 79
# at 622
#160817 10:20:38 server id 1  end_log_pos 672 CRC32 0xb1646398     Update_rows: table id 79 flags: STMT_END_F

BINLOG '9smzVxMBAAAAMAAAAG4CAAAAAE8AAAAAAAEABHRlc3QAAnQxAAIDDwIeAAO8yqOf
9smzVx8BAAAAMgAAAKACAAAAAE8AAAAAAAEAAgAC///8AgAAAAFi/AIAAAABY5hjZLE=
'/*!*/;
# at 672
#160817 10:20:38 server id 1  end_log_pos 703 CRC32 0x91a90c52     Xid = 184
COMMIT/*!*/;
# at 703
#160817 10:20:43 server id 1  end_log_pos 775 CRC32 0x5ae24c0b     Query    thread_id=12    exec_time=0    error_code=0
SET TIMESTAMP=1471400443/*!*/;
BEGIN
/*!*/;
# at 775
#160817 10:20:43 server id 1  end_log_pos 823 CRC32 0x33c52e84     Table_map: `test`.`t1` mapped to number 79
# at 823
#160817 10:20:43 server id 1  end_log_pos 865 CRC32 0x77e907a2     Delete_rows: table id 79 flags: STMT_END_F

BINLOG '+8mzVxMBAAAAMAAAADcDAAAAAE8AAAAAAAEABHRlc3QAAnQxAAIDDwIeAAOELsUz
+8mzVyABAAAAKgAAAGEDAAAAAE8AAAAAAAEAAgAC//wBAAAAAWGiB+l3
'/*!*/;
# at 865
#160817 10:20:43 server id 1  end_log_pos 896 CRC32 0xb0988385     Xid = 185
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; 

额，what is this，竟然没看到一条明文的 DML 语句

实际上，对于 ROW 格式的二进制日志，需要使用如下方式查看，这也是 STATEMENT 和 ROW 格式的差异之一

# mysqlbinlog mysql-bin.000025 -vv –base64-output=decode-rows

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;

# at 4
#160817 10:20:16 server id 1  end_log_pos 120 CRC32 0x5b15ac4f     Start: binlog v 4, server v 5.6.31-log created 160817 10:20:16
# Warning: this binlog is either in use or was not closed properly.

# at 120
#160817 10:20:22 server id 1  end_log_pos 188 CRC32 0x005847f0     Query    thread_id=12    exec_time=0    error_code=0
SET TIMESTAMP=1471400422/*!*/;
SET @@session.pseudo_thread_id=12/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;

# at 188
#160817 10:20:22 server id 1  end_log_pos 236 CRC32 0x2b8d2069     Table_map: `test`.`t1` mapped to number 79
# at 236
#160817 10:20:22 server id 1  end_log_pos 278 CRC32 0xadc98fbc     Write_rows: table id 79 flags: STMT_END_F
### INSERT INTO `test`.`t1`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='a' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */

# at 278
#160817 10:20:22 server id 1  end_log_pos 309 CRC32 0x552dc682     Xid = 175
COMMIT/*!*/;

# at 309
#160817 10:20:34 server id 1  end_log_pos 381 CRC32 0x17d8173e     Query    thread_id=12    exec_time=0    error_code=0
SET TIMESTAMP=1471400434/*!*/;
BEGIN
/*!*/;

# at 381
#160817 10:20:34 server id 1  end_log_pos 429 CRC32 0x71a27e19     Table_map: `test`.`t1` mapped to number 79

# at 429
#160817 10:20:34 server id 1  end_log_pos 471 CRC32 0xefda98ca     Write_rows: table id 79 flags: STMT_END_F
### INSERT INTO `test`.`t1`
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
###   @2='b' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */

# at 471
#160817 10:20:34 server id 1  end_log_pos 502 CRC32 0x7bed11c4     Xid = 183
COMMIT/*!*/;

# at 502
#160817 10:20:38 server id 1  end_log_pos 574 CRC32 0xd164b750     Query    thread_id=12    exec_time=0    error_code=0
SET TIMESTAMP=1471400438/*!*/;
BEGIN
/*!*/;

# at 574
#160817 10:20:38 server id 1  end_log_pos 622 CRC32 0x9fa3cabc     Table_map: `test`.`t1` mapped to number 79

# at 622
#160817 10:20:38 server id 1  end_log_pos 672 CRC32 0xb1646398     Update_rows: table id 79 flags: STMT_END_F
### UPDATE `test`.`t1`
### WHERE
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
###   @2='b' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
###   @2='c' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */

# at 672
#160817 10:20:38 server id 1  end_log_pos 703 CRC32 0x91a90c52     Xid = 184
COMMIT/*!*/;

# at 703
#160817 10:20:43 server id 1  end_log_pos 775 CRC32 0x5ae24c0b     Query    thread_id=12    exec_time=0    error_code=0
SET TIMESTAMP=1471400443/*!*/;
BEGIN
/*!*/;

# at 775
#160817 10:20:43 server id 1  end_log_pos 823 CRC32 0x33c52e84     Table_map: `test`.`t1` mapped to number 79

# at 823
#160817 10:20:43 server id 1  end_log_pos 865 CRC32 0x77e907a2     Delete_rows: table id 79 flags: STMT_END_F
### DELETE FROM `test`.`t1`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='a' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */

# at 865
#160817 10:20:43 server id 1  end_log_pos 896 CRC32 0xb0988385     Xid = 185
COMMIT/*!*/;

DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; 

对于 STATEMENT 格式的 binlog，所有的 DML 操作都记录在 QUERY_EVENT 中，而对于 ROW 格式的 binlog，所有的 DML 操作都记录在 ROWS_EVENT 中，ROWS_EVENT 分为三种：WRITE_ROWS_EVENT，UPDATE_ROWS_EVENT，DELETE_ROWS_EVENT，分别对应 insert，update 和 delete 操作。

对于 insert 操作，WRITE_ROWS_EVENT 包含了要插入的数据

对于 update 操作，UPDATE_ROWS_EVENT 不仅包含了修改后的数据，还包含了修改前的值。

对于 delete 操作，仅仅需要指定删除的主键（在没有主键的情况下，会给定所有列）

事实上，在 ROW 格式的 binlog 文件中，每个 ROWS_EVENT 事件前都会有一个 TABLE_MAP_EVENT，用于描述表的内部 id 和结构定义。

即便上述两个 insert 操作，一个没有执行 use test 操作，都不影响 TABLE_MAP_EVENT 的内容，这也会导致基于 ROW 格式下的库级别的复制和基于 STATEMENT 格式下库级别的复制的复制规则不一致。

如何在 ROW 格式中输出原生的 DML 语句？

MySQL 实际上提供了一个参数，可以用于输出原生的 DML 语句，但是该语句仅仅是其注释的作用，并不会被应用。

如下所示，

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> set binlog_rows_query_log_events=1;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(3,'c');
Query OK, 1 row affected (0.00 sec) 

对应的二进制的内容如下：

mysql> show binlog events in 'mysql-bin.000026';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000026 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.31-log, Binlog ver: 4 |
| mysql-bin.000026 | 120 | Query       |         1 |         192 | BEGIN                                 |
| mysql-bin.000026 | 192 | Rows_query  |         1 |         244 | # insert into t1 values(3,'c')        |
| mysql-bin.000026 | 244 | Table_map   |         1 |         292 | table_id: 79 (test.t1)                |
| mysql-bin.000026 | 292 | Write_rows  |         1 |         334 | table_id: 79 flags: STMT_END_F        |
| mysql-bin.000026 | 334 | Xid         |         1 |         365 | COMMIT /* xid=189 */                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
6 rows in set (0.00 sec)

实际上，MySQL 新增了一个事务类型来输出 ROW 格式中原生的 DML 语句，即 ROWS_QUERY_EVENT。

 

自此以后，再也不用顾虑 ROW 格式的二进制日志中无法查看原生的 DML 语句了。

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2016-08/134391.htm

正文完
星哥玩云-微信公众号
post-qrcode
MongoDB MySQL MySQL二进制日志 NoSQL Oracle Oracle教程 Oracle数据库 PostgreSQL 数据库 浅析MySQL基于ROW格式的二进制日志
发表至： 数据库技术
2022-01-22
 0
星锅
版权声明：本站原创文章，由 星锅 于2022-01-22发表，共计10394字。
转载说明：除特殊说明外本站文章皆由CC-4.0协议发布，转载请注明出处。
【腾讯云】推广者专属福利，新客户无门槛领取总价值高达2860元代金券，每种代金券限量500张，先到先得。
Spotlight On Oracle 监控软件
欧盟对开源项目 Apache HTTP Server 和 Keepass 提供免费安全审计
Oracle 11g 单实例到单实例OGG同步实施文档-RMAN 初始化
MySQL 使用 SSL 连接(附 Docker 例子)
SQL语言中的基本操作
MongoDB启动前的系统设置，解决部分Warning问题
MySQL数据库之触发器
Postgres-XL集群搭建详细创建步骤
阿里云-最新活动爆款每日限量供应
评论（没有评论）
验证码
星哥说事
星哥说事
分享互联网知识
阅读量
3460962
评论数
4
文章搜索
热门文章
基于开源MaxKB构建大语言模型的本地知识库系统

基于开源MaxKB构建大语言模型的本地知识库系统

基于开源 MaxKB 构建大语言模型的本地知识库系统 什么是 MaxKB MaxKB 是一款基于 LLM（La...
获取各大人工智能AI工具通过API和KEY调用的方法

获取各大人工智能AI工具通过API和KEY调用的方法

获取各大人工智能 AI 工具通过 API 和 KEY 调用的方法 前言 随着人工智能技术的飞速发展，AI 工具...
安装开源软件ChatALL（齐叨）来聚合各大人工智能工具

安装开源软件ChatALL（齐叨）来聚合各大人工智能工具

安装开源软件 ChatALL（齐叨）来聚合各大人工智能工具 前言 如果你跟我一样无论遇到什么问题都要询问人工智...
给你的NAS无限可能，安装小晓雅全家桶影音库

给你的NAS无限可能，安装小晓雅全家桶影音库

给你的 NAS 无限可能，安装小晓雅全家桶影音库 家庭存储和娱乐的需求日益增长。无论是高清电影、电视剧，还是音...
vmware下的网卡分配问题

vmware下的网卡分配问题

导读 vmware 虚拟机下 linux centos6.6 只有 lo，没有 eth0 网卡、随机分配 ip...
CDN
阿里云CDN-提高用户访问的响应速度和成功率
随机文章
Linux 命令行下的数学运算

Linux 命令行下的数学运算

  导读     可以在 Linux 命令行下做数学运算吗？当然可以！事实上，有不少命令可以轻松完成这些操作，...
Linux命令之进阶之路

Linux命令之进阶之路

导读 当我们熟悉一定的基础命令时，我们往往会接触到一些高级的命令操作，或者说是陌生的命令操作，今天我们就来和大...
Linux安装使用pidstat命令以对进程数据进行监控

Linux安装使用pidstat命令以对进程数据进行监控

导读 Linux 系统监控的命令其实有很多，pidstat 命令就是其中的一种，使用 pidstat 命令可以...
linux中文文件名乱码解决

linux中文文件名乱码解决

由于安装英文版的系统不支持中文，出现中文文件名乱码。下面操作时在 centos 上安装中文支持。 1) 先下载...
如何解决Oracle临时表空间过大

如何解决Oracle临时表空间过大

导读 解决 Oracle 临时表空间过大有两种方法，方法一增加临时表空间的大小，方法二重建临时表空间，解决临时...
一言一句话
-「」
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中