Three ways to fix a table for MySQL

A power outage or abnormal shutdown can cause table corruption in the MySQL database, especially when the MyISAM table data is large. There are three ways, one way to use MySQL's Repair table SQL statement, the other is to use the myisamchk provided by MySQL, and the last is to mysqlcheck command-line tools.

1.repair Table (recommended method, useful for MyISAM engine table)
(1) Check table tabtest;
If the result says status is OK, then do not repair, if there is error, you can use:

(2) Repair table tabtest;
Fixed and can be checked using the Check Table command after the repair. You can also use the Check/repair feature in the new version of phpMyAdmin.


2. Myisamchk (the tool must be running under service termination conditions, useful for MyISAM engine tables)。
(1) Myisamchk tablename. MYI
For testing.

(2) myisamchk-of tablename. MYI
Other ways to speak online:
Then the method for repairing the test table is
Myisamchk-r-q/var/lib/mysql/db/test. MYI
If you fix all the tables, use this command
Myisamchk-r-q/var/lib/mysql/db/*. MYI


3. Run the Mysqlcheck command-line tool (the tool can be executed while the service is running)

Turn from: 63275154

To check a specific table:
If the application indicates that a table is broken, use the following command to check.
$ mysqlcheck-c Newmandela order-uroot-p
Enter Password:
Newmandela.order OK
Newmandela is a library name, order is a table name, and you need to enter a user name and password

Check all the 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 the libraries and tables have been checked again.
$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 want to check only a few 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

Using the Mysqlcheck Analysis 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 parse the Payment_transactionrecord table of the RADIUS library, and-a represents analyze
Use Mysqlcheck to optimize tables:
# Mysqlcheck-o Radius payment_transactionrecord-uroot-p
Enter Password:
Radius.payment_transactionrecord OK

-O stands for optimize, which is the Payment_transactionrecord table for the optimized RADIUS library
To repair a table using Mysqlcheck:
# Mysqlcheck-r Radius payment_transactionrecord-uroot-p
Enter Password:
Radius.payment_transactionrecord OK

-R stands for repair, which is the Payment_transactionrecord table for repairing RADIUS libraries
Check, refine, fix table Combo command:
# mysqlcheck-uroot-p--auto-repair-c-O Newmandela
Error:mysqlcheck doesn ' t support multiple contradicting commands.

The above command 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

Each table appears with 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's answer.
Mysqlcheck Common Options

A,–all-databases represents all libraries
-a,–analyze Analysis Table
-o,–optimize Optimization Table
-r,–repair Fix Table Error
-c,–check checking the table for errors
–auto-repair automatic repair of damaged tables
-b,–databases Selecting multiple libraries
-1,–all-in-1 use one query per database with tables listed in a comma separated
-c,–check-only-changed Check table changes after the last check
-g,–check-upgrade Check for version dependent changes in the tables
-f,–fast Check tables that is not closed properly
–fix-db-names Fix db names
–fix-table-names Fix table names
-f,–force Continue Even when there was an error
-e,–extended Perform extended check on a table. This would take a long time to execute.
-m,–medium-check Faster than extended check option, but does most checks
-q,–quick Faster than medium check option

Three ways to fix a table for MySQL