MySQL从库部署触发器
数据库环境:MySQL数据库为主从状态
需求:在从库部署触发器,实时监控user表的insert\update\delete
流程:MySQL主库insert\update\delete操作了user表的数据,首先通过主从同步机制,将数据同步到MySQL从库的user表。然后通过部署在MySQL从库user表上的触发器,触发insert\update\delete操作,并将操作行为记录到trigger_user表中
1.建表
主库
主库建表user后,会自动在从库同步建表user
CREATE TABLE `user` (
`id` int(11) DEFAULT NULL,
`name` varchar(25) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
从库
从库建表trigger_user
CREATE TABLE `trigger_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`operate` varchar(25) DEFAULT NULL,
`sentence` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
2. 触发器
2.1 Trigger-Insert
trigger_user表的主键id为自增的,所以只需要再传入操作类型operate,和操作行为sentence
new指的是打算insert的数据
delimiter &&
drop trigger before_insert_user;
create trigger before_insert_user before insert on user for each row
begin
declare sentence varchar(255);
set sentence = concat('insert into targer_user values(',new.id,',\'',new.name,'\')');
insert into trigger_user(operate,sentence) values('insert',sentence);
end;
&&
delimiter;
2.2 Trigger_Update
trigger_user表的主键id为自增的,所以只需要再传入操作类型operate,和操作行为sentence
old指的是update之前的数据
new指的是update之后的数据
delimiter &&
drop trigger before_update_user;
create trigger before_update_user before update on user for each row
begin
declare sentence varchar(255);
set sentence = concat('update targer_user set id = ',new.id,',','name = \'',new.name,'\' where id = ',old.id);
insert into trigger_user(operate,sentence) values('update',sentence);
end;
&&
delimiter;
2.3 Trigger_delete
trigger_user表的主键id为自增的,所以只需要再传入操作类型operate,和操作行为sentence
old指的是打算delete的数据
delimiter &&
drop trigger before_delete_user;
create trigger before_delete_user before delete on user for each row
begin
declare sentence varchar(255);
set sentence = concat('delete from targer_user where id = ',old.id);
insert into trigger_user(operate,sentence) values('delete',sentence);
end;
&&
delimiter;
3.测试环节
3.1 主库模拟插入数据
成功标准:主库插入数据后,在主库和从库的user表可以查到数据,并在从库的trigger_user表有明确的记录
## 1.主库插入数据
mysql> insert into user values(1,'zxy')
1 row in set (0.00 sec)
## 2.主库查询user表
mysql> select * from user;
+------+------+
| id | name |
+------+------+
| 1 | zxy |
+------+------+
1 row in set (0.00 sec)
## 3.从库查询user表
mysql> select * from user;
+------+------+
| id | name |
+------+------+
| 1 | zxy |
+------+------+
1 row in set (0.00 sec)
## 4.从库查询trigger_user表记录
mysql> select * from trigger_user;
+----+---------+-----------------------------------------+
| id | operate | sentence |
+----+---------+-----------------------------------------+
| 1 | insert | insert into targer_user values(1,'zxy') |
+----+---------+-----------------------------------------+
1 row in set (0.00 sec)
3.2 主库模拟修改数据
成功标准:主库修改数据后,在主库和从库的user表可以查到修改后的数据,并在从库的trigger_user表有明确的记录
## 1.主库修改数据
mysql> update user set name = 'ZXY' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
## 2.主库查询user表
mysql> select * from user;
+------+------+
| id | name |
+------+------+
| 1 | ZXY |
+------+------+
1 row in set (0.00 sec)
## 3.从库查询user表
mysql> select * from user;
+------+------+
| id | name |
+------+------+
| 1 | ZXY |
+------+------+
1 row in set (0.00 sec)
## 4.从库查询trigger_user表记录
mysql> select * from trigger_user;
+----+---------+---------------------------------------------------------+
| id | operate | sentence |
+----+---------+---------------------------------------------------------+
| 1 | insert | insert into targer_user values(1,'zxy') |
| 2 | update | update targer_user set id = 1,name = 'ZXY' where id = 1 |
+----+---------+---------------------------------------------------------+
2 rows in set (0.00 sec)
3.3 主库模拟删除数据
成功标准:主库删除数据后,在主库和从库的user表可以看到数据的变化,并在从库的trigger_user表有明确的记录
## 1.主库修改数据
mysql> delete from user where id = 1;
Query OK, 1 row affected (0.00 sec)
## 2.主库查询user表
mysql> select * from user;
Empty set (0.00 sec)
## 3.从库查询user表
mysql> select * from user;
Empty set (0.00 sec)
## 4.从库查询trigger_user表记录
mysql> select * from trigger_user;
+----+---------+---------------------------------------------------------+
| id | operate | sentence |
+----+---------+---------------------------------------------------------+
| 1 | insert | insert into targer_user values(1,'zxy') |
| 2 | update | update targer_user set id = 1,name = 'ZXY' where id = 1 |
| 3 | delete | delete from targer_user where id = 1 |
+----+---------+---------------------------------------------------------+
3 rows in set (0.00 sec)