MySQL master-master replication, online table schema modification _ MySQL

MySQL master-master replication, modifying table structure bitsCN.com online

MySQL master-master replication, modifying the table structure online

I always thought that the dual-master architecture could solve the mysql table online DDL requirements, but I had 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 as follows:

1. first, set up the master-master replication architecture. only one server provides services. set this architecture to database A, and the other database B is idle.

2. Database A stops copying stop slave

3. modify the table structure on database B, for example, adding a field

After database B completes the table modification, the DDL statement is not copied to database A because Database A stops copying. No impact on database

4. switch to database B. Database B provides read/write services, and database A is idle.

5. enable database A replication.

First, Database A copies the DDL statement to modify the table structure, and then copies the data.

Generally, 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. The actual test is as follows: www.bitsCN.com

Test 1:

Create A table in database A, insert data, and stop copying

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 |

+ ------ +

Add Field id2 to database B

Mysql> alter table tbl_testonlineddl add id2 int;

Mysql> select * from tbl_testonlineddl;

+ ------ +

| Id | id2 |

+ ------ +

| 1 | NULL |

+ ------ +

Insert A data record on database A. The field list is not specified here.

Mysql> insert into tbl_testonlineddl values (2 );

Mysql> select * from tbl_testonlineddl;

+ ------ +

| Id |

+ ------ +

| 1 |

| 2 |

+ ------ +

Database B replication 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/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 )'

Test 2: dml statements specify the field name

On Database A, specify the field name to insert data

Mysql> insert into tbl_testonlineddl (id) values (3 );

Mysql> select * from tbl_testonlineddl;

+ ------ +

| Id |

+ ------ +

| 1 |

| 2 |

| 3 |

+ ------ +

On database B, the newly inserted data is synchronized successfully.

Mysql> select * from tbl_testonlineddl;

+ ------ +

| 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 does not perceive the existence of new fields and the field name specified for data update is displayed, it is feasible for the dual-master architecture to add fields in turn. The same is true for modifying or deleting fields.

Mysql 5.1 adds support for heterogeneous replication of master/slave tables. the brief rules are as follows:

Heterogeneous replication version 5.1.21

Master/slave tables are divided into two heterogeneous scenarios:

1. the number of fields in the master/slave table is different.

Master-slave replication can be performed if the following conditions are met:

The fields in the same master/slave table must be in the same order.

All fields must be located before other fields.

In addition to the same fields in the master/slave table, each field must have a default value.

The first two tables can be simply understood as master-slave tables with few fields. their fields are the prefixes of tables with multiple fields and contain contained relationships.

2. the fields in the master/slave table have different types.

Simply put, as long as the field definitions in the 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 types of fields in the master/slave table are supported.

RBR: Row-based replication, the rules are relatively complex, because the data type ing in binlog may be different from that in the server.

The supported data types are:

From (Master) To (Slave)

BINARY CHAR

BLOB TEXT

CHAR BINARY

DECIMAL NUMERIC

NUMERIC DECIMAL

TEXT BLOB

VARBINARY VARCHAR

If the field type is converted into varchar varbinary with insufficient precision, data is truncated. The conversion result is related to the conversion mode: slave_type_convertions. The specific rules are not listed here.

In analyticdb 5.1, the number of fields in the master/slave table is different.

Database

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 |

+ ------ +

Database B

Mysql> alter table tbl_testmsdiff drop column id2;

Mysql> select * from tbl_testmsdiff;

+ ------ +

| Id1 |

+ ------ +

| 1 |

+ ------ +

Database

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

Database B replication error

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 )'

Test row mode replication

Database

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 |

+ ------ +

BitsCN.com