1MYSQLCHECK(1) MySQL Database System MYSQLCHECK(1)
2
6 mysqlcheck - a table maintenance program
7
9 mysqlcheck [options] [db_name [tbl_name ...]]
10
12 The mysqlcheck client performs table maintenance: It checks, repairs,
13 optimizes, or analyzes tables.
14 Each table is locked and therefore unavailable to other sessions while
16 it is being processed, although for check operations, the table is
17 locked with a READ lock only (see Section 13.3.6, “LOCK TABLES and
18 UNLOCK TABLES Syntax”, for more information about READ and WRITE
19 locks). Table maintenance operations can be time-consuming,
20 particularly for large tables. If you use the --databases or
21 --all-databases option to process all tables in one or more databases,
22 an invocation of mysqlcheck might take a long time. (This is also true
23 for the MySQL upgrade procedure if it determines that table checking is
24 needed because it processes tables the same way.)
25 mysqlcheck must be used when the mysqld server is running, which means
27 that you do not have to stop the server to perform table maintenance.
28 mysqlcheck uses the SQL statements CHECK TABLE, REPAIR TABLE, ANALYZE
30 TABLE, and OPTIMIZE TABLE in a convenient way for the user. It
31 determines which statements to use for the operation you want to
32 perform, and then sends the statements to the server to be executed.
33 For details about which storage engines each statement works with, see
34 the descriptions for those statements in Section 13.7.3, “Table
35 Maintenance Statements”.
36 All storage engines do not necessarily support all four maintenance
38 operations. In such cases, an error message is displayed. For example,
39 if test.t is an MEMORY table, an attempt to check it produces this
40 result:
41 shell> mysqlcheck test t
43 test.t
44 note : The storage engine for the table doesn't support check
45 If mysqlcheck is unable to repair a table, see Section 2.11.13,
47 “Rebuilding or Repairing Tables or Indexes” for manual table repair
48 strategies. This will be the case, for example, for InnoDB tables,
49 which can be checked with CHECK TABLE, but not repaired with REPAIR
50 TABLE.
51 Caution
53 It is best to make a backup of a table before performing a table
54 repair operation; under some circumstances the operation might
55 cause data loss. Possible causes include but are not limited to
56 file system errors.
57 There are three general ways to invoke mysqlcheck:
59 shell> mysqlcheck [options] db_name [tbl_name ...]
61 shell> mysqlcheck [options] --databases db_name ...
62 shell> mysqlcheck [options] --all-databases
63 If you do not name any tables following db_name or if you use the
65 --databases or --all-databases option, entire databases are checked.
66 mysqlcheck has a special feature compared to other client programs. The
68 default behavior of checking tables (--check) can be changed by
69 renaming the binary. If you want to have a tool that repairs tables by
70 default, you should just make a copy of mysqlcheck named mysqlrepair,
71 or make a symbolic link to mysqlcheck named mysqlrepair. If you invoke
72 mysqlrepair, it repairs tables.
73 The names shown in the following table can be used to change mysqlcheck
75 default behavior.
76 ┌──────────────┬───────────────────────┐
78 │Command │ Meaning │
79 ├──────────────┼───────────────────────┤
80 │mysqlrepair │ The default option is │
81 │ │ --repair │
82 ├──────────────┼───────────────────────┤
83 │mysqlanalyze │ The default option is │
84 │ │ --analyze │
85 ├──────────────┼───────────────────────┤
86 │mysqloptimize │ The default option is │
87 │ │ --optimize │
88 └──────────────┴───────────────────────┘
89 mysqlcheck supports the following options, which can be specified on
91 the command line or in the [mysqlcheck] and [client] groups of an
92 option file. For information about option files used by MySQL programs,
93 see Section 4.2.2.2, “Using Option Files”.
94 · --help, -?
96 Display a help message and exit.
98 · --all-databases, -A
100 Check all tables in all databases. This is the same as using the
102 --databases option and naming all the databases on the command
103 line, except that the INFORMATION_SCHEMA and performance_schema
104 databases are not checked. They can be checked by explicitly naming
105 them with the --databases option.
106 · --all-in-1, -1
108 Instead of issuing a statement for each table, execute a single
110 statement for each database that names all the tables from that
111 database to be processed.
112 · --analyze, -a
114 Analyze the tables.
116 · --auto-repair
118 If a checked table is corrupted, automatically fix it. Any
120 necessary repairs are done after all tables have been checked.
121 · --bind-address=ip_address
123 On a computer having multiple network interfaces, use this option
125 to select which interface to use for connecting to the MySQL
126 server.
127 · --character-sets-dir=dir_name
129 The directory where character sets are installed. See
131 Section 10.15, “Character Set Configuration”.
132 · --check, -c
134 Check the tables for errors. This is the default operation.
136 · --check-only-changed, -C
138 Check only tables that have changed since the last check or that
140 have not been closed properly.
141 · --check-upgrade, -g
143 Invoke CHECK TABLE with the FOR UPGRADE option to check tables for
145 incompatibilities with the current version of the server.
146 · --compress
148 Compress all information sent between the client and the server if
150 possible. See Section 4.2.6, “Connection Compression Control”.
151 As of MySQL 8.0.18, this option is deprecated. It will be removed
153 in a future MySQL version. See the section called “Legacy
154 Connection Compression Configuration”.
155 · --compression-algorithms=value The permitted compression algorithms
157 for connections to the server. The available algorithms are the
158 same as for the protocol_compression_algorithms system variable.
159 The default value is uncompressed.
160 For more information, see Section 4.2.6, “Connection Compression
162 Control”.
163 This option was added in MySQL 8.0.18.
165 · --databases, -B
167 Process all tables in the named databases. Normally, mysqlcheck
169 treats the first name argument on the command line as a database
170 name and any following names as table names. With this option, it
171 treats all name arguments as database names.
172 · --debug[=debug_options], -# [debug_options]
174 Write a debugging log. A typical debug_options string is
176 d:t:o,file_name. The default is d:t:o.
177 · --debug-check
179 Print some debugging information when the program exits.
181 · --debug-info
183 Print debugging information and memory and CPU usage statistics
185 when the program exits.
186 · --default-character-set=charset_name
188 Use charset_name as the default character set. See Section 10.15,
190 “Character Set Configuration”.
191 · --defaults-extra-file=file_name
193 Read this option file after the global option file but (on Unix)
195 before the user option file. If the file does not exist or is
196 otherwise inaccessible, an error occurs. file_name is interpreted
197 relative to the current directory if given as a relative path name
198 rather than a full path name.
199 For additional information about this and other option-file
201 options, see Section 4.2.2.3, “Command-Line Options that Affect
202 Option-File Handling”.
203 · --defaults-file=file_name
205 Use only the given option file. If the file does not exist or is
207 otherwise inaccessible, an error occurs. file_name is interpreted
208 relative to the current directory if given as a relative path name
209 rather than a full path name.
210 Exception: Even with --defaults-file, client programs read
212 .mylogin.cnf.
213 For additional information about this and other option-file
215 options, see Section 4.2.2.3, “Command-Line Options that Affect
216 Option-File Handling”.
217 · --defaults-group-suffix=str
219 Read not only the usual option groups, but also groups with the
221 usual names and a suffix of str. For example, mysqlcheck normally
222 reads the [client] and [mysqlcheck] groups. If the
223 --defaults-group-suffix=_other option is given, mysqlcheck also
224 reads the [client_other] and [mysqlcheck_other] groups.
225 For additional information about this and other option-file
227 options, see Section 4.2.2.3, “Command-Line Options that Affect
228 Option-File Handling”.
229 · --extended, -e
231 If you are using this option to check tables, it ensures that they
233 are 100% consistent but takes a long time.
234 If you are using this option to repair tables, it runs an extended
236 repair that may not only take a long time to execute, but may
237 produce a lot of garbage rows also!
238 · --default-auth=plugin
240 A hint about which client-side authentication plugin to use. See
242 Section 6.2.17, “Pluggable Authentication”.
243 · --enable-cleartext-plugin
245 Enable the mysql_clear_password cleartext authentication plugin.
247 (See Section 6.4.1.4, “Client-Side Cleartext Pluggable
248 Authentication”.)
249 · --fast, -F
251 Check only tables that have not been closed properly.
253 · --force, -f
255 Continue even if an SQL error occurs.
257 · --get-server-public-key
259 Request from the server the public key required for RSA key
261 pair-based password exchange. This option applies to clients that
262 authenticate with the caching_sha2_password authentication plugin.
263 For that plugin, the server does not send the public key unless
264 requested. This option is ignored for accounts that do not
265 authenticate with that plugin. It is also ignored if RSA-based
266 password exchange is not used, as is the case when the client
267 connects to the server using a secure connection.
268 If --server-public-key-path=file_name is given and specifies a
270 valid public key file, it takes precedence over
271 --get-server-public-key.
272 For information about the caching_sha2_password plugin, see
274 Section 6.4.1.3, “Caching SHA-2 Pluggable Authentication”.
275 · --host=host_name, -h host_name
277 Connect to the MySQL server on the given host.
279 · --login-path=name
281 Read options from the named login path in the .mylogin.cnf login
283 path file. A “login path” is an option group containing options
284 that specify which MySQL server to connect to and which account to
285 authenticate as. To create or modify a login path file, use the
286 mysql_config_editor utility. See mysql_config_editor(1).
287 For additional information about this and other option-file
289 options, see Section 4.2.2.3, “Command-Line Options that Affect
290 Option-File Handling”.
291 · --medium-check, -m
293 Do a check that is faster than an --extended operation. This finds
295 only 99.99% of all errors, which should be good enough in most
296 cases.
297 · --no-defaults
299 Do not read any option files. If program startup fails due to
301 reading unknown options from an option file, --no-defaults can be
302 used to prevent them from being read.
303 The exception is that the .mylogin.cnf file, if it exists, is read
305 in all cases. This permits passwords to be specified in a safer way
306 than on the command line even when --no-defaults is used.
307 (.mylogin.cnf is created by the mysql_config_editor utility. See
308 mysql_config_editor(1).)
309 For additional information about this and other option-file
311 options, see Section 4.2.2.3, “Command-Line Options that Affect
312 Option-File Handling”.
313 · --optimize, -o
315 Optimize the tables.
317 · --password[=password], -p[password]
319 The password of the MySQL account used for connecting to the
321 server. The password value is optional. If not given, mysqlcheck
322 prompts for one. If given, there must be no space between
323 --password= or -p and the password following it. If no password
324 option is specified, the default is to send no password.
325 Specifying a password on the command line should be considered
327 insecure. To avoid giving the password on the command line, use an
328 option file. See Section 6.1.2.1, “End-User Guidelines for Password
329 Security”.
330 To explicitly specify that there is no password and that mysqlcheck
332 should not prompt for one, use the --skip-password option.
333 · --pipe, -W
335 On Windows, connect to the server using a named pipe. This option
337 applies only if the server was started with the named_pipe system
338 variable enabled to support named-pipe connections. In addition,
339 the user making the connection must be a member of the Windows
340 group specified by the named_pipe_full_access_group system
341 variable.
342 · --plugin-dir=dir_name
344 The directory in which to look for plugins. Specify this option if
346 the --default-auth option is used to specify an authentication
347 plugin but mysqlcheck does not find it. See Section 6.2.17,
348 “Pluggable Authentication”.
349 · --port=port_num, -P port_num
351 For TCP/IP connections, the port number to use.
353 · --print-defaults
355 Print the program name and all options that it gets from option
357 files.
358 For additional information about this and other option-file
360 options, see Section 4.2.2.3, “Command-Line Options that Affect
361 Option-File Handling”.
362 · --protocol={TCP|SOCKET|PIPE|MEMORY}
364 The connection protocol to use for connecting to the server. It is
366 useful when the other connection parameters normally result in use
367 of a protocol other than the one you want. For details on the
368 permissible values, see Section 4.2.4, “Connecting to the MySQL
369 Server Using Command Options”.
370 · --quick, -q
372 If you are using this option to check tables, it prevents the check
374 from scanning the rows to check for incorrect links. This is the
375 fastest check method.
376 If you are using this option to repair tables, it tries to repair
378 only the index tree. This is the fastest repair method.
379 · --repair, -r
381 Perform a repair that can fix almost anything except unique keys
383 that are not unique.
384 · --secure-auth
386 This option was removed in MySQL 8.0.3.
388 · --server-public-key-path=file_name
390 The path name to a file containing a client-side copy of the public
392 key required by the server for RSA key pair-based password
393 exchange. The file must be in PEM format. This option applies to
394 clients that authenticate with the sha256_password or
395 caching_sha2_password authentication plugin. This option is ignored
396 for accounts that do not authenticate with one of those plugins. It
397 is also ignored if RSA-based password exchange is not used, as is
398 the case when the client connects to the server using a secure
399 connection.
400 If --server-public-key-path=file_name is given and specifies a
402 valid public key file, it takes precedence over
403 --get-server-public-key.
404 For sha256_password, this option applies only if MySQL was built
406 using OpenSSL.
407 For information about the sha256_password and caching_sha2_password
409 plugins, see Section 6.4.1.2, “SHA-256 Pluggable Authentication”,
410 and Section 6.4.1.3, “Caching SHA-2 Pluggable Authentication”.
411 · --shared-memory-base-name=name
413 On Windows, the shared-memory name to use for connections made
415 using shared memory to a local server. The default value is MYSQL.
416 The shared-memory name is case-sensitive.
417 This option applies only if the server was started with the
419 shared_memory system variable enabled to support shared-memory
420 connections.
421 · --silent, -s
423 Silent mode. Print only error messages.
425 · --skip-database=db_name
427 Do not include the named database (case-sensitive) in the
429 operations performed by mysqlcheck.
430 · --socket=path, -S path
432 For connections to localhost, the Unix socket file to use, or, on
434 Windows, the name of the named pipe to use.
435 On Windows, this option applies only if the server was started with
437 the named_pipe system variable enabled to support named-pipe
438 connections. In addition, the user making the connection must be a
439 member of the Windows group specified by the
440 named_pipe_full_access_group system variable.
441 · --ssl*
443 Options that begin with --ssl specify whether to connect to the
445 server using SSL and indicate where to find SSL keys and
446 certificates. See the section called “Command Options for Encrypted
447 Connections”.
448 · --ssl-fips-mode={OFF|ON|STRICT} Controls whether to enable FIPS
450 mode on the client side. The --ssl-fips-mode option differs from
451 other --ssl-xxx options in that it is not used to establish
452 encrypted connections, but rather to affect which cryptographic
453 operations are permitted. See Section 6.5, “FIPS Support”.
454 These --ssl-fips-mode values are permitted:
456 · OFF: Disable FIPS mode.
458 · ON: Enable FIPS mode.
460 · STRICT: Enable “strict” FIPS mode.
462 Note
465 If the OpenSSL FIPS Object Module is not available, the only
466 permitted value for --ssl-fips-mode is OFF. In this case,
467 setting --ssl-fips-mode to ON or STRICT causes the client to
468 produce a warning at startup and to operate in non-FIPS mode.
469 · --tables
471 Override the --databases or -B option. All name arguments following
473 the option are regarded as table names.
474 · --tls-ciphersuites=ciphersuite_list
476 The permissible ciphersuites for encrypted connections that use
478 TLSv1.3. The value is a list of one or more colon-separated
479 ciphersuite names. The ciphersuites that can be named for this
480 option depend on the SSL library used to compile MySQL. For
481 details, see Section 6.3.2, “Encrypted Connection TLS Protocols and
482 Ciphers”.
483 This option was added in MySQL 8.0.16.
485 · --tls-version=protocol_list
487 The permissible TLS protocols for encrypted connections. The value
489 is a list of one or more comma-separated protocol names. The
490 protocols that can be named for this option depend on the SSL
491 library used to compile MySQL. For details, see Section 6.3.2,
492 “Encrypted Connection TLS Protocols and Ciphers”.
493 · --use-frm
495 For repair operations on MyISAM tables, get the table structure
497 from the data dictionary so that the table can be repaired even if
498 the .MYI header is corrupted.
499 · --user=user_name, -u user_name
501 The user name of the MySQL account to use for connecting to the
503 server.
504 · --verbose, -v
506 Verbose mode. Print information about the various stages of program
508 operation.
509 · --version, -V
511 Display version information and exit.
513 · --write-binlog
515 This option is enabled by default, so that ANALYZE TABLE, OPTIMIZE
517 TABLE, and REPAIR TABLE statements generated by mysqlcheck are
518 written to the binary log. Use --skip-write-binlog to cause
519 NO_WRITE_TO_BINLOG to be added to the statements so that they are
520 not logged. Use the --skip-write-binlog when these statements
521 should not be sent to replication slaves or run when using the
522 binary logs for recovery from backup.
523 · --zstd-compression-level=level The compression level to use for
525 connections to the server that use the zstd compression algorithm.
526 The permitted levels are from 1 to 22, with larger values
527 indicating increasing levels of compression. The default zstd
528 compression level is 3. The compression level setting has no effect
529 on connections that do not use zstd compression.
530 For more information, see Section 4.2.6, “Connection Compression
532 Control”.
533 This option was added in MySQL 8.0.18.
535
537 Copyright © 1997, 2019, Oracle and/or its affiliates. All rights
538 reserved.
539 This documentation is free software; you can redistribute it and/or
541 modify it only under the terms of the GNU General Public License as
542 published by the Free Software Foundation; version 2 of the License.
543 This documentation is distributed in the hope that it will be useful,
545 but WITHOUT ANY WARRANTY; without even the implied warranty of
546 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
547 General Public License for more details.
548 You should have received a copy of the GNU General Public License along
550 with the program; if not, write to the Free Software Foundation, Inc.,
551 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see
552 http://www.gnu.org/licenses/.
553
556 For more information, please refer to the MySQL Reference Manual, which
557 may already be installed locally and which is also available online at
558 http://dev.mysql.com/doc/.
559
561 Oracle Corporation (http://dev.mysql.com/).
562MySQL 8.0 09/06/2019 MYSQLCHECK(1)