MySQL scheduled event and replication conflict

Seeking some advice as what the best setup for scheduled events in a master-slave replication scenario in MySQL is.

I have a Master database (SQL1) with 2 slave database (SQL2 and SQL3) each on their own servers. SQL1 has scheduled events enabled and runs a nightly event to clean some data and logs some messages. SQL2 has scheduled events disabled all together so not an issue. SQL3 needs to have has scheduled events enabled as there's another database that runs some ETL task.

When the replication stops working occasionally a back up of SQL1 is restored on SQ2 and SQL3 and then the sync is re-established. However this means the scheduled event from SQL1 would also run on SQL3 and log the same messages, which will already be replicated from SQL1 and this again breaks the link due to duplicate keys etc.

I can of course disable/delete the event on SQL3 manually after the restore but wondering if there's a better way of achieving this?

Solution

What you describe is usually not an issue. When you create an event it is stored on which server the event was created. When you restore a backup on a different server, the event is created, but it gets the status slaveside_disabled (which means of course it won't be executed on the slave), since the server_id differs from the server_id in the event. You can verify this with

mysql> show events\G

Example output:

                  Db: some_db_name
                Name: some_event_name
             Definer: root@localhost
           Time zone: SYSTEM
                Type: ONE TIME
          Execute at: 2016-01-01 00:00:01
      Interval value: NULL
      Interval field: NULL
              Starts: NULL
                Ends: NULL
              Status: SLAVESIDE_DISABLED
          Originator: 101
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci

The originator is the server_id on which the event was created. You can check the server_id on the slave with

mysql> select @@global.server_id;
+--------------------+
| @@global.server_id |
+--------------------+
|                105 |
+--------------------+

And no worries, the server_id is surely different from the master. Otherwise your replication wouldn't work.

EDIT: Adding a source: manual

EDIT 2: If you still have this problem, you probably are dumping and restoring the mysql schema as well. In this schema is a table event, which holds information about all the events in the different schemas.

To solve your problem, do not dump the mysql schema. Include the --events parameter when executing mysqldump (read the paragraph about this parameter: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html). When you need all your users on the slave as well (only necessary when you're accessing the slave directly), dump the users separately. I'd recommend pt-show-grants from the percona-toolkit for this: enter link description here

Alternatively keep everything like you have it working right now and do this after restoring the backup:

ALTER EVENT your_event_name DISABLE ON SLAVE;

(read more about it here: https://dev.mysql.com/doc/refman/5.7/en/alter-event.html)