MySQL双主复制的主备数据一致性知多少

分享到

作者：Eugene ，发布于2012-11-8

为提高MySQL服务器提供数据服务的可用性和可靠性，实际生产环境中，大量使用简洁易行的异步数据复制技术，且多采用双向复制的架构，以便做到自动或人力快速切换的效果。关于MySQL的数据异步复制技术的数据一致性，在推出支持基于行、混合模式复制之后，是否真如手册所言，彻底解决数据复制的一致性呢？关于二进制日志登记模式的知识，大家可以阅读曾写过的文章，超链接地址：解读MySQL事务的隔离级别和日志登记模式选择技巧。

自从本人使用mysql复制技术以来，就一直对业务场景为：用户自身的操作行为，会使用户之间的数据操更改存在交叉行为，若使用双向复制的架构，但是不要对数据库的更新操作进行负载均衡，也即更新操作会均衡或非均衡方式，发送给2台服务器同时进行处理，而应该坚持把写操作只发送给其中一台数据库服务器或称MASTER的数据库服务器。常用的双向复制技术架构，按处理读写业务方式分，有三种提供数据服务的方式，如图1-1：

建议大家使用图1-1中的前二种方式，第三种提供数据服务的方式，在大多数业务场景下，建议大家谨慎使用，主要是指用户自身的操作行为，能触发程序不仅修改自身的数据信息，还会修改其他用户的数据信息的场景。那么接下来的内容，将给大家介绍MySQL基于行、混合模式数据复制的架构中，Slave端的SQL线程是如何根据从Master端读取的二进制格式的SQL语句，更新Slave端的数据。

n 测试环境

操作系统：CentOS release 5.4

MySQL版本号：5.1.40

存储引擎：built-in InnoDB

事务隔离级别（注：transaction_isolation）：REPEATABLE-READ

复制模式（注：binlog-format）：row、mixed

复制环境：同一台服务器上跑了两个mysqld服务，端口号分别为3306、3307，且做双主复制，测试阶段以3306端口跑的mysqld服务为主服务器，

3307端口跑的mysqld服务为备服务器。

测试用例的表结构和初始化数据：

CREATE DATABASE eugene CHARACTER SET ‘utf8′ COLLATE ‘utf8_general_ci’;

USE eugene;

CREATE TABLE test_repl_primary(ID INT UNSIGNED NOT NULL AUTO_INCREMENT,

email VARCHAR(40) NOT NULL,

gmt_create TIMESTAMP NOT NULL DEFAULT ’0000-00-00 00:00:00′,

PRIMARY KEY(ID)

)ENGINE=InnoDB CHARACTER SET ‘utf8′ COLLATE ‘utf8_general_ci’;

INSERT INTO test_repl_primary(email,gmt_create) VALUES

(CONCAT(SUBSTRING(RAND(),4,10),’@qq.com’),DATE_ADD(NOW(),INTERVAL -SUBSTRING(RAND(),4,2) DAY)),

(CONCAT(SUBSTRING(RAND(),4,10),’@qq.com’),DATE_ADD(NOW(),INTERVAL -SUBSTRING(RAND(),4,2) DAY));

动态随机生成的数据：

n 测试和反馈信息

我们先测试复制模式为ROW的数据一致性情况，再测试复制模式为MIXED的数据一致性情况，测试大致步骤：

(1). 人为事先破坏主备服务器上的数据一致性；

(2). 人为比对主备数据库中，即将要被修改的数据；

(3). 在主服务器上执行测试用的更新数据的SQL语句，为测试有效，全为UPDATE语句；

(4). 人为比对主备数据库中被修改的数据信息，及复制是否正常；

(5). 翻译主服务器上对应登记的二进制日志内容；

u 基于ROW的复制

1.1. 表上有主键，根据主键更新数据

在Slave_3307上执行：

SET sql_log_bin=0;

UPDATE test_repl_primary SET email=CONCAT(SUBSTRING(RAND(),4,10),’@qq.com’) WHERE ID=1;

执行之后主备服务器数据的异同，详细信息见图1-3：

在Master_3306上执行：

UPDATE test_repl_primary SET email=CONCAT(SUBSTRING(RAND(),4,10),’@qq.com’) WHERE ID=1;

查看复制信息一切正常，比对主备的数据信息如图1-4：

再看主服务器上登记的二进制日志信息：

### UPDATE eugene.test_repl_primary

### WHERE

### @1=1 /* INT meta=0 nullable=0 is_null=0 */

### @2=’8724544093@qq.com’ /* VARSTRING(120) meta=120 nullable=0 is_null=0 */

### @3=1296468192 /* TIMESTAMP meta=0 nullable=0 is_null=0 */

### SET

### @1=1 /* INT meta=0 nullable=0 is_null=0 */

### @2=’4070138217@qq.com’ /* VARSTRING(120) meta=120 nullable=0 is_null=0 */

### @3=1296468192 /* TIMESTAMP meta=0 nullable=0 is_null=0 */

小结：

通过人为制造的主备服务器的数据不一致后，再根据主键去更新数据，二进制日志中记录所有字段的更新前和更新后的值，且把更新前的字段值都写到WHERE子句部分，但是二进制SQL语句复制到Slave端，依然是根据主键更新Slave端的数据，故无法发现数据的差异，除非主键值不存，也即找不到纪录才能发现差异。

1.2. 表上有主键，根据非索引条件更新数据

在Slave_3307上执行：

SET sql_log_bin=0;

UPDATE test_repl_primary SET gmt_create=DATE_ADD(NOW(), INTERVAL -SUBSTRING(RAND(),4,2) DAY) WHERE email=’1206352953@qq.com’;

执行之后主备服务器数据的异同，详细信息见图1-5：

在Master_3306上执行：

UPDATE test_repl_primary SET gmt_create=DATE_ADD(NOW(), INTERVAL -SUBSTRING(RAND(),4,2) DAY) WHERE email=’1206352953@qq.com’;

查看复制信息一切正常，比对主备的数据信息如图1-6：

再看主服务器上登记的二进制日志信息：

### UPDATE eugene.test_repl_primary

### WHERE

### @1=2 /* INT meta=0 nullable=0 is_null=0 */

### @2=’1206352953@qq.com’ /* VARSTRING(120) meta=120 nullable=0 is_null=0 */

### @3=1296295392 /* TIMESTAMP meta=0 nullable=0 is_null=0 */

### SET

### @1=2 /* INT meta=0 nullable=0 is_null=0 */

### @2=’1206352953@qq.com’ /* VARSTRING(120) meta=120 nullable=0 is_null=0 */

### @3=1289732479 /* TIMESTAMP meta=0 nullable=0 is_null=0 */

小结：

即使主备服务器上的数据存在不一致，非索引字段条件更新数据的SQL语句，复制到Slave端，其更新Slave端数据的根据，依然是主键，而不是按登记到二进制日志的WHERE子句中所有出现的字段条件值，故无法发现数据的不一致。

1.3. 表上有主键/非空唯一索引，且有普通索引，根据普通索引条件更新数据

在Slave_3307上执行：

SET sql_log_bin=0;

UPDATE test_repl_primary SET gmt_create=DATE_ADD(NOW(), INTERVAL -SUBSTRING(RAND(),4,2) DAY) WHERE email=’4399836942@qq.com’;

执行之后主备服务器数据的异同，详细信息见图1-7：

在Master_3306上执行：

ALTER TABLE test_repl_primary ADD INDEX idx_email(email);

UPDATE test_repl_primary SET gmt_create=DATE_ADD(NOW(), INTERVAL -SUBSTRING(RAND(),4,2) DAY) WHERE email=’4399836942@qq.com’;

查看复制信息一切正常，比对主备的数据信息如图1-8：

再看主服务器上登记的二进制日志信息：

### UPDATE eugene.test_repl_primary

### WHERE

### @1=3 /* INT meta=0 nullable=0 is_null=0 */

### @2=’4399836942@qq.com’ /* VARSTRING(120) meta=120 nullable=0 is_null=0 */

### @3=1291543392 /* TIMESTAMP meta=0 nullable=0 is_null=0 */

### SET

### @1=3 /* INT meta=0 nullable=0 is_null=0 */

### @2=’4399836942@qq.com’ /* VARSTRING(120) meta=120 nullable=0 is_null=0 */

### @3=1290424311 /* TIMESTAMP meta=0 nullable=0 is_null=0 */

在Slave_3307上执行：

SET sql_log_bin=0;

UPDATE test_repl_primary SET email=’test_index_not_using@sina.com’ WHERE email=’1451340812@qq.com’;

执行之后主备服务器数据的异同，详细信息见图1-9：

在Master_3306上执行：

UPDATE test_repl_primary SET gmt_create=’2011-10-5 00:00:00′ WHERE email=’1451340812@qq.com’;

执行之后再对比主备上的数据，详细信息见图1-10：:

我们再看主服务器上登记的二进制日志信息：

### UPDATE eugene.test_repl_primary

### WHERE

### @1=8 /* INT meta=0 nullable=0 is_null=0 */

### @2=’1451340812@qq.com’ /* VARSTRING(120) meta=120 nullable=0 is_null=0 */

### @3=1293357792 /* TIMESTAMP meta=0 nullable=0 is_null=0 */

### SET

### @1=8 /* INT meta=0 nullable=0 is_null=0 */

### @2=’1451340812@qq.com’ /* VARSTRING(120) meta=120 nullable=0 is_null=0 */

### @3=1317744000 /* TIMESTAMP meta=0 nullable=0 is_null=0 */

小结：

有主键/普通索引的情况下，分二种情况测试，根据普通索引条件更新记录，即使我们的普通索引值不存在，也无法发现数据的差异，可以印证此情况下，Slave端只是根据主建的值去更新备库上的数据。

2.1.表上无主键/非空唯一索引，但有普通索引，根据普通索引条件更新数据

在Slave_3307上执行：

SET sql_log_bin=0;

UPDATE test_repl_primary SET gmt_create=DATE_ADD(NOW(), INTERVAL -SUBSTRING(RAND(),4,2) DAY) WHERE email=’2739752989@qq.com’;

执行之后主备服务器数据的异同，详细信息见图1-11：

在Master_3306上执行：

ALTER TABLE test_repl_primary MODIFY ID INT UNSIGNED NOT NULL,DROP PRIMARY KEY;

UPDATE test_repl_primary SET gmt_create=DATE_ADD(NOW(), INTERVAL -SUBSTRING(RAND(),4,2) DAY) WHERE email=’2739752989@qq.com’;

查看复制信息一切正常，比对主备的数据信息如图1-12：

再看主服务器上登记的二进制日志信息：

### UPDATE eugene.test_repl_primary

### WHERE

### @1=4 /* INT meta=0 nullable=0 is_null=0 */

### @2=’2739752989@qq.com’ /* VARSTRING(120) meta=120 nullable=0 is_null=0 */

### @3=1295863392 /* TIMESTAMP meta=0 nullable=0 is_null=0 */

### SET

### @1=4 /* INT meta=0 nullable=0 is_null=0 */

### @2=’2739752989@qq.com’ /* VARSTRING(120) meta=120 nullable=0 is_null=0 */

### @3=1293534996 /* TIMESTAMP meta=0 nullable=0 is_null=0 */

小结：

在删除表上的主键之后，人为把主备服务器上的数据修改成有差异的状态，在主服务器上执行根据索引条件去更新的SQL语句，MySQL发现主备的数据存在不一致，从而复制的SQL 线程停止，从中我们可以得知，Slave端此情况下，不是根据普通索引值条件或者执行时写的WHERE子句条件，而是根据登记二进制日志的WHERE子句所有字段去匹配的。

2.2.表上无主键/非空唯一索引，但有普通索引，根据非索引条件更新数据

在Slave_3307上执行：

SET sql_log_bin=0;

UPDATE test_repl_primary SET email=CONCAT(SUBSTRING(RAND(),4,10),’@qq.com’) WHERE gmt_create=’2010-12-18 18:03:12′;

执行之后主备服务器数据的异同，详细信息见图1-13：

在Master_3306上执行：

UPDATE test_repl_primary SET email=CONCAT(SUBSTRING(RAND(),4,10),’@qq.com’) WHERE gmt_create=’2010-12-18 18:03:12′;

查看复制信息一切正常，比对主备的数据信息如图1-14：

再看主服务器上登记的二进制日志信息：

### UPDATE eugene.test_repl_primary

### WHERE

### @1=5 /* INT meta=0 nullable=0 is_null=0 */

### @2=’2456801983@qq.com’ /* VARSTRING(120) meta=120 nullable=0 is_null=0 */

### @3=1292666592 /* TIMESTAMP meta=0 nullable=0 is_null=0 */

### SET

### @1=5 /* INT meta=0 nullable=0 is_null=0 */

### @2=’8535819257@qq.com’ /* VARSTRING(120) meta=120 nullable=0 is_null=0 */

### @3=1292666592 /* TIMESTAMP meta=0 nullable=0 is_null=0 */

# at 330

小结：

表上无主键，又普通索引，根据非索引字段条件去更新数据，Slave端使用登记到二进制日志的WHERE子句所有字段值，作为条件的方式更新Slave端的数据，从而发现主备数据不一致的信息，而中止SQL线程的执行。

3.表上无主键/非空唯一索引/普通索引，更新表中的数据

上述的2.1、2.2小节的测试数据、分析结果，可以看到无主键的情况，Slave端是根据二进制日志登记的WHERE子句中所有字段，也即表上的所有字段值作为条件方式更新Slave端的数据，此章节的测试信息业印证此推断，且与2.2小节的测试用例和测试环境几乎一样，为减少篇幅就不贴上测试过程和比对的数据信息。

u 基于MIXED的复制

1.1. 表上无主键，有普通索引，主备上某记录的索引字段的值改成不一致，且根据索引更新

在Slave_3307上执行：

SET sql_log_bin=0;

UPDATE test_repl_primary SET email=’test_mixed_index@sina.com’ WHERE email=’1451340812@qq.com’;

执行之后主备服务器数据的异同，详细信息见图1-15：

在Master_3306上执行：

UPDATE test_repl_primary SET ID=SUBSTRING(RAND(),4,5) WHERE email=’1451340812@qq.com’;

查看复制信息一切正常，比对主备的数据信息如图1-16：

再看主服务器上登记的二进制日志信息：

#110215 2:58:25 server id 3306 end_log_pos 775 Rand

SET @@RAND_SEED1=85324077, @@RAND_SEED2=381174855/*!*/;

# at 775

#110215 2:58:25 server id 3306 end_log_pos 925 Query thread_id=28 exec_time=0 error_code=0

SET TIMESTAMP=1297709905/*!*/;

UPDATE test_repl_primary SET ID=SUBSTRING(RAND(),4,5) WHERE email=’1451340812@qq.com’

小结：

基于MIXED复制模式，二进制SQL日志文件是基于命令行方式登记，人为制造主备数据不一致，且修改的为用作测试条件的字段的值，造成主上可以正确更新到记录，而备库是无法更新到数据，即使此情况下，MySQL也无法发现主备数据不一致的信息。

1.2.表上无主键/索引，把master改成与slave数据不一致的前提时

在Master_3306上执行：

ALTER TABLE test_repl_primary DROP INDEX idx_email;

SET sql_log_bin=0;

UPDATE test_repl_primary SET ID=7000 WHERE email=’6297089947@qq.com’;

执行之后主备服务器数据的异同，详细信息见图1-17：

在Master_3306上执行：

SET sql_log_bin=1;

UPDATE test_repl_primary SET ID=7 WHERE email=’6297089947@qq.com’;

查看复制信息一切正常，比对主备的数据信息如图1-18：

再看主服务器上登记的二进制日志信息：

#110214 19:51:11 server id 3306 end_log_pos 306 Query thread_id=3 exec_time=0 error_code=0

use eugene/*!*/;

SET TIMESTAMP=1297684271/*!*/;

UPDATE test_repl_primary SET ID=7 WHERE email=’6297089947@qq.com’

小结：

无主键及任何索引的环境，人为把主备的数据改成不一致，然后更新此存在异同的字段的值，MySQL无法发现主备数据的差异。

2.有主键无普通索引、有主键有普通索引、普通索引三种环境

有主键无普通索引、有主键有普通索引、普通索引三种环境下，重新跑测试1.1、1.2小结的内容，得到的信息依然一样。

n 总结

此次，只测试了MySQL支持的复制模式：ROW、MIXED，对于基于STATEMENT复制模式，其优缺点是大家众所周知，而5.1及后续版本支持的基于ROW和MIXED模式，在手册上有相关数据一致性的描述，以及网络上有很多介绍，传递一个信息：能解决复制过程中的数据一致性问题，其实这句话存在正确的一面，然而也容易误导大家，正确一面就是：能把在主服务器上执行的SQL或转换后的信息正确地传递给备用服务器，换句话说二进制日志再次执行，能重现主服务器上执行的一样效果，那么对于主备上数据一致性检测及是否能确保一致就容易被忽略了，为此特意测试和撰写一篇文章分享出来，使更多的朋友正视依然存在的缺陷，以及想办法规避。针对测试而得到的信息，以及一些非测试而知道的常理，总结的内容如下：

n 基于ROW复制模式（备注：行复制模式）

u 表上是否有主键或非空唯一索引，对识别主备服务器上的数据是否一致，有重大影响；

u 表上有主键或非空唯一索引时，Slave端SQL线程是根据解析出来的二进制日志文件中主键的值，查找更新记录；

u 表上有主键或非空唯一索引时，除作为主键或非空唯一索引字段值外，MySQL无法发现主备库上其他字段的数据是否存在差异；

u 无论表上是否有主键或非空唯一索引，相同的SQL在主备库上执行能查找到不同数量的记录个数，且是主服务器多于备服务器的情况下，MySQL能发现其异同，并且会终止复制的SQL线程执行，否则不能；

u 表上无主键或非空唯一索引时，不管主服务器上执行的SQL是否根据普通索引更新，Slave端SQL线程都是根据被更新表上所有字段的值，作为WHERE条件更新记录；

n 基于MIXED复制模式，登记二进制日志为STATEMENT格式（备注：混合复制模式）

u 无论表上是否有主键、非空唯一索引、普通索引，对识别主备服务器上的数据一致性无任何影响；

u 二进制日志文件中登记日志信息为非做任何转换过的SQL，Slave端SQL线程也是根据二进制日志中增加的额外提示信息和主服务器上执行的SQL，更新备库上的数据；

u 同一条SQL在主备服务器上执行时，受影响的行数不同，以及备库上是否有受影响的记录，MySQL都无法辨别；