你好,游客 登录 注册 搜索
背景:
阅读新闻

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

[日期:2017-09-01] 来源:Linux社区  作者:神谕丶 [字体: ]

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


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

  1. shell> mysqlcheck [options] db_name [tbl_name ...]
  2. shell> mysqlcheck [options] --databases db_name ...
  3. shell> mysqlcheck [options] --all-databases


比如对mysql库进行mysqlcheck操作:

  1. [op@sAno1y ~]$ mysqlcheck mysql -uroot -p
  2. Enter password:
  3. mysql.columns_priv OK
  4. mysql.db OK
  5. mysql.event OK
  6. mysql.func OK
  7. mysql.general_log OK
  8. mysql.help_category OK
  9. mysql.help_keyword OK
  10. mysql.help_relation OK
  11. mysql.help_topic OK
  12. mysql.innodb_index_stats OK
  13. mysql.innodb_table_stats OK
  14. mysql.ndb_binlog_index OK
  15. mysql.plugin OK
  16. mysql.proc OK
  17. mysql.procs_priv OK
  18. mysql.proxies_priv OK
  19. mysql.servers OK
  20. mysql.slave_master_info OK
  21. mysql.slave_relay_log_info OK
  22. mysql.slave_worker_info OK
  23. mysql.slow_log OK
  24. mysql.tables_priv OK
  25. mysql.time_zone OK
  26. mysql.time_zone_leap_second OK
  27. mysql.time_zone_name OK
  28. mysql.time_zone_transition OK
  29. mysql.time_zone_transition_type OK
  30. 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

  1. mysql> use test;
  2. Database changed
  3. mysql> show tables;
  4. +----------------+
  5. | Tables_in_test |
  6. +----------------+
  7. | archive_tb     |
  8. | blackhole_tb   |
  9. | memory_tb      |
  10. | mrg_myisam_tb  |
  11. +----------------+
  12. 4 rows in set (0.00 sec)


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

  1. [op@sAno1y ~]$ mysqlcheck test -uroot -p
  2. Enter password:
  3. test.archive_tb OK
  4. test.blackhole_tb
  5. note : The storage engine for the table doesn't support check
  6. test.memory_tb
  7. note : The storage engine for the table doesn't support check
  8. test.mrg_myisam_tb OK



其他选项参考:

FormatDescriptionIntroduced
--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.

本文永久更新链接地址http://www.linuxidc.com/Linux/2017-09/146647.htm

linux
相关资讯       mysqlcheck 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款