Automatically Check MySQL Health
Posted on 26 February 2009 by admin
MySQL is in my opinion the best Database system out there pound for pound – dollar for dollar. Oh wait…it’s FREE! It’s still a fantastic system. Even though it’s a fantastically robust database server the tables may get fragmented with overhead due to continuous update, or delete and insert operation on data stored in database. The solution is to check the DB for integrity. And wouldn’t it be nice if that automagically happened? Here’s how.
You can automate the optimization of MySQL process by using crontab to check and optimize MySQL databases using the mysqlcheck client utility. mysqlcheck 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 (this is usually, crontab -e to add in the following line of text.
0 1 * * * mysqlcheck -Aao –auto-repair -u root -p[password] > /dev/null
You can change the scheduled running time to a time when your server experiences the least amount of use 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...]
All the details and the technical documentation can be found here including all of the switches and options.
