Optimizing MySQL Databases on Dedicated Server

28Apr/10

Optimizing MySQL Databases on Dedicated Server

The dedicated servers may seems to be advantageous in many conditions, however it can also be time consuming. If you have ever opt for a a dedicated server, then you may have encountered that there are infinite logs, services, and users to check and recheck.

In order to reduce the load people uses some kind of automation to get ease of the work load. Any kind of automation you setup on your server will effectively reduce your daily, weekly, and monthly work load.

There is a popular automation integrated in a server is the Cron. Setting up a cron job is a convenient way to manage your daily tasks.

MySQL databases, which is an important part of any website particularly needs a routine maintenance, though the maintenance includes of just checking the databases and optimizing the tables of it. However, ignoring the database maintenance can make your website face trouble if the databases tables gets corrupted or fragmented.

Both the mentioned tasks are quick and simple, however, going through every single database can consume tremendous amount of time.

Using Cron function, you can easily automate this process and save your valuable time. To do that you will require to edit the crontab file. Below are the simple guidelines to optimize the MySQL databases on Dedicated Servers:

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 whatever you want for your server. This will check all of the databases on the server. If you only want to check certain databases, use the following syntax: