mysqlcheck使用介绍检查、修复、优化、分析表

187次阅读

共计 5130 个字符，预计需要花费 13 分钟才能阅读完成。

MySQLcheck 使用介绍检查、修复、优化、分析表，以下内容主要适用于 mysql 5.6，5.5 的版本可能有部分选项不可用。

通常使用该工具一般语法为：

shell> mysqlcheck [options] db_name [tbl_name …]
shell> mysqlcheck [options] –databases db_name …
shell> mysqlcheck [options] –all-databases

比如对 mysql 库进行 mysqlcheck 操作：

[op@sAno1y ~]$ mysqlcheck mysql -uroot -p
Enter password:
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK

实际上该工具是为了方便用户使用，而使用了 CHECK TABLE、REPAIR TABLE、ANALYZE TABLE、OPTIMIZE TABLE 语句。

–analyze 选项：实际上是执行了 ANALYZE TABLE（支持 InnoDB，MyISAM，NDB）
–check 选项：实际上是执行了 CHECK TABLE（支持 InnoDB，MyISAM，ARCHIVE，CSV）
–optimize 选项：实际上执行了 OPTIMIZE TABLE（支持 InnoDB，MyISAM，ARCHIVE）
–repair 选项：实际上执行 REPAIR TABLE（支持 MyISAM，ARCHIVE，CSV）

一般情况不需要加这些选项，除非需要修复

其他修改选项：
–repair –quick 尝试快速修复
–repair 正常修复（除非快速修复失败）
–repair –force 强行修复

当然，在 mysqlcheck 时，每张表会被加上 READ LOCK。
跑该进程时，尤其是大表，将会变得十分耗时。
且该工具必须在 mysqld 服务运行的情况下使用。

此外，部分存储引擎的表是不被支持的：
我创了四张表，其存储引擎分别为 ARCHIVE、BLACKHOLE、MEMORY、MRG_MYISAM

mysql> use test;
Database changed
mysql> show tables;
+––––––––––––––––+
| Tables_in_test |
+––––––––––––––––+
| archive_tb |
| blackhole_tb |
| memory_tb |
| mrg_myisam_tb |
+––––––––––––––––+
4 rows in set (0.00 sec)

然后 check 了一下，发现 blackhole 和 memory 是不被支持的，因为这两个存储引擎只存储.frm 的表定义在磁盘上。

[op@sAno1y ~]$ mysqlcheck test -uroot -p
Enter password:
test.archive_tb OK
test.blackhole_tb
note : The storage engine for the table doesn’t support check
test.memory_tb
note : The storage engine for the table doesn’t support check
test.mrg_myisam_tb OK

其他选项参考：

Format	Description	Introduced
–all-databases	Check all tables in all databases
–all-in-1	Execute a single statement for each database that names all the tables from that database
–analyze	Analyze the tables
–auto-repair	If a checked table is corrupted, automatically fix it
–bind-address=ip_address	Use specified network interface to connect to MySQL Server
–character-sets-dir=path	Directory where character sets are installed
–check	Check the tables for errors
–check-only-changed	Check only tables that have changed since the last check
–check-upgrade	Invoke CHECK TABLE with the FOR UPGRADE option
–compress	Compress all information sent between client and server
–databases	Process all tables in the named databases
–debug[=debug_options]	Write a debugging log
–debug-check	Print debugging information when program exits
–debug-info	Print debugging information, memory, and CPU statistics when program exits
–default-auth=plugin	Authentication plugin to use	5.6.2
–default-character-set=charset_name	Specify default character set
–defaults-extra-file=file_name	Read option file in addition to usual option files
–defaults-file=file_name	Read only named option file
–defaults-group-suffix=str	Option group suffix value
–extended	Check and repair tables
–fast	Check only tables that have not been closed properly
–fix-db-names	Convert database names to 5.1 format
–fix-table-names	Convert table names to 5.1 format
–force	Continue even if an SQL error occurs
–help	Display help message and exit
–host=host_name	Connect to MySQL server on given host
–login-path=name	Read login path options from .mylogin.cnf	5.6.6
–medium-check	Do a check that is faster than an –extended operation
–no-defaults	Read no option files
–optimize	Optimize the tables
–password[=password]	Password to use when connecting to server
–pipe	On Windows, connect to server using named pipe
–plugin-dir=path	Directory where plugins are installed	5.6.2
–port=port_num	TCP/IP port number to use for connection
–print-defaults	Print defaults
–protocol=type	Connection protocol to use
–quick	The fastest method of checking
–repair	Perform a repair that can fix almost anything except unique keys that are not unique
–secure-auth	Do not send passwords to the server in old (pre-4.1.1) format	5.6.17
–shared-memory-base-name=name	The name of shared memory to use for shared-memory connections
–silent	Silent mode
–skip-database=db_name	Omit this database from performed operations	5.6.11
–socket=path	For connections to localhost, the Unix socket file to use
–ssl	Enable SSL for connection
–ssl-ca=file_name	Path of file that contains list of trusted SSL CAs
–ssl-capath=dir_name	Path of directory that contains trusted SSL CA certificates in PEM format
–ssl-cert=file_name	Path of file that contains X509 certificate in PEM format
–ssl-cipher=cipher_list	List of permitted ciphers to use for SSL encryption
–ssl-crl=file_name	Path of file that contains certificate revocation lists	5.6.3
–ssl-crlpath=dir_name	Path of directory that contains certificate revocation list files	5.6.3
–ssl-key=file_name	Path of file that contains X509 key in PEM format
–ssl-verify-server-cert	Verify server Common Name value in its certificate against host name used when connecting to server
–tables	Overrides the –databases or -B option
–use-frm	For repair operations on MyISAM tables
–user=user_name,	MySQL user name to use when connecting to server
–verbose	Verbose mode
–version	Display version information and exit
–write-binlog	Log ANALYZE, OPTIMIZE, REPAIR statements to binary log. –skip-write-binlog adds NO_WRITE_TO_BINLOG to these statements.