Mysql中不同库的两个表怎么做数据同步

'# Mysql中不同库的两个表怎么做数据同步

一、背景与问题

在分布式系统中，数据同步是核心需求之一。当两个表分别位于不同的数据库（schema）中时，如何实现高效、可靠的数据同步成为关键问题。例如：

订单系统中的orders表（库：order_db）和库存系统中的stock表（库：inventory_db）需要保持数据一致性
跨业务系统的日志表和统计表需要定时同步
数据归档场景中，历史表需要与主表同步

传统方案面临三大挑战：

数据一致性保障（避免脏读/丢失）
性能开销控制（避免锁表/阻塞）
故障恢复机制（数据回滚/补偿）

二、基本原理

MySQL提供了三种核心同步机制：

1. 触发器（Triggers）

通过BEFORE INSERT/UPDATE/DELETE事件，主动触发同步逻辑。适用于实时性要求高的场景。

2. 事件调度器（Event Scheduler）

通过定时任务实现批量同步。适合周期性数据同步需求，如每日凌晨同步。

3. 主从复制（Replication）

通过二进制日志实现异步复制。适用于数据分片、读写分离等场景。

不同方案的性能对比：

方案	同步延迟	数据一致性	性能开销	适用场景
触发器	实时	强	高	实时同步
事件调度器	分钟级	弱	中	批量处理
主从复制	秒级	弱	低	分布式架构

三、环境准备

确保MySQL版本支持所需功能（建议5.6+）：

# 检查版本
mysql --version

创建测试数据库和表：

CREATE DATABASE sync_test;
USE sync_test;

-- 创建源表
CREATE TABLE order_db.orders (
    order_id INT PRIMARY KEY,
    product_id INT,
    quantity INT
) ENGINE=InnoDB;

-- 创建目标表
CREATE TABLE inventory_db.stock (
    product_id INT PRIMARY KEY,
    stock INT
) ENGINE=InnoDB;

四、核心实现

1. 触发器方案（实时同步）

代码示例1：创建触发器

DELIMITER $$
CREATE TRIGGER sync_stock_after_update
AFTER UPDATE ON order_db.orders
FOR EACH ROW
BEGIN
    -- 计算库存变化
    DECLARE change INT;
    
    -- 计算库存变化量
    SELECT quantity INTO change FROM order_db.orders 
    WHERE order_id = NEW.order_id;
    
    -- 更新库存表
    UPDATE inventory_db.stock
    SET stock = stock - change
    WHERE product_id = NEW.product_id;
    
    -- 记录同步日志
    INSERT INTO sync_log(sync_time, action, table_name, detail)
    VALUES (NOW(), 'UPDATE', 'orders', CONCAT('Order ', NEW.order_id, ' changed stock'));
END $$
DELIMITER ;

关键代码解释：

使用AFTER触发器确保数据变更后执行
使用DECLARE定义局部变量
通过NEW关键字访问新值
使用事务保证操作原子性（需在会话中开启）

性能优化：

避免在触发器中执行复杂计算

对stock表添加索引：

ALTER TABLE inventory_db.stock ADD INDEX idx_product(product_id);

2. 事件调度器方案（定时同步）

代码示例2：创建定时任务

DELIMITER $$
CREATE EVENT sync_stock_event
ON SCHEDULE EVERY 1 HOUR
STARTS '2023-09-01 00:00:00'
DO
BEGIN
    -- 计算总库存
    DECLARE total_stock INT;
    
    -- 获取所有订单的总销量
    SELECT SUM(quantity) INTO total_stock
    FROM order_db.orders;
    
    -- 更新库存表
    UPDATE inventory_db.stock
    SET stock = total_stock
    WHERE product_id = 1;
    
    -- 记录同步日志
    INSERT INTO sync_log(sync_time, action, table_name, detail)
    VALUES (NOW(), 'SCHEDULED', 'orders', 'Scheduled stock sync');
END $$
DELIMITER ;

关键代码解释：

使用EVERY指定执行频率
使用DECLARE定义变量
通过STARTS设置初始执行时间
需要确保事件调度器已启用：
```
SET GLOBAL event_scheduler = ON;
```

3. 主从复制方案（异步同步）

代码示例3：配置主从复制

主库配置：

-- 修改主库配置
[mysqld]
log-bin=mysql-bin
server-id=1

从库配置：

[mysqld]
server-id=2

配置主库：

-- 创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

-- 获取binlog位置
SHOW MASTER STATUS;

配置从库：

CHANGE MASTER TO
MASTER_HOST='master_host',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=4;

START SLAVE;

关键点：

使用GTID（全局事务标识符）提升可靠性
通过SHOW SLAVE STATUS监控复制状态
使用binlog_format=ROW保证数据一致性

五、完整案例

订单库存同步案例

业务场景：
当用户下单时，订单表orders更新，需同步更新库存表stock。要求：

实时同步
保证事务一致性
有回滚机制

完整实现：

1. 创建同步日志表

CREATE TABLE sync_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sync_time DATETIME,
    action VARCHAR(20),
    table_name VARCHAR(50),
    detail TEXT
) ENGINE=InnoDB;

2. 创建触发器

DELIMITER $$
CREATE TRIGGER sync_stock_after_insert
AFTER INSERT ON order_db.orders
FOR EACH ROW
BEGIN
    DECLARE change INT;
    
    -- 计算库存变化
    SELECT quantity INTO change FROM order_db.orders
    WHERE order_id = NEW.order_id;
    
    -- 更新库存表
    START TRANSACTION;
    UPDATE inventory_db.stock
    SET stock = stock - change
    WHERE product_id = NEW.product_id;
    
    -- 记录日志
    INSERT INTO sync_log(sync_time, action, table_name, detail)
    VALUES (NOW(), 'INSERT', 'orders', CONCAT('Order ', NEW.order_id, ' created'));
    
    COMMIT;
    
    -- 检查库存是否为负
    IF (SELECT stock FROM inventory_db.stock
        WHERE product_id = NEW.product_id) < 0 THEN
        ROLLBACK;
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '库存不足，订单同步回滚';
    END IF;
END $$
DELIMITER ;

3. 事务处理关键点：

使用START TRANSACTION显式开启事务
通过SIGNAL触发自定义错误
在IF中使用ROLLBACK回滚事务
在ROLLBACK后需确保事务状态正确

六、源码解析

以触发器代码为例，逐步分析：

DELIMITER $$：修改结束符以避免与SQL关键字冲突
CREATE TRIGGER：定义触发器名称和事件
AFTER INSERT：在插入操作后触发
FOR EACH ROW：对每一行数据执行
DECLARE change INT;：声明局部变量
SELECT quantity INTO change：从当前行获取数据
START TRANSACTION：显式开启事务
UPDATE：执行库存更新
INSERT INTO sync_log：记录同步日志
COMMIT：提交事务
IF条件判断：检查库存是否为负
ROLLBACK：回滚事务
SIGNAL：抛出自定义错误

七、进阶使用

1. 多表同步方案

-- 同步多个表
DELIMITER $$
CREATE TRIGGER sync_all
AFTER UPDATE ON order_db.orders
FOR EACH ROW
BEGIN
    -- 同步库存
    UPDATE inventory_db.stock
    SET stock = stock - NEW.quantity
    WHERE product_id = NEW.product_id;
    
    -- 同步物流
    INSERT INTO logistics.logistics
    (order_id, status)
    VALUES (NEW.order_id, 'SHIPPED');
END $$
DELIMITER ;

2. 异步同步队列

-- 使用消息队列
CREATE TABLE sync_queue (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(50),
    action VARCHAR(10),
    data JSON,
    created_at DATETIME
) ENGINE=InnoDB;

同步逻辑：

-- 异步处理队列
START TRANSACTION;
INSERT INTO sync_queue (table_name, action, data, created_at)
VALUES ('orders', 'UPDATE', JSON_OBJECT('order_id' VALUE NEW.order_id), NOW());
COMMIT;

3. 复杂计算优化

-- 使用临时表优化计算
CREATE TEMPORARY TABLE temp_stock AS
SELECT product_id, SUM(quantity) AS total
FROM order_db.orders
GROUP BY product_id;

八、性能与工程实践

1. 性能优化策略

优化项	方法	效果
索引优化	在`stock`表上添加`product_id`索引	查询速度提升200%
分批次处理	使用`LIMIT`分页处理	减少锁表时间
事务拆分	将复杂操作拆分为小事务	减少事务回滚概率
并行处理	使用多线程同步	提升同步速度

2. 异常处理机制

-- 异常处理示例
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    INSERT INTO sync_log(sync_time, action, table_name, detail)
    VALUES (NOW(), 'ERROR', 'orders', '同步异常');
END;

3. 安全风险分析

潜在风险：

触发器可能引发循环引用（如订单同步库存，库存又影响订单）
超级用户权限可能导致数据篡改
日志表未加密可能泄露敏感信息

解决方案：

使用DEFINER指定触发器执行者
对敏感字段进行加密存储
使用SHOW CREATE TRIGGER审计触发器定义

九、常见问题与踩坑

1. 触发器循环引用问题

错误示例：

-- 错误：库存更新触发订单更新
CREATE TRIGGER update_orders_after_stock
AFTER UPDATE ON inventory_db.stock
FOR EACH ROW
BEGIN
    UPDATE order_db.orders
    SET quantity = quantity + NEW.quantity
    WHERE product_id = NEW.product_id;
END;

解决办法：

使用OLD/NEW关键字判断变化
添加IF条件判断
使用事务隔离级别控制

2. 主从复制延迟问题

错误日志：

Last_SQL_Error: Got fatal error 1236 from master when reading data

解决办法：

检查主库binlog格式是否为ROW
增加innodb_flush_log_at_trx_commit=2提升性能
使用GTID实现故障自动恢复

3. 事件调度器未生效

常见原因：

未开启事件调度器：SET GLOBAL event_scheduler = ON;
事件名称拼写错误
未指定正确的时间格式

验证方法：

SHOW EVENTS;

十、最佳实践

1. 选择方案建议

场景	推荐方案	原因
实时同步	触发器	保证数据一致性
批量处理	事件调度器	降低系统负载
分布式架构	主从复制	实现读写分离

2. 安全实践

使用DEFINER指定触发器执行者
对敏感字段进行加密
使用SHOW CREATE TRIGGER审计触发器定义
定期清理同步日志

3. 性能实践

对同步表建立合适的索引
使用事务隔离级别控制并发
对复杂计算使用临时表
定期优化表结构

十一、总结

MySQL不同库的表数据同步是分布式系统中的关键环节。本文深入探讨了三种核心实现方式（触发器、事件调度器、主从复制），并通过实际案例展示了不同场景下的应用。重点分析了：

触发器的实时同步机制和事务控制
事件调度器的定时同步方案
主从复制的异步同步架构
性能优化策略和安全注意事项
常见错误的排查方法

在实际项目中，应根据业务需求选择合适的方案：

高并发实时场景建议使用触发器
定时批量处理推荐事件调度器
分布式架构应采用主从复制

同时需注意：

避免触发器循环引用
控制事务隔离级别
建立完善的异常处理机制
定期进行数据校验和日志审计

通过合理的设计和实践，可以实现高效、可靠的数据同步，保障系统稳定性。