Sysadmin Garden of Eden Docs

Version 1337.42.0

Events

Ever wanted to run queries in a cronjob, but not amused about putting credentials on the filesystem? MySQL / MariaDB can do it.

Official documentation can be found here:

Enable the event_scheduler System

You must have the event_scheduler enabled, this can be done by running the following query:

1
SET GLOBAL event_scheduler = ON;

NOTE

It is recommended to use the config file of your MySQL / MariaDB server or you can use a init_file option which is executed on server startup.

Create an Event to run a SQL Query

The CREATE EVENT query below would run the query after the DO every day at 02:00 in the exampledb database.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
CREATE EVENT `exampledb`.`my_cool_table_reset_userOption45` ON
SCHEDULE EVERY 1 DAY STARTS CURRENT_DATE + INTERVAL 1 DAY + INTERVAL 2 HOUR DO
UPDATE
    `exampledb`.`my_cool_table`
SET
    userOption45 = ''
WHERE
    userOption45 != ''
    AND STR_TO_DATE(userOption45,
    '%Y-%m-%d') <= NOW();

Show existing Events

NOTE

exampledb is the database name.

1
SHOW EVENTS FROM `exampledb`;
Last updated on 21 Feb 2020
Published on 21 Feb 2020
Edit on GitHub