Troubleshooting

mysqlcheck

-c reports on corruption

Check a Specific Table in a Database

mysqlcheck -c db_name table_name

Check All Tables in a Database

mysqlcheck -c db_name

Check All Tables and All Databases

mysqlcheck -c --all-databases

Repair Tables

mysqlcheck -r db_name table_name

Optimize Tables

Internally mysqlcheck command uses “OPTIMIZE TABLE” command. When you delete lot of rows from a table, optimizing it helps to get the unused space and defragment the data file. This might improve performance on huge tables that has gone through several updates.

mysqlcheck -o db_name table_name

Combine Check, Optimize, and Repair Tables

mysqlcheck --auto-repair -c -o --all-databases
Huge ibdata1 file with mostly innodb engine type and innodb_file_per_table set to on

Potentially this can be caused by undo transactions

To check, run the following:

innochecksum /var/lib/mysql/ibdata1

More info here: https://www.percona.com/blog/2013/08/20/why-is-the-ibdata1-file-continuously-growing-in-mysql/