最近碰到一个业务是,需要将我们某几个表的数据实时推送到第三方数据库。于是打算使用mysql触发器来进行实时增量同步。
本人使用的是mysql 5.7
1、开启federated引擎
进入mysql命令窗口,Federated是NO,说明没有安装
在my.ini(windows)中的mysqld下添加federated这一个属性就可开启.(linux系统则是在my.cnf文件下)
添加完成之后重启mysql服务
2.1、在建表语句中加入Federated
federated引擎
声明引擎:ENGINE=FEDERATED
连接属性: CONNECTION='mysql://用户名:密码@ip地址:端口号/数据库名/表名';
连接属性:CONNECTION='mysql://root:root@123@192.168.10.2:3306/test/s_user';
在slave中创建表结构的时候加入federated引擎
CREATE TABLE `user` (
`id` varchar(255) NOT NULL COMMENT 'id',
`name` varchar(255) DEFAULT NULL COMMENT '用户名',
`password` varchar(255) DEFAULT NULL COMMENT '密码',
`mobile` varchar(40) DEFAULT NULL COMMENT '手机号码',
`email` varchar(100) DEFAULT NULL COMMENT '邮箱',
`sex` int(1) DEFAULT NULL COMMENT '性别(0:男,1:女)',
PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT='用户表'
-- 声明引擎 连接属性
ENGINE =FEDERATED CONNECTION='mysql://root:root@123@192.168.10.2:3306/test/s_user';
然而在建表时我的建表语句报错了。
[Err] 1432 - Can't create federated table. The data source connection string mysql://root:root@123@192.168.10.2:3306/test/s_user' is not in the correct format
由于我的密码包含了@与格式冲突了。
2.2.1、使用 CREATE SERVER 创建 FEDERATED table
上面说到我connection方式格式不正确只能使用 Create Server 这种方法
格式:
CREATE SERVER fedlink
FOREIGN DATA WRAPPER mysql
-- 依次是远程数据库的用户名、密码、地址、端口、数据库名
OPTIONS (USER 'root', PASSWORD 'root@123',HOST '192.168.10.2', PORT 3306, DATABASE 'test');
创建create server 需要管理员权限
若写错了可以使用
-- 删除语句
DROP SERVER fedlink;
2.2.2、在建表语句中加入Federated
在建表连接中使用刚建好的create server
CREATE TABLE `s_user` (
`id` varchar(255) NOT NULL COMMENT 'id',
`name` varchar(255) DEFAULT NULL COMMENT '用户名',
`password` varchar(255) DEFAULT NULL COMMENT '密码',
`mobile` varchar(40) DEFAULT NULL COMMENT '手机号码',
`email` varchar(100) DEFAULT NULL COMMENT '邮箱',
`sex` int(1) DEFAULT NULL COMMENT '性别(0:男,1:女)',
PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT='用户表'
-- 声明引擎 连接属性 Federated/slave表
ENGINE=FEDERATED CONNECTION = 'fedlink/s_user';
3、创建触发器
语法格式如下:
CREATE <触发器名> < BEFORE | AFTER >
<INSERT | UPDATE | DELETE >
ON <表名> FOR EACH Row<触发器主体>
NEW与OLD关键字详解
MySQL 中定义了 NEW 和 OLD,用来表示触发器的所在表中,触发了触发器的那一行数据,来引用触发器中发生变化的记录内容,具体地:
① 在INSERT型触发器中,NEW用来表示将要(BEFORE)或已经(AFTER)插入的新数据;
② 在UPDATE型触发器中,OLD用来表示将要或已经被修改的原数据,NEW用来表示将要或已经修改为的新数据;
③ 在DELETE型触发器中,OLD用来表示将要或已经被删除的原数据;
3.1、新增触发器
-- 插入触发器
drop trigger if exists user_insert_trigger;
delimiter $
create trigger user_insert_trigger after insert
on `user` for each row
begin
insert into s_user(`id`,`name`,`password`,`mobile`,`email`,`sex`)
values (new.id, new.name, new.password, new.mobile, new.email, new.sex);
end $;
delimiter ;
3.2、新增触发器
-- 修改触发器
drop trigger if exists user_update_trigger;
delimiter $
create trigger user_update_trigger after update
on `user` for each row
begin
update s_user set `name`=new.name, `password`=new.password,`mobile`=new.mobile,`email`=new.email,`sex`=new.sex where `id`=old.id;
end $;
delimiter ;
3.3、删除触发器
-- 删除触发器
drop trigger if exists user_delete_trigger;
delimiter $
create trigger user_delete_trigger after delete
on `user` for each row
begin
delete from s_user where `id` = old.id;
end $;
delimiter ;