用mysql的federated数据引擎和触发器实现跨服务器表数据同步

     现有服务器A和服务器B,分别装有mysql数据库,存在同一个库名的数据库,并且库中有相同的表,2服务器可外网访问,要实现服务器A中数据库发生inset,update 时把响应到的数据记录同步到服务器B中的数据库中。

       

     1, 检查mysql是否支持federated数据引擎,若不支持可能需要修改mysql配置文件;

     2,增加mysql触发器,让触发器把数据同步到federated表中,这时该服务器federated 表会通过一个可访问B数据库的帐户把数据同步到B数据库表中;


    -- 查看开启的储存引擎

 
   SHOW ENGINES;
 
   -- 若没有federated 引擎需要配置my.ini 文件。
   --在[mysqld]后面直接加federated,并且注释掉skip-federated(前面加#)

<strong>-- 创建federated引擎表</strong>
  我们用用户表user实例

 CREATE TABLE `fed_user` (
  `uid` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `user_name` char(17) DEFAULT NULL COMMENT '用户名',
  `user_pwd` char(32) DEFAULT NULL COMMENT '密码',
  PRIMARY KEY (`uid`),
  UNIQUE KEY `user_name` (user_name`),
) ENGINE=FEDERATED CONNECTION='mysql://triggeruser:123456@192.168.1.2:3306/db_name/table_name'; 
 


ENGINE=FEDERATED CONNECTION='mysql://triggeruser:123456@192.168.1.2:3306/db_name/table_name';

关键是这一行,其中triggeruser为访问另外一台服务器上mysql的用户名,后面123456是密码。先要保证这个帐户可以访问到192.168.1.2这台服务器的mysql。
当然要注意3306这个端口。若不能访问看下是不是没有这个帐户在当前服务器ip下的访问权限。比如当前服务器是192.168.1.3,就要看下192.168.1.2 是不是
允许192.168.1.3通过triggeruser这个用户名和123456这个密码在3306端口访问。


实现了上面的操作,可以试试在192.168.1.3服务器的fed_user 表中插入数据后,192.168.1.2 是不是已经有了该条记录,实现了手动的数据同步,现在我们再通过mysql的触发器实现数据的自动同步。


创建触发器:(触发器具体方法可以百度下)

DELIMITER $$

CREATE
    TRIGGER `user_insert_trigger` AFTER INSERT ON `user` 
    FOR EACH ROW BEGIN
    -- 插入数据
    INSERT INTO `fed_user`(
`uid`, 
`user_name`, 
`user_pwd`, 
) VALUES (
new.`uid`, 
new.`user_name`, 
new.`user_pwd`,        
    );

    END;
$$

DELIMITER ;

其中user_insert_trigger 是触发器名,after insert on 是在插入表格之前触发,new.是插入之前的值,old.是数据完成之后的值,若是insert就是插入,若是update是就更新之后的值。



这个同步的总思路就是通过federated 数据引擎实现跨服务器数据同步,加上触发器就是自动同步。


数据-》触发器-》fed表-》另外的服务器

<think>嗯,用户有两个数据,结构一样但数据同步,想用SQL来同步。首先我得理清楚他们的需求。同步数据通常有几种情况:全量同步或者增量同步。全量的话可能每次覆盖,但数据量大时效率低。增量的话需要识别新增、修改删除的数据。 用户提到结构一致,所以字段应该是对应的。那关键就是找到差异的数据。可能需要用主键或者唯一标识来对比两边数据。比如用UNION ALLGROUP BY来找差异,或者用EXCEPT/INTERSECT这样的集合操作符,不过得看数据库类型是否支持,比如SQL Server有EXCEPT,而MySQL可能得用其他方法。 然后要考虑同步的方法。可能需要将数据从一个库导出,再导入另一个库。中间可能需要临时,或者用INSERT INTO ... SELECT语句跨库查询。但跨库操作可能需要权限,或者使用数据库链接,比如MySQLFEDERATED引擎或者SQL Server的链接服务器。 还有事务处理,确保同步过程中数据一致性,避免部分更新导致的问题。另外,删除的数据如何处理?是双向同步还是单向?用户可能需要明确同步方向,比如从A到B还是双向。 可能还需要考虑时间戳字段,如果有最后更新时间的话,可以更容易识别哪些数据需要同步。否则可能需要全对比,效率可能不高。触发器也是一种方式,但会增加数据库负担。 总结步骤的话,可能先识别差异数据,然后生成对应的INSERT、UPDATE、DELETE语句,在目标库执行。但具体实现要依数据库类型而定,比如MySQLSQL Server的语法可能有差异。最后建议用户先备份,测试后再操作,避免数据丢失。</think>我将分步骤为您讲解如何通过SQL实现数据数据同步: 一、同步前准备 1. 确认两个数据库类型(MySQL/SQL Server等),不同数据库的跨库查询语法不同 2. 确保两张有唯一主键或唯一索引(如id字段),这是数据比对的关键 3. 建议先备份数据(重要!) ```sql -- 示例备份语句(MySQL) CREATE TABLE backup_table_20231130 LIKE source_table; INSERT INTO backup_table_20231130 SELECT * FROM source_table; ``` 二、数据差异分析 1. 使用FULL OUTER JOIN查找差异(适用于支持跨库查询的环境): ```sql /* SQL Server示例(需建立链接服务器)*/ SELECT COALESCE(a.id, b.id) AS id, CASE WHEN a.id IS NULL THEN '仅存在DB_B' WHEN b.id IS NULL THEN '仅存在DB_A' ELSE '数据不一致' END AS diff_type FROM DB_A.dbo.table a FULL OUTER JOIN DB_B.dbo.table b ON a.id = b.id WHERE a.id IS NULL OR b.id IS NULL OR a.checksum <> b.checksum; ``` 2. 或使用EXCEPT集合运算(SQL Server/Oracle适用): ```sql -- 查找DB_A中存在但DB_B中没有的记录 SELECT * FROM DB_A.dbo.table EXCEPT SELECT * FROM DB_B.dbo.table; ``` 三、同步策略实现 方案1:全量覆盖同步(适合小数据量) ```sql -- MySQL示例(需配置FEDERATED引擎) TRUNCATE TABLE db2.target_table; INSERT INTO db2.target_table SELECT * FROM db1.source_table; ``` 方案2:增量同步(推荐) 步骤1:同步新增数据 ```sql /* SQL Server示例 */ INSERT INTO DB_B.dbo.table SELECT a.* FROM DB_A.dbo.table a LEFT JOIN DB_B.dbo.table b ON a.id = b.id WHERE b.id IS NULL; ``` 步骤2:同步更新数据 ```sql /* MySQL示例(跨库更新)*/ UPDATE db2.target_table t JOIN db1.source_table s ON t.id = s.id SET t.column1 = s.column1, t.column2 = s.column2 WHERE t.updated_at < s.updated_at; -- 假设有时间戳字段 ``` 步骤3:同步删除数据(谨慎操作) ```sql /* PostgreSQL示例 */ DELETE FROM target_db.target_table t WHERE NOT EXISTS ( SELECT 1 FROM source_db.source_table s WHERE s.id = t.id ); ``` 四、高级方案建议 1. 使用数据库工具: - MySQLmysqldump + mysqlimport - SQL Server:SSIS(集成服务) - Oracle:Data Pump 2. 添加版本控制字段(推荐): ```sql ALTER TABLE your_table ADD ( version INT DEFAULT 0, last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); ``` 五、注意事项 1. 事务处理:使用BEGIN TRANSACTION...COMMIT确保操作原子性 2. 性能优化:大数据同步建议分批次进行(使用LIMIT/OFFSET) 3. 网络连接:跨数据同步需确保网络连通性权限配置 4. 同步日志:建议记录同步操作日志 建议先在一个测试环境中验证同步方案,再应用到生产环境。如果涉及双向同步,需要特别注意处理冲突问题,推荐使用Last-Write-Win或业务规则解决冲突。
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

yijiliangfang

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值