Mysqlcheck
Mysqlcheck在mysqld运行时使用,其内嵌check/repair/analyze/optimize table命令;
在执行时相应表被锁定,analyze/check table使用读锁,其它均为写锁;
同myisamchk功能近似,但是后者最好在mysqld关闭时运行;
Mysqlcheck可以通过重命名或创建链接的方式生成mysqlrepair/mysqlanalyze/mysqloptimize
可以在表和数据库级别执行
Mysqlcheck db_name [tbl_name …]
Mysqlcheck –databases db_name
Mysqlcheck –all-databases
选项
--analyze:执行analyze table
--auto-repair:如果表损坏则自动修复
--check:执行check table
--check-only-changed:只对上次操作以来变化过的表执行check table
--check-upgrade:执行check table for upgrade
--compress:压缩服务器和客户端之间的所有通信信息
--extended:检查并修复表;如果用于检查表,会确保100%一致但耗时较长;如果用于修复表,可能会产生垃圾数据;
--fast:只检查没有正常关闭的表
--medium-check:比extended选项稍快,可检查99.99%的错误
--optimize:执行optimize table
--quick:只修复索引树,是最快的方法
--repair:除了unique key包含重复数据,可以修复其它大部分错误
--use-frm:修复MyISAM表
--write-binlog:将analyze/optimize/repair语句记录进binlog
Analyze table
要求对候选表有select和insert权限;
可用于分区表,alter table … analyze partition
用于分析表中key键的分布情况,即收集统计信息;
如果自上次analyze以来表没有改动,则analyze table并不会被实际执行;
如果不想记录binlog,可使用NO_WRITE_TO_BINLOG或LOCAL
Check table
可用于MyISAM/InnoDB,该命令有一系列选项,包括
For upgrade/quick/fast/medium/extened/changed
对于MyISMA默认为medium,等同于Myisamchk –m tbl_name
表损坏经常来自索引,故所有的检查级别都会完整的检查索引;
可用于分区表,alter table … check partition
QUICK | Do not scan the rows to check for incorrect links. Applies to InnoDB and MyISAM tables and views. |
FAST | Check only tables that have not been closed properly. Applies only to MyISAM tables and views; ignored for InnoDB. |
CHANGED | Check only tables that have been changed since the last check or that have not been closed properly. Applies only to MyISAM tables and views; ignored for InnoDB. |
MEDIUM | Scan rows to verify that deleted links are valid. This also calculates a key checksum for the rows and verifies this with a calculated checksum for the keys. Applies only to MyISAM tables and views; ignored for InnoDB. |
EXTENDED | Do a full key lookup for all keys for each row. This ensures that the table is 100% consistent, but takes a long time. Applies only to MyISAM tables and views; ignored for InnoDB. |
InnoDB表
如果check table检测到InnoDB表错误,则关闭服务器防止错误传播;
Ibd的前3页为头信息,check table对此无能为力,可用innochecksum检查;
http://dev.mysql.com/doc/refman/5.5/en/innochecksum.html
optimize table
语法为optimize [NO_WRITE_TO_BINLOG | LOCAL] table
重新组织表和索引的物理结构,以消除存储碎片;
对InnoDB需要慎用;
会对MyISAM表收集统计信息
Repair table
Repair [NO_WRITE_TO_BINLOG | LOCAL] table [ QUICK ] [ EXTENDED ][USE_FRM]
默认等同于myisamchk –r tbl_name
没有Myisamchk选项多
Quick:只修复索引文件,等同于myisamchk –recover –quick
Extended:逐行创建索引,等同于myisamchk –safe-recover
Use_frm:当MYI文件不可用时使用,通过frm重建索引文件,myisamchk没有改选项
可临时调大key_buffer_size/myisam_sort_buffer_size/myisam_max_sort_file_size/read_buffer_size加速索引创建
Myisamchk
--medium-check:Do a check that is faster than an --extend-check operation
--extened-check:Do very thorough table check or repair that tries to recover every possible row from the data file
--recover:Do a repair that can fix almost any problem except unique keys that aren't unique
--safe-recover:Do a repair using an old recovery method that reads through all rows in order and updates all index trees based on the rows found
--max-record-length:Skip rows larger than the given length if myisamchk cannot allocate memory to hold them
--sort-index or -s:Sort the index blocks
--sort-records=index_num or -R index_num:Sort data rows according to a given index
Mysql服务器默认不开启external locking,此时调用myisamchk必须确保该表没有被其他程序访问,最好关闭mysqld,否则会告警该表已经corrupt;
若开启此功能,则可使用放心的使用myisamchk检查表,其他访问该表的程序会等到myisamchk运行完毕再开始;
Repair/optimize表时必须确保没有其他并发访问程序,如不能停止mysqld,至少也要做一次mysqladmin flush-tables
Myisamchk工作时会逐行复制并生成一个新的MYD文件,最后将老的文件删除,然后将新文件重命名;
若指定--quick选项, 则跳过MYD,仅仅生成一个新的MYI文件,若MYD有损坏则自动终止,声明两次则在遇到错误时会尝试自动修复;
如何检查MyISAM表
根据选项可分为4个等级
Myisamchk tbl_name—可检索99.99%的错误,对于只有myd文件损坏的情形无能为力;
Myisamchk –m tbl_name –可检索99.999%的错误,先检查index然后挨个读取所有数据行,对行中的key值计算checksum然后同index tree中相应的checksum进行比较
Myisamchk –e tbl_name—挨个检查每行的key以确保它们指向正确的行
Myisamchk –e –I tbl_name—打印出额外的信息
如何修复MyISAM表
MyISAM表损坏的原因多种多样,常见的有如下
- tbl_name.frm is locked against change
- Can't find file tbl_name.MYI (Errcode: nnn)
- Unexpected end of file
- Record file is crashed
- Got error nnn from table handler
对于出现的错误号nnn,可以调用perror nnn查看具体原因
如从命令行修复,必须先关闭mysqld,可使用mysqladmin shutdown
总体有4个阶段,每个阶段可修复不同的错误,越往后则难度越高
阶段1:检查
检查表myisamchk *.MYI,如mysqld已经停止,则需声明—update-state选项将该表标注为checked,如期间遇到错误跳至阶段2,如遇到诸如out of memory或myisamchk崩溃,则跳至阶段3
阶段2:简易修复
首先调用myisamchk –r –q tbl_name,会在不触及数据文件的前提下修复索引文件,如不能成功则执行如下步骤:
备份数据文件;使用myisamchk –r tbl_name删除数据文件中不正确的行并重建索引文件;若前一步失败则执行myisamchk –safe-recover tbl_name,但速度会比较慢;
可临时调大sort_buffer_size/key_buffer_size以加速其处理;
若本阶段遇到诸如out of memory的错误,则跳至阶段3
阶段3:复杂恢复
此阶段遇到的问题一般是索引文件丢失/索引文件前16kb块损坏,需要重建索引文件
备份数据文件;将该表truncate,数据/索引文件随之清空;用备份的数据文件覆盖清空的文件,若在使用replication则需临时关闭,因为此步骤不会记录到二进制日志;运行mysql –r –q tbl_name;
阶段4:超复杂恢复
只有在frm元数据文件损坏时才会进入此阶段,有两种解决办法:
从备份中恢复该文件跳转至阶段3;如果没有备份但是知道该表的DDL,在其他数据库上创建该表,然后把frm/myi复制过来,跳转置阶段2继续处理;
myisamchk -dvv profiles.MYI
MyISAM file: profiles.MYI
Record format: Packed
Character set: latin1_swedish_ci (8)
File-version: 1
Creation time: 2007-08-16 18:46:59
Status: open,changed,analyzed,optimized keys,sorted index pages
Auto increment key: 1 Last value: 88
Data records: 88 Deleted blocks: 0
Datafile parts: 118 Deleted data: 0
Datafile pointer (bytes): 4 Keyfile pointer (bytes): 4
Datafile length: 6292 Keyfile length: 6144
Max datafile length: 4294967294 Max keyfile length: 4398046510079
Recordlength: 2124
table description:
Key Start Len Index Type Rec/key Root Blocksize
1 2 3 unique int24 1 1024 1024
2 5 765 unique char packed stripped 1 2048 4096
Field Start Length Nullpos Nullbit Type
1 1 1
2 2 3 no zeros
3 5 765 no endspace
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15480802/viewspace-757890/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15480802/viewspace-757890/