MySQL replication cluster 若干问题及方案

1. 基本问题概述
主从镜像的方式，排除硬件等方面的问题，正常情况下基本上是可以很好的做到同步，从服务器与主服务器之间感觉不到差距。当主服务器当机时，利用 HA 软件如 heartbeat 或 lvs，使从服务器接替主服务器继续对外服务，这通过由从服务器接管两机共用的浮动 IP(float ip)来实现。

这样看来是不错，但实际上却有潜在的问题存在。其核心实质是，主从之间的镜像并不是一个原子操作，它们之间的同步由于系统负载、网络带宽以及其他一些意料之外的可能而必然存在一个时延。正常情况下这个时延很小可以忽略，但当系统负载和网络流量不断增加的时候，没有任何机制保证时延可以控制在一个可以接受的范围之内。 这会带来如下问题：

(1) 首先 slave 的读取线程 IO_THREAD 和执行线程 SQL_THREAD 并不完全一致，在 slave 上用"show slave status \G;"察看数据库的情况可以看到 read_log_pos 和 exec_log_pos 两个值，正常情况是，read_log_pos > exec_log_pos，如果 read_log_pos > exec_log_pos 时 master 当机，并且从服务器接管之后仍然 read_log_pos > exec_log_pos，这时新插入的数据就可能会和没有执行完的这部分日志发生键值冲突，特别是对于 AUTO_INCREMENT 字段，例如一个帐户数据库的 UID 字段。 SQL_THREAD 出错退出，余下的这部分日志将不能自动执行完成。我将其称为执行差或者执行空洞。

(2) 主从之间的时延使得从服务器接管之后，可能并没有将主服务器的二进制日志全部读取过来，也就是丢失了一部分记录。如果主服务器彻底崩溃且不可重新使用，则这部分数据将无法使用常规的方法恢复。也许可以使用一些数据恢复的办法从物理硬盘上找回数据，当然通常这部分记录的数量不会很大，而数据恢复的代价则可能很大。

但是，如果主服务器可以重新启动，则其重启后就会发生问题。重启后，主服务器重新接管，从服务器重新连接主服务器请求日志，但由于从服务器接管时已经插入了一些记录，这部分记录就极可能会和请求过来的记录发生键值冲突。从服务器将放弃同步，而两台服务器的数据则不统一。

即使进行一些设置，让主服务器不进行接管而仍然由从服务器提供服务，但数据不统一和键值冲突的情况仍然存在。而不论是把主服务器多余的日志拿到从服务器上执行，还是将从服务器接管后新增的数据加入到主服务器上，都必须首先解决这个键值冲突的问题。所以问题变成了如何将它们合并到一起以达到数据的一致，并重新建立同步。 由于应用条件的限制，这并不容易做到。 在下面具体分析。

另外，对于在一定环境下，数据库同步所能承受的压力状况及其极限并不清楚。即在多大的连接数、多大的网络流量及其带宽和多大的负载的情况下，同步的时延究竟会有多大。我没有现成的数据参考，现在也还没有想到合适的测试方案，而且使用 shell 编写的自动生成 SQL语句的脚本其执行效率又太低，同时又不可能建立一个类似生产环境的测试环境来进行测试。

2. "双向同步"
为了解决这个数据不一致的问题，最直接的思路可能会是双向同步，即两台数据库互为主从。但是，如果两台服务器同时对外提供服务，那么很快数据就会被弄脏，两者都将放弃同步；当然如果只由一台主机对外提供服务，但由于镜像并不是原子操作，所以单纯用双向同步依然不能解决问题。实际的情况和上一节提到的情况完全一样，因为正常时并不直接向 slave 插入或修改数据，那么和单向同步就完全是一种情景。

事实上，对于双向同步，在 MySQL 的文档中是有说明的：

* It is safe to connect servers in a circular master/slave relationship with the --log-slave-updates option specified. Note, however, that many statements do not work correctly in this kind of setup unless your client code is written to take care of the potential problems that can occur from updates that occur in different sequence on different servers.

This means that you can create a setup such as this:
A -> B -> C -> A

Server IDs are encoded in binary log events, so server A knows when an event that it reads was originally created by itself and does not execute the event (unless server A was started with the --replicate-same-server-id option, which is meaningful only in rare cases). Thus, there are no infinite loops. This type of circular setup works only if you perform no conflicting updates between the tables. In other words, if you insert data in both A and C, you should never insert a row in A that may have a key that conflicts with a row inserted in C. You should also not update the same rows on two servers if the order in which the updates are applied is significant.

* Note that when you are using replication, all updates to the tables that are replicated should be performed on the master server. Otherwise, you must always be careful to avoid conflicts between updates that users make to tables on the master and updates that they make to tables on the slave.

http://dev.mysql.com/doc/refman/4.1/en/replication-features.html

Shutting down the slave (cleanly) is also safe, as it keeps track of where it left off. Unclean shutdowns might produce problems, especially if disk cache was not flushed to disk before the system went down. Your system fault tolerance is greatly increased if you have a good uninterruptible power supply. \label{log_hole}Unclean shutdowns of the master may cause inconsistencies between the content of tables and the binary log
in master; this can be avoided by using InnoDB tables and the --innodb-safe-binlog option on the master. See Section 5.10.4, “The Binaryy Log”.

*http://dev.mysql.com/doc/refman/4.1/en/replication-intro.html

Q: What issues should I be aware of when setting up two-way replication?

A: MySQL replication currently does not support any locking protocol between master and slave to guarantee the atomicity of a distributed (cross-server) update. In other words, it is possible for client A to make an update to co-master 1, and in the meantime, before it propagates to co-master 2, client B could make an update to co-master 2 that makes the update of client A work differently than it did on co-master 1. Thus, when the update of client A makes it to co-master 2, it produces tables that are different than what you have on co-master 1, even after all the updates from co-master 2 have also propagated. This means that you should not chain two servers together in a two-way replication relationship unless you are sure that your updates can safely happen in any order, or unless you take care of mis-ordered updates somehow in the client code.

You must also realize that two-way replication actually does not improve performance very much (if at all), as far as updates are concerned. Both servers need to do the same number of updates each, as you would have one server do. The only difference is that there is a little less lock contention, because the updates originating on another server are serialized in one slave thread. Even this benefit might be offset by network delays.

*http://dev.mysql.com/doc/refman/4.1/en/replication-faq.html

在以上的 FAQ 文档中，还提到一种 HA 的方案，可以看到，其方法是当主服务器宕机之后，从服务器成为主服务器，而原来的主服务器即使重启，也不会再成为主服务器。

3. 具体情况及解决方案分析
在开始考虑同步恢复的解决方案之前，先解决一个问题，即数据库缓存和其二进制日志之间存在的差值，可以称之为“日志空洞”。默认情况下，为了提高性能，最近进行的更新和插入等操作并不会马上被记录到二进制日志中，而是放置在缓存中。如果这时服务器出现问题，那么其自身的数据库实际内容和其二进制日志直接就已经存在不一致了。 此时可以使用 --innodb-safe-binlog 来启动数据库(如果使用 INNODB 类型)，或在 /etc/my.cnf 或数据库中设置 sync_binlog 变量。 参见 page \pageref{log_hole}, section \ref{log_hole}。

首先来看看键值冲突是怎样发生的。 以具体情况，AUTO_INCREMENT 类型的字段来看：

master 
------------------->[2]---->[3]     [5]----->
slave,state 2
--------->[1]------>[2]---->[3]---->[4]
slave,state 1
--------->[1]---------------------->[4]

[1] ID = 8000, slave 执行 master 的日志所到达的位置，exec_master_log_pos
[2] ID = 9000, slave 读取到的 master 的日志的位置，read_master_log_pos
[3] ID = 10000, master 实际执行到的位置，也就是故障点
[4] ID = 12000, slave 交出控制权
[5] ID = 10000 = p3，master 恢复并重新接管

可以看到，[4],[5] 实际上是同一个时间点，但实际的 ID 值却不一样。 slave state 1 即第一节提到的第一种情况，slave state 2 即第二种情况。如果从服务器本身情况良好，则是第二种情况，也就是说，从服务器将读取到的日志都执行完了才开始对外提供服务器，接受新的数据。为了实现这一点，必须在 HA 切换时对从服务器进行阻塞，直到 relay_master_log_file = master_log_file && exec_master_log_pos = read_master_log_pos。

--read-only

This option causes the slave to allow no updates except from slave threads or from users with the SUPER privilege. This can be useful to
ensure that a slave server accepts no updates from clients.

This option is available as of MySQL 4.0.14.

http://dev.mysql.com/doc/refman/4.1/en/replication-options.html

使用该选项启动与在数据库中使用 "flush tables with read lock;" 的效果是不同的，后者会阻止一切的写操作，必然影响到 replication。同样可以通过设置 read_only=ON 变量来实现这一点。

然后需要一个脚本，在从服务器的 exec = read 后解除这个 read_only。例如：

#!/bin/sh
USER=$1
PASS=$2
while 1; do
    ss=`echo "show slave status \G;" | mysql -u $USER -p$PASS`
    rlogfile=`echo $ss | awk -F': ' '/Master_Log_File/ {print $2}'`
    elogfile=`echo $ss | awk -F': ' '/Relay_Master_Log_File/ {print $2}'`
    rlogpos=`echo $ss | awk -F': ' '/Read_Master_Log_Pos/ {print $2}'`
    elogpos=`echo $ss | awk -F': ' '/Exec_Master_Log_Pos/ {print $2}'`
    if [ $rlogfile -eq $elogfile ] && [ $rlogpos -eq $elogpos ]; then
        echo "set global read_only=OFF;" | mysql -u $USER -p$PASS
        break
    fi
done

那么如何利用 LVS 在其切换时自动运行该脚本呢？

于是现在就只有 slave state 2 的情况。前面已经说过，[4],[5] 是同一时间点，但 ID 却不同；同时比较[2]-[4](9000-12000) 和 [2]-[3](9000-10000) 的记录，很明显出现了重叠。此时从服务器只能放弃同步。

如何恢复则可能取决于具体的应用了。简单的方案是，如果记录的 ID 值可以更改，则可以将 p2-p3 部分的记录的 ID 值改为一个较大的值，需要注意的是这时所有含有 ID 字段的表都需要 update 才能保持数据的一致，因为某些表的该字段并非 primary key。

我遇到过的情况是，记录的 UID 值不允许更改，因为这个值被告知用户作为登陆时的一个标识，并且在其他多处地方使用到——即使 slave 和 master 之间产生的差距很小，即上图中 [2]-[3] 只有数条记录也不行。我不知道对于数条记录进行手工解决是否可行，我的意思是，在更改了 ID 值后知会相关用户，因为这些 ID 是新加入的，应该还没有进行过太多其他操作。但最大的问题在于，仅仅如此，则没有任何机制可以保证相差的记录可以保持在一个可以接受的范围之内。

我考虑的另一个方案是可以使用一个监测程序，每隔数秒则查询一次主服务器数据库，记录日志当前位置，并记录所有含有 AUTO_INCREMENT ID 的表的 MAX(ID) (使用 SQL_NO_CACHE 查询)，如果主服务器不可用，则连接从服务器，在其中相应的表中插入 MAX(ID) 得到的值(或+1)，从而预留一部分“空间”，并将当前相关的结果记入文件，以备将来恢复之用。

master
------------------->[2]---->[3]     [5]
slave,state 2
--------->[1]------>[2]     [3]---->[4]

但遗憾的是，这也只能是理想状态，因为使用外部机制总归是不能避免时延，所以记录的 MAX(ID) 可能并不是真实的 MAX ID，这又回到了前面的情况；而且记录 log_pos 和 MAX(ID) 的操作如果不能在一个原子内完成，则它们自身之间又存在差异了，在恢复时利用这些数据就不够准确，可能会有问题。

而且即使是在理想的情况下，使用这种方法在恢复的时候操作也会比较麻烦。 我觉得唯一的好处就是它记录了一个最接近故障点的 ID 值，这样可以将记录差距控制在一个比较小的范围之内。 但是显然不能只让它允许在 slave 上。事实上，如果这一脚本(包括恢复程序)得以实现，那么其实现的是一种集群控制器的作用了。 而我对于集群的理解还比较粗浅，不知能否利用现有工具。

{
% 可以将 master [2]-[3] 的日志放到 slave 上执行使 slave 完整，也可以将 slave [3]-[4] 在 master 执行使 master 完整。 考虑到 master [2]-[3] 通常应该会比较少，所以前者更好。

% 如果使 master 完整，一种方法是直接执行 slave [3]-[4] 的日志，但这时要注意 slave 的同步机制会使得从服务器重复执行这部分记录。

% 如果使 slave 完整，则也可以使用同步方式。
}

由于上述方法中存在的限制，所以不再详加讨论。 而且以上只讨论了键值冲突的问题，即由于 INSERT 操作所造成的问题，而没有考虑 UPDATE 时会是什么情况。如果实在不能允许 ID 值得更改，则只能考虑在从服务器中禁止 INSERT。 那么，开放 UPDATE 权限是否可行？

仅仅开放从服务器的 UPDATE 权限，则 AUTO_INCREMENT 类型的 ID 值不会自增，可以避免键值冲突，事实数据差异依然存在。 在前文引用的 MySQL 的文档中已经说明，除非这种变动没有对于顺序的要求。

例如，对于点数这样的数据，可能出现如下的情况：

master             num=1000             -200                       num=800
----------------------------->[2]--------->[3]          [5]--------->
insert + update                |
                               \---------replication--------\
                                                            |
slave                                   -200                V      num=600
------------------->[1]------>[2]---------------------->[4]--------->
update only

方案一是使 slave [2]-[4] 的数据能够在 master 上恢复，两者就可以恢复同步：如果将 slave [2]-[4] 的日志拿到主服务器上执行，则需要注意 stop slave，因为同步机制会使这部分数据重复执行，并且这时要禁止向 master 写入其他数据(可利用前面提到的 read_only 变量)，因为待执行完成后，需要在 slave 上使用 change master 来调整从服务器的读取位置来跳过这一段，如果这时有其他有效数据写入 master，就会“跳得太多”。

当然也可以不考虑从服务器，而待主服务器恢复之后，“洗”掉从服务器的数据重来或使用另外的新的从服务器。 也就是重建一个从服务器。这时，由于会有日志失效处理的问题，所以最好能从一个备份点开始，即先将从服务器恢复到这个备份点，再利用 change master 调整从服务器的状态。这个备份点除了包含之前的完整数据之外，还必须有该点主服务器日志位置的记录，否则从服务器恢复到备份点后将无法得知应该将其调整到哪个日志点。
% 或者在备份点对主服务器的二进制日志进行实效处理，\textbf{但失效日志的内容必须和备份的内容完全一致}，

如果没有常规备份，则需要按如下操作步骤：
* master read lock
* 拷贝 master 数据目录(/usr/local/mysql/var) 下相应的数据库目录及 innodb 表空间到从服务器；RIGHT?
* 记录 master 此时的日志执行位置
* master unlock
* slave 利用拷贝恢复
* slave 调整到刚才记录的位置

第二种方案是，由于从服务器没有数据插入，故不会产生键值冲突，从服务器自动同步 master [2]-[3] 以及 master [5]-
的数据，所以从服务器的数据是完整的，于是可以将从服务器变为主服务器，而将原来的主服务器变为从服务器(可以“洗掉”重来或者直接作为从服务器——参考方案三)。但这时需要停止从服务器的镜像，更改两机数据库的配置，并且 LVS 或 heartbeat 必须重新分配主从，以防止原来的主服务器意外接管。

这样似乎很麻烦(当然应该可以利用脚本来自动化)。第三种方案就考虑将 master 配置为 slave 的一个临时从服务器，即将两者配置为临时的双向同步。由于此时主服务器已经接管，所以不会有数据写入从服务器，为保险起见，可以设置 slave 的 read_only。 注意 slave 必须设置了 --log-slave-updates(在 /etc/my.cnf 中设置了 log-slave-updates)，由于在日志中记入了 server-id，主服务器在同步(注意仅限于同步时)会跳过由他自己产生的纪录。这样确实能够工作。 用如下方法可以看到效果：

master> use sampledb
master> select * from POINTBONUS0000 where ACCOUNT='Chowroc';
+---------+---------+-------+-------+-----------+
| UID     | ACCOUNT | POINT | BONUS | HADFILLED |
+---------+---------+-------+-------+-----------+
| 1000437 | Chowroc |     0 | 10000 |         0 |
+---------+---------+-------+-------+-----------+
1 row in set (0.01 sec)
master# mysqladmin -u root -p$PASS shutdown

slave> slave stop;
slave> begin;
slave> update POINTBONUS set BONUS=BONUS-6000 where ACCOUNT='Chowroc';
slave> commit;
slave> select * from POINTBONUS0000 where ACCOUNT='Chowroc';
+---------+---------+-------+-------+-----------+
| UID     | ACCOUNT | POINT | BONUS | HADFILLED |
+---------+---------+-------+-------+-----------+
| 1000437 | Chowroc |     0 |  4000 |         0 |
+---------+---------+-------+-------+-----------+
1 row in set (0.01 sec)
master> select * from POINTBONUS0000 where ACCOUNT='Chowroc';
+---------+---------+-------+-------+-----------+
| UID     | ACCOUNT | POINT | BONUS | HADFILLED |
+---------+---------+-------+-------+-----------+
| 1000437 | Chowroc |     0 | 10000 |         0 |
+---------+---------+-------+-------+-----------+
1 row in set (0.01 sec)
slave> grant replication slave on *.* to '$SLAVE_RPL_USER'@'$MASTER_ADDR' identified by '$SLAVE_RPL_PASS';

master# vi /etc/my.cnf
master-host = $SLAVE_ADDR
master-user = $SLAVE_RPL_USER
master-password = $SLAVE_RPL_PASS
master-connect-retry = 60
replicate-do-db = sampledb
log-slave-updates
master# mysqld_safe --user=mysql --skip-name-resolve --open-files-limit=20480 &
master> select * from POINTBONUS0000 where ACCOUNT='Chowroc';
+---------+---------+-------+-------+-----------+
| UID     | ACCOUNT | POINT | BONUS | HADFILLED |
+---------+---------+-------+-------+-----------+
| 1000437 | Chowroc |     0 |  4000 |         0 |
+---------+---------+-------+-------+-----------+
1 row in set (0.01 sec)

如果情况仅仅是这样，那么确实有效，因为这些数据是与顺序无关的。而且单纯只考虑这种情况，完全可以更进一步将两者配置为“双向同步”，注意从服务器不能插入。

但这时需要考虑一点，如果在 master [2]-[3] 减少的数值比较大应当如何？ 比如在上例中，如果减少的是 10000，则可能导致“透支”问题，即由于从服务器没有跟上，从服务器不知道数值的变化，此用户的点数没有减少，于是下一次他就多了 10000
点，而如果同步可以恢复，又会出现负值；另外对于点数增加的情况，也可能带来麻烦，即“充值”未能生效。仍必须指出，正常情况下，这个差值不会太大，但以现有结构，我没有找到保证机制，如果涉及到实际的金钱问题，则应当更加小心。

同时，还需要考虑与顺序有关的数据。 如果按照上面的做双向同步，则可能出现如下的情况：

master            pass=str1       str1                    str2      pass=str2
---------------------------->[2]--------->[3]         [5]--------->
insert + update               |                            ^ 
                              |                            |
                              \-------------\/-------------/
                                            || replication
                              /-------------/\-------------\
                              |                            |
slave                         |   str2                     V str1   pass=str1
------------------->[1]----->[2]----------------------[4]--------->
update only

可参考如下操作：

master> use sampledb;
master> select * from ACCSTORE0000 where ACCOUNT='Chowroc';
+---------+---------+--------+-------+------+
| UID     | ACCOUNT | PASSWD | STATE | TYPE |
+---------+---------+--------+-------+------+
| 1000613 | Chowroc | 123456 |     0 |    0 |
+---------+---------+--------+-------+------+
1 row in set (0.00 sec)
slave> select * from ACCSTORE0000 where ACCOUNT='Chowroc';
+---------+---------+--------+-------+------+
| UID     | ACCOUNT | PASSWD | STATE | TYPE |
+---------+---------+--------+-------+------+
| 1000613 | Chowroc | 123456 |     0 |    0 |
+---------+---------+--------+-------+------+
1 row in set (0.00 sec)

slave> slave stop;

master> begin;
master> update ACCSTORE0000 set PASSWD='abcdef' where ACCOUNT='Chowroc';
master> commit;
master> select * from ACCSTORE0000 where ACCOUNT='Chowroc';
+---------+---------+--------+-------+------+
| UID     | ACCOUNT | PASSWD | STATE | TYPE |
+---------+---------+--------+-------+------+
| 1000613 | Chowroc | abcdef |     0 |    0 |
+---------+---------+--------+-------+------+
1 row in set (0.00 sec)

slave> begin;
slave> update ACCSTORE0000 set PASSWD='uvwxyz' where ACCOUNT='Chowroc';
slave> commit;
slave> select * from ACCSTORE0000 where ACCOUNT='Chowroc';
+---------+---------+--------+-------+------+
| UID     | ACCOUNT | PASSWD | STATE | TYPE |
+---------+---------+--------+-------+------+
| 1000613 | Chowroc | uvwxyz |     0 |    0 |
+---------+---------+--------+-------+------+
1 row in set (0.00 sec)

master# mysqladmin -u root -p$PASS shutdown
master# vi /etc/my.cnf
... /*@ (同前) @*/
master# mysqld_safe --user=mysql --skip-name-resolve --open-files-limit=2048 &

slave> slave start;

master> select * from ACCSTORE0000 where ACCOUNT='Chowroc';
+---------+---------+--------+-------+------+
| UID     | ACCOUNT | PASSWD | STATE | TYPE |
+---------+---------+--------+-------+------+
| 1000613 | Chowroc | uvwxyz |     0 |    0 |
+---------+---------+--------+-------+------+
1 row in set (0.00 sec)
slave> select * from ACCSTORE0000 where ACCOUNT='Chowroc';
+---------+---------+--------+-------+------+
| UID     | ACCOUNT | PASSWD | STATE | TYPE |
+---------+---------+--------+-------+------+
| 1000613 | Chowroc | abcdef |     0 |    0 |
+---------+---------+--------+-------+------+
1 row in set (0.00 sec)

如果前述的点数问题和这里的字符问题都不容忽视，那么方案三只能被放弃，同时 slave 将不能允许任何直接的修改操作。 要达到这一点，只需利用前面提到的 read_only 参数即可，它禁止一切非 replication 和非 super privileges 的写操作。这样，当主服务器当机时，只能等待主服务器恢复后重新接管，或者可以确定主从直接没有差以及主服务器彻底崩溃时，手工解除从服务器的 read_only 值，停止其镜像并更改配置，使其作为主服务器接受数据插入和修改的请求。