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.

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 time and effort. You will need to edit the crontab file to accomplish this. Follow these steps:

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

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 the above, you will have cut down your server maintenance and made your server run a little better.


--
If you like this post please post your comment
Please post your comments so that we can imporve the blog.

No comments:

Post a Comment