A car runs longer and is more reliable if maintained well. The same can be said about databases. It is good idea to regularly check databases for errors. How often – depends on how busy your server is, and how often databases are being updated.

Personally, I prefer running this with no other processes accessing databases. So I shut down web server, stop MySQL to make sure all processes exited, and then start MySQL:

httpd -k stop
service mysqld stop
service mysqld start

Of course this is only good if you can afford stopping web server. On busy web sites you may need to run these without stopping servers.

From the terminal window, issue the following commands:

mysqlcheck -uroot -p --check    --silent --all-databases
mysqlcheck -uroot -p --repair   --silent --all-databases
mysqlcheck -uroot -p --optimize --silent --all-databases

Option -p tells that there will be password typed from the command propmt. It is also possible to have password specified as argument so that the whole process can run without user (e.g. using cron schedule) although it is not a very good idea to have password stored as plain text. Note that these are run from the command prompt, you don’t need to login to MySQL.

There might be two warnings regarding tables general_log and slow_log – ignore those. Don’t forget to start the Apache once finished:

httpd -k start

So, the full script to perform all the checks and optimization on all databases:

httpd -k stop
service mysqld stop
service mysqld start
mysqlcheck -uroot -p --check    --silent --all-databases
mysqlcheck -uroot -p --repair   --silent --all-databases
mysqlcheck -uroot -p --optimize --silent --all-databases
httpd -k start

And sample output:

Full manual on mysqlcheck command can be found here.

8,645 total views, 2 views today