Power failure or abnormal shutdown may cause table corruption in the MySQL database, especially when the MyISAM table data is large. There are three methods, one method is to use the sql statement of MySQL’s repair table, the other method is to use the myisamchk provided by MySQL, and the last one is the mysqlcheck command line tool.

1.repair table (recommended method, useful for MyISAM engine table)

(1)check table tabTest;

If the result appears that the Status is OK, there is no need to fix it, if there is an Error, you can use:

(2)repair table tabTest;

Repair, after repairing, you can use the check table command to check. The check/repair function can also be used in the new version of phpMyAdmin.

2. myisamchk (this tool must be run under the condition of service termination, it is useful for MyISAM engine table).

(1)myisamchk tablename.MYI

Perform testing.

(2)myisamchk -of tablename.MYI

Other methods mentioned on the Internet:

Then the method to repair the test table is

myisamchk -r -q /var/lib/mysql/db/test.MYI

If you repair all tables, use this command

myisamchk -r -q /var/lib/mysql/db/*.MYI

3. Run the mysqlcheck command line tool (this tool can be executed under the running state of the service)

Check the specific table:

If the application prompts that a table is broken, use the following command to check.

$ mysqlcheck -c newmandela order -uroot -p

Enter password:

newmandela.order OK

newmandela is the library name, order is the table name, you also need to enter the user name and password

Check all tables in a library:

$ mysqlcheck -c newmandela -uroot -p

Enter password:

newmandela.account OK

newmandela.alarm OK

newmandela.alarm_settings OK

newmandela.auth_group OK

newmandela.auth_group_permissions OK

newmandela.auth_permission OK

Check all tables in all libraries:

All libraries and tables have been checked.

$mysqlcheck -c –all-databases -uroot -p

Enter password:

apmonitor.acinfo OK

apmonitor.apdailysts OK

apmonitor.apinfo OK

apmonitor.apmonthsts OK

apmonitor.apscanlog OK

apmonitor.auth_group OK

What if you only want to check certain libraries? You can use the –databases parameter:

$ mysqlcheck -c –databases newmandela radius -uroot -p

Enter password:

newmandela.account OK

newmandela.alarm OK

newmandela.alarm_settings OK

newmandela.auth_group OK

Use mysqlcheck to analyze the table:

$ mysqlcheck -a radius payment_transactionrecord -uroot -p

Enter password:

radius.payment_transactionrecord Table is already up to date

The above command is used to analyze the payment_transactionrecord table of the radius library, -a means analyze

Use mysqlcheck to optimize the table:

mysqlcheck -o radius payment_transactionrecord -uroot -p

Enter password:

radius.payment_transactionrecord OK

-o stands for optimize, here is the payment_transactionrecord table of the optimized radius library

Use mysqlcheck to repair the table:

mysqlcheck -r radius payment_transactionrecord -uroot -p

Enter password:

radius.payment_transactionrecord OK

-r stands for repair, here is the payment_transactionrecord table of the repair radius library

Check, optimize, and repair table combination commands:

mysqlcheck -uroot -p –auto-repair -c -o newmandela

Error: mysqlcheck doesn’t support multiple contradicting commands.

The above command reported an error, remove -c

mysqlcheck -uroot -p –auto-repair -o newmandela

Enter password:

newmandela.account

note : Table does not support optimize, doing recreate + analyze instead

status : OK

newmandela.alarm

note : Table does not support optimize, doing recreate + analyze instead

status : OK

newmandela.alarm_settings

note : Table does not support optimize, doing recreate + analyze instead

status : OK

Every table appears Table does not support optimize, doing recreate + analyze instead, what does it mean? It does not mean that the innodb engine does not support optimization, you can refer to http://stackoverflow.com/questions/30635603/what-does-table-does-not-support-optimize-doing-recreate-analyze-instead-me answer .

mysqlcheck common options

A, –all-databases means all libraries

-a, –analyze analysis table

-o, –optimize optimize table

-r, –repair repair table errors

-c, –check check whether the table is wrong

–Auto-repair automatically repair damaged tables

-B, –databases select multiple libraries

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

-C, –check-only-changed Check list changes after the last check

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

-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