mysql触发器适合做审计日志吗_mysql设计实践分析

mysql触发器不适合做核心审计日志，因其在事务内执行，回滚时日志一同丢失；推荐使用官方audit_log插件或解析binlog，二者均绕过事务控制且更可靠。

MySQL 触发器写审计日志容易丢数据

不适合做核心审计日志。触发器在事务内执行，一旦事务回滚，AFTER INSERT 或 BEFORE UPDATE 里写的日志也会被一起回滚——你看到的“成功插入日志”其实从未真正落盘。

常见错误现象：

INSERT INTO users VALUES (1, 'alice'); -- 触发器往 audit_log 写一行
UPDATE users SET name = 'bob' WHERE id = 1;
ROLLBACK; -- audit_log 表里那条记录也消失了

这种场景下，审计方以为操作已留痕，实际完全不可追溯。

• 所有触发器日志表必须与业务表同引擎（如都是 InnoDB），否则事务无法统一控制
• MyISAM 日志表虽能“逃过”回滚，但会破坏事务一致性，且不支持行级锁，高并发下易卡死
• 触发器无法捕获连接层行为（如 SET autocommit = 0、KILL CONNECTION）

替代方案：用 MySQL 官方审计插件 audit_log

这是最接近“开箱即用”的合规方案，由服务端在语句执行前后直接写文件或 socket，不走 SQL 引擎层，天然绕过事务控制。

启用方式简单但有硬性前提：

INSTALL PLUGIN audit_log SONAME 'audit_log.so';
SET GLOBAL audit_log_policy = 'ALL';
SET GLOBAL audit_log_format = 'jsON';

日志默认写入 /var/lib/mysql/audit.log（路径由 audit_log_file 变量控制）。

• 仅限 MySQL 5.7.28+ / 8.0.19+，旧版本需用 Percona Server 或 mariadb 的对应插件
• json 格式含 timestamp、user、query、connection_id，但不含变更前后的字段值（如 old_value / new_value）
• 高频写入时可能成为 I/O 瓶颈，建议配合 audit_log_buffer_size 和异步刷盘策略

需要字段级变更详情？得靠应用层或 binlog 解析

如果审计要求精确到 “users.phone 从 13800138000 改为 13900139000”，触发器理论上能做，但代价极高：

• 每个被审计表都要配一套 BEFORE UPDATE 触发器，手动比对 OLD.phone != NEW.phone
• 触发器里不能调用存储函数查关联表（如把 user_id 转成用户名），否则性能雪崩
• DDL 变更（如加字段）后必须同步更新所有触发器，漏掉一个就导致日志缺失

更稳的做法是解析 binlog：开启 binlog_format = ROW 后，用 mysqlbinlog --base64-output=DECODE-ROWS 或 python 库 mysql-replication 拆出每一行变更。它天然包含前后镜像，且不侵入业务逻辑。

真要用触发器，至少守住这三条底线

如果因历史包袱或权限限制必须用触发器，务必做到：

• 日志表用 BLACKHOLE 引擎 + 外部代理（如 syslog 或 kafka）转发，避免事务耦合
• 触发器体里禁用 select、CALL、SLEEP 等阻塞操作，防止锁表
• 所有日志字段设为 NOT NULL default ''，避免因 NULL 值导致插入失败进而中断主流程

真正难的不是写出能跑的触发器，而是保证它在凌晨三点主库 CPU 98%、连接数 2000 时，依然不拖垮业务——这点上，它永远比不上一条配置好的 audit_log 插件。