Events

Just like a cron on a Linux server, MySQL has EVENTS to handle the scheduled tasks. MySQL uses a special thread called the event schedule thread to execute all scheduled events. By default, the event scheduler thread is not enabled (version < 8.0.3), to enable it, execute: 

mysql> SETGLOBALevent_scheduler=ON;

How to do it...

Suppose you no longer need to keep salary audit records that are more than a month old, you can schedule an event that runs daily and deletes the records from the salary_audit table that are a month old.

mysql> DROP EVENT IF EXISTS purge_salary_audit;
DELIMITER $$
CREATE EVENT IF NOT EXISTS purge_salary_audit
ON SCHEDULE
  EVERY 1 WEEK
  STARTS CURRENT_DATE 
    DO BEGIN
        DELETE FROM salary_audit WHERE date_modified < DATE_ADD(CURDATE(), INTERVAL -7 day);
    END $$
DELIMITER ;

Once the event is created, it will automatically do the job of purging the salary audit records.

• To check the events, execute:

mysql> SHOW EVENTS\G
*********************...