如何通过触发器实现MySQL数据库之间的双向同步？

MySQL通过触发器实现双向同步，可以在两个数据库之间自动复制数据。

MySQL通过触发器的形式同步数据库，实现MySQL到MySQL的双向同步，是一种高效且自动化的数据同步方法，以下是详细的步骤和示例：

检查并启用Federated引擎

1、检查是否支持Federated引擎：

执行SHOW ENGINES;语句，如果Support值为NO，说明未支持该引擎。

需要修改MySQL的配置文件进行设置，找到mysql的配置文件，在[mysqld]后添加federated，然后重启MySQL服务。

2、创建映射远程表数据的本地表：

在本地数据库中创建一个可以映射远程表数据的桥接表（结构与远程服务器一致）。

注意：该表与远程服务器中需要同步数据的表结构一致。

3、使用CREATE SERVER创建FEDERATED表：

示例代码：

“`sql

DROP TABLE IF EXISTSuser;

CREATE SERVER fedlink

FOREIGN DATA WRAPPER mysql

OPTIONS (USER ‘test’, PASSWORD ‘root@1’, HOST ‘192.168.0.1’, PORT 3306, DATABASE ‘shujuku’);

CREATE TABLEuser (

openid varchar(56) NOT NULL DEFAULT ” COMMENT ‘openid’,

PRIMARY KEY (uid) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT=’用户表’ ENGINE=FEDERATED CONNECTION = ‘fedlink/user’;

“`

创建触发器

1、插入触发器：

当本地表中有数据插入时，同步到远程表。

示例代码：

“`sql

DROP TRIGGER IF EXISTS insert_user;

CREATE TRIGGER insert_user

触发表 user

AFTER INSERT ON user

FOR EACH ROW

BEGIN

插入目标表、本地：本地数据库.用户表，如需指定字段在后面拼接(本地数据库.用户表.id)

INSERT INTO test.user values(pro.user);

END;

“`

2、删除触发器：

当本地表中有数据删除时，同步到远程表。

示例代码：

“`sql

DROP TRIGGER IF EXISTS delete_user;

CREATE TRIGGER delete_user

触发表 user

AFTER DELETE ON user

FOR EACH ROW

BEGIN

本地表.user where uid = 远程表.uid

DELETE FROM test.user where uid=pro.uid;

END;

“`

验证触发器

在任何一表中插入或删除修改数据，另一个表数据同步增删改。

可以通过SELECT * FROM information_schema.TRIGGERS;查看所有触发器。

注意事项

1、字段一致性：

本地表的字段数目只能小于等于远程的表字段，并且这些字段均存在于远程表中，表名也要一样。

如果字段不一致，需要指定字段名字，不过这种情况没尝试过。

2、权限问题：

如果遇到“Access denied”错误，需要用更高级别权限的账号操作。

如果数据源连接字符串格式不正确，可以参考MySQL文档中的声明，避免在密码中使用@符号。

通过上述步骤，可以实现MySQL到MySQL的双向同步，确保数据的一致性和实时更新。

序号	触发器名称	触发器类型	触发事件	触发时机	触发器动作	目标数据库	说明
1	on_insert_before	AFTER	INSERT	对表A的INSERT操作之前	INSERT INTO 表B (列1, 列2, …) VALUES (NEW.列1, NEW.列2, …);	目标数据库B	当在表A中插入新记录时，在目标数据库B中插入相同的记录，实现从A到B的同步
2	on_update_before	AFTER	UPDATE	对表A的UPDATE操作之前	UPDATE 表B SET 列1 = NEW.列1, 列2 = NEW.列2, … WHERE 表B.主键 = NEW.主键;	目标数据库B	当在表A中更新记录时，在目标数据库B中更新对应的记录，实现从A到B的同步
3	on_delete_before	AFTER	DELETE	对表A的DELETE操作之前	DELETE FROM 表B WHERE 表B.外键 = OLD.外键;	目标数据库B	当在表A中删除记录时，在目标数据库B中删除对应的记录，实现从A到B的同步
4	on_insert_after	AFTER	INSERT	对表B的INSERT操作之后	INSERT INTO 表A (列1, 列2, …) VALUES (NEW.列1, NEW.列2, …);	目标数据库A	当在表B中插入新记录时，在目标数据库A中插入相同的记录，实现从B到A的同步
5	on_update_after	AFTER	UPDATE	对表B的UPDATE操作之后	UPDATE 表A SET 列1 = NEW.列1, 列2 = NEW.列2, … WHERE 表A.主键 = NEW.主键;	目标数据库A	当在表B中更新记录时，在目标数据库A中更新对应的记录，实现从B到A的同步
6	on_delete_after	AFTER	DELETE	对表B的DELETE操作之后	DELETE FROM 表A WHERE 表A.外键 = OLD.外键;	目标数据库A	当在表B中删除记录时，在目标数据库A中删除对应的记录，实现从B到A的同步