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 ...
Get MySQL Cookbook, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.