利用mysql slave 修復master MyISAM table

前些天遇到一個分割槽表索引損壞的情況，最終通過slave成功修復了，記錄一下：

 

1 查詢該表和log裡都有報錯，這個應該是當天維護的同事沒有正常關閉mysql導致的：

mysql> select count(*) from cm_newlogs;

ERROR 145 (HY000): Table './cm/cm_newlogs#P#p1209' is marked as crashed and should be repaired

mysql>

mysql> desc cm_newlogs;

ERROR 145 (HY000): Table './cm/cm_newlogs#P#p1209' is marked as crashed and should be repaired

 

120916 13:51:19 [ERROR] /usr/sbin/mysqld: Table './cm/cm_newlogs#P#p1209' is marked as crashed and should be repaired

 

2 這個分割槽的記錄很多，光資料檔案就有30多G，如果要通過正常的途徑來修復MYI，肯定非常慢。

-rw-rw----  1 mysql mysql 37531299140 Sep 16 00:45 cm_newlogs#P#p1209.MYD

-rw-rw----  1 mysql mysql  6392213504 Sep 16 05:43 cm_newlogs#P#p1209.MYI

 

3對比了一下slave上的檔案，發現資料檔案跟master一致。但是MYI檔案比master的大。

-rw-rw----  1 mysql mysql 37531299140 Sep 16 00:45 cm_newlogs#P#p1209.MYD

-rw-rw----  1 mysql mysql  6443253760 Sep 16 04:51 cm_newlogs#P#p1209.MYI

 

在slave上嘗試查詢一下該表，正常。

mysql> select count(*) from cm_newlogs;

+-----------+

| count(*)  |

+-----------+

| 184628516 |

+-----------+

1 row in set (1.04 sec)

 

4 修復：把slave上的MYI檔案scp到master上，master恢復正常：

scp cm_newlogs#P#p1209.MYI x.x.x.x:/data/mysqldata/mysql

 

[root@localhost cm]# ls -al cm_newlogs#P#p1209.MYI

-rw-rw----  1 root root 6443253760 Sep 16 13:53 cm_newlogs#P#p1209.MYI

 

chown mysql:mysql cm_newlogs#P#p1209.MYI

 

mysql> select count(*) from cm_newlogs;

ERROR 1036 (HY000): Table 'cm_newlogs' is read only（這個是許可權導致的）

mysql> select count(*) from cm_newlogs;

+-----------+

| count(*)  |

+-----------+

| 184628516 |

+-----------+

1 row in set (0.01 sec)

 

從上面可以看出，如果有slave，我們可以利用slave來修復master，從而避免了長時間的repair。這個也是slave存在的作為一個備份的意義。

另外，MySQL引數DELAY_KEY_WRITE是指在表關閉之前，將對錶的update操作指跟新資料到磁碟，而不更新索引到磁碟，把對索引的更改記錄在記憶體。這樣MyISAM表可以使索引更新更快。在關閉表的時候一起更新索引到磁碟。當DELAY_KEY_WRITE使用的時候，如果出現重啟或者掉電等情況，會導致在cache的索引update沒來得及更新，所以必須在啟動引數加上 --myisam-recover，這樣在你啟動mysql的時候會檢查你的表並同步表和索引.或者在重啟伺服器之前執行myisamchk。(然而，即使在這種情況下，應通過使用DELAY_KEY_WRITE保證不丟失資料，因為關鍵字資訊總是可以從資料行產生）

 

 

下面是我們一般正常的處理方法（摘錄自《MySQL Troubleshooting》）

 

Repairing a MyISAM table from SQL

CHECK TABLE without parameters shows the current table status:

mysql> CHECK TABLE t2;

+---------+-------+----------+------------------------------------------------------+

| Table | Op | Msg_type | Msg_text |

+---------+-------+----------+------------------------------------------------------+

| test.t2 | check | warning | Table is marked as crashed and last repair failed |

| test.t2 | check | warning | Size of indexfile is: 1806336 Should be: 495616 |

| test.t2 | check | error | Record-count is not ok; is 780 Should be: 208 |

| test.t2 | check | warning | Found 780 key parts. Should be: 208 |

| test.t2 | check | error | Corrupt |

+---------+-------+----------+------------------------------------------------------+

5 rows in set (0.09 sec)

This is an example of output for a corrupted table. Your first resort is to run REPAIR

TABLE without parameters:

mysql> REPAIR TABLE t2;

+---------+--------+----------+----------------------------------------+

| Table | Op | Msg_type | Msg_text |

+---------+--------+----------+----------------------------------------+

| test.t2 | repair | warning | Number of rows changed from 208 to 780 |

| test.t2 | repair | status | OK |

+---------+--------+----------+----------------------------------------+

2 rows in set (0.05 sec)

This time we were lucky and the table was repaired successfully. We can run CHECK

TABLE again to confirm this:

mysql> CHECK TABLE t2;

+---------+-------+----------+----------+

| Table | Op | Msg_type | Msg_text |

+---------+-------+----------+----------+

| test.t2 | check | status | OK |

+---------+-------+----------+----------+

1 row in set (0.02 sec)

If a simple REPAIR TABLE run does not help, there are two more options. REPAIR TABLE

EXTENDED works more slowly than the bare REPAIR TABLE, but can fix 99% of errors. As

a last resort, run REPAIR TABLE USE_FRM, which does not trust the information in the

index file. Instead, it drops and then recreates the index using the description from the

table_name.frm file and fills the key with values from the table_name.MYD file.

 

Repairing a MyISAM table using myisamchk

All of these steps can also be performed using myisamchk, which has a lot of additional

table maintenance options. I won’t describe all the features of the utility here, but

instead concentrate on those specific to table repair.

myisamchk directly accesses table files and does not require the MySQL server to be

started. This can be very useful in some situations. At the same time, myisamchk

requires exclusive access to table files, and you should avoid using it when the MySQL

server is running.

 

If you have to use myisamchk while the server is running, issue the

queries FLUSH TABLES and LOCK TABLE table_name WRITE, then wait until

the latest query returns a command prompt, and then run myisamchk

in a parallel session. If other processes besides myisamchk access the

table while myisamchk is running, even worse corruption can occur.

 

A basic recovery command is:

$myisamchk --backup --recover t2

- recovering (with sort) MyISAM-table 't2'

Data records: 208

- Fixing index 1

- Fixing index 2

Data records: 780

The --backup option tells myisamchk to back up the datafile before trying to fix the

table, and --recover does the actual repair. If this command is insufficient, you can use

the --safe-recover option. The latter option uses a recovery method that has existed

since very early versions of MySQL and can find issues that the simple --recover option

cannot. An even more drastic option is --extend-check.

You can also use the option --sort-recover, which uses sorting to resolve the keys even

when the temporary file is very large.

Among other options, which I recommend you study carefully, is the very useful

--description option, which prints a description of the table. Taken together with -v

or its synonym, --verbose, it will print additional information. You can specify the -v

option twice or even three times to get more information.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/758322/viewspace-745805/，如需轉載，請註明出處，否則將追究法律責任。