一聚教程网:一个值得你收藏的教程网站

mysql myisamchk修复数据库常用命令记录

时间:2013-08-09 00:00:00 编辑:简简单单 来源:原创

实例

 代码如下

#locate myisamchk

#ln -s /usr/local/mysql/bin/myisamchk /usr/bin/myisamchk

#myisamchk tablename.MYI

#myisamchk -r -q tablename

如果-r不行就采用强制执行命令如下

 代码如下

#myisamchk -r -o -f

好了根据上面我们来分享一个实例


MyISAM是MySQL的预设storage engine. MyISAM table很容易烂掉(corrupted)。
此文章将教你如何检查/修?驼庑├玫舻?yISAM tables.

每次你在MySQL DB ?造一个 table,将会在档案系统上同时?造出*.frm、*.MYD,跟*.MYI等三种格式的档案。

*.frm = 用来储存资料表格式(file to store table format)
*.MYD(MyData) = 用来储存资料(file to store data)
*.MYI(MyIndex) =用来储存索引(file to store index)

如何检查DB?哪个table 需要修?停?/p>

用root 执行以下指令,假设要检查DB1下的各个table

 代码如下

 #myisamchk /var/lib/mysql/DB1/*.MYI >> /tmp/myisamchk.log

萤幕输出中,如果发现以下字样,该资料表就应修??/p>

 代码如下

myisamchk: error: Keypointers and record positions doesn't match
MyISAM-table '/var/lib/mysql/DB1/news_post_comment.MYI' is corrupted
Fix it using switch "-r" or "-o"
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/DB1/news_post.MYI' is usable but should be fixed
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/DB1/news_post_push_log.MYI' is usable but should be fixed

Redirect出来的档案中会显示更多资讯。如下

 代码如下

Checking MyISAM file: /var/lib/mysql/DB1/yam_bbauth.MYI
Data records: 14 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check record links

如何利用myisamchk修?屠玫舻?ables:

找出烂掉的table之后,用以下指令修?停?/p>

 代码如下

 #myisamchk –r /var/lib/mysql/DB1/news_post_comment.MYI
- recovering (with sort) MyISAM-table '/var/lib/mysql/DB1/news_post_comment.MYI
Data records: 1
- Fixing index 1

如果table正被某个application使用,你可能会看到: clients are using or haven't closed the table properly。
所以建议修?颓敖?ysqld关掉或用FLUSH TABLES锁住TABLES,防止修?褪庇?pplication对DB TABLE做更动。

如何让检查跟修?屯?苯?校?/p>

 代码如下

 #myisamchk --silent --force --fast --update-state /var/lib/mysql/DB1/*.MYI
myisamchk: MyISAM file /var/lib/mysql/DB1/groups.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: MyISAM file /var/lib/mysql/DB1/profiles.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly

options 的意义如下:
? s, –silent option: Prints only errors. You can use two -s to make myisamchk very silent.
? -f, –force option: Restart myisamchk automatically with repair option -r, if there are any errors in the table.
? -F, –fast option: Check only tables that haven't been closed properly.
? -U –update-state option: Marks tables as crashed, when it finds any error.

修?褪笔侄?峙浼且涮甯?哟蟮?B

庞大的table,修?托枰?艹さ氖奔洹P?褪笨梢允侄?黾蛹且涮宀问??/p>

 代码如下
 # myisamchk --silent --force --fast --update-state --key_buffer_size=512M --sort_buffer_size=512M
--read_buffer_size=4M --write_buffer_size=4M /var/lib/mysql/DB1/*.MYI

用myisamchk 获取table资讯

 代码如下

 #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

文章评论

热门栏目