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