需求:
同步不同服务器上的数据库表的数据。即一方修改另一方也跟着修改
优化:之前桥接表,本地表什么的有点混乱重新整理下:统一使用本地表, 远程表
步骤:
只更新一张表,使用触发器解决
检查本地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的表中, 之前桥接表,本地表什么的有点混乱重新整理下统一使用本地表, 远程表