MySQL Cookbook, 2nd Edition

Using Events to Schedule Database Actions

Problem

You want to set up a database operation that runs periodically without user intervention.

Solution

Create an event that executes according to a schedule.

Discussion

As of MySQL 5.1, one of the capabilities available to you is an event scheduler that enables you to set up database operations that run at times that you define. This section describes what you must do to use events, beginning with a simple event that writes a row to a table at regular intervals. Why bother creating such an event? One reason is that the rows serve as a log of continuous server operation, similar to the MARK line that some Unix syslogd servers write to the system log periodically so that you know they’re alive.

Begin with a table to hold the mark records. It contains a TIMESTAMP column (which MySQL will initialize automatically) and a column to store a message:

mysql>CREATE TABLE mark_log (ts TIMESTAMP, message VARCHAR(100));

Our logging event will write a string to a new row. To set it up, use a CREATE EVENT statement:

mysql>CREATE EVENT mark_insert
    -> ON SCHEDULE EVERY 5 MINUTE
    -> DO INSERT INTO mark_log (message) VALUES('-- MARK --');

The mark_insert event causes the message '-- MARK --' to be logged to the mark_log table every five minutes. Use a different interval for more or less frequent logging.

This event is simple and its body contains only a single SQL statement. For an event body that needs to execute multiple statements, use BEGIN ... END compound-statement ...