[MySQL] How to use Mysqlcheck to check and fix, optimize the table

Mysqlcheck is a tool that MySQL comes with, the function is the 保养 table, actually is examines, analyzes, fixes and optimizes. Here's an introduction to the simple use of the Mysqlcheck tool, the official documentation here

Original URL: http://blog.csdn.net/orangleliu/article/details/63275154

The following examples are based on the MySQL 5.6 version of the running state (Mysqlcheck is an online tool), different storage engine for this command support is different (refers to the check, repair, analyze, optimize), the following is biased to the operation, Mainly based on the InnoDB engine.

Tip: OPTIMIZE may consume a lot of time in large tables, and it is not clear that the principle should be used with caution!!! InnoDB generally do not use OPTIMIZE, see Using MySQL OPTIMIZE tables? For InnoDB? Stop

Check for specific tables

Note that executing in the shell is not in MySQL's interactive environment

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

-c newmandela order -uroot -pEnter 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 tables in a library

$ mysqlcheck -c newmandela -uroot -pEnter password:newmandela.account                                 OKnewmandela.alarm                                   OKnewmandela.alarm_settings                          OKnewmandela.auth_group                              OKnewmandela.auth_group_permissions                  OKnewmandela.auth_permission                         OK...

Check all tables in all libraries

All the libraries and tables have been checked again.

$mysqlcheck -c --all-databases -uroot -pEnter password:apmonitor.acinfo                                   OKapmonitor.apdailysts                               OKapmonitor.apinfo                                   OKapmonitor.apmonthsts                               OKapmonitor.apscanlog                                OKapmonitor.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 -pEnter password:newmandela.account                                 OKnewmandela.alarm                                   OKnewmandela.alarm_settings                          OKnewmandela.auth_group                              OK...

Using the Mysqlcheck Analysis table

-a radius payment_transactionrecord  -uroot -pEnter password:radius.payment_transactionrecord                   Table is already up to date

The above command is used to parse the table of the RADIUS library payment_transactionrecord , -a representing analyze

Using Mysqlcheck to optimize tables

# mysqlcheck -o radius payment_transactionrecord  -uroot -pEnter password:radius.payment_transactionrecord                   OK

-oOn behalf of optimize, this is the table that optimizes the RADIUS library payment_transactionrecord

Repairing a table with Mysqlcheck

# mysqlcheck -r radius payment_transactionrecord  -uroot -pEnter password:radius.payment_transactionrecord                   OK

-rOn behalf of repair, this is the table that fixes the radius library payment_transactionrecord

Check, refine, fix table combo commands

# mysqlcheck -uroot -p --auto-repair -c -o newmandelaError:  mysqlcheck doesn‘t support multiple contradicting commands.

The above command gave an error and removed-c

# mysqlcheck -uroot -p --auto-repair -o newmandelaEnter password:newmandela.accountnote     : Table does not support optimize, doing recreate + analyze insteadstatus   : OKnewmandela.alarmnote     : Table does not support optimize, doing recreate + analyze insteadstatus : OKnewmandela.alarm_settingsnote : Table does not support optimize, doing recreate + analyze insteadstatus : OK

Each 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's answer.

Mysqlcheck Common Options

• A, –all-databasesRepresents all libraries
• -a, –analyzeAnalysis table
• -o, –optimizeOptimizing tables
• -r, –repairFix table Error
• -c, –checkCheck table for errors
• –auto-repairAutomatic repair of corrupted tables
• -B, –databasesSelect multiple libraries
• -1, –all-in-1Use one query per database with tables listed in a comma separated
• -C, –check-only-changedCheck the changes after the last check of the table
• -g, –check-upgradeCheck for version dependent changes in the tables
• -F, –fastCheck tables that is not closed properly
• –fix-db-namesFix DB Names
• –fix-table-namesFix table names
• -f, –forceContinue even when there was an error
• -e, –extendedPerform extended check on a table. This would take a long time to execute.
• -m, –medium-checkFaster than extended check option, but does most checks
• -q, –quickFaster than medium check option

Copyright NOTICE: This article is Orangleliu (http://blog.csdn.net/orangleliu/) original article, free dissemination, the article reproduced please declare, thank you.

[MySQL] How to use Mysqlcheck to check and fix, optimize the table