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.

Comments

What makes it complicated in the event case is that the event on the slave is only executed if the slave is running. If the slave is down for an hour and comes back online, it misses all events from that time whereas all trigger executions are done as the statements invoking the triggers are replicated. So handling events like triggers is also not the final solution.

Fri, 11/12/2010 - 00:22

The way events are replicated is the other way round to the way triggers are replicated. With triggers, only the trigger itself is replicated, but the actions the trigger make do not get replicated.Thus, a slave would just have the same trigger activated, and by determinism ,reach the same result as the master.It's too bad things do not work the same for events, where both event definition *and* event statements get replicated.I'm not sure which solution is more correct, but I just wish it would be consistent.

Tue, 11/09/2010 - 02:09

Reply

The content of this field is kept private and will not be shown publicly.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

More information about formatting options

By submitting this form, you accept the Mollom privacy policy.
Website by Digital Loom