TL;DR
- 场景：需要配置 MySQL 二进制日志进行数据恢复、主从复制或增量审计的场景
- 结论：binlog 是 MySQL 核心组件，通过配置
log-bin、binlog-format=ROW可实现可靠的变更追踪与复制能力 - 产出：完整的 binlog 配置清单、常用命令速查、可落地的工作配置示例
版本矩阵
| 功能 | 状态 | 说明 |
|---|---|---|
| binlog 基本介绍 | ✅ 已验证 | MySQL 5.7+ / MySQL 8.0 通用 |
| STATEMENT 模式 | ✅ 已验证 | 记录 SQL 语句，日志较小但存在一致性问题 |
| ROW 模式 | ✅ 已验证 | 记录行变化，可靠安全，推荐用于复制场景 |
| MIXED 模式 | ✅ 已验证 | 混合自动选择，默认以 ROW 为主 |
| log-bin 配置 | ✅ 已验证 | 开启二进制日志的核心参数 |
| binlog-format 配置 | ✅ 已验证 | 指定日志格式，推荐 ROW |
| Canal + binlog 集成 | ⚠️ 待验证 | 需确保 slaveId 不冲突 |
配置 MySQL 的 binlog
基本介绍
MySQL 的二进制日志（Binary Log，简称 binlog）是 MySQL 数据库中的一种日志文件类型，它记录了对数据库执行的所有更改操作（不包括 SELECT 和 SHOW 等查询操作）。它主要用于数据恢复、复制和审计等场景。
Binlog 的作用
- 数据恢复：在数据库崩溃或误操作导致数据丢失时，可以通过 binlog 重放来恢复数据。
- 主从复制：Binlog 是 MySQL 主从复制机制的核心，通过将主库的 binlog 传输到从库并重放，从而实现数据同步。
- 数据审计：记录了所有数据更改的具体时间和操作人，便于审计与分析。
- 增量备份：Binlog 支持记录增量数据变化，结合快照备份，可以快速恢复到指定时间点。
Binlog 的工作原理
- 事件记录：Binlog 将每一个对数据的更改操作记录为"事件"（Event），按时间顺序存储。
- 日志格式：记录的数据包含事务 ID、表名、变更类型（INSERT、UPDATE、DELETE）、具体变更内容等。
- 写入过程：当用户执行事务时，数据变更先写入 binlog 缓冲区，事务提交后刷新到 binlog 文件。
- 日志滚动：Binlog 文件会按配置大小或时间定期轮转生成新的日志文件，并删除旧日志（根据配置）。
Binlog 的日志格式
STATEMENT 模式
记录 SQL 语句。 优点：日志较小。 缺点：依赖环境，某些 SQL 执行结果可能不一致。
ROW 模式
记录具体的行变化。 优点：安全可靠，适合复制。 缺点：日志较大。
MIXED 模式
混合模式，自动选择最合适的模式（一般以 ROW 为主）。
常见命令
是否启用binlog日志
show variables like 'log_bin';
执行结果如下图所示：
查看binlog类型
show global variables like 'binlog_format';
执行结果如下图所示：
查看详细的日志配置信息
show global variables like '%log%';
执行结果如下图所示：
mysql数据存储目录
show variables like '%dir%';
执行结果如下图所示：
查看binlog的目录
show global variables like "%log_bin%";
执行结果如下图所示：
查看当前服务器使用的biglog文件及大小
show binary logs;
执行结果如下图所示：
查看最新一个binlog日志文件名称和Position
show master status;
执行结果如下图所示：
查询binlog 变动信息
show binlog events;
执行结果如下图所示：
修改MySQL
在 MySQL 中，需要先开启 binlog 写入功能，配置 binlog-format 为 ROW 模式。
vim /etc/my.cnf.d/mariadb-server.cnf
我们对应的修改为：
[mysqld]
# 配置 MySQL replaction 需要定义，不要和 Canal 的 slaveId 重复
server-id=1
# 开启 binlog
log-bin=mysql-bin
# 选择 ROW 模式
binlog-format=ROW
# dwshow是数据库的名称
binlog-do-db=dwshow
修改内容如下所示：
重启MySQL
修改后需要重启 MySQL，只有重启之后配置才能生效。
systemctl restart mariadb
cd /var/lib/mysql
ll
可以看到如下的内容：
配置授权
授权 Canal 链接 MySQL 账号具有作为 MySQL Slave的权限，如果已有账户可以直接 Grant。 我们需要在 MySQl 中执行：
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%' IDENTIFIED BY 'canal' ;
执行结果如下图所示：
进行验证：
select Host,User from user;
show grants for 'canal'@'%';
docker run --name mysql8-container -p 3306:3306 -v ./data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=mysql@wzk.icu -d mysql:8
执行结果如下图所示：
导入数据
接下来往 dbshow 数据库中导入业务数据进行测试，导入数据后，观察 /var/lib/mysql 目录中是否有 mysql-bin.* 文件，如下所示：
cd /var/lib/mysql
ll
可以看到是有日志的：
错误速查卡
| 症状 | 根因 | 定位 | 修复 |
|---|---|---|---|
show variables like 'log_bin' 返回 OFF | binlog 未开启 | 检查 my.cnf 配置文件是否包含 log-bin | 在 [mysqld] 段添加 log-bin=mysql-bin 后重启 |
| Canal 连接失败，权限不足 | Canal 账户未授权 REPLICATION 权限 | 执行 show grants for 'canal'@'%' | 重新执行 GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%' IDENTIFIED BY 'canal'; |
| binlog-do-db 不生效 | 配置的数据库名与实际不一致 | 确认 show variables like 'datadir' 实际库名 | 确保 binlog-do-db 与实际数据库名完全匹配 |
| Canal 与从库 slaveId 冲突 | 多个消费者使用相同 slaveId | 检查 show slave status\G 中的 Slave_IO_Running | 为每个 Canal 实例分配唯一 server-id，不同于 MySQL 从库的 slave-id |
| binlog 文件不生成 | 事务未提交或只有查询操作 | 执行 DML 操作（INSERT/UPDATE/DELETE）后检查 | binlog 只记录数据变更操作，确认执行了写事务后 show binary logs |
