mysql 发器_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

v1pgVheAEwQKJQAAAFMBAAAAADIAAAAAAAEAAv/8AQAAAIO/Dw==

';

### INSERT INTO test.t_log

### SET

###  @1=1

###  @2='2015:12:03'

# at 339

#151203 23:07:43 server id 168039296

end_log_pos 366  Xid =

322

COMMIT;

DELIMITER ;

# End of log file

ROLLBACK ;

;

从库上查看：

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 ;

;

从库上查看：

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 相互交流