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.
Related Articles
- How to Disable Linux and Unix Cron Jobs (Crontab) Email Sending
- MySQL Database Performance Tuning Best Practices Video Tutorial
- Enable Logging of Slow Queries (Slow Query Log) in MySQL Database
- How to Find and Replace Text in MySQL Database using SQL
- Remove or Trim First or Last Few Characters in MySQL Database with SQL
- Installing Web Server in FreeBSD 6.0 with Apache 2.2, MySQL 5.0 and PHP 5 – Part 3
- Change and Reset MySQL root Password
- How to Backup and Restore (Export and Import) MySQL Databases Tutorial
- Reset the Root Password of MySQL Server
- Using phpMyAdmin for Effective MySQL Management in Windows XP
Entries (RSS)
August 13th, 2009 22:33
Thanks!!, I’ll test it on my testing server.
April 2nd, 2009 09:41
I’m a newby on managing MySQL.
I want to ask you, how to to make sure if the Cron is working?
Thanks
November 16th, 2008 12:48
along the lines of what Zubin is suggesting you can use the –silent(-s) switch as well, so you only get output if there is an error, something like
MAILTO=email@domain.com
0 1 * * * mysqlcheck -Aaos -uroot -ppassword
this way you will only get an email if there is an error
September 20th, 2008 18:34
Sure mysql does need frequent tunning, and also I have seen a lot of the servers running in the default configuration. which happens to be a very modest one. If you have access to the mysql configuration file, there are some values that can make your server breathe more often. And make your visitors more happy.
May 6th, 2008 19:15
I did not know that it was nesessary to perform a regular check for mysql.
January 16th, 2008 06:40
Note that -–auto-repair should have two dashes
Also, you might want to leave “> /dev/null” out for the first few runs, to see the results of the command (if you put MAILTO=me@mydomain.com at the top of the crontab, the results will get sent there).