时间戳

首页 > 解决方案 > 在某些表中记录删除行 | 备份删除的数据

mysql - 在某些表中记录删除行 | 备份删除的数据

问题描述

我想记录某些表中已删除的数据。从日志中我可以恢复已删除的数据。Mysql 支持开箱即用的类似功能吗？理想情况下，获取带有“插入”的 sql 文件。

标签： mysqlmysql-5.7

解决方案

There is a partial solution. For every table you want to audit, create BEFORE DELETE trigger and an audit table. See code below.

Keep in mind that triggers work only for DELETE FROM command. TRUNCATE bypass triggers and your deletes will not be audited.

DELIMITER //

CREATE TRIGGER table1_before_delete
BEFORE DELETE
   ON table1 FOR EACH ROW

BEGIN

   -- Insert record into audit table
   INSERT INTO table1_audit ( table1_id, deleted_date) VALUES ( OLD.id, NOW());

END; //

DELIMITER ;

推荐阅读