Check Database and Optimize Everything (-A = all databases)

Luke Hebert

- 2 minutes read - 263 words

Running a single command to verify table integrity and reclaim wasted space across every database on a MySQL server saves a lot of manual work. The mysqlcheck utility handles both checking and optimizing in one pass, and the -A flag lets you skip naming individual databases entirely.

Basic Syntax

mysqlcheck --optimize -u username -ppassword <database>

This targets a single database. Replace username, password, and database with your actual values.

Optimizing All Databases

To run the check and optimization across every database on the server, swap the database name for the -A flag:

mysqlcheck --optimize -A -u username -ppassword

The -A (or --all-databases) flag tells mysqlcheck to iterate over all databases it can access with the supplied credentials.

What It Does

  • CHECK - Runs CHECK TABLE on each table to detect corruption.
  • OPTIMIZE - Runs OPTIMIZE TABLE to reclaim unused space and defragment data files.
  • ANALYZE - Updates index statistics so the query planner has accurate cardinality data.

You can run any of these individually by replacing --optimize with --check or --analyze.

Tips

  • Run this during a low-traffic window. OPTIMIZE TABLE can lock tables briefly on MyISAM engines.
  • If you only need to verify integrity without modifying data, use --check instead of --optimize.
  • The -p flag must be immediately followed by the password with no space. For interactive prompts, use just -p and type the password when prompted.

Verifying Results

After the run, mysqlcheck prints a per-table status line. Look for OK next to each table name. Any ERROR or Corrupt entries need follow-up with REPAIR TABLE or a restore from backup.