MySQL Tip: How To Check, Repair & Optimize All Tables in All Databases
By Angsuman Chakraborty, Gaea News NetworkSunday, April 20, 2008
Here is a simple command to auto repair, check and optimize all the tables in all databases running on a MySQL server:
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 must execute it on running database. It is recommended that you kill other incoming queries (like from your website) before running this which makes it a lot faster. I simply stop my httpd server before running it. You may want to use a Site Unavailable message instead so that the MySQL server is left alone.
|
May 7, 2010: 1:17 am
You may also want to include –check-upgrade to upgrade any tables to new formats if necessary. |
|
February 23, 2010: 12:55 am
thank you so much, i was struggling with table-by-table procedure for the last few years. |
|
January 30, 2010: 10:26 pm
A couple of caveats: Have Fun |
 Bjoern |
May 3, 2008: 4:10 pm
Hi, great command! But what about this: Don’t forget to FLUSH TABLES after execution of any of the following - REPAIR TABLE, TRUNCATE TABLE, OPTIMIZE TABLE, or ANALYZE TABLE on tables that are mapped into MERGE table. And shouldn´t we also run a ’sort-index’ ? |
Shay Telfer