ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 利用mysql slave 修复master MyISAM table

利用mysql slave 修复master MyISAM table

原创 Linux操作系统 作者:aaqwsh 时间:2012-10-06 16:43:01 0 删除 编辑

前些天遇到一个分区表索引损坏的情况,最终通过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 这个分区的记录很多,光数据文件就有30G,如果要通过正常的途径来修复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文件scpmaster上,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 wont 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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-11-24

  • 博文量
    132
  • 访问量
    265736