mysql 主从复制双主架构在线修改表结构、在线DDL
时间:2015-10-10 13:59 来源:linux.it.net.cn 作者:IT
MySQL主主复制，在线修改表结构
一直以为双主架构能解决mysql的表在线DDL的需求，但没有实际测试经验。后来发现一直的想法还是有问题的。这里做一个测试。
双主架构，在线DDL的实现步骤是：
1，首先搭建主主复制架构，只有一台提供服务，这里设为A库，另一台B库空闲
2，A库停止复制stop slave
3，在B库上修改表结构，如加字段
B库完成表修改后，因为A库暂停了复制，所以DDL语句不会复制到A库。对A库没影响
4，切换读写到B库。B库提供读写服务，A库空闲
5，开启A库复制。
首先A库会复制刚才的DDL语句，实现表结构修改，然后复制数据
大致一看，上面的步骤没有问题。A/B库修改表结构，都没有影响数据库对外服务。其实隐藏着一个问题。假设修改的表为X，在步骤3 B库修改X表时，A库上X表数据有更新，这些更新复制到B库，会等待锁，因为X表在进行DDL操作。B库修改完成以后，这些更新语句可能会执行失败，因为表结构改变可能导致更新语句出错。现实际测试如下： www.2cto.com
测试1：
A库上建表，插入数据，停止复制
mysql> create table tbl_testonlineddl(id int);sss
mysql> insert into tbl_testonlineddl values(1);
mysql> stop slave;
mysql> select * from tbl_testonlineddl;
+------+
| id |
+------+
| 1 |
+------+
B库上增加字段id2
mysql> alter table tbl_testonlineddl add id2 int;
mysql> select * from tbl_testonlineddl;
+------+------+
| id | id2 |
+------+------+
| 1 | NULL |
+------+------+
A库上再插入一条数据，这里不指定字段列表
mysql> insert into tbl_testonlineddl values(2);
mysql> select * from tbl_testonlineddl;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
B库复制报错Last_Errno: 1136，字段数目不匹配。这个错误很好理解，但如果A库指定字段列表insert会怎样呢？
mysql> show slave status\G
Last_Errno: 1136
Last_Error: Error 'Column count doesn't match value count at row 1' on query.
Default database: 'test'. Query: 'insert into tbl_testonlineddl values(2)'
测试2：dml语句指定字段名
A库上，指定字段名插入数据
mysql> insert into tbl_testonlineddl(id) values(3);
mysql> select * from tbl_testonlineddl;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
B库上，新插入数据同步成功
mysql> select * from tbl_testonlineddl;
+------+------+
| id | id2 |
+------+------+
| 1 | NULL |
+------+------+
mysql> select * from tbl_testonlineddl;
+------+------+
| id | id2 |
+------+------+
| 1 | NULL |
| 3 | NULL |
+------+------+
结论：只要业务中的sql语句，感知不到新增字段存在，并且显示指定需要的字段名进行数据更新，那么双主架构互切轮流加字段的方法，是可行。对于修改字段或者删除字段，猜测也是一样的道理。
mysql 5.1版本增加了对主从表异构的复制支持，简要规则如下
异构复制出现版本5.1.21
主从表异构分两种情况：
1，主从表字段数目不同
满足以下条件时，主从复制可以进行：
主从表相同的字段部分，字段顺序要一样
主从表相同的字段部分，所有字段必须位于其他字段之前
主从表相同的字段部分之外，每个字段必须有default值
前两条可以简单理解为，主从表，字段少的表，它的字段是字段多的表的前缀，是一种包含被包含的关系。
2，主从表字段类型不同
简单来说，只要从库表的字段定义能够容纳主库表的字段定义，就可以在不同数据类型之间复制。另外主从表字段类型不同的复制，与binlog格式有关。
SBR：基于语句的复制，简单的规则是，只要在主库执行的语句，在从库也能成功执行，则支持主从表字段类型不同
RBR：基于行的复制，规则相对复杂，因为binlog中的数据类型与服务器的数据类型映射可能有差异
支持的数据类型转换为：
From (Master) To (Slave)
BINARY CHAR
BLOB TEXT
CHAR BINARY
DECIMAL NUMERIC
NUMERIC DECIMAL
TEXT BLOB
VARBINARY VARCHAR
VARCHAR VARBINARY 如果字段类型转换后，精度不够，会发生数据截断。转换结果与转换模式有关：slave_type_convertions。具体规则这里就不列出了。
5.1版本中，主从表字段数目不一样的测试
A库
mysql> create table tbl_testmsdiff(id1 int,id2 int);
mysql> insert into tbl_testmsdiff (id1,id2)values(1,1);
mysql> select * from tbl_testmsdiff;
+------+------+
| id1 | id2 |
+------+------+
| 1 | 1 |
+------+------+
B库
mysql> alter table tbl_testmsdiff drop column id2;
mysql> select * from tbl_testmsdiff;
+------+
| id1 |
+------+
| 1 |
+------+
A库
mysql> set binlog_format=statement;
mysql> insert into tbl_testmsdiff (id1,id2)values(2,2);
mysql> select * from tbl_testmsdiff;
+------+------+
| id1 | id2 |
+------+------+
| 1 | 1 |
| 2 | 2 |
+------+------+ssss
B库复制报错
Last_Errno: 1054
Last_Error: Error 'Unknown column 'id2' in 'field list'' on q
uery. Default database: 'test'. Query: 'insert into tbl_testmsdiff (id1,id2)valu
es(2,2)'
测试row模式复制
A库
mysql> set binlog_format=row;
mysql> insert into tbl_testmsdiff (id1,id2)values(3,3);
mysql> select * from tbl_testmsdiff;
+------+------+
| id1 | id2 |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+------+
B库复制成功
mysql> select * from tbl_testmsdiff;
+------+
| id1 |
+------+
| 1 |
| 3 |
+------+
(责任编辑：IT)
MySQL主主复制，在线修改表结构
一直以为双主架构能解决mysql的表在线DDL的需求，但没有实际测试经验。后来发现一直的想法还是有问题的。这里做一个测试。
双主架构，在线DDL的实现步骤是：
1，首先搭建主主复制架构，只有一台提供服务，这里设为A库，另一台B库空闲
2，A库停止复制stop slave
3，在B库上修改表结构，如加字段
B库完成表修改后，因为A库暂停了复制，所以DDL语句不会复制到A库。对A库没影响
4，切换读写到B库。B库提供读写服务，A库空闲
5，开启A库复制。
首先A库会复制刚才的DDL语句，实现表结构修改，然后复制数据
大致一看，上面的步骤没有问题。A/B库修改表结构，都没有影响数据库对外服务。其实隐藏着一个问题。假设修改的表为X，在步骤3 B库修改X表时，A库上X表数据有更新，这些更新复制到B库，会等待锁，因为X表在进行DDL操作。B库修改完成以后，这些更新语句可能会执行失败，因为表结构改变可能导致更新语句出错。现实际测试如下： www.2cto.com
测试1：
A库上建表，插入数据，停止复制
mysql> create table tbl_testonlineddl(id int);sss
mysql> insert into tbl_testonlineddl values(1);
mysql> stop slave;
mysql> select * from tbl_testonlineddl;
+------+
| id |
+------+
| 1 |
+------+
B库上增加字段id2
mysql> alter table tbl_testonlineddl add id2 int;
mysql> select * from tbl_testonlineddl;
+------+------+
| id | id2 |
+------+------+
| 1 | NULL |
+------+------+
A库上再插入一条数据，这里不指定字段列表
mysql> insert into tbl_testonlineddl values(2);
mysql> select * from tbl_testonlineddl;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
B库复制报错Last_Errno: 1136，字段数目不匹配。这个错误很好理解，但如果A库指定字段列表insert会怎样呢？
mysql> show slave status\G
Last_Errno: 1136
Last_Error: Error 'Column count doesn't match value count at row 1' on query.
Default database: 'test'. Query: 'insert into tbl_testonlineddl values(2)'
测试2：dml语句指定字段名
A库上，指定字段名插入数据
mysql> insert into tbl_testonlineddl(id) values(3);
mysql> select * from tbl_testonlineddl;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
B库上，新插入数据同步成功
mysql> select * from tbl_testonlineddl;
+------+------+
| id | id2 |
+------+------+
| 1 | NULL |
+------+------+
mysql> select * from tbl_testonlineddl;
+------+------+
| id | id2 |
+------+------+
| 1 | NULL |
| 3 | NULL |
+------+------+
结论：只要业务中的sql语句，感知不到新增字段存在，并且显示指定需要的字段名进行数据更新，那么双主架构互切轮流加字段的方法，是可行。对于修改字段或者删除字段，猜测也是一样的道理。
mysql 5.1版本增加了对主从表异构的复制支持，简要规则如下
异构复制出现版本5.1.21
主从表异构分两种情况：
1，主从表字段数目不同
满足以下条件时，主从复制可以进行：
主从表相同的字段部分，字段顺序要一样
主从表相同的字段部分，所有字段必须位于其他字段之前
主从表相同的字段部分之外，每个字段必须有default值
前两条可以简单理解为，主从表，字段少的表，它的字段是字段多的表的前缀，是一种包含被包含的关系。
2，主从表字段类型不同
简单来说，只要从库表的字段定义能够容纳主库表的字段定义，就可以在不同数据类型之间复制。另外主从表字段类型不同的复制，与binlog格式有关。
SBR：基于语句的复制，简单的规则是，只要在主库执行的语句，在从库也能成功执行，则支持主从表字段类型不同
RBR：基于行的复制，规则相对复杂，因为binlog中的数据类型与服务器的数据类型映射可能有差异
支持的数据类型转换为：
From (Master) To (Slave)
BINARY CHAR
BLOB TEXT
CHAR BINARY
DECIMAL NUMERIC
NUMERIC DECIMAL
TEXT BLOB
VARBINARY VARCHAR
VARCHAR VARBINARY 如果字段类型转换后，精度不够，会发生数据截断。转换结果与转换模式有关：slave_type_convertions。具体规则这里就不列出了。
5.1版本中，主从表字段数目不一样的测试
A库
mysql> create table tbl_testmsdiff(id1 int,id2 int);
mysql> insert into tbl_testmsdiff (id1,id2)values(1,1);
mysql> select * from tbl_testmsdiff;
+------+------+
| id1 | id2 |
+------+------+
| 1 | 1 |
+------+------+
B库
mysql> alter table tbl_testmsdiff drop column id2;
mysql> select * from tbl_testmsdiff;
+------+
| id1 |
+------+
| 1 |
+------+
A库
mysql> set binlog_format=statement;
mysql> insert into tbl_testmsdiff (id1,id2)values(2,2);
mysql> select * from tbl_testmsdiff;
+------+------+
| id1 | id2 |
+------+------+
| 1 | 1 |
| 2 | 2 |
+------+------+ssss
B库复制报错
Last_Errno: 1054
Last_Error: Error 'Unknown column 'id2' in 'field list'' on q
uery. Default database: 'test'. Query: 'insert into tbl_testmsdiff (id1,id2)valu
es(2,2)'
测试row模式复制
A库
mysql> set binlog_format=row;
mysql> insert into tbl_testmsdiff (id1,id2)values(3,3);
mysql> select * from tbl_testmsdiff;
+------+------+
| id1 | id2 |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+------+
B库复制成功
mysql> select * from tbl_testmsdiff;
+------+
| id1 |
+------+
| 1 |
| 3 |
+------+
(责任编辑：IT) |