Mysql 触发器-两个数据库的表数据同步-步骤与问题解决

需求：

同步不同服务器上的数据库表的数据。即一方修改另一方也跟着修改

优化:之前桥接表,本地表什么的有点混乱重新整理下:统一使用本地表, 远程表

步骤：

只更新一张表，使用触发器解决

检查本地mysql是否支持Federated数据引擎

执行    show ENGINES; 语句

Support值为NO，说明未支持该引擎。需修改mysql的配置文件进行设置。

找到mysql的配置文件，在[mysqld]后添加federated即可；添加完成，重启mysql服务再次查询该引擎状态为YES即为支持。

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

在本地数据库中创建一个可以映射远程表数据的桥接表 [结构与远程服务器一致，下面叫本地]

注意：该表与远程服务器中需要同步数据的表结构一致（创建表结构即可，表数据会映射过来）

在原来的表结构后加上：

ENGINE=FEDERATED  CONNECTION = 'mysql://user:password@ip:port/databaseName/tableName';

错误信息1：

Access denied; you need (at least one of) the SUPER privilege(s) for this operation

登录账户没有权限，用更高级别权限的账号操作

错误信息2：

Can't create federated table. The data source connection string 'mysql://xxxxxx' is not in the correct format

数据源连接字符串格式不正确:,mysql参考文档中声明，使用connection时不能解析密码中的 @ 符号

使用CREATE SERVER创建FEDERATED表

CREATE SERVER fedlink
FOREIGN DATA WRAPPER mysql
-- 依次是远程数据库的用户名、密码、地址、端口、数据库名
OPTIONS (USER 'user', PASSWORD 'aa@bb',HOST 'remote_host', PORT 9306, DATABASE 'federated');

-- 再次执行语句，下面CONNECTION改为下面的 ......
ENGINE=FEDERATED 
CONNECTION='fedlink/my_user';

示例：

(本地数据库执行)

DROP TABLE IF EXISTS `user`;
CREATE SERVER fedlink
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'test', PASSWORD 'root@1', HOST '192.168.0.1', PORT 3306, DATABASE 'shujuku');

CREATE TABLE `user` (
	-- 表结构字段
  `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';

创建触发器

(本地数据库执行)

插入

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里直接拼接(远程数据库.用户表.id)
	values(pro.user);
end;

删除

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`

至此，所有步骤完成；

可进行验证；任何一表中插入或删除修改数据，另一个表数据同步增删改。

2022/09/02问题补充更新

字段可以不一致吗？也就是表结构不同(来自评论博友"是小王")

可以不一致,

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

(指定字段名字不一致应该也可以, 不过这种情况没尝试)

关于触发器

这些触发器什么的，都是写在目标库的查询里面吗？

是的, 就跟存储过程一样, 触发器脚本和CREATE SERVER都执行在带FEDERATED的表中, 之前桥接表,本地表什么的有点混乱重新整理下统一使用本地表, 远程表