How to use Mysqlcheck to check and repair, detailed explanation of optimizing tables

mysqlcheck is a tool that comes with MySQL. Its function is to maintain the table. In fact, it is to check, analyze, repair and optimize. The following is an introduction to the simple use of the mysqlcheck tool. The official document is here

The following examples are based on the running state of MySQL 5.6 version (mysqlcheck is an online tool). Different storage engines support this command. The degree is different (referring to check, repair, analyze, optimize). The following content is more operational, mainly based on the innodb engine.

Tip: OPTIMIZE may consume a lot of time when working with large tables. Please use it with caution if you don’t know the principle!!! OPTIMIZE is generally not used in innodb, please refer to Using MySQL OPTIMIZE tables? For InnoDB? Stop

Check specific tables

Be careful to execute in the shell, not in the interactive environment of mysql

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

$ mysqlcheck -c newmandela order -uroot -pEnter password:
newmandela.order                                   
OK

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

Check all tables in one 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 -pEnter password:
radius.payment_transactionrecord                   Table is already up to date

The above command is used to analyze the radius librarypayment_transactionrecordTable, -a represents analyze

Use mysqlcheck to optimize the table

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

-o represents optimize, here is the optimization radius library The payment_transactionrecord table

Use mysqlcheck to repair the table

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

-r represents repair, here is the payment_transactionrecord that repairs the radius library Table

Check, optimize, and repair table combination commands

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

The above command reported an error, remove it-c

# mysqlcheck -uroot -p --auto-repair -o newmandelaEnter 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 appearsTable does not support optimize, doing recreate + analyze instead, what does it mean? It does not mean that the innodb engine does not support optimization.

Mysqlcheck common options

• A, –all-databases means all libraries

• -a, –analyze Analysis table

• ##-o, –optimize Optimize table

• -r, –repair Repair the table error

• -c, –check Check the table for errors

• –auto-repair Automatically repair damaged tables

• -B, –databases Select multiple databases

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

• -C, –check-only-changed Check the changes since the last check of the table

• -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

• -q, –quick

  Faster than medium check option

The above is the detailed content of How to use Mysqlcheck to check and repair, detailed explanation of optimizing tables. For more information, please follow other related articles on the PHP Chinese website!