Shulou(Shulou.com)05/31 Report--

How to carry out a simple analysis of MySQL repair table, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

Today, a colleague asked me a question about the database. If he had told me all the details of the environment in the first place, I might have backed down. After I had a general understanding of the problem, I found that the background seemed to be much more complicated than I thought. This is a remote cloud environment, windows system, running MySQL, there is a problem while querying the table, and the developer colleagues did not fix it after repair, saying that it would get stuck and not respond.

Of course, after a little effort, I finally connected to this cloud host and found that the problem seemed to be a little more complicated than I thought. Of course, this is an environment used by an internal team, and it may be that we really need to use the environment, so we have to find a way to fix it.

The environment is MySQL version 5.5. Check the backend log and find that there has been an error since August. The error message is as follows:

161018 11:15:35 [ERROR] D:\ websoft\ mysql\ bin\ mysqld: Table'.\ utestdb\ test_forum_post' is marked as crashed and should be repaired

161018 11:15:36 [ERROR] D:\ websoft\ mysql\ bin\ mysqld: Table'.\ utestdb\ test_forum_post' is marked as crashed and should be repaired and looking at the log damaged more than one table, I focused on the wrong table for the time being.

If you use show create table test_forum_post or desc test_forum_post, an error will be thrown.

Mysql > show create table test_forum_post

ERROR 145 (HY000): Table'.\ utestdb\ test_forum_post' is marked as crashed and should be repaired

What scares me even more is that I can see from the background log that my development colleagues have also tried to restart the MySQL service many times. But there is always a problem.

Show create table cannot get the information, and show table status has limited information, because the storage engine at this time appears as NULL

They used MyISAM and looked at all the other table storage engines and found that they were all MyISAM. So I can basically conclude that the watch with the problem is also MyISAM.

For MyISAM table repair, you can use myisamchk to do or use repair way, of course, found that there are a lot of problems.

D:\ websoft\ mysql\ bin > myisamchk.exe-of..\ data\ utestdb\ test_forum_post.MYI

When this command goes on, a window pops up to show that the program crashes, as is the case with repeated attempts.

Using the repair command, you find that you have not returned for a long time and stop decisively.

There must have been something missing. I turned my head again to sort out the problem.

Check the log and find some of the following output, which seems to be a disk space problem.

161219 18:07:09 [Warning] Disk is full writing'.\ distoon\ pre_common_block.TMD' (Errcode: 28). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space)

161219 18:07:09 [Warning] Retry in 60 secs. Message reprinted in 600 secs

161219 18:07:18 [ERROR] D:\ websoft\ mysql\ bin\ mysqld: Table'.\ utestdb\ test_forum_post' is marked as crashed and should be repaired

After confirming that it was really caused by the disk space, they immediately cleaned up and set aside some space, and then asked me to continue to help fix it, and there would be no problem if I tried again.

Use the-of option first

D:\ websoft\ mysql\ bin > myisamchk.exe-of..\ data\ utestdb\ test_forum_post.MYI

-recovering (with keycache) MyISAM-table'..\ data\ utestdb\ test_forum_post.MYI'

Data records: 0

Data records: 55311

Then use the-r option to fix

D:\ websoft\ mysql\ bin > myisamchk.exe-r..\ data\ utestdb\ test_forum_post.MYI

-recovering (with sort) MyISAM-table'..\ data\ utestdb\ test_forum_post.MYI'

Data records: 55311

-Fixing index 1

-Fixing index 2

-Fixing index 3

-Fixing index 4

-Fixing index 5

-Fixing index 6

-Fixing index 7

-Fixing index 8

Final summary check

D:\ websoft\ mysql\ bin > myisamchk.exe..\ data\ utestdb\ test_forum_post.MYI

Checking MyISAM file:..\ data\ utestdb\ test_forum_post.MYI

Data records: 55311 Deleted blocks: 0

-check file-size

-check record delete-chain

-check key delete-chain

-check index reference

-check data record references index: 1

-check data record references index: 2

-check data record references index: 3

-check data record references index: 4

-check data record references index: 5

-check data record references index: 6

-check data record references index: 7

-check data record references index: 8

-check record links checks again and the problem does not exist.

Of course, if you try to use repair, such as repairing table pre_common_member, the output is as follows:

Mysql > repair table pre_common_member

+-+

| | Table | Op | Msg_type | Msg_text | |

+-+

| | utestdb.pre_common_member | repair | status | OK | |

+-+

1 row in set (1.64 sec)

To make the problem more comprehensive, I'll change the question slightly, that is, what's the difference between using the myisamchk tool and the check/repair command.

First of all, myisamchk and repair can only repair the MyISAM table, compared with the myisamchk output information is more detailed, optimization, analysis table information will be output, repair is more direct, repair can not repair the InnoDB table, otherwise the following error will be reported.

The storage engine for the table doesn't support repair

Check supports both MyISAM table and InnoDB table.

Secondly, when myisamchk operates the myisam table, it must ensure that the table cannot be used, while check/repair can operate online.

After reading the above, have you mastered the method of simple analysis of MySQL repair table? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.