mysql binlog

二进制日志文件记录了数据库修改的事件，像表的修改，表数据的变更等。也包含潜在的可能修改数据的语句事件。如一些delete或update最后修改的数据行可能是0，也会被记录在binlog中(和日志格式也有一定关系，非row-based)。除此之外binlog还会记录语句的执行时间信息。

binlog的作用

1、用于主从复制，数据同步。这个前面的文章有介绍。

2、数据恢复。可以用于恢复数据库到特定的时间点（Point-in-Time Recovery）或特定的事务状态。通过重放二进制日志中的操作，可以将数据库还原到某个特定时间点之前的状态。

binlog不记录不修改数据的操作语句，像select、show。

开启binlog需要在配置文件中配置log-bin项

[mysqld]
log-bin=mysql-bin

log-bin的值指定了binlog日志文件的base_name。如上指定为mysql-bin。则对应的binlog文件会是mysql-bin.000001、mysql-bin.000002这种。Mysqld在二进制日志base_name后面附加一个数字扩展名来生成二进制日志文件名。每次服务器创建一个新的日志文件时，这个数字都会增加，从而创建一个有序的文件系列。在以下情况mysql会创建一个新的文件：

1、mysql开启或重启时刻

2、当前日志文件达到了max_binlog_size设置的值，也就是最大binlog文件大小。默认1GB。

可以通过名mysql> SHOW BINARY LOGS;来查看当前数据库binlog文件。

由于有多个日志文件的存在，mysql会用一个index文件来记录已经使用的binlog文件列表，该文件和binlog日志文件名相同，文件后缀是’.index’。如mysql-bin.index。也可以通过log-bin-index来指定文件名。

如果当前session不需要记录binlog，可以设置 SET sql_log_bin=OFF 。暂时关闭当前会话的binlog记录。

binlog格式

binlog有三种可供选择的格式：

1、STATEMENT

MySQL将会记录执行的SQL语句，包括对表的增、删、改操作。这样可以确保在执行一条相同的SQL语句时，得到相同的结果。但是，如果在执行过程中涉及到了MySQL内部函数、触发器、存储过程等，则可能会导致数据不一致的问题。因此，在使用STATEMENT格式时需要特别注意这些情况。比如在插入一个日期类型的字段时候使用now()函数取值，now()取系统时间，在不同的时点now()值不同，可能导致数据不一致。还有类似的UUID()函数。

2、ROW

ROW格式中，MySQL将会记录每一行数据的变化，包括对表的增、删、改操作。这种格式可以确保数据的完整性和一致性，但是对于大表或者数据量较大的表，可能会导致比较大的binlog文件，造成存储和传输的困难。比如一个update或delete涉及修改行数较多时，会生成对应每行的修改更新语句日志。在一些数据实时同步时被要求必须使用该格式。

3、MIXED

MIXED格式是STATEMENT和ROW格式的混合形式，MySQL会自动根据不同的操作选择合适的格式。对于简单的操作（如插入、更新、删除操作），使用STATEMENT格式；对于较为复杂的操作（如涉及MySQL内部函数、触发器、存储过程等），则使用ROW格式。这种混合形式可以兼顾数据完整性和binlog文件大小的平衡。

可以通过binlog-format来进行设置日志格式。如binlog-format=ROW

binlog文件查看

可以使用mysqlbinlog命令来查看binlog日志文件，mysqlbinlog时一个单独的命令，不用登录直接命令行操作即可。

命令格式

mysqlbinlog [options] log_file 

mysqlbinlog有很多参数，这里列举下一些常见的参数

–base64-output=[decode-rows|auto|never] ：指定如何处理Base64编码的数据。

-d, --database=name ：指定数据库名

-r, --result-file=name：指定输出到对应的文件

–start-datetime=name：指定开始解析的时间点

–stop-datetime=name：指定结束解析的时间点

-j, --start-position=N：指定开始解析的位置

–stop-position=N：指定结束解析的位置

-v, --verbose：显示详细信息，在ROW模式下添加该参数可以看到具体的sql语句。

-o, --offset=N 跳过N行

指定数据库base64解码解析：

> mysqlbinlog --database=db_test --base64-output=decode-rows  mysql-bin.000003|more

按时间解析：

>mysqlbinlog --start-datetime="2024-02-20 00:00:00" --stop-datetime="2024-02-21 00:00:00" mysql-bin.000003 > output.sql

指定开始解析位置：

>mysqlbinlog -j 15028 mysql-bin.000003 > from-15028.out

连接远端mysql

>mysqlbinlog -R -h 192.168.101.2 -p mysql-bin.000003

-R 等价于–read-from-remote-server ，从远端服务器读取

-h,-p指定host和password，-P指定端口

解析出的binlog信息如下：

# at 504263
#240221 15:31:11 server id 1  end_log_pos 504342 CRC32 0xb683e8bc 	Anonymous_GTID	last_committed=41	sequence_number=42	rbr_only=yes	original_committed_timestamp=1708500671155876	immediate_commit_timestamp=1708500671155876	transaction_length=308
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1708500671155876 (2024-02-21 15:31:11.155876 CST)
# immediate_commit_timestamp=1708500671155876 (2024-02-21 15:31:11.155876 CST)
/*!80001 SET @@session.original_commit_timestamp=1708500671155876*//*!*/;
/*!80014 SET @@session.original_server_version=80028*//*!*/;
/*!80014 SET @@session.immediate_server_version=80028*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 504342
#240221 15:31:11 server id 1  end_log_pos 504429 CRC32 0xb5706921 	Query	thread_id=87	exec_time=0	error_code=0
SET TIMESTAMP=1708500671/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=255/*!*/;
BEGIN
/*!*/;
# at 504429
#240221 15:31:11 server id 1  end_log_pos 504486 CRC32 0x78d6bd44 	Table_map: `db_test`.`account` mapped to number 299
# at 504486
#240221 15:31:11 server id 1  end_log_pos 504540 CRC32 0xf5262e79 	Update_rows: table id 299 flags: STMT_END_F
### UPDATE `db_test`.`account`
### WHERE
###   @1=6
###   @2=1
### SET
###   @1=6
###   @2=3
# at 504540
#240221 15:31:11 server id 1  end_log_pos 504571 CRC32 0xfc8b0084 	Xid = 100230
COMMIT/*!*/;

上面一条更新语句，可以看到能解析出原sql语句，时间信息，postion信息，还有一些事务相关的信息。

binlog的写入

binlog在事务提交时候进行日志的写入。这里不禁想到上篇文章写的redo log也是在事务提交的时候进行写入。这里就涉及到了日志双写，都需要进行持久化且完整。binlog日志是mysql server层产生日志，redo log是引擎层Innodb需要产生的日志。server层需要解决多个引擎层之间的事务一致性。这里需要内部的XA事务（分布式事务）来完成。其中协调者是server层。

XA采用两阶段提交协议保证分布式事务的一致性。

两阶段提交

两阶段提交（Two-Phase Commit，2PC）是一种用于分布式系统中实现事务的协议，旨在确保所有参与者要么全部提交事务，要么全部回滚事务，以保持数据的一致性。

在两阶段提交协议中，有一个协调者（Coordinator）和多个参与者（Participants）。协调者负责协调每个参与者的状态，控制整个事务的提交或回滚过程。整个过程可以分为以下两个阶段：

1. 准备阶段（Prepare Phase）：
   • 协调者向所有参与者发送事务准备请求，要求它们准备好执行事务操作。
   • 参与者接收到请求后，会执行本地事务操作，并将准备好的状态反馈给协调者。
   • 协调者等待所有参与者的响应，如果所有参与者都准备就绪，则进入下一阶段；否则，中止事务。
2. 提交阶段（Commit Phase）：
   • 如果在准备阶段所有参与者都成功准备好，协调者向所有参与者发送提交事务的请求。
   • 参与者接收到提交请求后，执行提交操作，并向协调者发送确认消息。
   • 协调者收到所有参与者的确认消息后，最终提交事务；否则，向所有参与者发送回滚事务的请求。

通过两阶段提交协议，可以确保在分布式环境中的所有参与者要么全部提交事务，要么全部回滚事务，避免了数据不一致的情况发生。

回到mysql的binlog和redolog 这里的协调者是server层binlog。参与者是引擎层的redolog。

prepare阶段

1、 redo log日志写入，XA事务状态为prepare状态

2、binlog日志写入，XA事务状态prepare状态

commit阶段

3、binlog日志写入提交，XA事务状态为commit

4、redolog日志写入提交，XA事务状态为commit

这里将redolog日志的写入分成两步进行提交。在上面的解析的binlog内容可以看到Xid(XA事务ID)信息。来分析下两阶段提交如何能保证数据完整：

这里把2和3部合并了来说，因为都是binlog的连续操作，就是整个分三大步：

A、redolog xa_prepare

B、binlog write

C、redolog xa_commit

这样会有两处间隙时刻即A|B之间，B|C之间，如果在这两处操作空隙数据库发生crash。数据库如何恢复？

数据库crash，在数据库启动恢复的时候，会根据redolog进行恢复，遇到是prepare状态的，会根据对应的XID去binlog中去找对应的操作记录是否存在，如果存在事务进行提交，如果不存在则丢弃。即A|B之间的crash会进行回滚，B|C之间的crash可以提交，这样就能保证日志数据的一致性。

binlog的写入还受sync_binlog参数的影响。

1. sync_binlog=0：表示不进行二进制日志的同步操作，即异步写入。这种方式下，MySQL将日志写入操作系统的缓存，并不会等待日志真正写入磁盘，因此具有较高的性能，但也存在数据丢失的风险。
2. sync_binlog=1：表示每次提交事务都会强制将二进制日志同步到磁盘。这种方式下，MySQL会在每个事务提交时等待日志写入磁盘完成后再返回确认消息，以确保数据的持久性。虽然提供了较高的数据一致性，但也会对性能产生一定的影响。
3. sync_binlog=N（N大于1）：表示每N个事务才进行一次二进制日志的同步操作。这种方式是在性能和数据一致性之间进行权衡的选择，可以在一定程度上提升性能，但也会增加数据丢失的风险。

默认值是1，即每个事务同步写盘。建议和redolog的innodb_flush_log_at_trx_commit同步保持为1。