Mysql 即時自動同步兩個不同的table (自訂) 不需要binlog
我們有時需要即時自動同步兩個不同的table 而binlog未能自訂由master 的ａtable sync to slave 的b table 因此我們需要使用TRIGGER。
以下為一sample
mysql> CREATE TABLE t1 ( a INT, b CHAR(5) );
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE t2 ( a INT, b CHAR(5) );
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TRIGGER trg1 AFTER INSERT ON t1 FOR EACH ROW
-> INSERT INTO t2 VALUES (NEW.a, NEW.b);
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TRIGGER trg2 AFTER DELETE ON t1 FOR EACH ROW
-> DELETE FROM t2 WHERE a = OLD.a AND b = OLD.b LIMIT 1;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TRIGGER trg3 AFTER UPDATE ON t1 FOR EACH ROW
-> UPDATE t2 SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b LIMIT 1;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 VALUES (1, 'a');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t1 VALUES (2, 'a');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t1 VALUES (2, 'b');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t1 VALUES (2, 'b');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t1 VALUES (2, 'b');
Query OK, 1 row affected (0.00 sec)
mysql> DELETE FROM t1 WHERE a = 1;
Query OK, 1 row affected (0.00 sec)
mysql> UPDATE t1 SET a = 1 WHERE b = 'b' LIMIT 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM t1;
+—+—+
| a | b |
+—+—+
| 2 | a |
| 1 | b |
| 2 | b |
| 2 | b |
+—+—+
4 rows in set (0.00 sec)
mysql> SELECT * FROM t2;
+—+—+
| a | b |
+—+—+
| 2 | a |
| 1 | b |
| 2 | b |
| 2 | b |
+—+—+
4 rows in set (0.00 sec)
轉 自:http://www.flupps.org/2007/11/15/keeping-a-second-table-in-sync-with-a-main-table/
