binlog
binlog（二进制日志）记录数据库的变更（包括dml和ddl，不包括查询）。主要用于数据恢复、数据复制、高可用性（从库复制主库的binlog）
可以使用MySQL 提供的 mysqlbinlog 工具将binlog日志转换为sql语句
binlog格式（通过binlog_format参数配置）：
- statement（语句） - 只记录sql语句，占用空间较小，但是复杂的语句可能导致数据不一致
- row（行） - 记录变更的行以及前后值，占用空间较大
- mixed（混合） - 混合了前两种方式，简单sql用statement格式，复杂sql用row格式
主从复制
过程：
- 主库记录变更到binlog
- 从库通过I/O线程拉取binlog并存储到从库本地的中继日志relay log
- 从库执行binlog语句
种类：
- 异步复制：主库不会等待从库拉取binlog
- 半同步复制：主库至少会等待一个从库拉取binlog并存入中继日志
- 全同步复制：主库需要等待所有主库确认
- 多源复制：从库可以从多个主库同步binlog
常用配置：
| 配置参数 | 描述 | 默认值 | 示例值 |
|---|---|---|---|
binlog_format |
定义二进制日志的记录格式。可以是 ROW、STATEMENT、MIXED。 |
ROW (MySQL 8.0) |
ROW, STATEMENT, MIXED |
log_bin |
启用或禁用二进制日志功能。设置为 ON 或 OFF。 |
OFF |
ON, OFF |
log_bin_basename |
设置 Binlog 文件的基础路径和名称。 | NULL |
/var/lib/mysql/mysql-bin |
log_bin_index |
定义二进制日志索引文件的位置和名称，索引文件记录所有 Binlog 文件的列表。 | 自动生成 | /var/lib/mysql/mysql-bin.index |
expire_logs_days |
设置 Binlog 文件的自动删除天数。超出此天数的 Binlog 文件将被自动删除。 | 0 (不自动删除) |
7, 30 |
max_binlog_size |
设置每个 Binlog 文件的最大大小。达到该大小后，会生成一个新的 Binlog 文件。 | 1GB |
100MB, 500MB |
sync_binlog |
设置多少次事务提交后，将 Binlog 数据同步到磁盘。 | 1 |
0 (异步), 1 (每次同步) |
binlog_cache_size |
设置用于存储事务期间 Binlog 事件的缓存大小。事务完成后，缓存会被释放。 | 32768 (32KB) |
65536 (64KB), 128K |
binlog_checksum |
定义是否对 Binlog 事件添加校验和。可以是 NONE 或 CRC32。 |
CRC32 |
NONE, CRC32 |
binlog_row_image |
设置在 Binlog 中记录的行数据的细节程度。可选值为 FULL、MINIMAL 和 NOBLOB。 |
FULL |
FULL, MINIMAL, NOBLOB |
binlog_rows_query_log_events |
启用或禁用在行格式日志中记录原始 SQL 语句。 | OFF |
ON, OFF |
log_slave_updates |
使从库也记录它从主库接收到的所有更新到它自己的 Binlog 中。 | OFF |
ON, OFF |
binlog_order_commits |
控制在 Binlog 中事务提交的顺序。启用时，事务按提交顺序记录。 | ON |
ON, OFF |
binlog_group_commit_sync_delay |
设置在组提交时，事务写入 Binlog 前等待的时间（微秒）。 | 0 |
100, 1000 |
binlog_group_commit_sync_no_delay_count |
设置在组提交时，不等待时间的最大事务数。 | 0 |
10, 100 |
binlog_expire_logs_seconds |
以秒为单位设置 Binlog 文件的自动删除时间（替代 expire_logs_days）。 |
0 |
604800 (7天) |
enforce_gtid_consistency |
强制 GTID 一致性。启用后，仅允许在所有存储引擎中执行安全的事务。 | OFF |
ON, OFF |
gtid_mode |
启用或禁用 GTID（全局事务标识）功能，支持值为 OFF、ON、OFF_PERMISSIVE、ON_PERMISSIVE。 |
OFF |
ON, OFF |
binlog常用命令
- 强制刷新binlog到磁盘
flush logs - 删除某些范围的binlog
purge binary logs before sometime; purge binary logs to binlogfilename（删除binlogfilename之前的binlog文件） - 开启关闭binlog
set GLOBAL log_bin=ON/OFF - 查看binlog文件列表
show binary logs; - 查看正在写入的binlog文件名、位置、相关的 GTID 和其他信息
show master status - 查看从库的binlog位置、主从复制状态等
show slave status - 设置binlog格式
set GLOBAL binlog_format='ROW' - 按事件的顺序展示文件日志中的事件
show binary events in binaryfile - 解析binlogfile
mysqlbinlog binlogfille - 从binlog中恢复数据
mysqlbinlog mysql-bin.000001 | mysql -u root -p - 查看从库的 Relay Log 状态
SHOW RELAYLOG EVENTS IN 'relay-log.000001';
事务提交与binlog的关系
过程中涉及2种角色：
- binlog-cache 是mysql在内存中为每个会话session分配的临时缓冲区
- binlog-file 位于磁盘中
- 在事务发生过程中，会先把变更记录到binlog-cache
- 事务提交时，会把这部分日志写入操作系统的缓存中，此时还没到磁盘中的binlog-file
- 根据
sync-binlog参数来控制同步到磁盘中的binlog-file的时机
sync_binlog = 1：每次事务提交时，MySQL 都会将 Binlog 数据同步到磁盘。这种方式最安全，但也会降低性能。sync_binlog = 0：事务提交后不强制将 Binlog 数据同步到磁盘，数据会在操作系统认为合适的时间同步。这种方式性能较高，但在崩溃时可能导致数据丢失。sync_binlog = N：MySQL 每提交 N 个事务时，将 Binlog 数据同步到磁盘。它在性能和数据安全性之间做了折中。
需要注意的是，sync-binlog参数只是用来控制同步binlog的时机，不会控制实际数据存储到数据文件（ibd）的时机（此时机通常是innodb_flush_log_at_trx_commit控制的）。innodb_flush_log_at_trx_commit 的三个取值区别在于：0 将日志每秒写入磁盘，性能最佳但可能丢失1秒内的数据；1 每次事务提交时立即写入并同步日志到磁盘，确保数据安全但性能较低；2 每次提交时写入内存并每秒刷新到磁盘，提供性能和安全性的平衡。