#binlog 三种格式对比

-- Master 执行：
UPDATE orders SET amount = amount + 100 WHERE user_id = 1;

-- binlog 记录：
UPDATE orders SET amount = amount + 100 WHERE user_id = 1;
-- (记录的是原始 SQL 语句)

-- Master：
INSERT INTO orders (order_no, created_at) VALUES ('A001', NOW());
-- binlog: INSERT INTO orders (order_no, created_at) VALUES ('A001', NOW());

-- Slave：
-- NOW() 在 Slave 执行时可能返回不同时间
-- 导致 created_at 不一致

-- Master 执行：
UPDATE orders SET amount = amount + 100 WHERE user_id = 1;

-- binlog 记录（每行变更）：
UPDATE orders
WHERE id=1 AND user_id=1 AND amount=1000
SET amount=1100;
-- (记录每行的具体变更前后值)

-- MySQL 自动判断：
-- 普通 SQL：用 STATEMENT 格式（节省 binlog）
-- 非确定性 SQL（NOW(), RAND() 等）：用 ROW 格式（保证一致性）

-- 以下函数在 STATEMENT 格式下会导致主从不一致：
NOW(), CURDATE(), RAND(), UUID(), USER(), FOUND_ROWS()
LOAD_FILE(), VERSION(), etc.

-- 案例：
-- Master: INSERT INTO log (msg, created) VALUES ('login', NOW());
-- binlog: INSERT INTO log (msg, created) VALUES ('login', NOW());

-- Slave: INSERT INTO log (msg, created) VALUES ('login', NOW());
-- NOW() 在 Slave 执行时可能差几毫秒

-- STATEMENT 格式下，自增列在 Master 和 Slave 的行为可能不同
INSERT INTO orders VALUES (NULL, 'A001');
-- 如果 Master 分配了 ID=100，但 Slave 因为某种原因分配了 ID=101

-- 触发器中的 SQL 在 STATEMENT 格式下可能产生不同结果
CREATE TRIGGER tr_after_insert AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    UPDATE inventory SET count = count - 1 WHERE item_id = NEW.item_id;
END;

-- 触发器内部的 SQL 在 binlog 中如何记录？
-- 如果触发器执行了多次，Master 和 Slave 的行为可能不一致

-- 批量更新 100 万行：
-- STATEMENT 格式 binlog：
UPDATE orders SET status = 2 WHERE created_at < '2024-01-01';
-- 大小：几十字节

-- ROW 格式 binlog：
UPDATE orders SET status=2 WHERE id=1 AND status=1;
UPDATE orders SET status=2 WHERE id=2 AND status=1;
... (100 万条)
-- 大小：可能几十 MB 甚至几百 MB

-- 控制 ROW 格式下记录多少数据
SHOW VARIABLES LIKE 'binlog_row_image';

-- FULL（默认）：记录变更行的所有列
-- MINIMAL：只记录主键列和变更的列
-- NOBLOB：只记录变更的列，不记录未变更的 BLOB/TEXT 列
SET GLOBAL binlog_row_image = 'MINIMAL';

-- 以下情况使用 ROW 格式：
-- 1. 存储过程、触发器、函数中执行的 SQL
-- 2. 使用了非确定性函数：NOW(), RAND(), UUID(), etc.
-- 3. 连接了用户变量：@var
-- 4. LOAD DATA INFILE
-- 5. 调用了不支持 STATEMENT 格式的存储引擎

-- 其他情况使用 STATEMENT 格式

-- 互联网业务（高并发写入、数据量大）：
-- 推荐 MIXED 或 ROW
-- binlog_row_image = MINIMAL 进一步节省空间

-- 审计/金融类业务（精确性要求高）：
-- 推荐 ROW
-- 牺牲一些性能，换取数据精确性

-- 报表类业务（只读副本）：
-- 可以用 STATEMENT
-- 从库只做查询，不需要高精确性

-- 动态设置（会话级别）
SET SESSION binlog_format = 'ROW';
SET SESSION binlog_format = 'MIXED';

-- 静态设置（全局，需重启）
-- my.cnf:
-- binlog_format = ROW
-- binlog_row_image = MINIMAL

-- 查看 binlog 文件大小
SHOW BINARY LOGS;

-- 查看当前 binlog
SHOW MASTER STATUS;

-- 自动清理 binlog
SHOW VARIABLES LIKE 'expire_logs_days';
SET GLOBAL expire_logs_days = 7;  -- 保留 7 天
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';