mysql主從和觸發器的關係

mysql主從和觸發器的關係
binlog_format=row
和
binlog_format=statement是不一樣的




環境準備：
mysql  5.5
主庫上：
create database test;
use test;
create table t (id int);
create table t_log (id int, dd date);
 create trigger tri_t  after insert on t
     for each row
     insert into t_log values (new.id,now());
主從上檢視trigger都是存在的
主：
mysql> show triggers from test;
+---------+--------+-------+-----------------------------------------+--------+---------+--------------------+---------+----------------------+----------------------+--------------------+
| Trigger | Event  | Table | Statement                               | Timing | Created | sql_mode           | Definer | character_set_client | collation_connection | Database Collation |
+---------+--------+-------+-----------------------------------------+--------+---------+--------------------+---------+----------------------+----------------------+--------------------+
| tri_t   | INSERT | t     | insert into t_log values (new.id,now()) | AFTER  | NULL    | ONLY_FULL_GROUP_BY | root@%  | utf8                 | utf8_general_ci      | utf8_general_ci    |
+---------+--------+-------+-----------------------------------------+--------+---------+--------------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
從：
mysql> show triggers from test;
+---------+--------+-------+-----------------------------------------+--------+---------+--------------------+---------+----------------------+----------------------+--------------------+
| Trigger | Event  | Table | Statement                               | Timing | Created | sql_mode           | Definer | character_set_client | collation_connection | Database Collation |
+---------+--------+-------+-----------------------------------------+--------+---------+--------------------+---------+----------------------+----------------------+--------------------+
| tri_t   | INSERT | t     | insert into t_log values (new.id,now()) | AFTER  | NULL    | ONLY_FULL_GROUP_BY | root@%  | utf8                 | utf8_general_ci      | utf8_general_ci    |
+---------+--------+-------+-----------------------------------------+--------+---------+--------------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
場景1：
主從上都存在trigger:
主庫上執行操作：
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t values (1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_log;
+------+------------+
| id   | dd         |
+------+------------+
|    1 | 2015-12-03 |
+------+------------+
1 row in set (0.00 sec)
解析binlog,發現兩條語句都被寫入到binlog
BINLOG '
v1pgVhOAEwQKKAAAAN8AAAAAADEAAAAAAAEABHRlc3QAAXQAAQMAAQ==
v1pgVhOAEwQKLQAAAAwBAAAAADIAAAAAAAEABHRlc3QABXRfbG9nAAIDCgAD
v1pgVheAEwQKIgAAAC4BAAAAADEAAAAAAAAAAf/+AQAAAA==
### INSERT INTO test.t
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
v1pgVheAEwQKJQAAAFMBAAAAADIAAAAAAAEAAv/8AQAAAIO/Dw==
'/*!*/;
### INSERT INTO test.t_log
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='2015:12:03' /* DATE meta=0 nullable=1 is_null=0 */
# at 339
#151203 23:07:43 server id 168039296  end_log_pos 366   Xid = 322
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;    
從庫上檢視：
mysql> select * from t_log;
+------+------------+
| id   | dd         |
+------+------------+
|    1 | 2015-12-03 |
+------+------------+
1 row in set (0.00 sec)
mysql> select * from t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
主從都存在trigger時，主庫會記錄下所有的操作，包含trigger的操作，從庫上資料和主庫一致.
場景2： 在從庫上刪掉trigger
從：
mysql> drop trigger tri_t;
Query OK, 0 rows affected (0.00 sec)
主： 再次插入一條資料：
mysql> insert into t values (2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_log;
+------+------------+
| id   | dd         |
+------+------------+
|    1 | 2015-12-03 |
|    2 | 2015-12-03 |
+------+------------+
2 rows in set (0.00 sec)
從上檢視：
mysql>  select * from t_log;
+------+------------+
| id   | dd         |
+------+------------+
|    1 | 2015-12-03 |
|    2 | 2015-12-03 |
+------+------------+
2 rows in set (0.00 sec)
主從資料一致
從庫上沒有trigger時，依然不影響
場景3： 主庫上沒有 trigger,從庫上有時
主庫：
mysql> drop trigger tri_t;
Query OK, 0 rows affected (0.00 sec)
從庫：
mysql>  create trigger tri_t  after insert on t
    ->      for each row
    ->      insert into t_log values (new.id,now());
Query OK, 0 rows affected (0.00 sec)
主庫：
mysql> insert into t values (3);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_log;
+------+------------+
| id   | dd         |
+------+------------+
|    1 | 2015-12-03 |
|    2 | 2015-12-03 |
+------+------------+
2 rows in set (0.00 sec)
從庫：
mysql> select * from t_log;
+------+------------+
| id   | dd         |
+------+------------+
|    1 | 2015-12-03 |
|    2 | 2015-12-03 |
+------+------------+
2 rows in set (0.00 sec)
主從資料依然一致，從庫上的trigger沒有被觸發
binlog_format=row
結論：
1 主從都存在trigger時，主庫會記錄下所有的操作，包含trigger的操作，從庫上資料和主庫一致.
2 主有trigger,從庫上沒有trigger時，依然不影響主從同步
3 主上無trigger,從上有trigger時 ，主從資料依然一致，從庫上的trigger沒有被觸發








場景1：
主從上都存在trigger:
主庫上執行操作：
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
ysql> insert into t values (1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_log;
+------+------------+
| id   | dd         |
+------+------------+
|    1 | 2015-12-03 |
+------+------------+
1 row in set (0.00 sec)
解析binlog發現只有一條語句被記錄，觸發器沒被記錄：
use test/*!*/;
SET TIMESTAMP=1449156230/*!*/;
insert into t values (1)
/*!*/;
# at 278
#151203 23:23:50 server id 168039296  end_log_pos 305   Xid = 357
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
從庫上檢視：
mysql> select * from t_log;
+------+------------+
| id   | dd         |
+------+------------+
|    1 | 2015-12-03 |
+------+------------+
1 row in set (0.00 sec)
mysql> select * from t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
主從都存在trigger時，主庫會記錄sql語句，不包含trigger的操作，從庫上資料和主庫一致.
場景2： 在從庫上刪掉trigger
從：
mysql> drop trigger tri_t;
Query OK, 0 rows affected (0.00 sec)
主： 再次插入一條資料：
mysql> insert into t values (2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_log;
+------+------------+
| id   | dd         |
+------+------------+
|    1 | 2015-12-03 |
|    2 | 2015-12-03 |
+------+------------+
2 rows in set (0.00 sec)
從上檢視：
mysql>  select * from t_log;
+------+------------+
| id   | dd         |
+------+------------+
|    1 | 2015-12-03 |
+------+------------+
1 rows in set (0.00 sec)
主從資料不一致
從庫上沒有trigger時，觸發器不會被執行
場景3： 主庫上沒有 trigger,從庫上有時
主庫：
mysql> drop trigger tri_t;
Query OK, 0 rows affected (0.00 sec)
從庫：
mysql>  create trigger tri_t  after insert on t
    ->      for each row
    ->      insert into t_log values (new.id,now());
Query OK, 0 rows affected (0.00 sec)
主庫：
mysql> insert into t values (3);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_log;
+------+------------+
| id   | dd         |
+------+------------+
|    1 | 2015-12-03 |
|    2 | 2015-12-03 |
+------+------------+
2 rows in set (0.00 sec)
從庫：
mysql> select * from t_log;
+------+------------+
| id   | dd         |
+------+------------+
|    1 | 2015-12-03 |
|    3 | 2015-12-03 |
+------+------------+
2 rows in set (0.00 sec)
主從資料不一致，從庫上的trigger被觸發
binlog_format=statement
結論：
1 主從都存在trigger時，主庫會記錄sql語句，不包含trigger的操作，從庫上資料和主庫一致..
2 主有trigger,從庫上沒有trigger時，從庫上沒有trigger時，觸發器不會被執行
3 主上無trigger,從上有trigger時 ，主從資料不一致，從庫上的trigger被觸發




轉載請註明源出處 
QQ 273002188 歡迎一起學習 
QQ 群 236941212 
oracle,mysql,mongo 相互交流

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25099483/viewspace-1853209/，如需轉載，請註明出處，否則將追究法律責任。