Ever come across a situation, where you’d like to check all tables in a database and have them all repaired and optimized? My guess is yes.

In case you didn’t know, there is a helpful MySQL utility called mysqlcheck, available as of version 3.23.38. It does exactly what we need.

To check all tables in all databases for corruption and errors and also fix them in one go, this is your command:

mysqlcheck -u username -p password  –check –optimize –auto-repair –all-databases

mysqlcheck executes statements like CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, and OPTIMIZE TABLE and chooses the best statements for any given operation and storage engine.

Note that the operations complete a lot faster if you can afford to to disable any external services, especially if your database is large.