Events and Replication, part 1

MySQL events have been in GA for 2 years.  Events are not used as much as I would like -- often organizations will have cron jobs or scheduled tasks to perform ETL, maintenance and small aggregation tasks.  For example, a website that shows how many users are logged in might update a "cache table" with a count of active sessions every 5 minutes.

Events are great because they are a part of the database.  They only run when the database is running, and they are backed up with the database.  There is no need to store a password in a file or in a cron listing.

Events and replication can be a tricky matter.  Events can change data, and data changes are replicated.  In the example of an event that updates a table with a count of active sessions every 5 minutes, let's assume that the event itself would run on a primary master (write) server.  All slaves (including a secondary master) would receive the table update.

The tricky part is that CREATE EVENT statements are replicated.  There are three ways to avoid an event that changes data running on more than just the master:

0) Do not replicate the CREATE EVENT, or replicate it but then delete the event on any slaves.  I do not like this approach because if you promote one of the slaves to be a master, the event will not be in place.

1) Replicate the CREATE EVENT, knowing that the SLAVESIDE_DISABLED bit will be set even if you do not specify DISABLE ON SLAVE in the CREATE statement.  If you promote a slave to be a master, all you need to do is ALTER EVENT event_name ENABLED for each event that needs to be turned on.  This is the method I prefer, even though it still requires knowledge to turn on the events when a slave is promoted, and turn off events on the old master.

2) Replicate the CREATE EVENT, and enable the event right away, but set the event scheduler off.  This is easier if there are lots of events, because all that needs to be done when a slave is promoted to a master is:

mysql> SET GLOBAL event_scheduler=ON;

However, this means that you cannot have any events running on the slave itself - it is either all the events running, or not.

Part 2 will discuss some specific issues I have run into with events and replication.