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/