mysql 主站和从站以及触发器之间的关系

最编程 2024-06-13 21:52:35

...

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被触发

上一篇：主从 JAVA 主从 RS 触发器

下一篇：燕十八php视频教程：2023最新燕十八php相关视频教程全集