Considerations for MySQL failover event

In the master-slave architecture, create an event on the master, as shown below:

 
 

  
  
mysql> show create event `insert`\G; 
  
  
*************************** 1. row *************************** 
  
  
               Event: insert 
  
  
            sql_mode:  
  
  
           time_zone: SYSTEM 
  
  
        Create Event: CREATE DEFINER=`root`@`localhost` EVENT `insert` 
  
  
ON SCHEDULE EVERY 1 MINUTE STARTS '2012-11-20 16:10:09' 
  
  
ON COMPLETION PRESERVE ENABLE DO BEGIN 
  
  
insert into t3(name) values('aa'); 
  
  
END 
  
  
character_set_client: utf8 
  
  
collation_connection: utf8_general_ci 
  
  
  Database Collation: utf8_general_ci 
  
  
1 row in set (0.02 sec) 
  
  
 
  
  
ERROR:  
  
  
No query specified 
 
 

When slave is synced, the result is as follows. Pay attention to the red font:

 
 

  
  
mysql> show create event `insert`\G; 
  
  
*************************** 1. row *************************** 
  
  
               Event: insert 
  
  
            sql_mode:  
  
  
           time_zone: SYSTEM 
  
  
        Create Event: CREATE DEFINER=`root`@`localhost` EVENT `insert` 
  
  
ON SCHEDULE EVERY 1 MINUTE STARTS '2012-11-20 16:10:09' 
  
  
ON COMPLETION PRESERVE DISABLE ON SLAVE DO BEGIN 
  
  
insert into t3(name) values('aa'); 
  
  
END 
  
  
character_set_client: utf8 
  
  
collation_connection: utf8_general_ci 
  
  
  Database Collation: utf8_general_ci 
  
  
1 row in set (0.02 sec) 
  
  
 
  
  
ERROR:  
  
  
No query specified 
 
 

Let's look back at the event status and pay attention to the red font:

On the master

 
 

  
  
mysql> 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 | 
  
  
+------+--------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+ 
  
  
| test | insert | root@localhost | SYSTEM    | RECURRING | NULL       | 1              | MINUTE         | 2012-11-20 16:10:09 | NULL | ENABLED |         25 | utf8                 | utf8_general_ci      | utf8_general_ci    |  
  
  
+------+--------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+ 
  
  
1 row in set (0.11 sec) 
 
 

On slave

 
 

  
  
mysql> 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 | 
  
  
+------+--------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+--------------------+------------+----------------------+----------------------+--------------------+ 
  
  
| test | insert | root@localhost | SYSTEM    | RECURRING | NULL       | 1              | MINUTE         | 2012-11-20 16:10:09 | NULL | SLAVESIDE_DISABLED |         25 | utf8                 | utf8_general_ci      | utf8_general_ci    |  
  
  
+------+--------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+--------------------+------------+----------------------+----------------------+--------------------+ 
  
  
1 row in set (0.10 sec) 
 
 

That is to say, the event can only be triggered on the master node, but not on the slave node. Otherwise, if the slave node is triggered, synchronization replication will break down.

After the master-slave failover, the VIP will drift to the previous slave, and the slave will become a new master.

However, the event status is still "SLAVESIDE_DISABLED", not changed to "ENABLED". In this way, the event cannot be executed after the switch.

Therefore, you need to manually re-enable the event status.

 
 

  
  
mysql> alter event `insert` enable;  
  
  
Query OK, 0 rows affected (0.05 sec) 
 
 

Reference manual:

650) this. width = 650; "border =" 0 "alt =" "src =" http://www.bkjia.com/uploads/allimg/131229/1Z42I4O-0.jpg "/>

 

This article is from the "hechun's technical column" blog, please be sure to keep this source http://hcymysql.blog.51cto.com/5223301/1067081