1 MASTER -SLAVE TRRGER测试
binlog_format=ROW
测试环境为master-master双主模式，配置的双向复制。
11:25: [(none)]> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.01 sec)
create database test;
create table class (classID int,stuCount int);
create table student(stuID int primary key,classID int);
1 在master 数据库student 插入一行数据
insert into student values (1,2);
insert into class values(2,1);
master
11:11: [test]> select * from student;
+-------+---------+
| stuID | classID |
+-------+---------+
| 1 | 2 |
+-------+---------+
1 row in set (0.00 sec)
11:11: [test]> select * From class;
+---------+----------+
| classID | stuCount |
+---------+----------+
| 2 | 1 |
+---------+----------+
1 row in set (0.00 sec)
DELIMITER |
create trigger tri_stuInsert after insert
on student for each row
begin
declare c int;
set c = (select stuCount from class where classID=new.classID);
update class set stuCount = c + 1 where classID = new.classID;
end|
DELIMITER ;
insert into student values (2,2);
查看slave和master的数据均为：
11:12: [test]> select * from student;
+-------+---------+
| stuID | classID |
+-------+---------+
| 1 | 2 |
| 2 | 2 |
+-------+---------+
2 rows in set (0.01 sec)
11:17: [test]> select * From class;
+---------+----------+
| classID | stuCount |
+---------+----------+
| 2 | 2 |
+---------+----------+
1 row in set (0.01 sec)
11:17: [test]>
在ORACLE数据库逻辑复制OGG环境中，对于trigger 外键以及JOB需要，目标端需要临时禁用。
那么MYSQL主从复制逻辑复制是否也需要在数据
查看binlog日志如下：
master：
mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS /data/master-1-bin.000007
SET @@SESSION.GTID_NEXT= '552561c8-df9f-11ee-be05-000c29947a85:45'/*!*/;
# at 733
#240326 11:08:12 server id 125 end_log_pos 803 Query thread_id=41 exec_time=0 error_code=0
SET TIMESTAMP=1711476492/*!*/;
BEGIN
/*!*/;
# at 803
#240326 11:08:12 server id 125 end_log_pos 852 Table_map: `mgr`.`student` mapped to number 98
# has_generated_invisible_primary_key=0
# at 852
#240326 11:08:12 server id 125 end_log_pos 899 Table_map: `mgr`.`class` mapped to number 93
# has_generated_invisible_primary_key=0
# at 899
#240326 11:08:12 server id 125 end_log_pos 939 Write_rows: table id 98
# at 939
#240326 11:08:12 server id 125 end_log_pos 989 Update_rows: table id 93 flags: STMT_END_F
### INSERT INTO `mgr`.`student`
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2=2 /* INT meta=0 nullable=1 is_null=0 */
### UPDATE `mgr`.`class`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2=1 /* INT meta=0 nullable=1 is_null=0 */
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2=2 /* INT meta=0 nullable=1 is_null=0 */
# at 989
#240326 11:08:12 server id 125 end_log_pos 1016 Xid = 144
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;slave
SET @@SESSION.GTID_NEXT= '552561c8-df9f-11ee-be05-000c29947a85:45'/*!*/;
# at 731
#240326 11:08:12 server id 125 end_log_pos 796 Query thread_id=41 exec_time=0 error_code=0
SET TIMESTAMP=1711476492/*!*/;
SET @@session.sql_mode=1168637984/*!*/;
BEGIN
/*!*/;
# at 796
#240326 11:08:12 server id 125 end_log_pos 843 Table_map: `mgr`.`class` mapped to number 93
# has_generated_invisible_primary_key=0
# at 843
#240326 11:08:12 server id 125 end_log_pos 892 Table_map: `mgr`.`student` mapped to number 98
# has_generated_invisible_primary_key=0
# at 892
#240326 11:08:12 server id 125 end_log_pos 932 Write_rows: table id 98
# at 932
#240326 11:08:12 server id 125 end_log_pos 982 Update_rows: table id 93 flags: STMT_END_F
### INSERT INTO `mgr`.`student`
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2=2 /* INT meta=0 nullable=1 is_null=0 */
### UPDATE `mgr`.`class`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2=1 /* INT meta=0 nullable=1 is_null=0 */
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2=2 /* INT meta=0 nullable=1 is_null=0 */
# at 982
#240326 11:08:12 server id 125 end_log_pos 1009 Xid = 143
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@master-2 ~]# 结论：可以看到该语句以row格式的binlog被记录，并在备库上执行。这样备库触发器无法被触发。
双向复制原理：数据为什么没有循环复制就是因为UUID和serverid了。
binlog_format=statement
如果将原来数据库的
准备工作
create table class (classID int,stuCount int);
create table student(stuID int primary key,classID int);
1 在master 数据库student 插入一行数据
insert into student values (1,2);
insert into class values(2,1);
master
11:11: [test]> select * from student;
+-------+---------+
| stuID | classID |
+-------+---------+
| 1 | 2 |
+-------+---------+
1 row in set (0.00 sec)
11:11: [test]> select * From class;
+---------+----------+
| classID | stuCount |
+---------+----------+
| 2 | 1 |
+---------+----------+
1 row in set (0.00 sec)
DELIMITER |
create trigger tri_stuInsert after insert
on student for each row
begin
declare c int;
set c = (select stuCount from class where classID=new.classID);
update class set stuCount = c + 1 where classID = new.classID;
end|
DELIMITER ;
insert into student values (2,2);
查看slave和master的数据均为：
11:12: [test]> select * from student;
+-------+---------+
| stuID | classID |
+-------+---------+
| 1 | 2 |
| 2 | 2 |
+-------+---------+
2 rows in set (0.01 sec)
11:17: [test]> select * From class;
+---------+----------+
| classID | stuCount |
+---------+----------+
| 2 | 2 |
+---------+----------+
1 row in set (0.01 sec)
在主库中进行3条数据插入操作
10:41: [mgr]> insert into student values(5,2); 班级人数+1（触发器）
Query OK, 1 row affected (0.00 sec)
10:41: [mgr]> insert into student values(6,2); 班级人数+1（触发器）
Query OK, 1 row affected (0.00 sec)
10:41: [mgr]> insert into student values(7,2); 班级人数+1（触发器）
Query OK, 1 row affected (0.00 sec)
10:41: [mgr]> select * from class;
+---------+----------+
| classID | stuCount |
+---------+----------+
| 2 | 5 |
+---------+----------+
1 row in set (0.00 sec)
修改binglog format为statement之后发现主库进行插入的日志如下：
如下binlog日志中可以发现如下：
SET @@SESSION.GTID_NEXT= '552561c8-df9f-11ee-be05-000c29947a85:36'/*!*/;
SET @@SESSION.GTID_NEXT= '552561c8-df9f-11ee-be05-000c29947a85:37/*!*/;
SET @@SESSION.GTID_NEXT= '552561c8-df9f-11ee-be05-000c29947a85:38'/*!*/;
执行了3个事务， 触发器的操作去哪了？？？？？、
10:54: [(none)]> show master status \G
*************************** 1. row ***************************
File: master-1-bin.000006
Position: 3497
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 4fa87c5a-e78b-11ee-bf9b-000c29369037:1-8,
552561c8-df9f-11ee-be05-000c29947a85:1-38
1 row in set (0.00 sec)
10:54: [(none)]>
SET @@SESSION.GTID_NEXT= '552561c8-df9f-11ee-be05-000c29947a85:36'/*!*/;
# at 2732
#240326 10:41:24 server id 125 end_log_pos 2808 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1711474884/*!*/;
BEGIN
/*!*/;
# at 2808
#240326 10:41:24 server id 125 end_log_pos 2910 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1711474884/*!*/;
insert into student values(5,2)
/*!*/;
# at 2910
#240326 10:41:24 server id 125 end_log_pos 2937 Xid = 88
COMMIT/*!*/;
# at 2937
#240326 10:41:27 server id 125 end_log_pos 3012 GTID last_committed=10 sequence_number=11 rbr_only=no original_committed_timestamp=1711474887000872 immediate_commit_timestamp=1711474887000872 transaction_length=280
# original_commit_timestamp=1711474887000872 (2024-03-26 10:41:27.000872 PDT)
# immediate_commit_timestamp=1711474887000872 (2024-03-26 10:41:27.000872 PDT)
/*!80001 SET @@session.original_commit_timestamp=1711474887000872*//*!*/;
/*!80014 SET @@session.original_server_version=80036*//*!*/;
/*!80014 SET @@session.immediate_server_version=80036*//*!*/;
SET @@SESSION.GTID_NEXT= '552561c8-df9f-11ee-be05-000c29947a85:37'/*!*/;
# at 3012
#240326 10:41:27 server id 125 end_log_pos 3088 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1711474887/*!*/;
BEGIN
/*!*/;
# at 3088
#240326 10:41:27 server id 125 end_log_pos 3190 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1711474887/*!*/;
insert into student values(6,2)
/*!*/;
# at 3190
#240326 10:41:27 server id 125 end_log_pos 3217 Xid = 92
COMMIT/*!*/;
# at 3217
#240326 10:41:29 server id 125 end_log_pos 3292 GTID last_committed=11 sequence_number=12 rbr_only=no original_committed_timestamp=1711474889851520 immediate_commit_timestamp=1711474889851520 transaction_length=280
# original_commit_timestamp=1711474889851520 (2024-03-26 10:41:29.851520 PDT)
# immediate_commit_timestamp=1711474889851520 (2024-03-26 10:41:29.851520 PDT)
/*!80001 SET @@session.original_commit_timestamp=1711474889851520*//*!*/;
/*!80014 SET @@session.original_server_version=80036*//*!*/;
/*!80014 SET @@session.immediate_server_version=80036*//*!*/;
SET @@SESSION.GTID_NEXT= '552561c8-df9f-11ee-be05-000c29947a85:38'/*!*/;
# at 3292
#240326 10:41:29 server id 125 end_log_pos 3368 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1711474889/*!*/;
BEGIN
/*!*/;
# at 3368
#240326 10:41:29 server id 125 end_log_pos 3470 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1711474889/*!*/;
insert into student values(7,2)
/*!*/;
# at 3470
#240326 10:41:29 server id 125 end_log_pos 3497 Xid = 96
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
在slave端进行查询分析如下：
0:56: [mgr]> select * from student;
+-------+---------+
| stuID | classID |
+-------+---------+
| 1 | 2 |
| 2 | 2 |
| 5 | 2 |
| 6 | 2 |
| 7 | 2 |
+-------+---------+
7 rows in set (0.04 sec)
10:56: [mgr]> select * from class;-
+---------+----------+
| classID | stuCount |
+---------+----------+
| 2 | 5 |
+---------+----------+
1 row in set (0.00 sec)
1 两个表的数据是同步的，并且没有循环执行GTID。
查看binlog日志如下：
10:41: [mgr]> show binary logs;
+---------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------------+-----------+-----------+
| master-2-bin.000001 | 6344 | No |
| master-2-bin.000002 | 3415 | No |
| master-2-bin.000003 | 3565 | No |
+---------------------+-----------+-----------+
发现slave 数据库也是执行了
SET @@SESSION.GTID_NEXT= '552561c8-df9f-11ee-be05-000c29947a85:36'/*!*/;
SET @@SESSION.GTID_NEXT= '552561c8-df9f-11ee-be05-000c29947a85:37'/*!*/;
SET @@SESSION.GTID_NEXT= '552561c8-df9f-11ee-be05-000c29947a85:38'/*!*/;
触发器的事务日志没有发现？？？？？？ 触发器是执行了
SET @@SESSION.GTID_NEXT= '552561c8-df9f-11ee-be05-000c29947a85:36'/*!*/;
# at 2786
#240326 10:41:24 server id 125 end_log_pos 2862 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1711474884/*!*/;
BEGIN
/*!*/;
# at 2862
#240326 10:41:24 server id 125 end_log_pos 2964 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1711474884/*!*/;
insert into student values(5,2)
/*!*/;
# at 2964
#240326 10:41:24 server id 125 end_log_pos 2991 Xid = 81
COMMIT/*!*/;
# at 2991
#240326 10:41:27 server id 125 end_log_pos 3073 GTID last_committed=10 sequence_number=11 rbr_only=no original_committed_timestamp=1711474887000872 immediate_commit_timestamp=1711474887001852 transaction_length=287
# original_commit_timestamp=1711474887000872 (2024-03-26 10:41:27.000872 PDT)
# immediate_commit_timestamp=1711474887001852 (2024-03-26 10:41:27.001852 PDT)
/*!80001 SET @@session.original_commit_timestamp=1711474887000872*//*!*/;
/*!80014 SET @@session.original_server_version=80036*//*!*/;
/*!80014 SET @@session.immediate_server_version=80036*//*!*/;
SET @@SESSION.GTID_NEXT= '552561c8-df9f-11ee-be05-000c29947a85:37'/*!*/;
# at 3073
#240326 10:41:27 server id 125 end_log_pos 3149 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1711474887/*!*/;
BEGIN
/*!*/;
# at 3149
#240326 10:41:27 server id 125 end_log_pos 3251 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1711474887/*!*/;
insert into student values(6,2)
/*!*/;
# at 3251
#240326 10:41:27 server id 125 end_log_pos 3278 Xid = 86
COMMIT/*!*/;
# at 3278
#240326 10:41:29 server id 125 end_log_pos 3360 GTID last_committed=11 sequence_number=12 rbr_only=no original_committed_timestamp=1711474889851520 immediate_commit_timestamp=1711474889852995 transaction_length=287
# original_commit_timestamp=1711474889851520 (2024-03-26 10:41:29.851520 PDT)
# immediate_commit_timestamp=1711474889852995 (2024-03-26 10:41:29.852995 PDT)
/*!80001 SET @@session.original_commit_timestamp=1711474889851520*//*!*/;
/*!80014 SET @@session.original_server_version=80036*//*!*/;
/*!80014 SET @@session.immediate_server_version=80036*//*!*/;
SET @@SESSION.GTID_NEXT= '552561c8-df9f-11ee-be05-000c29947a85:38'/*!*/;
# at 3360
#240326 10:41:29 server id 125 end_log_pos 3436 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1711474889/*!*/;
BEGIN
/*!*/;
# at 3436
#240326 10:41:29 server id 125 end_log_pos 3538 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1711474889/*!*/;
insert into student values(7,2)
/*!*/;
# at 3538
#240326 10:41:29 server id 125 end_log_pos 3565 Xid = 91
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
从测试中我们发现如果采用statement模式，binlog中记录的GTID为执行的insert语句，触发器的操作未记录，并且触发器执行了，slave数据库也执行了也未记录到binlog日志。
2 MASTER -SLAVE EVENT测试
首先这个event必须是在master上创建并且同步到slave上的，即通过create event
语句同步到slave上的，而不能直接手动在salve上创建。通过主从同步在从节点上
创建的event默认是disable的，即不会触发。
在双主全部运行的条件下在master1创建event如下：
master1
11:22: [mgr]> CREATE EVENT IF NOT EXISTS e_test
-> ON SCHEDULE EVERY '1' SECOND STARTS '2024-03-27 07:19:00'
-> ON COMPLETION PRESERVE COMMENT '每1一'
-> DO INSERT INTO timeevent(currenttime) VALUES (NOW());
Query OK, 0 rows affected (0.03 sec)
11:22: [mgr]>
11:31: [mgr]> show events;
+------+---------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation |
+------+---------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| mgr | e_test | root@localhost | SYSTEM | RECURRING | NULL | 1 | SECOND | 2024-03-27 07:19:00 | NULL | ENABLED | 125 | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_general_ci |
| mgr | e_test1 | root@localhost | SYSTEM | RECURRING | NULL | 1 | SECOND | 2024-03-26 11:28:00 | NULL | ENABLED | 125 | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_general_ci |
+------+---------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
2 rows in set (0.02 sec)
11:31: [mgr]>
master2 event的状态：
11:32: [(none)]> select event_name,status from information_schema.events;
+------------+--------------------+
| EVENT_NAME | STATUS |
+------------+--------------------+
| e_test | SLAVESIDE_DISABLED |
| e_test1 | SLAVESIDE_DISABLED |
+------------+--------------------+
2 rows in set (0.01 sec)
11:32: [(none)]> master binlog
SET @@SESSION.GTID_NEXT= '552561c8-df9f-11ee-be05-000c29947a85:64'/*!*/;
# at 5340
#240326 11:28:15 server id 125 end_log_pos 5418 Query thread_id=65 exec_time=0 error_code=0
SET TIMESTAMP=1711477695/*!*/;
BEGIN
/*!*/;
# at 5418
#240326 11:28:15 server id 125 end_log_pos 5470 Table_map: `mgr`.`timeevent` mapped to number 100
# has_generated_invisible_primary_key=0
# at 5470
#240326 11:28:15 server id 125 end_log_pos 5510 Write_rows: table id 100 flags: STMT_END_F
### INSERT INTO `mgr`.`timeevent`
### SET
### @1=16 /* INT meta=0 nullable=0 is_null=0 */
### @2=1711477695 /* TIMESTAMP(0) meta=0 nullable=1 is_null=0 */
# at 5510
#240326 11:28:15 server id 125 end_log_pos 5537 Xid = 194
COMMIT/*!*/;
# at 5537
#240326 11:28:16 server id 125 end_log_pos 5612 GTID last_committed=19 sequence_number=20 rbr_only=yes original_committed_timestamp=1711477696213946 immediate_commit_timestamp=1711477696213946 transaction_length=272
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1711477696213946 (2024-03-26 11:28:16.213946 PDT)
# immediate_commit_timestamp=1711477696213946 (2024-03-26 11:28:16.213946 PDT)
/*!80001 SET @@session.original_commit_timestamp=1711477696213946*//*!*/;
/*!80014 SET @@session.original_server_version=80036*//*!*/;
/*!80014 SET @@session.immediate_server_version=80036*//*!*/;
SET @@SESSION.GTID_NEXT= '552561c8-df9f-11ee-be05-000c29947a85:65'/*!*/;
# at 5612
#240326 11:28:16 server id 125 end_log_pos 5690 Query thread_id=66 exec_time=0 error_code=0
SET TIMESTAMP=1711477696/*!*/;
BEGIN
/*!*/;
# at 5690
#240326 11:28:16 server id 125 end_log_pos 5742 Table_map: `mgr`.`timeevent` mapped to number 100
# has_generated_invisible_primary_key=0
# at 5742
#240326 11:28:16 server id 125 end_log_pos 5782 Write_rows: table id 100 flags: STMT_END_F
### INSERT INTO `mgr`.`timeevent`
### SET
### @1=17 /* INT meta=0 nullable=0 is_null=0 */
### @2=1711477696 /* TIMESTAMP(0) meta=0 nullable=1 is_null=0 */
# at 5782
#240326 11:28:16 server id 125 end_log_pos 5809 Xid = 195
COMMIT/*!*/;
# at 5809
#240326 11:28:17 server id 125 end_log_pos 5884 GTID last_committed=20 sequence_number=21 rbr_only=yes original_committed_timestamp=1711477697215453 immediate_commit_timestamp=1711477697215453 transaction_length=272
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1711477697215453 (2024-03-26 11:28:17.215453 PDT)
# immediate_commit_timestamp=1711477697215453 (2024-03-26 11:28:17.215453 PDT)
/*!80001 SET @@session.original_commit_timestamp=1711477697215453*//*!*/;
/*!80014 SET @@session.original_server_version=80036*//*!*/;
/*!80014 SET @@session.immediate_server_version=80036*//*!*/;
SET @@SESSION.GTID_NEXT= '552561c8-df9f-11ee-be05-000c29947a85:66'/*!*/;
# at 5884
#240326 11:28:17 server id 125 end_log_pos 5962 Query thread_id=67 exec_time=0 error_code=0
SET TIMESTAMP=1711477697/*!*/;
BEGIN
/*!*/;
# at 5962
#240326 11:28:17 server id 125 end_log_pos 6014 Table_map: `mgr`.`timeevent` mapped to number 100
# has_generated_invisible_primary_key=0
# at 6014
#240326 11:28:17 server id 125 end_log_pos 6054 Write_rows: table id 100 flags: STMT_END_F
### INSERT INTO `mgr`.`timeevent`
### SET
### @1=18 /* INT meta=0 nullable=0 is_null=0 */
### @2=1711477697 /* TIMESTAMP(0) meta=0 nullable=1 is_null=0 */
# at 6054
#240326 11:28:17 server id 125 end_log_pos 6081 Xid = 196
COMMIT/*!*/;
# at 6081
#240326 11:28:18 server id 125 end_log_pos 6156 GTID last_committed=21 sequence_number=22 rbr_only=yes original_committed_timestamp=1711477698216720 immediate_commit_timestamp=1711477698216720 transaction_length=272
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1711477698216720 (2024-03-26 11:28:18.216720 PDT)
# immediate_commit_timestamp=1711477698216720 (2024-03-26 11:28:18.216720 PDT)
/*!80001 SET @@session.original_commit_timestamp=1711477698216720*//*!*/;
/*!80014 SET @@session.original_server_version=80036*//*!*/;
/*!80014 SET @@session.immediate_server_version=80036*//*!*/;slave的binlog
SET @@SESSION.GTID_NEXT= '552561c8-df9f-11ee-be05-000c29947a85:164'/*!*/;
# at 32622
#240326 11:29:55 server id 125 end_log_pos 32687 Query thread_id=49 exec_time=0 error_code=0
SET TIMESTAMP=1711477795/*!*/;
BEGIN
/*!*/;
# at 32687
#240326 11:29:55 server id 125 end_log_pos 32739 Table_map: `mgr`.`timeevent` mapped to number 100
# has_generated_invisible_primary_key=0
# at 32739
#240326 11:29:55 server id 125 end_log_pos 32779 Write_rows: table id 100 flags: STMT_END_F
### INSERT INTO `mgr`.`timeevent`
### SET
### @1=116 /* INT meta=0 nullable=0 is_null=0 */
### @2=1711477795 /* TIMESTAMP(0) meta=0 nullable=1 is_null=0 */
# at 32779
#240326 11:29:55 server id 125 end_log_pos 32806 Xid = 393
COMMIT/*!*/;
# at 32806
#240326 11:29:56 server id 125 end_log_pos 32888 GTID last_committed=121 sequence_number=122 rbr_only=yes original_committed_timestamp=1711477796331751 immediate_commit_timestamp=1711477796337506 transaction_length=266
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1711477796331751 (2024-03-26 11:29:56.331751 PDT)
# immediate_commit_timestamp=1711477796337506 (2024-03-26 11:29:56.337506 PDT)
/*!80001 SET @@session.original_commit_timestamp=1711477796331751*//*!*/;
/*!80014 SET @@session.original_server_version=80036*//*!*/;
/*!80014 SET @@session.immediate_server_version=80036*//*!*/;
SET @@SESSION.GTID_NEXT= '552561c8-df9f-11ee-be05-000c29947a85:165'/*!*/;
# at 32888
#240326 11:29:56 server id 125 end_log_pos 32953 Query thread_id=49 exec_time=0 error_code=0
SET TIMESTAMP=1711477796/*!*/;
BEGIN
/*!*/;
# at 32953
#240326 11:29:56 server id 125 end_log_pos 33005 Table_map: `mgr`.`timeevent` mapped to number 100
# has_generated_invisible_primary_key=0
# at 33005
#240326 11:29:56 server id 125 end_log_pos 33045 Write_rows: table id 100 flags: STMT_END_F
### INSERT INTO `mgr`.`timeevent`
### SET
### @1=117 /* INT meta=0 nullable=0 is_null=0 */
### @2=1711477796 /* TIMESTAMP(0) meta=0 nullable=1 is_null=0 */
# at 33045
#240326 11:29:56 server id 125 end_log_pos 33072 Xid = 396
COMMIT/*!*/;
# at 33072
#240326 11:29:57 server id 125 end_log_pos 33154 GTID last_committed=122 sequence_number=123 rbr_only=yes original_committed_timestamp=1711477797333223 immediate_commit_timestamp=1711477797338893 transaction_length=266
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1711477797333223 (2024-03-26 11:29:57.333223 PDT)
# immediate_commit_timestamp=1711477797338893 (2024-03-26 11:29:57.338893 PDT)
/*!80001 SET @@session.original_commit_timestamp=1711477797333223*//*!*/;
/*!80014 SET @@session.original_server_version=80036*//*!*/;
/*!80014 SET @@session.immediate_server_version=80036*//*!*/;
SET @@SESSION.GTID_NEXT= '552561c8-df9f-11ee-be05-000c29947a85:166'/*!*/;例如有2个mysql实例， A 和 B， A是主。
在A上执行create event 创建event c， 这个event c也会被同步到B上，但c在B上
默认是disable的。
其次当主从发生切换的时候需要手动去enable从节点上的事件。
例如当A和B 发生主从切换的时候， 需要在B上执行
ALTER EVENT event_name ENABLED
并且当A成为B的从之后需要手动执行
ALTER EVENT event_name DISABLED
3 总结
1 在master-slave环境中最好将binlog format参数值设置为row，可以忽略触发器问题。
2 在生成备库时，要注意evnet的状态，最好将参数event_scheduler 暂时设置为0，或者通过命令ALTER EVENT event_name DISABLED 将手工创建的event进行diable避免数据不一致。
11:33: [mgr]> alter event e_test1 disable;
Query OK, 0 rows affected (0.00 sec)
11:33: [mgr]> alter event e_test disable;
Query OK, 0 rows affected (0.00 sec)
11:33: [mgr]>