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
-o
On 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
-r
On 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-databases
Represents all libraries
-a, –analyze
Analysis table
-o, –optimize
Optimizing tables
-r, –repair
Fix table Error
-c, –check
Check table for errors
–auto-repair
Automatic repair of corrupted tables
-B, –databases
Select multiple libraries
-1, –all-in-1
Use one query per database with tables listed in a comma separated
-C, –check-only-changed
Check the changes after the last check of the table
-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
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