Learn how to schedule MySQL jobs

I am using MariaDB 5.x for this post. However this should work on most recent MySQL databases.
Make sure that MySQL server is running and login with the root or with the user that has privileges of creating events on the database you would like to use.

mysql -u root -p password123 db_name
SET GLOBAL event_scheduler = ON;

(or)

mysql -u root -p
SET GLOBAL event_scheduler = ON;
use db_name;

CREATE EVENT e_hourly
    ON SCHEDULE
      EVERY 1 HOUR
    COMMENT 'Clears out not active users after 24 hours of link being sent.'
    DO
      DELETE FROM Users_table WHERE activated='n' AND DATEDIFF( NOW(),  time ) >1;

The above statement will create an event that runs every hour starting from the moment you execute this script. But if you want it to start executing at some other time (ex: January 30th 2018 at 00:00), then you can use the START command in the following way.

CREATE EVENT 
  ON SCHEDULE 
    EVERY 1 DAY
      STARTS '2018-01-30 00:00:00'
    COMMENT 'Clears out not active users after 24 hours of link being sent.'
DO
DELETE FROM Users_table_message WHERE activated='n' AND DATEDIFF( NOW(),  time ) >1;

SHOW EVENTS;

Cronjobs using Crontab

This can also be done directly using crontab and scheduling a cronjob. Consider the following code for example.

crontab -e

This will usually open crontab file with the nano editor.

#!/bin/bash
mysql --user=root --password=password123 --database=db_name --execute="DELETE FROM Users_table WHERE activated='n' AND DATEDIFF( NOW(),  time ) >1"

Now to save the changes to disk and exit while using the nano editor, use the following commands.

Ctrl+O
Enter
Ctrl+X

Scripts

You can also use php scripts to run the mysql query. For example the following script runs every hour.

0 * * * * /usr/local/bin/php /var/www/html/clean_mysql_script.php

Cronjobs using Crontab

Scripts

Related