MySQL master-master replication, modifying the table structure online

MySQL master-master replication, online table structure modification has always been to think that the dual-master architecture can solve the mysql table online DDL requirements, but there is no practical test experience. Later I found that there were still problems with my thoughts. Here is a test. In the dual-master architecture, the online DDL implementation steps are: www.2cto.com 1. First, we set up the master-master replication architecture. Only one server provides services. Here we set it to database A, and the other database B is idle 2, database A stops copying stop slave3 and modifies the table structure on Database B. For example, after adding database B to complete the table modification, the DDL statement will not be copied to database A because database A suspends the replication. No effect on database A 4. Switch to database B for read/write. Database B provides the read/write service. Database A is 5 idle and database A replication is enabled. First, database A copies the DDL statement to modify the table structure, and then copies the data. The above steps are correct. Database A/database B modifies the table structure without affecting the external services of the database. There is actually a problem hidden. Assume that the modified table is X. When database B modifies table X, data in table X in database A is updated. These updates are copied to database B and will wait for the lock, because Table X is performing DDL operations. After database B is modified, these update statements may fail to be executed because the table structure may change, resulting in an error in the update statement. Test 1: create A table in database A, insert data, stop copying mysql> create table tbl_testonlineddl (id int); sssmysql> insert into tbl_testonlineddl values (1 ); mysql> stop slave; mysql> select * from tbl_testonlineddl; + ------ + | id | + ------ + | 1 | + ------ + add the field id2mysql> alter table tbl_testonlineddl add id2 int; mysql> select * from tbl_testonlineddl; + ------ + | id | id2 | + ------ + | 1 | NULL | + ------ + Insert a data record. The field list is not specified here. mysql> insert into tbl_testonlineddl values (2); mysql> select * from tbl_testonlineddl; + ------ + | id | + ------ + | 1 | 2 | + ------ + database B copy error Last_Errno: 1136, the number of fields does not match. This error is understandable, but what if insert is specified for the Field List in database? Mysql> show slave status \ GLast_Errno: 1136Last_Error: Error 'column count doesn' t match value count at row 1' on query. default database: 'test '. query: 'insert into tbl_testonlineddl values (2) 'Test 2: dml statement specifies the field name in database A and specifies the field name to insert data into mysql> insert into tbl_testonlineddl (id) values (3 ); mysql> select * from tbl_testonlineddl; + ------ + | id | + ------ + | 1 | 2 | 3 | + ------ + database B, synchronized newly inserted data mysql> select * from tbl_teston Lineddl; + ------ + | id | id2 | + ------ + | 1 | NULL | + ------ + mysql> select * from tbl_testonlineddl; + ------ + | id | id2 | + ------ + | 1 | NULL | 3 | NULL | + ------ + conclusion: as long as the SQL statement in the business is used, if the new field does not exist and the specified field name is displayed for data update, it is feasible to add fields in turn in the dual-master architecture. The same is true for modifying or deleting fields. Mysql 5.1 supports heterogeneous replication of Master/Slave tables. The following rule describes the heterogeneous replication of Master/Slave tables in version 5.1.21: 1, if the number of fields in the Master/Slave table is different and the following conditions are met, Master/Slave replication can be performed: the fields in the Master/Slave table are the same, and the field order must be the same as those in the master/Slave table, all fields must be located outside the fields of the same master-slave table before other fields. Each field must have the first two default values, which can be simply understood as a master-slave table with fewer fields, its field is the prefix of a table with multiple fields, and it is a link that contains contained. 2. In simple terms, as long as the field definitions in the slave database table can accommodate the field definitions in the master database table, they can be copied between different data types. In addition, the replication of different fields in the Master/Slave table depends on the binlog format. SBR: Statement-based replication. The simple rule is that statements executed in the master database can also be successfully executed in the slave database. Different RBR fields in the Master/Slave table are supported: Row-based replication, the rules are relatively complex, because the data type ing in the binlog may be different From the server's data type ing, And the supported data types can be converted To: From (Master) To (Slave) binary charblob textchar binarydecimal numericnumeric decimaltext blobvarbinary varcharvarchar varbinary if the field type conversion fails, data truncation occurs. The conversion result is related to the conversion mode: slave_type_convertions. The specific rules are not listed here. In mysql 5.1, test database A with different numbers of fields in the master/Slave table> 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 | + ------ + database B mysql> alter table tbl_testmsdiff drop column id2; mysql> select * from tbl_testmsdiff; + ------ + | id1 | + ------ + | 1 | + ------ + database 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 | + ------ + ssssB database copy error Last_Errno: 1054Last_Error: error 'unknown column 'id2' in 'field list''' on query. default database: 'test '. query: 'insert into tbl_testmsdiff (id1, id2) values () 'test row mode replication database 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 | + ------ + database B copied mysql> select * from tbl_testmsdiff; + ------ + | id1 | + ------ + | 1 | 3 | + ------ +