MySQL Myisamchk Repair in use, data table cannot be accessed

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.