Run a mySQL query as a cron job

Question

I would like to purge my SQL database from all entires older than 1 week  and I d like to do it nightly  So  I m going to set up a cron job  How do I query mySQL without having to enter my password manually every time   The query in PHP is as follows   mysql query  DELETE FROM tbl message WHERE DATEDIFF  NOW       timestamp    gt  7      Is there a way to run this as a shell script  If not  is there a way of making cron run a php file

User · Answer

I personally find it easier use MySQL event scheduler than cron.

Enable it with

SET GLOBAL event_scheduler = ON;

and create an event like this:

CREATE EVENT name_of_event
ON SCHEDULE EVERY 1 DAY
STARTS '2014-01-18 00:00:00'
DO
DELETE FROM tbl_message WHERE DATEDIFF( NOW( ) ,  timestamp ) >=7;

and that's it.

Read more about the syntax here and here is more general information about it.

User · Answer

This was a very handy page as I have a requirement to DELETE records from a mySQL table where the expiry date is  lt  Today     I am on a shared host and CRON did not like the suggestion AndrewKDay   it also said  and I agree  that exposing the password in this way could be insecure   I then tried turning Events ON in phpMyAdmin but again being on a shared host this was a no no   Sorry fancyPants     So I turned to embedding the SQL script in a PHP file   I used the example  here  1     1   https   www w3schools com php php mysql create table asp stored it in a sub folder somewhere safe and added an empty index php for good measure   I was then able to test that this PHP file  and my SQL script  was working from the browser URL line     All good so far   On to CRON   Following the above example almost worked   I ended up calling PHP before the path for my   php file   Otherwise CRON didn t know what to do with the file   my cron is set to run once per day and looks like this  modified for security     00         php mywebsiteurl com wp-content themes ForteChildTheme php DeleteExpiredAssessment php  For the final testing with CRON I initially set it to run each minute and had email alerts turned on   This quickly confirmed that it was running as planned and I changed it back to once per day   Hope this helps

User · Answer

Try creating a shell script like the one below      bin bash  mysql --user  username  --password  password  --database  db name  --execute  DELETE FROM tbl message WHERE DATEDIFF  NOW       timestamp    gt  7    You can then add this to the cron

User · Answer

It depends on what runs cron on your system  but all you have to do to run a php script from cron is to do call the location of the php installation followed by the script location  An example with crontab running every hour     crontab -e 00          usr local bin php  home path script php   On my system  I don t even have to put the path to the php installation   00         php  home path script php   On another note  you should not be using mysql extension because it is deprecated  unless you are using an older installation of php   Read here for a comparison

[php] Run a mySQL query as a cron job?

Examples related to php

Examples related to mysql

Examples related to shell

Examples related to cron