MySQL myisamchk is being used and cannot be accessed

Yesterday, due to power loss in the data center, a large scale in the mysql database was inaccessible or in use. Later, I checked that I could use the mysql myisamchk command to fix the problem. Next I will introduce it.

Posted on December 31,200 9 by axl MyISAM is the default storage engine. MyISAM table of MySQL ).
This article will teach you how to check/fix these rotten MyISAM tables.

Every time you create a table in MySQL DB, three formats of files, including *. frm, *. MYD, and *. MYI, will be created in the file system.

*. Frm = file to store table format)
*. MYD (MyData) = used to store data (file to store data)
*. MYI (MyIndex) = used to store the index (file to store index)

How to check the database? Which table needs to be repaired:

Run the following command with root: Check the tables in DB1

The Code is as follows:	Copy code
# Myisamchk/var/lib/mysql/DB1/*. MYI & gt;/tmp/myisamchk. log

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

Myisamchk: error: Keypointers and record positions doesn't match
MyISAM-table '/var/lib/mysql/DB1/news_post_comment.myi' is already upted
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 shoshould 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 shoshould be fixed

More information is 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:

Find the corrupted table and run the following command to fix it,

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 the table is being used by an application, you may see that clients are using or haven'tclosed the table properly.
Therefore, we recommend that you disable mysqld or use flush tables to lock the TABLES before the fix to Prevent the application from changing the database TABLE during the fix.

How to perform the check and repair at the same time:

The 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/mysql/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. 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'tbeen closed properly.
•-U-update-state option: Marks tables as crashed, when it finds any error.

Manually allocate memory to large databases during restoration

It takes a long time to fix a large table. You can manually add memory parameters during restoration.

The Code is as follows:	Copy code
# Myisamchk -- silent -- force -- fast -- update-state -- key_buffer_size = 512 M -- sort_buffer_size = 512 M -- Read_buffer_size = 4 M -- write_buffer_size = 4 M/var/lib/mysql/DB1/*. MYI

Use myisamchk to obtain table information

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 move

# Myisamchk -- help

When MySQL uses myisamchk to fix *. MYI, the following message is displayed:

Myisamchk: Disk is full writing '/tmp/ST3lJjsW' (Errcode: 28 ). waiting for someone to free space... (keep CT up to 60 secs delay for server to c ontinue after freeing disk space)

Myisamchk: Retry in 60 secs. Message reprintedin 600 secs

Solution 1:

■ The message indicates that myisamchk needs to use/tmp, and the message will appear if/tmp capacity is insufficient. Therefore, if/tmp can clear the space, clear/tmp space to solve the problem.
However, if the/tmp switch is not large enough, you can use the following solution.

Solution 2:

This solution is taken from: free disk space error using myisamchk

■ Myisamchk-rq -- sort_buffer_size = 256 M -- key_buffer_size = 256 M -- read_buffer_size = 32 M -- write_buffer_size = 32 M -- sort-index -- analyze -- tmpdir =/more-disk-space- directory // var/lib/mysql/database/T1.MYI
■? : Modify -- tmpdir =/more-disk-space-directory/to specify a place with a large space.