How to Automatically Optimize MySQL Databases

Running a dedicated server can be time consuming. There are a myriad of logs, services, and users to check and recheck. Any automation you can setup will greatly reduce your daily, weekly, and monthly work load. Cron is a handy way to automate tasks, as we have previously demonstrated.

MySQL databases, in particular, require routine maintenance, although that maintenance often only involves checking the databases and optimizing the tables. Nevertheless, neglecting maintenance on databases can cause websites to experience trouble if tables become corrupted or fragmented. Both tasks are quick and easy, but going through every database can take a tremendous amount of time.

Using cron, you can automate this process and save yourself valuable energy. You will need to edit the crontab file to accomplish this. Follow these steps:

1. Login to your server via SSH
2. Become root:

3. Edit the crontab file located at /etc/crontab

crontab -e

4. Enter the follow on a new line in crontab:

0 1 * * * mysqlcheck -Aao –auto-repair -u root -p[password] > /dev/null

5. Save an exit.

Now, every night at 1 AM, cron will execute mysqlcheck to optimize all of the databases on your server. If every day is to frequent, change the cron settings to something that works for your server, following our guide. This will check all of the databases on the server. If you only want to check certain databases, use the following syntax:

mysqlcheck [options] db_name[tables]

With that one-line command, you will have cut down your server maintenance and made your server run a little better.