MySQL – How To Analyze, Repair and Optimize all Tables
Whether you’re a DBA or just host your own blog, database maintenance is a task you’ll have to perform sooner or later. Either the website gets slow, data becomes corrupt or worse, the server crashes, having a few commands handy will be golden. So how do we perform a mysql repair and optimize all databases?
Many MySQL GUI clients allow for analyze, repair and optimize functions, but they usually are limited to one table at a time, or to a maximum of on database with all its tables. If you need a more selective solution, for example if you have a few InnoDB tables amongst a majority of MyISAM ones, and want only the InnoDB tables analyzed, you need to get a hold of mysqlcheck, a command line utility that ships with the MySQL database server.
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. You can also specify the database names if you want just a few of them checked, or specify table names to gain even more granularity.
Note that the operations complete a lot faster if you can afford to to disable any external services, especially if your database is large. And keep in mind the impact of performance before running these in a production environment. It may be more prudent to split up these operations if the impact is too risky, or even take a database offline just for this task.
