MySQL - Enabling Scheduled Event on Master and Slave Simultaneously

Question

This may seem a bit strange, but I am trying to get a Scheduled Event to execute on both Master and Slave.

I have two databases set up in a Master (A) to Master (B) replication environment.

Master A is READ_ONLY=OFF

Master B is READ_ONLY=ON

I create a user on both Databases: GRANT INSERT, EVENT ON test.* TO 'user'@'localhost' IDENTIFIED BY 'Password';

I then create my Event on Master A:

DROP EVENT `e_test`;
DELIMITER $$
CREATE DEFINER=`user`@`localhost` 
EVENT `e_test` 
ON SCHEDULE EVERY 1 MINUTE 
STARTS NOW() 
ON COMPLETION PRESERVE 
ENABLE 
COMMENT 'Adds a new row to test.tab1 every 1 minute' 
DO 
    BEGIN
        INSERT INTO test.tab1 (`fname`) VALUES (NOW());
    END;
$$

So far so good. It executes every minute, and adds an entry to the table, which replicates to the other Database.

However, on the Master B, it is marked as SlaveSide Disabled, and so doesn't execute.

If I do:

ALTER DEFINER=user@localhost EVENT e_test ENABLE;

on Master B, it starts to execute on Master B, but on Master A it is now flagged as Slaveside_Disabled, and so doesn't execute.

If I then enable it on Master A, Master B is Slaveside_Disabled.

The reason for wanting this (in case you were wondering), is so that as part of my failover script I simply need to execute SET GLOBAL READ_ONLY = { ON | OFF } on each database accordingly, as opposed to having to enable / disable all my events (one command vs many commands).

Under normal circumstances, on Master A (READ_ONLY=OFF) the events execute as per normal and adds the entry; On Master B (READ_ONLY=ON) the events execute, but don't insert an entry as they don't have permission.

I looked at using SET GLOBAL EVENT_SCHEDULER = { ON | OFF } as the one command, but if I set it to OFF as the default, then we need to remember to enable it each server restart. Alternatively if we set it to ON as the default we need to remember to disable it every server restart. The use of READ_ONLY seemed a better option as it can be easily included in a failover script.

Any ideas?

Answer 1

The only way I have found to do this so far is by using:

On the Master B:

SET SQL_LOG_BIN=0;
ALTER DEFINER=user@localhost EVENT e_test ENABLE;
SET SQL_LOG_BIN=1;

This works, but as soon as I alter the Event on Master A, Master B goes back to Slaveside_disabled and I have to repeat this process. So not perfect by any means, as it relies on us remembering to do this for each event, if we Alter them.