用MySQL的触发器实现本机上多库数据同步
MySQL replication只能实现主从机上库对库的数据同步，没法将主机上的库同步到多个。
除了可用MySQL的联合表来做之外，还可以用触发器来同步插入、更新、删除数据
一、建立测试环境
CREATE table mother(id int(6) primary key auto_increment,value text);
CREATE table son LIKE mother;
建立了一个mother表和一模一样的son表，我们将要在mother表上建触发器，对mother表进行增、删、改都应该能同步到son表中
二、触发器原型
MySQL变动前的数据集和和变动后的数据集分别叫OLD和NEW，INSERT没有OLD只有NEW，DELETE只有OLD没有NEW，UPDATE两个都有。
同步插入触发器
DELIMITER |
CREATE TRIGGER tg_sync_insert
AFTER INSERT
ON mother
FOR EACH ROW
BEGIN
INSERT INTO son SELECT * FROM mother WHERE id > LAST_INSERT_ID();
END;
|
DELIMITER ;
LAST_INSERT_ID()获取到最后插入表后的id值，所以mother表必须要有一个auto_increment的字段，以为存在同时插入多个记录的情况，需要用大于LAST_INSERT_ID()
同步删除触发器
DELIMITER |
CREATE TRIGGER tg_sync_delete
AFTER DELETE
ON mother
FOR EACH ROW
BEGIN
DELETE FROM son WHERE son.id=OLD.id;
END;
|
DELIMITER ;
同步更新触发器
DELIMITER |
CREATE TRIGGER tg_sync_update
AFTER UPDATE
ON mother
FOR EACH ROW
BEGIN
DELETE FROM son WHERE son.id=OLD.id;
INSERT INTO son SELECT * FROM mother WHERE id = OLD.id;
END;
|
DELIMITER ;
三、测试
插入
mysql> insert into mother (value) values (’first’);
Query OK, 1 row affected (0.00 sec)
mysql> select * from son;
+—-+——-+
| id | value |
+—-+——-+
| 6 | first |
+—-+——-+
1 row in set (0.00 sec)
mysql> insert into mother (value) values (’second’),(’third’);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from mother;
+—-+——–+
| id | value |
+—-+——–+
| 7 | second |
| 6 | first |
| 8 | third |
+—-+——–+
3 rows in set (0.00 sec)
mysql> select * from son;
+—-+——–+
| id | value |
+—-+——–+
| 7 | second |
| 6 | first |
| 8 | third |
+—-+——–+
3 rows in set (0.00 sec)
mysql>
更新
mysql> update mother set value=’updated’ where id=’8′;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from mother;
+—-+———+
| id | value |
+—-+———+
| 7 | second |
| 6 | first |
| 8 | updated |
+—-+———+
3 rows in set (0.00 sec)
mysql> select * from son;
+—-+———+
| id | value |
+—-+———+
| 7 | second |
| 6 | first |
| 8 | updated |
+—-+———+
3 rows in set (0.00 sec)
mysql>
删除
mysql> delete from mother where id=’8′;
Query OK, 1 row affected (0.00 sec)
mysql> select * from son;
+—-+——–+
| id | value |
+—-+——–+
| 7 | second |
| 6 | first |
+—-+——–+
2 rows in set (0.00 sec)
mysql> delete from mother where id in (7,6);
Query OK, 2 rows affected (0.01 sec)
mysql> select * from son;
Empty set (0.01 sec)
mysql>