mysql 获取修改记录

基础概念

MySQL 获取修改记录通常指的是跟踪数据库表中的数据变更历史。这可以通过多种方式实现，包括使用触发器、日志文件、变更数据捕获（CDC）等。

类型

触发器：在表上创建触发器，当数据发生插入、更新或删除操作时，自动记录这些操作。
日志文件：利用MySQL的二进制日志（Binary Log）或慢查询日志来记录数据变更。
变更数据捕获（CDC）：使用专门的工具或框架来捕获和记录数据变更。

应用场景

金融系统：记录交易历史以满足审计和合规性要求。
内容管理系统：跟踪文章、页面等的修改历史。
电子商务平台：记录订单状态的变更历史。

遇到的问题及解决方法

问题：如何使用触发器记录修改历史？

解决方法：

创建一个用于存储修改历史的表：

CREATE TABLE table_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(255),
    operation ENUM('INSERT', 'UPDATE', 'DELETE'),
    old_data TEXT,
    new_data TEXT,
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

创建触发器：

DELIMITER //

CREATE TRIGGER after_table_update
AFTER UPDATE ON your_table
FOR EACH ROW
BEGIN
    INSERT INTO table_history (table_name, operation, old_data, new_data)
    VALUES ('your_table', 'UPDATE', OLD.*, NEW.*);
END //

CREATE TRIGGER after_table_insert
AFTER INSERT ON your_table
FOR EACH ROW
BEGIN
    INSERT INTO table_history (table_name, operation, new_data)
    VALUES ('your_table', 'INSERT', NEW.*);
END //

CREATE TRIGGER after_table_delete
AFTER DELETE ON your_table
FOR EACH ROW
BEGIN
    INSERT INTO table_history (table_name, operation, old_data)
    VALUES ('your_table', 'DELETE', OLD.*);
END //

DELIMITER ;

问题：如何使用二进制日志记录修改历史？

解决方法：

启用二进制日志：

SET GLOBAL log_bin = 'ON';

使用mysqlbinlog工具解析二进制日志文件，提取修改记录。

mysqlbinlog /path/to/binlog-file > changes.sql

问题：如何使用CDC工具记录修改历史？

解决方法：

可以使用如Debezium等CDC工具来捕获和记录数据变更。

安装Debezium：

docker run -it --rm --name debezium -p 9092:9092 -p 8083:8083 -e DB_VENDOR=mysql -e DB_HOST=mysql-host -e DB_USER=mysql-user -e DB_PASSWORD=mysql-password -e DB_DATABASE=mysql-db debezium/connect:latest

配置Debezium：

{
  "name": "mysql-connector",
  "config": {
    "connector.class": "io.debezium.connector.mysql.MySqlConnector",
    "tasks.max": "1",
    "database.hostname": "mysql-host",
    "database.port": "3306",
    "database.user": "mysql-user",
    "database.password": "mysql-password",
    "database.server.id": "184054",
    "database.server.name": "dbserver1",
    "database.include.list": "your_table",
    "database.history.kafka.bootstrap.servers": "kafka:9092",
    "database.history.kafka.topic": "schema-changes.mysql"
  }
}