Repair mysql Tables using Mysqlcheck

This happened when my cacti crashed after a power failure.

 

Repair a table inside database

This will repair host table in cacti databse

# mysqlcheck -r cacti host -u root -p

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

# mysqlcheck -u root -p --auto-repair -c -o cacti

Check All Tables and All Databases

To check all the tables and all the databases use the “–all-databases” along with -c option as shown below.

# mysqlcheck -c  -u root -p --all-databases
Enter password:

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