《MySQL系列-主从相关》MySQL从库部署触发器

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)