4.8.5 Using mysqlcheck for Table Maintenance and Crash Recovery

Since MySQL version 3.23.38 you will be able to use a new checking and repairing tool for MyISAM tables. The difference to myisamchk is that mysqlcheck should be used when the mysqld server is running, whereas myisamchk should be used when it is not. The benefit is that you no longer have to take the server down for checking or repairing your tables.

mysqlcheck uses MySQL server commands CHECK, REPAIR, ANALYZE and OPTIMIZE in a convenient way for the user.

There are three alternative ways to invoke mysqlcheck:

shell> mysqlcheck [OPTIONS] database [tables]
shell> mysqlcheck [OPTIONS] --databases DB1 [DB2 DB3...]
shell> mysqlcheck [OPTIONS] --all-databases

So it can be used in a similar way as mysqldump when it comes to what databases and tables you want to choose.

mysqlcheck does have a special feature compared to the other clients; the default behaviour, checking tables (-c), can be changed by renaming the binary. So if you want to have a tool that repairs tables by default, you should just copy mysqlcheck to your harddrive with a new name, mysqlrepair, or alternatively make a symbolic link to mysqlrepair and name the symbolic link as mysqlrepair. If you invoke mysqlrepair now, it will repair tables by default.

The names that you can use to change mysqlcheck default behaviour are here:

mysqlrepair:   The default option will be -r
mysqlanalyze:  The default option will be -a
mysqloptimize: The default option will be -o

The options available for mysqlcheck are listed here, please check what your version supports with mysqlcheck --help.

-A, --all-databases

Check all the databases. This will be same as --databases with all databases selected

-1, --all-in-1

Instead of making one query for each table, execute all queries in 1 query separately for each database. Table names will be in a comma separated list.

-a, --analyze

Analyse given tables.

--auto-repair

If a checked table is corrupted, automatically fix it. Repairing will be done after all tables have been checked, if corrupted ones were found.

-#, --debug=...

Output debug log. Often this is 'd:t:o,filename'

--character-sets-dir=...

Directory where character sets are

-c, --check

Check table for errors

-C, --check-only-changed

Check only tables that have changed since last check or haven't been closed properly.

--compress

Use compression in server/client protocol.

-?, --help

Display this help message and exit.

-B, --databases

To check several databases. Note the difference in usage; in this case no tables are given. All name arguments are regarded as database names.

--default-character-set=...

Set the default character set

-F, --fast

Check only tables that hasn't been closed properly

-f, --force

Continue even if we get an sql-error.

-e, --extended

If you are using this option with CHECK TABLE, it will ensure that the table is 100 percent consistent, but will take a long time. If you are using this option with REPAIR TABLE, it will run an extended repair on the table, which may not only take a long time to execute, but may produce a lot of garbage rows also!

-h, --host=...

Connect to host.

-m, --medium-check

Faster than extended-check, but only finds 99.99 percent of all errors. Should be good enough for most cases.

-o, --optimize

Optimise table

-p, --password[=...]

Password to use when connecting to server. If password is not given it's solicited on the tty.

-P, --port=...

Port number to use for TCP/IP connections.

--protocol=(TCP | SOCKET | PIPE | MEMORY)

To specify the connect protocol to use. New in MySQL 4.1.

-q, --quick

If you are using this option with CHECK TABLE, it prevents the check from scanning the rows to check for wrong links. This is the fastest check. If you are using this option with REPAIR TABLE, it will try to repair only the index tree. This is the fastest repair method for a table.

-r, --repair

Can fix almost anything except unique keys that aren't unique.

-s, --silent

Print only error messages.

-S, --socket=...

Socket file to use for connection.

--tables

Overrides option --databases (-B).

-u, --user=#

User for login if not current user.

-v, --verbose

Print information about the various stages.

-V, --version

Output version information and exit.

User Comments

Add your own comment.