MySQL中怎么實現多庫數據同步

本篇文章為大家展示了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>

上述內容就是MySQL中怎么實現多庫數據同步，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關注億速云行業資訊頻道。