Magento Daily Maintenance Script
This is just a short(ish) post for anybody interested in setting up a little cron job to keep your Magento database a bit tidier. The cron job itself is quite simple, it truncates some ‘not so necessary’ tables in your Magento database (mostly logs) and then goes on to re-index everything in Magento as a final step.
However, one of the cron jobs also trunactes the core_url_rewrite table, which can lead to some 404 errors until everything is re-indexed (hence the last part of the cron).
You have been warned…
Before putting this into action on a production site it is always smart to give it a test first on a development version, just incase!
Okay, so here goes…
Cron jobs
3022***/usr/bin/mysql magento-e”TRUNCATE TABLE core_url_rewrite”
3022***/usr/bin/mysql magento-e”TRUNCATE TABLE log_customer”
3022***/usr/bin/mysql magento-e”TRUNCATE TABLE log_visitor”
3022***/usr/bin/mysql magento-e”TRUNCATE TABLE log_visitor_info”
3022***/usr/bin/mysql magento-e”TRUNCATE TABLE log_url”
3022***/usr/bin/mysql magento-e”TRUNCATE TABLE log_url_info”
3022***/usr/bin/mysql magento-e”TRUNCATE TABLE log_quote”
3022***/usr/bin/mysql magento-e”TRUNCATE TABLE report_viewed_product_index”
3022***/usr/bin/mysql magento-e”TRUNCATE TABLE report_compared_product_index”
3022***/usr/bin/mysql magento-e”TRUNCATE TABLE report_event”
3022***/usr/bin/mysql magento-e”TRUNCATE TABLE catalog_compare_item”
4522***/usr/bin/php-f/var/www/shell/indexer.php reindexall
This is quite a simple set of cron jobs the purpose of which is to truncate various tables in your database and then re-index everything afterwards (as mentioned above). In this particular example all truncates happen at 22:30pm and then the re-index occurs at 22:45pm.
The script does have some variables that are application specific, i.e. your database might not be named ‘magento’, or your MySQL install may be in a different directory. This means that copying and pasting the above code into the crontab of your own server doesn’t necessarily mean it will start working automatically.
To help with this I will give a quick run down of each section in the code above so that it is easier to figure out what needs changing.
Choosing When The Cron Jobs Run.
Firstly, we have to specify the times/days that we want to run the cron jobs. The syntax for this is shown below:
Minute / Hour / Day of Month / Month / Day of Week
In crontab an asterisk (*) generally means all, so * in the month column would mean that the cron job runs every month.
To give an example, the code below would run at 15:00 every Monday:
#Minute / Hour / Day of Month / Month / Day of Week
0015**1
A handy tool for checking over your cron syntax and making sure everything will work as expected is Cron Checker.
Define Where MySQL is.
Next we have to define where MySQL is on the server using the below code:
This may vary dependant on your set-up. If you don’t know where MySQL is hiding then try the two snippets below in SSH to see if you can get a better idea:
mysql-uYOUR_USERNAME_GOES_HERE-p-e’SHOW VARIABLES WHERE Variable_Name LIKE “%dir”’
Or…
Define Which Database You Want To Make Changes To.
Next you want to define which database you are referring to. In the example above I simply named it magento — again, yours may be something different. Once you find this make the changes accordingly.
Re-indexing Data After Truncating.
Lastly, I’ll just take a quick look at the final line in the crontab; the line that re-indexes everything in Magento after the previous cron jobs have finished running.
4522***/usr/bin/php-f/var/www/shell/indexer.php reindexall
Similar to how we defined MySQL’s directory earlier we now have to do the same for php:
We can find this with the code below:
Then change your cron job so that it matches up.
Point the cron to your Magento directory.
And to finish it all up we simply need to make sure that the cron is pointing toward the Magento directory when running the re-indexing script. This particular Magento install is located in /var/www/ so we use the code:
/var/www/shell/indexer.php
If your Magento installation resides elsewhere then change this part of the cron job to that folder instead.
Now that’s all finished you can save your crontab and hopefully it should all start working. If you notice that it isn’t doing anything then take a look at your syslog (generally in /var/log/) and see if there are any errors just after the cron runs. Feel free to drop a comment below if you need help with any of this.
If you like this post, why not give us a follow on Twitter and stay up-to-date!
Originally published at artistlab.co.uk on July 5, 2015.
