MySQL Myisamchk Repair in use, data table cannot be accessed

Posted on December and 2009 by Axl MyISAM is MySQL's preset storage engine. MyISAM table is very easy to rot away (corrupted).
This article will teach you how to check/fix these rotten MyISAM tables.

Every time you make a table in MySQL DB, you will create *.FRM, * at the same time on the file system. MyD, with *. MYI, etc. three file formats.

*.frm = used to store data table style (file to store table format)
*. MyD (MyData) = for storing data (file to store data)
*. Myi (Myindex) = used to store indexes (file to store index)

How do I check db? which table needs to be repaired:

Use ROOT to execute the following instructions, assuming that you want to check each table under DB1

The code is as follows	Copy Code
#myisamchk/var/lib/mysql/db1/*. Myi >> /tmp/myisamchk.log

In the screen output, if the following words are found, the table should be repaired

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 to 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 to be fixed

More information will be displayed in the redirect file. As follows

The code is as follows	Copy Code
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

How to use Myisamchk to fix rotten tables:

After you find the rotten table, fix it with the following instructions,

The code is as follows	Copy Code
#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

If a table is being used by a application, you may see that the clients are using or haven ' t closed the table properly.
So it is recommended that you turn off the mysqld or use flush tables to lock tables before fixing them to prevent application from making changes to DB table.

How to make a check with a repair at the same time:

code is as follows	copy code
# 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/mysq L/db1/profiles. Myi Myisamchk:warning:1 Client is using or hasn ' t closed the table properly

The meaning of options is as follows:
s,–silent option:prints only errors. can use Two-s to make myisamchk very silent.
-f,–force Option:restart myisamchk automatically with repair option-r, if there are all 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.

Manually allocate memory to large db when repairing

Large table, the repair takes a long time. You can manually increase the memory parameters when repairing.

The code is as follows	Copy Code
# 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

Get table information with Myisamchk

1

The code is as follows

Copy Code

#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

Last resort.

#myisamchk--help

MySQL uses MYISAMCHK to repair *. When myi, the following message appears:

Myisamchk:disk is full writing '/TMP/ST3LJJSW ' (errcode:28). Waiting for someone. (Expect up to secs delay for server to C ontinue after freeing disk spaces)

Myisamchk:retry in secs. Message reprinted in secs

Solution 1:

The message can be known, myisamchk need to use to/TMP,/tmp capacity is not enough to appear this message, so if/tmp can clear the space, the clearance of the/TMP space can be solved.
However, if/TMP itself is not large enough, you can use the following solution.

Solution 2:

This solution is derived from: free disk space error using MYISAMCHK

Myisamchk-rq--sort_buffer_size=256m--key_buffer_size=256m--read_buffer_size=32m--write_buffer_size=32M-- Sort-index--analyze--tmpdir=/more-disk-space-directory//var/lib/mysql/database/t1. Myi
?: Modify the--tmpdir=/more-disk-space-directory/specify to the place where the space is relatively large.