Check and Optimize MySQL Database Automatically with Crontab/Cron

MySQL is a very popular free yet powerful database system. But even in great databases, the tables may get fragmented with overhead due to continuous update, or delete and insert operation on data stored in database. Beside, it’s also possibility that the databases may get corrupted. Thus, performing health check on database and optimize MySQL server regularly is an important task.

It’s a bit troublesome if the database administrators have to login to the server or launching the phpMyAdmin to optimize the databases one by one or table by table manually. Sometimes DB admin may simply forgets to do the job or set the frequency of optimization to lesser times. It’s recommended that every tables in MySQL databases are checked at least once a day on a busy server.

It’s possible to automate the optimization of MySQL process by using crontab function in Linux/Unix/CentOS/FreeBSD. The cron job to check and optimize MySQL databases can be created by using mysqlcheck client utility comes MySQL installation. mysqlcheck client can checks, repairs, optimizes, and analyzes tables in MySQL database.

To create a new cron job, login to the server as root or any other user, and then edit the crontab file (in most operating system, crontab -e will open crontab file in default text editor) to add in the following line of text. For users using cPanel, click on “Cron job” where you can set up crontab at daily, hourly and other interval. Experience webmasters can also set up a crontab file in rc.hourly or rc.daily or other cron directory. Note that if you login as a MySQL or normal user with no access privileges to all database, it’s not possible to optimize all databases, unless user ID and password for root is specified as in example below.

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

The above statement has the syntax similar to “mysqlcheck [options] –all-databases”, where –all-databases parameter is the default action is no databases is specified thus can be omitted. The command will run mysqlcheck client to automatically analyze and optimize all databases at 1 am everyday. Note that there is not space between -p and your password for root. You can change the running time to your preference, and also change the options for mysqlcheck command. If you just want to check and optimize certain databases or certain tables without the database, use the following syntax:

mysqlcheck [options] db_name [tables]
mysqlcheck [options] --databases DB1 [DB2 DB3...]

You may want to remove –auto-repair switch from the above command, as a table repair operation might cause data loss under some circumstances the operation due to causes include but are not limited to filesystem errors. For those who has changed the character set and collation of MySQL databases may also need to use –default-character-set option. More information on all available switches and options available can be found here.