mysql binlog解析

1 打开binlog

打开db管理工具，或者登陆mysql服务器：mysql -h127.0.0.1 -P3306 -uroot -p1008611

查看binlog是否打开：show variables like 'log_%';

ON即为打开了

如果是OFF，则需要打开binlog：vi /etc/my.cnf

在[mysqld]下面增加： log-bin=mysql-bin binlog-format=ROW server_id=1

重启mysql： service mysqld restart

2 如何查看binlog

【如果只是了解，不要找最大id的log，可能正在写入中或者主从同步中，不要影响了线上操作】

3 binlog格式

【下面的方法都是ROW格式的方法。Statement格式的，需要验证一下。不过，如果需要的是数据清洗等，则需要ROW，因为要获取到具体的数据；如果是主从同步等，则任意格式都可。】

3.1 insert

大概描述一下这里的数据结构：

# at 886746代表本次Event log开始的地址。

#230902 17:53:37Event开始的时间。

server id 1本实例的id。一台服务器上可以部署多个实例，此值为my.conf文件中配置的server_id值。

end_log_pos 886811本条Event结束的地址。也就是下一个Event开始的地址。

CRC32 0xa4b56817循环冗余校验码。一种hash函数，redis集群模式下，判断key属于哪个slot的时候用的CRC64。

Anonymous_GTID在MySQL复制中，每个事务都会被分配一个唯一的GTID，以便在主服务器上生成的事务可以被从服务器正确地复制和应用。然而，有时会出现一些特殊情况，例如在从服务器上执行的本地事务或在主服务器上执行的未命名事务。这些事务没有明确的标识符，因此被称为"Anonymous_GTID"。GTID（Global Transaction Identifier）是MySQL中用于全局事务标识的机制，用于确保在复制环境中的数据一致性和可靠性。

last_committed=80上次commit的id。通常用于描述数据库事务的状态。它表示在一个数据库事务中，最后一个已经被提交（committed）的操作或语句。在数据库中，事务是一组相关的操作，这些操作要么全部成功执行并提交，要么全部回滚（撤销）。当事务中的所有操作都成功执行并提交时，可以说事务已经被"last_committed"，也就是最后一个操作已经被提交。

sequence_number=81本次commit的id。一次事务包含多个操作或者Event，比如上面的都包含了4个Event，它们是同一个事务。如果有多条插入的话，事件只会更多。

rbr_only=yes是MySQL中的一个配置选项，用于启用基于行的复制（Row-Based Replication，RBR）模式。RBR是MySQL复制的一种模式，它以行为单位复制数据更改操作。在MySQL复制中，有三种复制模式可供选择：语句复制（Statement-Based Replication，SBR）、混合复制（Mixed-Based Replication，MBR）和基于行的复制（Row-Based Replication，RBR）。

SET @@SESSION.GTID_NEXT='ANONYMOUS'用于设置当前会话的下一个GTID（全局事务标识）值为'ANONYMOUS'。GTID是MySQL用于在复制环境中唯一标识事务的一种机制。每个事务都会被分配一个唯一的GTID，以便在主服务器上生成的事务可以被从服务器正确地复制和应用。'ANONYMOUS'在这里是一个示例值，可以根据需要设置不同的GTID值，开心就好。

Query：在MySQL复制中，Query Event是指在主服务器上执行的SQL查询语句所生成的二进制日志Event。当启用二进制日志（binary logging）时，MySQL会将主服务器上执行的每个SQL查询语句记录到二进制日志中，以便在复制过程中将这些查询语句传递给从服务器。在DDL的alter操作中，Query会将操作的语句一起携带；DML不会，因为有更详细的Event可以携带。

thread_id=3086本次操作的线程id。

SETTIMESTAMP=1693648417当前的时间戳。

BEGIN、COMMIT开启和提交一个事物，这中间就是一个事务的具体内容。

Table_map:`fatp`.`t_dw_conf_pipeline_template` mapped to number 244table_map事件，携带了database，table名称，并且设置了一个map值为244，后续操作直接用244，不用database，table参数。

Write_rows:table id 244：写入一行，在244表。并不是只有这个一个Flag表示写入一条数据，不同的版本有不同的Flag，详见附录判断写入Event的逻辑。

flags: STMT_END_F表示该事件结束了。此标志出现意味着Event完成。

BINLOG 一连串的十六进制这些数据代表了在主服务器上执行的数据库更改操作。具体来说，BINLOG记录包含了一系列的事件，每个Event都以特定的格式和结构进行编码，并以十六进制的形式进行记录。这些Event可以是查询事件、插入事件、更新事件、删除事件等，用于记录对数据库的各种操作。每个BINLOG Event都有其特定的格式和字段，用于描述和记录相应的数据库更改操作。这些字段包括事件类型、时间戳、数据库名称、表名称、受影响的行数、修改前后的数据值等。可以认为就是本例子中，非# 和 /*开头的内容。

INSERT INTO ****sql逻辑内容。但是里面可以看到，是从table的第一列开始，按照列index进行赋值的。并无具体的字段信息。如果要字段信息的话，需要通过其他的sql逻辑获取。

Xid =22402本次事务id。

自此，一个事务结束。

后续两个属性为文件的结尾，文件有更新则在这之间添加。

SET @@SESSION.GTID_NEXT='AUTOMATIC'GTID_NEXT是一个系统变量，用于指定下一个要执行的事务的GTID值。当将GTID_NEXT设置为 'AUTOMATIC' 时，MySQL会自动为当前会话生成下一个要执行的事务的GTID值。这意味着MySQL会根据复制配置和规则自动分配一个适当的GTID值。

DELIMITER ;用于指定SQL语句分隔符的关键字。在MySQL中，分号（;）通常用作SQL语句的结束符号。然而，在某些情况下，SQL语句本身可能包含分号，这会导致解析器错误地将其视为语句的结束。为了解决这个问题，可以用"DELIMITER"关键字来指定一个自定义的分隔符，以替代默认的分号。这样，解析器就会将自定义的分隔符作为语句的结束符号，而不是使用默认的分号。

【这里比较关键的一个点是，这是一个事务，包含了多个Event，依赖end_log_pos关键字关联，它们是一个整体。】

3.2 update

Update_rows更改字段的事件。不同的版本有不同的Flag，详见附录判断更新事件的逻辑。

这里的BINLOG中，可以看到WHERE查询条件即为原始数据，SET即为更新后的数据。

3.3 delete

Delete_rows删除rows的事件。不同的版本有不同的Flag，详见附录判断删除事件的逻辑。

这里的WHERE即为原始数据。

3.4 alter

Query并且携带了alter语句：alter事件。这个比较特别，没有具体的Event类型来判断，需要结合逻辑。

如果有其他可以判断的方式，还请麻烦联系作者进行补充。

3.4.1 binlog格式

这里补充一个点，binlog有三种格式：ROW、STATEMENT、MIX。

ROW：记录每一行的数据的具体变化。有个不好的地方是，所有的变化的数据都记录了，有的数据后续删除了，但是前面的记录还在，导致binlog很多无效的数据，file利用率不高。

STATEMENT：记录每一个导致数据变化的sql，这样减少了数据的备份。但是会导致复制的主从不一致，比如主从版本不一致导致函数不一致，从而导致两方的值不一致，比如now()函数，还和机器的时间有关系。

MIX：混合两者。

ROW格式在新版本中进行了优化，对于数据变化的操作，记录了数据的具体变化。而对于表结构的变化使用了STATEMENT的形式，只记录sql语句，所以上面的alter binlog只有一条简单的sql，的确是省了很多空间。

Redis的RDB是创建所有的数据备份，适合初始化；AOF是append新数据，并会重写去掉无用的数据，比如过期的、delete的，适合增量同步，减少带宽占用。和binlog是不同的模式，不要搞混了。

3.5 注意

从上可以看出，一个事务包含了多个Event并按照顺序存储在binlog中，是一个整体。

如果要处理binlog，则需要按照顺序处理Event。具体的Delete_rows/Update_rows/Write_rows并不会有db、table的信息，需要从前一个的Table_map事件获取。并根据事件类型做相应的处理。

所以这个binlog文件，只能按照顺序读取事件，获得的事务内容可以并发处理，如数仓的ETL等。

4 谁干的！

有时候，数据库的记录被更改了，不知道谁或者哪个逻辑改的，需要找到具体操作者。

从binlog中可以找到操作更改的事务，在这个事务中找到Query事件，Query事件有个字段为thread_id，从而可以找到本次操作的线程id。

拿着这个线程id，在information_schema.processlist中可以找到属于哪个client ip，从而可以找到是哪个服务、客户端操作了这个事务导致语句被变更了。

【不清楚这个线程是不是一直存在，最好是及时处理。】

5 附录，事件类型

CRC32循环冗余校验码之后的一列是事件类型，具体内容如下：

这里的事件都会传递给使用端，可以在借助这个模块进行binlog监听并做定制化处理。