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

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.

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    gt  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

[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