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

This was a very handy page as I have a requirement to DELETE records from a mySQL table where the expiry date is < 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

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

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