Repair check and optimize every table in all databases
To solve this problem we can run a very simple command.
This solution also works when you come across the SQL error is marked as crashed and should be repaired.
Use the following command to automically repair and optimize(decrease overhead) your tables.
mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
mysqlcheck is available in MySQL 3.23.38 and later.
mysqlcheck uses the SQL statements CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, and OPTIMIZE TABLE in a convenient way for the user. It determines which statements to use for the operation you want to perform, and then sends the statements to the server to be executed. The exact operations are determined by the underlying storage engine used.
You have to use it on operational database. It is advised that you kill other currently incoming queries before running this. You could do this by closing your mysqldeamon or just lock all tables like the commands repair and analyze would do.
Posted by James on 2009-06-17 in the category " mysql "