一点关于MySQL参数delay_key_write、myisam_recover_options的使_MySQL

bitsCN.com

一点关于MySQL参数delay_key_write、myisam_recover_options的使用经验

 

    最近在做数据库实例迁移的时候遇到了几个比较诡异的问题：

MyISAM实例正常shutdown后rsync数据文件到另外一台机器上起实例后，访问表时提示表自动修复失败需要repair table。提示信息：Table './test/record_03' is marked as crashed and last (automatic?) repair failed

表损坏后，利用repair table命令将表修复时提示表里存在很多的duplicate key.

     warning  : Duplicate key for record at 129584986 against record at 62008769

     warning  : Duplicate key for record at 104678355 against record at 61426294

     warning  : Duplicate key for record at 297493788 against record at 61697778

     warning  : Duplicate key for record at 209950328 against record at 61548867

     warning  : Duplicate key for record at 105894968 against record at 61866949

...

...

补充一下当时的环境信息：

1.MySQL版本为官方版的5.5.12，表引擎是MyISAM

2.迁移的步骤：stop slave->flush tables->正常shutdown实例->rsync表文件->在新机器上起实例。rsync前后表文件MD5值一样。

3.表的结构类似这样：

[sql] 

create table test (  

id int auto_increment primary key,  

a varchar(100) not null,  

b int not null,  

c int,  

unique key (a,b)  

)  

 

        首先说第一个问题，这里分两点：1.为什么会需要修复？2.为什么自动修复失败？

我是正常关闭实例，不明白表需要修复的原因于是请淘宝丁奇帮忙分析了数据文件，显示文件头里记录显示表被关闭仍有6个线程在访问。但我可以确定当时rsync数据文件前是正常shutdown实例的，这个可能是MyISAM本身的问题或者是本身数据文件就已经存在问题都不得而知，总而言之当我迁移过来后访问表时需要修复这点是合理的，因为表被关闭时还有线程在访问，这会被MySQL认为表非正常关闭，需要修复。

为什么会自动修复失败，这是由于我配置了参数myisam_recover_options=default，这个配置表示每次访问MyISAM表之前都会先检测表是否需要修复，如果需要则自动进行，这也就是前面看到信息last (automatic?) repair failed。而修复失败是因为这个参数带来的修复行为默认是从key cache里面找需要修复的数据，而我当时是shutdown实例，rsync到新环境中起实例，此时已没有当时的现场(key cache环境)，加上default不会强制进行修复（强制修复表如果索引文件和数据文件数据不一致则自动进行删除或者增加行），（如果是myisam_recover_options=force，那么即使此时key cache不存在了也会进行强制修复，此时做的就是对比数据文件和索引文件，然后删除数据文件中多余的行，因此这样可能会丢数据）。在自动修复时可以再error log中看到如下信息：

130410  9:31:23 [ERROR] /usr/local/mysql55/bin/mysqld: Table './image_0/record_00' is marked as crashed and should be repaired

130410  9:31:23 [Warning] Checking table:   './image_0/record_00'

130410  9:31:24 [ERROR] Got an error from unknown thread, /export/home/pb2/build/sb_0-3198286-1302530066.93/mysql-5.5.12/storage/myisam/ha_myisam.cc:868

130410  9:31:24 [Warning] Recovering table: './image_0/record_00'

130410  9:31:28 [Note] Retrying repair of: './image_0/record_00' with keycache

130410  9:31:37 [ERROR] Couldn't repair table: image_0.record_00

 

 

接着说第二个问题，在第一个问题中提示表损坏，需要手工repair table，而我执行了这条命令后提示表里存在很多重复键数据:

warning  : Duplicate key for record at 129584986 against record at 62008769

warning  : Duplicate key for record at 104678355 against record at 61426294

warning  : Duplicate key for record at 297493788 against record at 61697778

warning  : Duplicate key for record at 209950328 against record at 61548867

warning  : Duplicate key for record at 105894968 against record at 61866949

这个重复键指的是唯一键(a,b)，然后进行了一个小测试验证是数据文件里面有重复的行（在a,b字段上）。逻辑图大概是这样

[plain] 

id a b c  

1  1 2 3  

2  1 2 4  

3  2 3 55  

4  2 3 54  

 

 

        上面这个列表是我自己模拟的，在(a,b)上有唯一键约束，但是在数据文件中却存在(a,b)重复的行。验证方法较简单：

/*强制走全表扫描*/select * from test where a=val1 and b=val2;

结果集有2行

/*强制走索引*/select * from test where a=val1 and b=val2;

结果集只有1行

 

        当时遇到问题第一反应是开发人员那边是不是曾经导过数据且导数据的时候禁止了唯一约束检查，但是事实上开发说自己没这么干过，于是开发说这是MySQL的bug，我怎么也觉得MySQL不会存在这样大的bug，一直觉得是对MySQL使用方法不对。于是对迁移前后的环境做了对比检查终于发现了疑点：老的环境中配置了delay_key_write=ALL，myisam_recover_options=OFF（默认是OFF）。而我的新环境中则是delay_key_write=ON，myisam_recover_options=default 。

简单解释下delay_key_write,每次更新MyISAM表索引后不立即将更新flush到物理磁盘上，这些修改依然保留在内存中，直到这个表被关闭时再flush，ALL表示对所有的表都使用这一行为，ON表示对创建表时使用了delay_key_write的表使用这个功能。OFF表示不进行delay key write。而官方建议在使用delay_key_write时同时用myisam_recover_options这样才能保证数据一致性，否则，举个最简单的例子，索引数据一致都没flush到磁盘上，突然表被非正常关闭，等下次表被访问如果不进行相应的检测修复，那么就会出现数据文件与索引不一致的情况，这样就会导致数据不一致。

再简单解释下myisam_recover_options，default表示每次访问表时会先判断是否需要修复，但是不会强制修复（仅仅尝试从key cache中修复），backup表示修复时会先将老的数据文件先做个备份，force表示强制修复，具体用法见下图。

 

 

        此时就大致可以确定是由于MySQL参数使用不合理导致的数据不一致问题，所以在使用MyISAM时如果配置了delay_key_write，就千万记得同时配置一个myisam_recover_options这样才可能防止数据不一致性的情况出现。那么我是怎么解决这个问题的呢？目前来说还是通过迁移后的MySQL配置参数环境保持与老环境一致，否则如果我现在强制加上参数myisam_recover_options，那么就会导致所有的表损坏，损坏之后恢复的唯一途径就是repair table，这会丢失数据（或者配置myisam_recover_options=force,backup，这个效果是跟repair table一样，且同时对将要修改的MYD文件做一个备份），因为对于重复键的行，如果是利用repair table去做的话，我无法干预MySQL留下其中具体的哪行，所以如果强行repair table虽然可以解决重复键错误，但是最终留下的哪行不一定是用户想要的那行数据。当然还有另外两种可行的方法，第一，咨询业务负责人后手工删除重复键中不需要的行。第二，强制repair table 然后将老的数据留一份做备份，然后用户端发现异常了再进行数据恢复，不过这个做法不可取，否则都会被用户投诉致死。

 

        总之，都21世纪了，都5.6了，不要再用MyISAM了吧，我这是历史系统，没办法，后期进行升级改造也是必须进行的。

bitsCN.com