MySQL Binlog 格式对比

# MySQL Binlog 格式对比Binlog 是 MySQL 复制和数据恢复的核心。三种格式各有优劣选错了会影响数据一致性。三种格式-- 查看当前格式SHOWVARIABLESLIKEbinlog_format;-- 三种值-- STATEMENTSBR-- ROWRBR-- MIXEDMBRSTATEMENT 格式SBR-- 记录原始 SQL 语句-- Binlog 内容INSERTINTOuser(id,name)VALUES(1,Tom);UPDATEaccountSETbalancebalance100WHEREid1;优点- 日志量小只存 SQL - 节省磁盘空间 - 主从延迟相对小缺点-- 不确定函数在主从结果不一致INSERTINTOlog(created_at)VALUES(NOW());-- 主库 NOW() 2024-01-01 12:00:00-- 从库延迟 5 秒执行NOW() 2024-01-01 12:00:05-- 主从数据不一致-- 其他问题-- LIMIT 不带 ORDER BY-- 存储过程/触发器-- UUID() 等不确定函数ROW 格式RBR-- 记录行变化修改前 修改后-- Binlog 内容-- ### UPDATE account-- ### WHERE-- ### 11 (id)-- ### 2500 (balance 修改前)-- ### SET-- ### 11-- ### 2600 (balance 修改后)优点- 数据一致性最好记录实际变化 - 不会受不确定函数影响 - 安全可靠缺点- 日志量大大事务尤其明显 -- DELETE 100万条ROW 格式要记录 100 万行的变化 -- STATEMENT 只记录一条 SQL - 无法从 binlog 直接看到 SQL需要 mysqlbinlog --base64-outputDECODE-ROWS -vMIXED 格式MBRMySQL 自动选择 - 一般情况用 STATEMENT日志小 - 遇到不确定函数自动切换 ROW 是 STATEMENT 和 ROW 的折中方案自动切换到 ROW 的情况-- 包含 NOW()、UUID() 等函数-- 包含 LIMIT 没有 ORDER BY-- 使用存储过程/触发器/自定义函数-- UPDATE 中使用了不确定表达式如何选择场景推荐格式主从复制ROW推荐数据恢复ROW日志审计STATEMENT可读性好磁盘空间有限STATEMENT折中方案MIXED结论新项目一律用 ROW数据一致性最重要。查看 Binlog 内容# STATEMENT 格式mysqlbinlog /var/lib/mysql/mysql-bin.000001# ROW 格式需要解码mysqlbinlog --base64-outputDECODE-ROWS-v/var/lib/mysql/mysql-bin.000001# 按时间范围mysqlbinlog --start-datetime2024-01-01 00:00:00--stop-datetime2024-01-02 00:00:00/var/lib/mysql/mysql-bin.000001# 按数据库过滤mysqlbinlog--databasemydb /var/lib/mysql/mysql-bin.000001Binlog 清理-- 自动清理保留 7 天SETGLOBALexpire_logs_days7;-- 手动清理PURGEBINARYLOGS BEFORE2024-01-01 00:00:00;PURGEBINARYLOGSTOmysql-bin.000010;-- 查看当前 binlogSHOWBINARYLOGS;小结格式一致性日志大小可读性推荐STATEMENT差小好不推荐ROW好大差推荐MIXED中中中折中相关阅读[MySQL 主从复制原理][MySQL GTID 模式详解][MySQL 数据备份与恢复]