Check, Repair, and Optimize MySQL Tables

AppDynamics for Databases uses an OEM version of MySQL Server "under the covers" in order to store historical data. Occasionally MySQL tables can become corrupted e.g. on a machine crash or similar event. You may see an error in the User Interface which looks like this:

java.sql.SQLException: Table '.dbtunasql_statements' is marked as crashed and should be repaired

Luckily the table(s) can be recovered easily using the MySQL Repair Table command to repair a corrupted MyISAM table.

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 uses the SQL statements CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, and OPTIMIZE TABLE in a quick and easy way. 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 a running database. It is recommended that you stop other incoming queries (like from your website) before running this which makes it a lot faster.