Tuesday, February 21, 2017

More things to know about events in MySQL

Events in MySQL are very useful for having some action kick off on a regular basis. I've used them for adding/removing partitions, killing long running queries and archiving data. However, when working with replication, they can some times be tricky. The data charmer wrote a blog post a few years ago which is useful:

http://datacharmer.blogspot.com/2009/03/something-to-know-about-event-scheduler.html

This is also a good reference:
http://anothermysqldba.blogspot.com/2013/05/using-mysql-event-scheduler.html

In MySQL only the master has running events. The event itself is replicated to the slave but is "SLAVESIDE_DISABLED". The tricky part is when you want to promote a slave to be a master. The event does not become ENABLED until you run an alter command like this:

ALTER EVENT <schema_name>.<event_name> ENABLE;

Also turning on the event scheduler does not enable individual events.

Your failover process could include a script that checks the slave for all events that are SLAVESIDE_DISABLED and then changes them to ENABLE when promoting a slave to a master. One of the tricky parts I have also experienced is when adding a new slave-replica to a cluster. I would copy over a previous backup from XtraBackup and then my script would add the slave into the cluster. If the backup contained events all the events on the SLAVE would be ENABLED. If the event was doing some thing like adding or removing partitions then I would notice that replication would break because the slave was doing what the master was also doing and when the statements were replicated to the slave, they would fail. I needed to add a check to my script which adds new slaves to a cluster and make all the events SLAVESIDE_DISABLED by running this on each event on the new replica:

ALTER EVENT zabbix.RotatePartitionedTables disable on slave;


No comments:

Post a Comment