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
-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
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.
7,036 total views, 2 views today