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

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

118次阅读
没有评论

共计 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张，先到先得。
PL/SQL编程基础简介及实践
MySQL单表查询
我是如何用2个Unix命令给SQL提速的
ORA-01033因误删表空间文件导致的解决方案
MySQL事务隔离级别的实现原理
CentOS环境下Zabbix监控PostgreSQL
MySQL的FEDERATED引擎实现类Oracle的DBlink
Oracle的start with connect by prior如何使用
阿里云-最新活动爆款每日限量供应
评论（没有评论）
验证码
星哥说事
星哥说事
分享互联网知识
阅读量
1917956
评论数
3
文章搜索
热门文章
开源项目搭建中国裁判文书网本地搜索WEB系统

开源项目搭建中国裁判文书网本地搜索WEB系统

2023 年 12 月听新闻说裁判文书网将不再对公众开放，对于这个消息对于中国法制进程影响可谓深远。 今天在 ...
历史往往不是由问题决定的，而是由对问题的应对方式决定的【转】

历史往往不是由问题决定的，而是由对问题的应对方式决定的【转】

历史往往不是由问题决定的，而是由对问题的应对方式决定的 文 / 孙立平 清华大学社会学系教授 前些天，我在一篇...
阿里云免费SSL证书有效期从1年缩短至3个月！

阿里云免费SSL证书有效期从1年缩短至3个月！

2024 阿里云： 2 核 2G3M 轻量服务器 61 元 / 年、2 核 2G3M 云服务器 ECS 优惠 ...
阿里云2核4G4M轻量应用服务器_297元/年【优惠购买入口】

阿里云2核4G4M轻量应用服务器_297元/年【优惠购买入口】

2024 阿里云： 2 核 2G3M 轻量服务器 61 元 / 年、2 核 2G3M 云服务器 ECS 优惠 ...
企业邮箱给谷歌Gmail报错550-5.7.25解决方案

企业邮箱给谷歌Gmail报错550-5.7.25解决方案

企业邮箱给谷歌 Gmail 报错 550-5.7.25 解决方案 问题表现 今天接到同事报告企业邮箱发送报错的...
CDN
阿里云CDN-提高用户访问的响应速度和成功率
随机文章
兄弟：双子传说 – 好评如潮的合作闯关游戏 (《双人成行》前作)

兄弟：双子传说 – 好评如潮的合作闯关游戏 (《双人成行》前作)

最近获得 2021 最佳年度游戏大奖殊荣的《 双人成行 》可谓是实至名归！两名玩家合作闯关的玩法在近些年来鲜有...
阿里云2核2G服务器99元e实例、3M带宽和40G ESSD Entry系统盘性能测评

阿里云2核2G服务器99元e实例、3M带宽和40G ESSD Entry系统盘性能测评

2024 阿里云： 2 核 2G3M 轻量服务器 61 元 / 年、2 核 2G3M 云服务器 ECS 优惠 ...
[限时免费正版] 经典武侠 RPG 仙剑奇侠传、轩辕剑、大富翁系列正版限免

[限时免费正版] 经典武侠 RPG 仙剑奇侠传、轩辕剑、大富翁系列正版限免

「方块游戏 」是国内一个类似 Steam 的正版游戏平台，今天它提供了多款经典 RPG 武侠游戏限时免费领取，...
[限时免费正版] 亚马逊免费送《三体》《垮掉的一代》正版电子书 (仅限1天)

[限时免费正版] 亚马逊免费送《三体》《垮掉的一代》正版电子书 (仅限1天)

马上就迎来大家喜闻乐见的国庆长假了！「亚马逊中国」最近一直在免费赠送正版 Kindle 电子书，每天送两本，连...
阿里云服务器ECS价格下调优惠折扣表_2024年3月降价整理

阿里云服务器ECS价格下调优惠折扣表_2024年3月降价整理

2024 阿里云： 2 核 2G3M 轻量服务器 61 元 / 年、2 核 2G3M 云服务器 ECS 优惠 ...
一言一句话
-「」
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中