• MySQL-FAQ 主从复制中的1032错误

    背景

    某个业务线的RD过来找我帮忙,数据库从库停止复制了。 经过了解发现从库复制出现如下错误:

    第一时间查看分析了对应的表结构与binlog pos对应的SQL,并没有发现什么异常。于是从配置入手,发现从库添加了如下配置项:

    replicate_do_db=XXX

    因为业务需求,从库只从主库更新一个database过来。 于是追踪问题的范围就变得小了很多。简单的从网上浏览了一下相关错误,怀疑可能是设置上述参数导致的跨库更新不同步问题。(因为查询都已经在问题发生的时候都切到了主库,所以我有充分的时间去测试。)

    首先,我用xtraback重新做了从库(配置文件没有改变),恢复了主从复制。

    然后,在主库上跨库更新发现无法同步到从库。例如,在主库上执行:

    然后发现从库没有这条记录,也就是说从库忽略了这个SQL的复制。

    于是我很机智的将从库中的这条配置改成了如下配置:

    replicate-wild-do-table = XXX.%

    重启从库使得此参数生效,然后我满心欢喜以为万事大吉了。 结果悲催的发现跨库更新还是无法同步。突然觉得自己太大意了,于是我排查主库的配置文件,发现原来主库里面也有如下配置:

    binlog_do_db = XXX

    这下悲剧了,看来要根本的解决这个跨库更新的问题,主库可能也要调整参数并重启。于是将主库的参数也换成了replica_wild_do_table 以后,发现这个问题解决了。 由于自己懒的去试验,所以下面是我在网上搜到的这三个参数的组合可能对跨库更新的影响,仅供参考:

    实验参数:

    主库参数:binlog-do-db

    从库参数:replicate-do-db和replicate-wild-do-table

    下面分六个情况进行实验,并观察日志变更情况。

    实验一:主库、从库均不设置参数

    实验二:主库不设置参数、从库设置replicate-do-db

    实验三:主库不设置参数、从库设置replicate-wild-do-table

    实验四:主库不设置参数、从库设置replicate-do-db和replicate-wild-do-table

    实验五:主库设置参数binlog-do-db、从库不设置参数

    实验六:主库设置参数binlog-do-db、从库设置replicate-do-db

    实验七:主库设置参数binlog-do-db、从库设置replicate-wild-do-table

    实验八:主库设置参数binlog-do-db、从库设置replicate-do-db和replicate-wild-do-table

    过程不写了,直接上结果。

    总结:

    1、主库、从库有没有任何参数,默认库操作都是可以同步的。

    2、主库未设置binlog-do-db的情况下:

        a.从库设置replicate-do-db,跨库操作不同步

        b.从库设置replicate-wild-do-table,跨库操作同步

        c.从库设置replicate-do-db和replicate-wild-do-table,跨库操作不同步

        d.跨库操作后binlog会有变化

    3、主库设置binlog-do-db的情况下:

        a.从库设置replicate-do-db,跨库操作不同步

        b.从库设置replicate-wild-do-table,跨库操作不同步

        c.从库设置replicate-do-db和replicate-wild-do-table,跨库操作不同步

        d.跨库操作后binlog没有变化

    4、只有在主库、从库不设置参数的情况下,主库新建库,从库才可以同步。

    问题到这里虽然被解决了,但是我们并没有明白MySQL内部是如何根据这些参数对数据库的主从复制进行控制的,而且貌似MySQL这类型的参数也不止是这两个,还有如下:

    这么多参数,我们不可能一步步去组合去试验,所以下面来看看这个相关的原理:

    首先,我们发现上面的这些参数可以分为两类,库级别的限制和表级别的限制。对于mysql来说,有如下特性:

    1. 库级别的规则,只针对binlog_format=”STATEMENT or MIXED”。
    2. 如果是binlog_format=”ROW”,不受库级别规则限制,只受表级别规则限制。

    db级别的限制对主从复制的影响:

    yy

    如上,对于ROW模式的主从复制来说,并没有Test the default database 这一步验证(注:之前说的row模式不受库级别的限制不是说它可以无视relicate这些参数的限制,而是他没有跨库检验的这一限制,也就是说db级别的replicate参数在row模式下不会产生跨库操作无法传递到从库的现象)。

    table级别的复制对主从复制的影响:

    yy

    看起来有点复杂是吗? 我也这样觉得,其实这两张图结合起来就表达了这么个意思,我们以一条跟新sql为例:

    首先,如果你是statement语句模式的复制,对它进行db级别的限制解析(row模式则直接到table级别),如果有replicate-do-db,则判断replicate-do-db,将不会走到replicate-ignore-db这层。 如果判断replicate-do-db符合条件,则进行table级别的限制解析。 如果不符合,则exit。

    在db级别的限制解析中,如果没有replicate-do-db,但是有replicate-ignore-db。 流程则是:符合replicate-ignore-db规则,则exit,不符合,则走到table级别的层继续判断。

    在Table级别的限制规则中,判断逻辑顺序自上而下为:replicate-do-table -> replicate-ignore-table -> replicate-wild-do-table -> replicate-wild-ignore-table。

    在Table级别的限制规则中, 从第一个阶段(replicate-do-table)开始,如果符合replicate-do-table判断规则,则exit。如果不符合,则跳到下一层(replicate-ignore-table)。 然后以此内推,直到最后一层(replicate-wild-ignore-table)都不符合,则最后判断是否有(replicate-do-table or replicate-wild-do-table),如果有,则ignore & exit。如果没有,则execute & exit。

    参考:

    http://www.programgo.com/article/65275316286/;jsessionid=37FD76EF9B334864CF9D61E0803001AB

    http://keithlan.github.io/2015/11/02/mysql_replicate_rule/

  • MySQL-FAQ 升级MySQL5.6导致的字符序错误问题

    背景

    用户反馈进行数据库自动迁移的时候失败了,我们的迁移逻辑很简单,用mysqldump将数据库导出来再灌到目标库进去。 于是我手动执行复现发现确实是mysqldump失败了。并且是由于这个库中的几张表导出失败了,但其他用户的数据库没有这个问题。

    然后登陆机器查看这两张表,但是却发现无从下手,因为不管我是select还是show table status再或者是show create table 都会报找不到253这个编码的字符序这个错误:

    MySQL – ERROR 1273 (HY000): Unknown collation ‘#253’ in table ‘some-table’ definition

    内容

    根据这个报错我觉得可能是数据库不支持这个编号为253的字符序了。于是我执行了一下show collation like “utf8_general50_ci”;发现果然是空的!!  再加上我联想到我刚升级完5.6。于是我决定把他放在5.5下面看看,因为我在5.5下面执行了show 语句发现是存在的!

    我进去数据库的data文件看了一眼,发现他是个myisam引擎的,于是就好办了。直接将.frm和.MYI和.MYD拷贝到5.5的一个实例下。发现是ok的。  然后我就可以查看表结构了,发现这两表都有一个字段是这样的:

    city varchar(32) CHARACTER SET utf8 COLLATE utf8_general50_ci NOT NULL

    而我在5.5下执行

    mysql> show collation like “utf8_general50_ci”;

    | Collation         | Charset | Id  | Default | Compiled | Sortlen |

    | utf8_general50_ci | utf8    | 253 |         | Yes      |       1 |

    但是我在5.6下执行却是空的! 也就是说5.6是不支持这种字符序列了。

    总结

    当我们的表字段中有类似的字符序应用时:utf8_general50_ci,我们升级5.6是不会有什么问题的,但是升级以后这个表却无法访问了。无法进行任何操作了。  想修改字符序都不行。

    —— 如果是MyISAM引擎的表,那么直接拷贝到一个5.5的实例上,然后将表用mysqldump出来,在dump出来的文件中将字段值的字符序去掉或修改。然后将这个文件灌入到数据库中就好了。

    —— 但如果是InnoDB表的,那就悲催了。你可能要起一个5.5的临时实例,然后再重复上面的步骤。