How to Check and Repair MySQL Tables Using Mysqlcheck

Mysqlcheck command can be used to check, repair, optimize and analysed table in MySQL when table gets corrupted.

Check a Specific Table in a Database

# mysqlcheck -c <Database Name> <Table Name> -u <User Name> -p

Example:

# mysqlcheck -c world city -u root -p

Enter password:

world.city                                     OK

Check All Tables in a Database

# mysqlcheck -c <Database Name>  -u <User Name> -p

Example:

# mysqlcheck -c world -u root -p

Enter password:

world.city                                     OK

world.country                              OK

world.countrylanguage              OK

Check All Tables and All Databases

# mysqlcheck -c  -u root -p --all-databases

If you want to check all tables of few databases, specify the database names using “--databases”.

# mysqlcheck -c  -u root -p --databases <Database Name> <Database Name>

Analyze Tables using Mysqlcheck

# mysqlcheck -a <Database Name> <Table Name> -u <User Name> -p

Example:

# mysqlcheck -a world city -u root -p

Enter password:

world.city                                     OK

Optimize Tables using Mysqlcheck

# mysqlcheck -o <Database Name> <Table Name> -u <User Name> -p

Example:

# mysqlcheck -o mysql columns_priv -u root -p

Repair Tables using Mysqlcheck

# mysqlcheck -r <Database Name> <Table Name> -u <User Name> -p

Example:

# mysqlcheck -r world city -u root -p

Combine Check, Optimize, and Repair Tables

The following checks, optimizes and repairs all the corrupted table in a database.

# mysqlcheck -u root -p --auto-repair -c -o <Database Name>

You can also check, optimize and repair all the tables across all your databases using the following command.

# mysqlcheck -u root -p --auto-repair -c -o --all-databases

If you want to know what the command is doing while it is checking, add the –debug-info as shown below. This is helpful while you are checking a huge table.

# mysqlcheck --debug-info -u root -p --auto-repair -c -o <Database Name> <Table Name>

Additional Useful Mysqlcheck Options

The following are some of the key options that you can use along with mysqlcheck.

§  -A, –all-databases Consider all the databases

§  -a, –analyze Analyze tables

§  -1, –all-in-1 Use one query per database with tables listed in a comma separated way

§  –auto-repair Repair the table automatically it if is corrupted

§  -c, –check Check table errors

§  -C, –check-only-changed Check tables that are changed since last check

§  -g, –check-upgrade Check for version dependent changes in the tables

§  -B, –databases Check more than one databases

§  -F, –fast Check tables that are not closed properly

§  –fix-db-names Fix DB names

§  –fix-table-names Fix table names

§  -f, –force Continue even when there is an error

§  -e, –extended Perform extended check on a table. This will take a long time to execute.

§  -m, –medium-check Faster than extended check option, but does most checks

§  -o, –optimize Optimize tables

§  -q, –quick Faster than medium check option

§  -r, –repair Fix the table corruption