MySQL 二进制日志
一、概述
二进制日志记录数据库更改和可能更改的事件，以及每个语句的执行时长。
- 不记录的操作：类似
show、select这类不更改数据的操作。 - 密码处理：二进制中的密码会被重写。
- 新文件创建场景：
- 服务启动或重启
- 服务端刷新日志
- 当前日志文件达到
max_binlog_size大小
- 事务记录特点：仅记录或读取完整事务；写入大事务时，日志可能大于
max_binlog_size，因同一事务不会拆分到多个文件。
二、配置相关
- 加密支持：8.0.14 版本开始，可对二进制日志文件和中继日志文件进行加密。
- 同步保障：InnoDB 对 XA 事务中两阶段提交的支持，确保二进制日志和 InnoDB 数据文件同步。
三、用途
1. 主从复制
主节点记录二进制日志，从节点复制主节点的二进制日志并进行回放。
2. 数据恢复
- 事务性表：二进制日志在事务完成后、提交之前（锁未释放时）记录。
- 非事务性表：更新后立即记录二进制日志。
四、系统变量与状态变量
1. 系统变量
（1）二进制日志属性
log_bin：控制是否开启二进制日志及日志文件名；会话级变量为sql_log_bin。log_bin_basename：二进制日志的目录和前缀，只读变量。log_bin_index：二进制日志的索引文件，包含日志名称，用于跟踪日志，扩展名为.index，与二进制日志在同一目录。log_slave_updates：默认从节点不将从主节点接收的日志写入自身二进制日志；启用后可记录，适用于从节点同时作为其他主节点的场景。binlog_format：二进制日志的格式。max_binlog_size：二进制日志的大小。
（2）缓存
binlog_cache_size：二进制日志缓冲区大小；处理事务的线程启动时为语句分配缓冲区，若语句大于缓冲区，会创建临时文件存储事务，线程结束后删除。max_binlog_cache_size：限制缓存事务的总大小；若事务大于此值，会失败并回滚；默认 4G，范围 4096-4G。
（3）同步机制
sync_binlog：二进制日志的同步机制（双 1 参数之一）；值为 1（默认）时，每次写入都同步到磁盘，最安全但最慢。
（4）过时变量
--innodb_support_xa：已过时；默认值 1 时，确保二进制日志和 InnoDB 数据文件同步。
2. 状态变量
binlog_cache_use：显示使用缓冲区和临时文件存储语句的事务数。binlog_cache_disk_use：显示实际使用临时文件的事务数。
五、事件校验
- 默认验证：服务器记录事件长度和事件本身，通过事件长度验证是否正确写入。
- 校验和验证：可设置校验和，相关系统变量包括
binlog_checksum、master_verify_checksum、slave_sql_verify_checksum。
六、评估方式
--binlog-do-db与--replicate-do-db评估方式相同。--binlog-ignore-db与--replicate-ignore-db评估方式相同。
七、写入二进制日志的变量
- 约束检查
foreign_key_checks、unique_checks
- 字符集和排序规则
character_set_client、collation_connection、collation_database、collation_server
- 其他
sql_mode（NO_DIR_IN_CREATE 模式不复制）、sql_auto_is_null
八、对二进制日志的操作
1. 查看二进制日志/中继日志内容
shell> mysqlbinlog log_file | mysql -h server_name
shell> mysqlbinlog -d zabbix --base64-output=decode-rows -vvv log_file
2. 清理二进制日志
（1）自动清理
-
系统变量
expire_logs_days：设置二进制日志的过期时间，默认值为 0。mysql> show variables like '%expire_log%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | expire_logs_days | 0 | +------------------+-------+ -
设置依据：建议保留 2 个全备周期的天数 + 1。
（2）手动清理
-
清理所有二进制日志：
mysql> reset master; -
清理部分二进制日志：
mysql> PURGE BINARY LOGS TO 'mysql-bin.000004'; mysql> PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26';Note：
purge binary logs语句会更新二进制日志的索引文件。
3. 日志轮换
（1）生成新日志
立即将脏数据刷新到磁盘，并启用新的二进制日志：
mysql> flush logs; -- 刷新所有日志
mysql> flush binary logs; -- 只刷新二进制日志
（2）重置日志编号
使日志编号从 1 开始重新计数：
mysql> reset master;
4. 截取二进制日志
（1）未开启 GTID
-
记录单元：事件（event），是二进制日志的最小单元。
- DDL 语句：每个语句为一个事件。
- DML 语句：一个事务包含多个事件（如
begin、语句、commit）。
-
事件的开始和结束：通过
pos（开始位置）和end_log_pos（结束位置）标识，方便截取。 -
截取方法：
-
查看日志列表：
mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 177 | | mysql-bin.000002 | 201 | | mysql-bin.000003 | 154 | +------------------+-----------+ -
查看正在使用的二进制日志：
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 414 | | | | +------------------+----------+--------------+------------------+-------------------+ -
查看事件列表：
mysql> show binlog events in 'mysql-bin.000003'; +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------+ | mysql-bin.000003 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.31-log, Binlog ver: 4 | | mysql-bin.000003 | 123 | Previous_gtids | 6 | 154 | | | mysql-bin.000003 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000003 | 219 | Query | 6 | 291 | BEGIN | | mysql-bin.000003 | 291 | Table_map | 6 | 339 | table_id: 109 (test.t1) | | mysql-bin.000003 | 339 | Write_rows | 6 | 383 | table_id: 109 flags: STMT_END_F | | mysql-bin.000003 | 383 | Xid | 6 | 414 | COMMIT /* xid=19 */ | | mysql-bin.000003 | 414 | Anonymous_Gtid | 6 | 479 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000003 | 479 | Query | 6 | 551 | BEGIN | | mysql-bin.000003 | 551 | Table_map | 6 | 599 | table_id: 109 (test.t1) | | mysql-bin.000003 | 599 | Write_rows | 6 | 644 | table_id: 109 flags: STMT_END_F | | mysql-bin.000003 | 644 | Xid | 6 | 675 | COMMIT /* xid=24 */ | | mysql-bin.000003 | 675 | Anonymous_Gtid | 6 | 740 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000003 | 740 | Query | 6 | 855 | use `test`; create table t2(id int, name varchar(20)) | +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------+ -
截取事件（如截取事务，需包含
begin到commit）：mysqlbinlog --start-position=740 --stop-position=855 mysql-bin.000003 /tmp/binlog.sql
-
（2）已开启 GTID
-
记录单元：事务。
-
截取方法：
shell> mysqlbinlog --skip-gtids --include-gtids=2dac652a-5705-11eb-9e3a-000c29a8b526:3 /mdata/binlog/mysql-bin.000004 /tmp/gtid.sql--skip-gtids：导出时忽略 GTID，恢复时系统自动生成新 GTID。--include-gtids：指定要截取的事务。--exclude-gtids：指定要排除的事务。--sync-binlog=1：每次事务提交立即刷写 binlog 到磁盘。
九、二进制日志的格式
1. 系统变量
（1）binlog_format
- 作用：定义二进制日志的记录格式。
- 有效值：
STATEMENT（基于语句模式）：主从复制时可能存在问题，如insert into t1 values(now());执行时间可能不同。ROW（默认，基于行模式）：复制结构中表必须有主键；并发插入会转换为普通插入（如CREATE ... SELECT），确保备份恢复时表的精确性。MIXED（混合模式）：默认使用基于语句模式，某些情况自动切换为基于行模式。
（2）binlog_row_event_max_size
- 作用：基于行模式下，二进制日志的最大大小。
- 说明：行以块大小存储，块大小不超过该值（单位 bytes），必须是 256 的倍数，默认 8192。
2. 设置二进制日志格式
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'STATEMENT';
Note：二进制日志的记录格式还可通过存储引擎设置或限制，有助于消除不同存储引擎间主从复制的问题。
（1）设置会话级变量的原因
- 多小更改的会话：适合基于行模式。
- 一次性多行更新：基于语句模式效率更高。
- 长时少量更改语句：基于行模式更有利。
（2）无法动态修改的情况
- 在函数和触发器中。
- 启用了 NDB 存储引擎。
- 当前启用基于行模式且有打开的临时表（临时表仅在基于语句模式和混合模式下记录，切换格式可能导致复制问题；主从复制中主从节点格式必须一致；基于行模式下 DDL 语句仍用基于语句模式）。
- 复制通道有打开的临时表。
- 当前正在运行任何复制通道应用程序线程。
3. 混合模式自动切换格式
混合模式下，以下情况自动从基于语句模式切换到基于行模式：
- DML 语句更新 NDBCLUSTER 表。
- 函数中包含
UUID()。 - 更新含自增列的表且调用触发器或存储过程。
- 视图主体需基于行模式时，创建视图的语句也使用该模式。
- 调用自定义函数。
- 语句按行记录且用到临时表时，后续语句均按行记录（直到临时表删除）；8.0 版本中临时表操作不影响日志模式。
- 用到
found_row()或row_count()。 - 用到
user()、current_user()、current_user变量。 - 语句引用一个或多个系统变量。
- 涉及 mysql 数据库中的日志表。
- 用到
load_file()。
十、二进制日志的事务压缩
1. 基本说明
启用后，使用 zstd 算法压缩事务有效负载，作为单个事件（Transaction_payload_event）写入二进制日志；以压缩状态发送到副本并写入中继日志，节省存储和带宽，但增加 CPU 和内存负载；仅在查看或回放时解压缩；可与连接压缩结合使用。
2. 不记入事务压缩的事件
- 与事务的 GTID 相关的事件。
- 其他控制事件（如视图更改事件、心跳事件）。
- 突发事件及包含这些事件的所有事务。
- 非事务性事件及包含它们的所有事务。
- 基于语句的二进制日志记录的事件（仅适用于基于行的格式）。
3. 启用二进制日志压缩的动作
半同步复制中，副本收到完整的 Transaction_payload_event 时确认事务。
4. 合并压缩和未压缩的事务
支持压缩的 MySQL 版本可处理压缩和未压缩事务有效载荷的混合；复制/组复制中，不同主机的压缩相关系统变量可不同。
5. 配置
（1）系统变量
binlog_transaction_compression：启用压缩，默认 OFF。binlog_transaction_compression_level_zstd：zstd 压缩级别，默认 3，范围 1-22。
（2）主从复制
- 取代
CHANGE MASTER TO的CHANGE REPLICATION SOURCE TO包含：SOURCE_COMPRESSION_ALGORITHMS、SOURCE_ZSTD_COMPRESSION_LEVEL。
（3）组复制
group_replication_compression_threshold：组复制的压缩阈值。group_replication_recovery_compression_algorithm：组复制恢复的压缩算法。group_replication_recovery_zstd_compression_level：组复制恢复的 zstd 压缩级别。
6. 监控二进制事务压缩
（1）性能表
-
performance_schema.binary_log_transaction_compression_stats：统计压缩率及最后一个事务的影响，可通过truncate重置。mysql> select * from performance_schema.binary_log_transaction_compression_stats\G *************************** 1. row *************************** LOG_TYPE: BINARY COMPRESSION_TYPE: NONE TRANSACTION_COUNTER: 1 COMPRESSED_BYTES_COUNTER: 107 UNCOMPRESSED_BYTES_COUNTER: 107 COMPRESSION_PERCENTAGE: 0 FIRST_TRANSACTION_ID: ANONYMOUS FIRST_TRANSACTION_COMPRESSED_BYTES: 107 FIRST_TRANSACTION_UNCOMPRESSED_BYTES: 107 FIRST_TRANSACTION_TIMESTAMP: 2021-03-17 08:15:07.809186 LAST_TRANSACTION_ID: ANONYMOUS LAST_TRANSACTION_COMPRESSED_BYTES: 107 LAST_TRANSACTION_UNCOMPRESSED_BYTES: 107 LAST_TRANSACTION_TIMESTAMP: 2021-03-17 08:15:07.809186 -
performance_schema.events_stages_current：显示事务在压缩/解压缩阶段的状态及进度。
（2）查看帮助
mysqlbinlog --verbose --help | more