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.7, “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.14, “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 both support compression.
151 · --databases, -B
153 Process all tables in the named databases. Normally, mysqlcheck
155 treats the first name argument on the command line as a database
156 name and any following names as table names. With this option, it
157 treats all name arguments as database names.
158 · --debug[=debug_options], -# [debug_options]
160 Write a debugging log. A typical debug_options string is
162 d:t:o,file_name. The default is d:t:o.
163 · --debug-check
165 Print some debugging information when the program exits.
167 · --debug-info
169 Print debugging information and memory and CPU usage statistics
171 when the program exits.
172 · --default-character-set=charset_name
174 Use charset_name as the default character set. See Section 10.14,
176 “Character Set Configuration”.
177 · --defaults-extra-file=file_name
179 Read this option file after the global option file but (on Unix)
181 before the user option file. If the file does not exist or is
182 otherwise inaccessible, an error occurs. file_name is interpreted
183 relative to the current directory if given as a relative path name
184 rather than a full path name.
185 For additional information about this and other option-file
187 options, see Section 4.2.8, “Command-Line Options that Affect
188 Option-File Handling”.
189 · --defaults-file=file_name
191 Use only the given option file. If the file does not exist or is
193 otherwise inaccessible, an error occurs. file_name is interpreted
194 relative to the current directory if given as a relative path name
195 rather than a full path name.
196 Exception: Even with --defaults-file, client programs read
198 .mylogin.cnf.
199 For additional information about this and other option-file
201 options, see Section 4.2.8, “Command-Line Options that Affect
202 Option-File Handling”.
203 · --defaults-group-suffix=str
205 Read not only the usual option groups, but also groups with the
207 usual names and a suffix of str. For example, mysqlcheck normally
208 reads the [client] and [mysqlcheck] groups. If the
209 --defaults-group-suffix=_other option is given, mysqlcheck also
210 reads the [client_other] and [mysqlcheck_other] groups.
211 For additional information about this and other option-file
213 options, see Section 4.2.8, “Command-Line Options that Affect
214 Option-File Handling”.
215 · --extended, -e
217 If you are using this option to check tables, it ensures that they
219 are 100% consistent but takes a long time.
220 If you are using this option to repair tables, it runs an extended
222 repair that may not only take a long time to execute, but may
223 produce a lot of garbage rows also!
224 · --default-auth=plugin
226 A hint about the client-side authentication plugin to use. See
228 Section 6.3.10, “Pluggable Authentication”.
229 · --enable-cleartext-plugin
231 Enable the mysql_clear_password cleartext authentication plugin.
233 (See Section 6.5.1.4, “Client-Side Cleartext Pluggable
234 Authentication”.)
235 · --fast, -F
237 Check only tables that have not been closed properly.
239 · --force, -f
241 Continue even if an SQL error occurs.
243 · --get-server-public-key
245 Request from the server the public key required for RSA key
247 pair-based password exchange. This option applies to clients that
248 that authenticate with the caching_sha2_password authentication
249 plugin. For that plugin, the server does not send the public key
250 unless requested. This option is ignored for accounts that do not
251 authenticate with that plugin. It is also ignored if RSA-based
252 password exchange is not used, as is the case when the client
253 connects to the server using a secure connection.
254 If --server-public-key-path=file_name is given and specifies a
256 valid public key file, it takes precedence over
257 --get-server-public-key.
258 For information about the caching_sha2_password plugin, see
260 Section 6.5.1.3, “Caching SHA-2 Pluggable Authentication”.
261 · --host=host_name, -h host_name
263 Connect to the MySQL server on the given host.
265 · --login-path=name
267 Read options from the named login path in the .mylogin.cnf login
269 path file. A “login path” is an option group containing options
270 that specify which MySQL server to connect to and which account to
271 authenticate as. To create or modify a login path file, use the
272 mysql_config_editor utility. See mysql_config_editor(1).
273 For additional information about this and other option-file
275 options, see Section 4.2.8, “Command-Line Options that Affect
276 Option-File Handling”.
277 · --medium-check, -m
279 Do a check that is faster than an --extended operation. This finds
281 only 99.99% of all errors, which should be good enough in most
282 cases.
283 · --no-defaults
285 Do not read any option files. If program startup fails due to
287 reading unknown options from an option file, --no-defaults can be
288 used to prevent them from being read.
289 The exception is that the .mylogin.cnf file, if it exists, is read
291 in all cases. This permits passwords to be specified in a safer way
292 than on the command line even when --no-defaults is used.
293 (.mylogin.cnf is created by the mysql_config_editor utility. See
294 mysql_config_editor(1).)
295 For additional information about this and other option-file
297 options, see Section 4.2.8, “Command-Line Options that Affect
298 Option-File Handling”.
299 · --optimize, -o
301 Optimize the tables.
303 · --password[=password], -p[password]
305 The password to use when connecting to the server. If you use the
307 short option form (-p), you cannot have a space between the option
308 and the password. If you omit the password value following the
309 --password or -p option on the command line, mysqlcheck prompts for
310 one.
311 Specifying a password on the command line should be considered
313 insecure. See Section 6.1.2.1, “End-User Guidelines for Password
314 Security”. You can use an option file to avoid giving the password
315 on the command line.
316 · --pipe, -W
318 On Windows, connect to the server using a named pipe. This option
320 applies only if the server supports named-pipe connections.
321 · --plugin-dir=dir_name
323 The directory in which to look for plugins. Specify this option if
325 the --default-auth option is used to specify an authentication
326 plugin but mysqlcheck does not find it. See Section 6.3.10,
327 “Pluggable Authentication”.
328 · --port=port_num, -P port_num
330 The TCP/IP port number to use for the connection.
332 · --print-defaults
334 Print the program name and all options that it gets from option
336 files.
337 For additional information about this and other option-file
339 options, see Section 4.2.8, “Command-Line Options that Affect
340 Option-File Handling”.
341 · --protocol={TCP|SOCKET|PIPE|MEMORY}
343 The connection protocol to use for connecting to the server. It is
345 useful when the other connection parameters normally would cause a
346 protocol to be used other than the one you want. For details on the
347 permissible values, see Section 4.2.2, “Connecting to the MySQL
348 Server”.
349 · --quick, -q
351 If you are using this option to check tables, it prevents the check
353 from scanning the rows to check for incorrect links. This is the
354 fastest check method.
355 If you are using this option to repair tables, it tries to repair
357 only the index tree. This is the fastest repair method.
358 · --repair, -r
360 Perform a repair that can fix almost anything except unique keys
362 that are not unique.
363 · --secure-auth
365 This option was removed in MySQL 8.0.3.
367 · --server-public-key-path=file_name
369 The path name to a file containing a client-side copy of the public
371 key required by the server for RSA key pair-based password
372 exchange. The file must be in PEM format. This option applies to
373 clients that authenticate with the sha256_password or
374 caching_sha2_password authentication plugin. This option is ignored
375 for accounts that do not authenticate with one of those plugins. It
376 is also ignored if RSA-based password exchange is not used, as is
377 the case when the client connects to the server using a secure
378 connection.
379 If --server-public-key-path=file_name is given and specifies a
381 valid public key file, it takes precedence over
382 --get-server-public-key.
383 For sha256_password, this option applies only if MySQL was built
385 using OpenSSL.
386 For information about the sha256_password and caching_sha2_password
388 plugins, see Section 6.5.1.2, “SHA-256 Pluggable Authentication”,
389 and Section 6.5.1.3, “Caching SHA-2 Pluggable Authentication”.
390 · --shared-memory-base-name=name
392 On Windows, the shared-memory name to use, for connections made
394 using shared memory to a local server. The default value is MYSQL.
395 The shared-memory name is case-sensitive.
396 The server must be started with the --shared-memory option to
398 enable shared-memory connections.
399 · --silent, -s
401 Silent mode. Print only error messages.
403 · --skip-database=db_name
405 Do not include the named database (case-sensitive) in the
407 operations performed by mysqlcheck.
408 · --socket=path, -S path
410 For connections to localhost, the Unix socket file to use, or, on
412 Windows, the name of the named pipe to use.
413 · --ssl*
415 Options that begin with --ssl specify whether to connect to the
417 server using SSL and indicate where to find SSL keys and
418 certificates. See Section 6.4.2, “Command Options for Encrypted
419 Connections”.
420 · --ssl-fips-mode={OFF|ON|STRICT} Controls whether to enable FIPS
422 mode on the client side. The --ssl-fips-mode option differs from
423 other --ssl-xxx options in that it is not used to establish
424 encrypted connections, but rather to affect which cryptographic
425 operations are permitted. See Section 6.6, “FIPS Support”.
426 These --ssl-fips-mode values are permitted:
428 · OFF: Disable FIPS mode.
430 · ON: Enable FIPS mode.
432 · STRICT: Enable “strict” FIPS mode.
434 Note
437 If the OpenSSL FIPS Object Module is not available, the only
438 permitted value for --ssl-fips-mode is OFF. In this case,
439 setting --ssl-fips-mode to ON or STRICT causes the client to
440 produce a warning at startup and to operate in non-FIPS mode.
441 · --tables
443 Override the --databases or -B option. All name arguments following
445 the option are regarded as table names.
446 · --tls-ciphersuites=ciphersuite_list
448 For client programs, specifies which TLSv1.3 ciphersuites the
450 client permits for encrypted connections. The value is a list of
451 one or more colon-separated ciphersuite names. The ciphersuites
452 that can be named for this option depend on the SSL library used to
453 compile MySQL. For details, see Section 6.4.6, “Encrypted
454 Connection Protocols and Ciphers”.
455 This option was added in MySQL 8.0.16.
457 · --tls-version=protocol_list
459 The protocols the client permits for encrypted connections. The
461 value is a list of one or more comma-separated protocol names. The
462 protocols that can be named for this option depend on the SSL
463 library used to compile MySQL. For details, see Section 6.4.6,
464 “Encrypted Connection Protocols and Ciphers”.
465 · --use-frm
467 For repair operations on MyISAM tables, get the table structure
469 from the data dictionary so that the table can be repaired even if
470 the .MYI header is corrupted.
471 · --user=user_name, -u user_name
473 The MySQL user name to use when connecting to the server.
475 · --verbose, -v
477 Verbose mode. Print information about the various stages of program
479 operation.
480 · --version, -V
482 Display version information and exit.
484 · --write-binlog
486 This option is enabled by default, so that ANALYZE TABLE, OPTIMIZE
488 TABLE, and REPAIR TABLE statements generated by mysqlcheck are
489 written to the binary log. Use --skip-write-binlog to cause
490 NO_WRITE_TO_BINLOG to be added to the statements so that they are
491 not logged. Use the --skip-write-binlog when these statements
492 should not be sent to replication slaves or run when using the
493 binary logs for recovery from backup.
494
496 Copyright © 1997, 2019, Oracle and/or its affiliates. All rights
497 reserved.
498 This documentation is free software; you can redistribute it and/or
500 modify it only under the terms of the GNU General Public License as
501 published by the Free Software Foundation; version 2 of the License.
502 This documentation is distributed in the hope that it will be useful,
504 but WITHOUT ANY WARRANTY; without even the implied warranty of
505 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
506 General Public License for more details.
507 You should have received a copy of the GNU General Public License along
509 with the program; if not, write to the Free Software Foundation, Inc.,
510 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see
511 http://www.gnu.org/licenses/.
512
515 For more information, please refer to the MySQL Reference Manual, which
516 may already be installed locally and which is also available online at
517 http://dev.mysql.com/doc/.
518
520 Oracle Corporation (http://dev.mysql.com/).
521MySQL 8.0 02/20/2019 MYSQLCHECK(1)