ProblemYou want to set up a database operation that runs periodically without user intervention. SolutionCreate an event that executes according to a schedule. DiscussionAs 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 syntax. In that case, if you use mysql to create the event, you need to change the statement delimiter while you're defining the event, as discussed in Section 16.1. At this point, you should wait a few minutes and then select the contents of the mark_log table to verify that new rows are being written on schedule. However, if this is the first event that you've set up, you might find that the table remains empty no matter how long you wait: mysql> SELECT * FROM mark_log; Empty set (0.00 sec) If that's the case, very likely the event scheduler isn't running (which is its default state until you enable it). Check the scheduler status by examining the value of the event_scheduler system variable: mysql> SHOW VARIABLES LIKE 'event_scheduler'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | 0 | +-----------------+-------+ To enable the scheduler interactively if it is not running, execute the following statement (which requires the SUPER privilege): mysql> SET GLOBAL event_scheduler = 1; That statement enables the scheduler, but only until the server shuts down. To make sure that the scheduler runs each time the server starts, set the system variable to 1 in your my.cnf option file: [mysqld] event_scheduler=1 When the event scheduler is enabled, the mark_insert event eventually will create many rows in the table. There are several ways that you can affect event execution to prevent the table from growing forever:
To check on event activity, look in the server's error log, where it records information about which events it executes and when. |