大数据-264 实时数仓-MySQL Binlog配置详解：从原理到实践｜数据恢复与主从复制实战

TL;DR场景需要配置 MySQL 二进制日志进行数据恢复、主从复制或增量审计的场景结论binlog 是 MySQL 核心组件通过配置log-bin、binlog-formatROW可实现可靠的变更追踪与复制能力产出完整的 binlog 配置清单、常用命令速查、可落地的工作配置示例版本矩阵功能状态说明binlog 基本介绍✅ 已验证MySQL 5.7 / MySQL 8.0 通用STATEMENT 模式✅ 已验证记录 SQL 语句日志较小但存在一致性问题ROW 模式✅ 已验证记录行变化可靠安全推荐用于复制场景MIXED 模式✅ 已验证混合自动选择默认以 ROW 为主log-bin 配置✅ 已验证开启二进制日志的核心参数binlog-format 配置✅ 已验证指定日志格式推荐 ROWCanal 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日志showvariableslikelog_bin;执行结果如下图所示查看binlog类型showglobalvariableslikebinlog_format;执行结果如下图所示查看详细的日志配置信息showglobalvariableslike%log%;执行结果如下图所示mysql数据存储目录showvariableslike%dir%;执行结果如下图所示查看binlog的目录showglobalvariableslike%log_bin%;执行结果如下图所示查看当前服务器使用的biglog文件及大小showbinarylogs;执行结果如下图所示查看最新一个binlog日志文件名称和Positionshowmasterstatus;执行结果如下图所示查询binlog 变动信息showbinlog events;执行结果如下图所示修改MySQL在 MySQL 中需要先开启 binlog 写入功能配置 binlog-format 为 ROW 模式。vim/etc/my.cnf.d/mariadb-server.cnf我们对应的修改为[mysqld]# 配置 MySQL replaction 需要定义不要和 Canal 的 slaveId 重复server-id1# 开启 binloglog-binmysql-bin# 选择 ROW 模式binlog-formatROW# dwshow是数据库的名称binlog-do-dbdwshow修改内容如下所示重启MySQL修改后需要重启 MySQL只有重启之后配置才能生效。systemctl restart mariadbcd/var/lib/mysql ll可以看到如下的内容配置授权授权 Canal 链接 MySQL 账号具有作为 MySQL Slave的权限如果已有账户可以直接 Grant。我们需要在 MySQl 中执行GRANTSELECT,REPLICATIONSLAVE,REPLICATIONCLIENTON*.*TOcanal%IDENTIFIEDBYcanal;执行结果如下图所示进行验证selectHost,User from user;show grantsforcanal%;dockerrun--namemysql8-container-p3306:3306-v./data:/var/lib/mysql-eMYSQL_ROOT_PASSWORDmysqlwzk.icu-dmysql:8执行结果如下图所示导入数据接下来往 dbshow 数据库中导入业务数据进行测试导入数据后观察 /var/lib/mysql 目录中是否有 mysql-bin.* 文件如下所示cd/var/lib/mysql ll可以看到是有日志的错误速查卡症状根因定位修复show variables like log_bin返回 OFFbinlog 未开启检查 my.cnf 配置文件是否包含log-bin在[mysqld]段添加log-binmysql-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-idbinlog 文件不生成事务未提交或只有查询操作执行 DML 操作INSERT/UPDATE/DELETE后检查binlog 只记录数据变更操作确认执行了写事务后show binary logs