MySQL: 揭开Binlog的神秘面纱,理解mysqlbinlog命令输出

运维开发王义杰

发布于 2023-10-23 20:26:38

2820

发布于 2023-10-23 20:26:38

在MySQL中，二进制日志（Binlog）是一个非常重要的组件，它记录了所有影响数据内容的事件。当我们使用mysqlbinlog工具以--verbose模式查看Binlog内容时，可能会看到一些看似复杂的输出。在本文中，我们将逐步解析这些输出，理解它们的含义。

1. Binlog格式

首先，我们需要理解MySQL的Binlog有三种格式：Statement, Row和Mixed。在Row格式下，Binlog记录的是每条变更的行级详情，而不是SQL语句本身。

2. 解析`mysqlbinlog`输出

mysqlbinlog --verbose --verbose /var/log/mysql/mysql-bin.000013    
### INSERT INTO `test`.`tb_temp_backup`
### SET
###   @1=7121090081640677437 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='DefaultRegion_Linux_www.y3140.com_22_yab140' /* VARSTRING(4096) meta=4096 nullable=1 is_null=0 */
###   @3=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @4=3 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @5=3 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @6='www.y3140.com' /* VARSTRING(4096) meta=4096 nullable=0 is_null=0 */
###   @7=22 /* INT meta=0 nullable=0 is_null=0 */
###   @8='y140' /* VARSTRING(4096) meta=4096 nullable=0 is_null=0 */
###   @9='' /* VARSTRING(4096) meta=4096 nullable=0 is_null=0 */
###   @10=0 /* SHORTINT meta=0 nullable=0 is_null=0 */
###   @11=0 /* SHORTINT meta=0 nullable=0 is_null=0 */
###   @12=0 /* SHORTINT meta=0 nullable=0 is_null=0 */
###   @13=NULL /* DATETIME(0) meta=0 nullable=1 is_null=1 */
###   @14=0 /* SHORTINT meta=0 nullable=0 is_null=0 */
###   @15=0 /* SHORTINT meta=0 nullable=0 is_null=0 */
###   @16=NULL /* DATETIME(0) meta=0 nullable=1 is_null=1 */
###   @17=0 /* SHORTINT meta=0 nullable=0 is_null=0 */
###   @18=NULL /* JSON meta=4 nullable=1 is_null=1 */
###   @19=0 /* SHORTINT meta=0 nullable=0 is_null=0 */
###   @20=0 /* SHORTINT meta=0 nullable=0 is_null=0 */
###   @21=0 /* SHORTINT meta=0 nullable=0 is_null=0 */
###   @22=0 /* SHORTINT meta=0 nullable=0 is_null=0 */
###   @23=0 /* SHORTINT meta=0 nullable=0 is_null=0 */
###   @24=0 /* SHORTINT meta=0 nullable=0 is_null=0 */
###   @25=NULL /* VARSTRING(256) meta=256 nullable=1 is_null=1 */
###   @26=NULL /* VARSTRING(2048) meta=2048 nullable=1 is_null=1 */
###   @27=NULL /* VARSTRING(256) meta=256 nullable=1 is_null=1 */
###   @28=NULL /* VARSTRING(2048) meta=2048 nullable=1 is_null=1 */
###   @29=NULL /* BLOB/TEXT meta=2 nullable=1 is_null=1 */
###   @30=NULL /* VARSTRING(2048) meta=2048 nullable=1 is_null=1 */
###   @31=NULL /* BLOB/TEXT meta=2 nullable=1 is_null=1 */
###   @32=NULL /* DATETIME(6) meta=6 nullable=1 is_null=1 */
###   @33='2023-10-19 19:11:52.000000' /* DATETIME(6) meta=6 nullable=1 is_null=0 */
###   @34=7120728248769577021 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @35=7120728248790548541 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @36=0 /* INT meta=0 nullable=1 is_null=0 */
###   @37=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @38=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @39=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @40=NULL /* VARSTRING(1024) meta=1024 nullable=1 is_null=1 */
###   @41=NULL /* VARSTRING(1024) meta=1024 nullable=1 is_null=1 */
###   @42=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @43=NULL /* JSON meta=4 nullable=1 is_null=1 */
###   @44=NULL /* JSON meta=4 nullable=1 is_null=1 */
###   @45=NULL /* DATETIME(6) meta=6 nullable=1 is_null=1 */
###   @46='DefaultRegion_Linux_www.y3140.com_22' /* VARSTRING(512) meta=512 nullable=1 is_null=0 */
###   @47=NULL /* BLOB/TEXT meta=2 nullable=1 is_null=1 */
###   @48=NULL /* BLOB/TEXT meta=2 nullable=1 is_null=1 */

前面的mysqlbinlog输出是一个Row格式的Binlog记录，它记录了一个INSERT操作在test.tb_temp_backup表上的具体数据。

插入的数据

每一行的输出代表了被插入的一列的数据。例如：

@1=7121090081640677437表示第一列的值是7121090081640677437。
@2='DefaultRegion_Linux_www.y3140.com_22_yab140'表示第二列的值是DefaultRegion_Linux_www.y3140.com_22_yab140。

数据类型和元数据

每一行输出还包含了关于该列的一些元数据，例如：

LONGINT meta=0 nullable=0 is_null=0表示这是一个LONGINT类型的列，不可为空，并且这个特定的值不是null。
VARSTRING(4096) meta=4096 nullable=1 is_null=0表示这是一个最大长度为4096的VARSTRING类型的列，可以为空，但这个特定的值不是null。

NULL和空值

在输出中，NULL和空字符串（''）是两种不同的值。NULL表示该列没有值，而空字符串表示该列的值是一个长度为0的字符串。

时间戳和日期

时间戳和日期列的值以字符串格式显示，例如：

@33='2023-10-19 19:11:52.000000'表示第33列是一个日期时间列，值是2023-10-19 19:11:52.000000。

3. 从Row格式Binlog恢复SQL语句

虽然Row格式的Binlog不直接提供SQL语句，但我们可以根据提供的数据手动重建SQL语句。例如，我们可以使用以下模板来构建插入语句：

INSERT INTO `test`.`tb_temp_backup` VALUES (列1的值, 列2的值, ...);

4. 总结

通过mysqlbinlog工具和--verbose选项，我们可以深入探查MySQL的Binlog，理解数据变更的具体细节。虽然Row格式的Binlog可能初看起来很难理解，但通过逐行分析，我们可以清晰地看到每个数据变更的详细信息。随着对MySQL内部工作机制的理解加深，我们将能够更好地利用Binlog来诊断问题，恢复数据，或优化数据库性能。

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2023-10-21，如有侵权请联系 cloudcommunity@tencent.com 删除