This is built-in Mysql row-based replication which supports replication with differing table definitions between Master and Slave.
So, when using row-based repplication source and target table do not have to be identical. A table on master can have more or fewer columns or use different data types.
But there are limitations you must watch over depending on MySQL version you are running.
- The database and table names must be the same on both Master and Slave
- Columns must be in the same order before any additional column
- Each extra column must have default value
- ...
Newer MySQL versions may tolerate more differences between source and target table - check documentation for your version.
I want to show you what could happen with your data if you do not pay attention on this limitations.
Suppose I have 3-node MariaDB Galera Cluster with table t.
I want to add several columns to the table while database is used by an application.
For such task I will use built-in Rolling Schema Change (RSU) method which enables me to perform schema changes on node without impact on rest of the cluster.
Add column c4 to the table t following rules above for row-based replication.
Table t has three columns and one row inserted.
NODE1
MariaDB [testdb]> create table t (c1 varchar(10), c2 varchar(10), c3 varchar(10));
Query OK, 0 rows affected (0.37 sec)
MariaDB [testdb]> insert into t values ('n1-1','n1-1','n1-1');
Query OK, 1 row affected (0.00 sec)
NODE2
MariaDB [testdb]> select * from t;
+------+------+------+
| c1 | c2 | c3 |
+------+------+------+
| n1-1 | n1-1 | n1-1 |
+------+------+------+
1 row in set (0.00 sec)
NODE3
MariaDB [testdb]> select * from t;
+------+------+------+
| c1 | c2 | c3 |
+------+------+------+
| n1-1 | n1-1 | n1-1 |
+------+------+------+
1 row in set (0.01 sec)
I will enable RSU mode which ensures that this server will not impact the rest of the cluster during ALTER command execution.
Add column c4 and INSERT row simulating application activity.
MariaDB [testdb]> set session wsrep_OSU_method='RSU';
Query OK, 0 rows affected (0.00 sec)
MariaDB [testdb]> alter table t add column c4 varchar(10);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [testdb]> set session wsrep_OSU_method='TOI';
Query OK, 0 rows affected (0.00 sec)
MariaDB [testdb]> insert into t(c1,c2,c3) values ('n1-1','n1-1','n1-1');
Query OK, 1 row affected (0.13 sec)
While table definition is different between Node1 and rest of the cluster INSERT few more rows on other nodes.
NODE2
insert into t(c1,c2,c3) values ('n2-1','n2-1','n2-1');
NODE3
insert into t(c1,c2,c3) values ('n3-1','n3-1','n3-1');
Check rows from table t.
NODE1 MariaDB [testdb]> select * from t; +------+------+------+------+ | c1 | c2 | c3 | c4 | +------+------+------+------+ | n1-1 | n1-1 | n1-1 | NULL | | n1-1 | n1-1 | n1-1 | NULL | | n2-1 | n2-1 | n2-1 | NULL | | n3-1 | n3-1 | n3-1 | NULL | +------+------+------+------+ 4 rows in set (0.00 sec) NODE2 MariaDB [testdb]> select * from t; +------+------+------+ | c1 | c2 | c3 | +------+------+------+ | n1-1 | n1-1 | n1-1 | | n1-1 | n1-1 | n1-1 | | n2-1 | n2-1 | n2-1 | | n3-1 | n3-1 | n3-1 | +------+------+------+ 4 rows in set (0.00 sec) NODE3 MariaDB [testdb]> select * from t; +------+------+------+ | c1 | c2 | c3 | +------+------+------+ | n1-1 | n1-1 | n1-1 | | n1-1 | n1-1 | n1-1 | | n2-1 | n2-1 | n2-1 | | n3-1 | n3-1 | n3-1 | +------+------+------+ 4 rows in set (0.01 sec)
As you can notice everything is OK with my data.
Add new column to Node2 and Node3 following the same steps as for Node1.
NODE2 MariaDB [testdb]> set session wsrep_OSU_method='RSU'; Query OK, 0 rows affected (0.00 sec) MariaDB [testdb]> alter table t add column c4 varchar(10); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [testdb]> set session wsrep_OSU_method='TOI'; Query OK, 0 rows affected (0.00 sec) NODE3 MariaDB [testdb]> set session wsrep_OSU_method='RSU'; Query OK, 0 rows affected (0.00 sec) MariaDB [testdb]> alter table t add column c4 varchar(10); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [testdb]> set session wsrep_OSU_method='TOI'; Query OK, 0 rows affected (0.00 sec)
And my task is completed. I have successfully changed model of the table.
But what can happen if I add new column between existing columns.
Remember, this is not permitted for a row-based replication and can cause replication to brake or something even worse.
Enable RSU mode on Node1 and add new column c11 after c1 column.
INSERT row simulating active application during schema change.
NODE1
MariaDB [testdb]> set session wsrep_OSU_method='RSU';
Query OK, 0 rows affected (0.00 sec)
MariaDB [testdb]>
MariaDB [testdb]> alter table t add column c11 varchar(10) after c1;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [testdb]> set session wsrep_OSU_method='TOI';
Query OK, 0 rows affected (0.00 sec)
MariaDB [testdb]> insert into t(c1,c2,c3) values ('n1-1','n1-1','n1-1');
Query OK, 1 row affected (0.01 sec)
MariaDB [testdb]> select * from t;
+------+------+------+------+------+
| c1 | c11 | c2 | c3 | c4 |
+------+------+------+------+------+
| n1-1 | NULL | n1-1 | n1-1 | NULL |
| n1-1 | NULL | n1-1 | n1-1 | NULL |
| n2-1 | NULL | n2-1 | n2-1 | NULL |
| n3-1 | NULL | n3-1 | n3-1 | NULL |
| n1-1 | NULL | n1-1 | n1-1 | NULL |
+------+------+------+------+------+
5 rows in set (0.00 sec)
INSERT row on other nodes because Galera Cluster allows us write on any node in the cluster configuration.
NODE2
MariaDB [testdb]> insert into t(c1,c2,c3) values ('n2-1','n2-1','n2-1');
Query OK, 1 row affected (0.01 sec)
MariaDB [testdb]> select * from t;
+------+------+------+------+
| c1 | c2 | c3 | c4 |
+------+------+------+------+
| n1-1 | n1-1 | n1-1 | NULL |
| n1-1 | n1-1 | n1-1 | NULL |
| n2-1 | n2-1 | n2-1 | NULL |
| n3-1 | n3-1 | n3-1 | NULL |
| n1-1 | NULL | n1-1 | n1-1 |
| n2-1 | n2-1 | n2-1 | NULL |
+------+------+------+------+
6 rows in set (0.00 sec)
NODE3
MariaDB [testdb]> insert into t(c1,c2,c3) values ('n3-1','n3-1','n3-1');
Query OK, 1 row affected (0.01 sec)
MariaDB [testdb]> select * from t;
+------+------+------+------+
| c1 | c2 | c3 | c4 |
+------+------+------+------+
| n1-1 | n1-1 | n1-1 | NULL |
| n1-1 | n1-1 | n1-1 | NULL |
| n2-1 | n2-1 | n2-1 | NULL |
| n3-1 | n3-1 | n3-1 | NULL |
| n1-1 | NULL | n1-1 | n1-1 |
| n2-1 | n2-1 | n2-1 | NULL |
| n3-1 | n3-1 | n3-1 | NULL |
+------+------+------+------+
7 rows in set (0.00 sec)
INSERT commands were successfully executed and everything is OK with my replication.
I don't have any errors in error.log that suggests that I have any problem.
But check contest of table t on the first node where new column is added.
NODE1 MariaDB [testdb]> select * from t; +------+------+------+------+------+ | c1 | c11 | c2 | c3 | c4 | +------+------+------+------+------+ | n1-1 | NULL | n1-1 | n1-1 | NULL | | n1-1 | NULL | n1-1 | n1-1 | NULL | | n2-1 | NULL | n2-1 | n2-1 | NULL | | n3-1 | NULL | n3-1 | n3-1 | NULL | | n1-1 | NULL | n1-1 | n1-1 | NULL | | n2-1 | n2-1 | n2-1 | NULL | NULL | | n3-1 | n3-1 | n3-1 | NULL | NULL | +------+------+------+------+------+ 7 rows in set (0.00 sec)
Notice how rows differ between nodes, and we should have exactly the same data on all tree nodes.
Let's complete schema changes on other two nodes.
NODE2 MariaDB [testdb]> set session wsrep_OSU_method='RSU'; Query OK, 0 rows affected (0.00 sec) MariaDB [testdb]> alter table t add column c11 varchar(10) after c1; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [testdb]> set session wsrep_OSU_method='TOI'; Query OK, 0 rows affected (0.00 sec) NODE3 MariaDB [testdb]> set session wsrep_OSU_method='RSU'; Query OK, 0 rows affected (0.00 sec) MariaDB [testdb]> alter table t add column c11 varchar(10) after c1; Query OK, 0 rows affected (0.34 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [testdb]> set session wsrep_OSU_method='TOI'; Query OK, 0 rows affected (0.00 sec)
I have successfully added new column, did not brake reapplication and everything seems OK, but my data is not consistent between nodes.
NODE1 MariaDB [testdb]> select * from t; +------+------+------+------+------+ | c1 | c11 | c2 | c3 | c4 | +------+------+------+------+------+ | n1-1 | NULL | n1-1 | n1-1 | NULL | | n1-1 | NULL | n1-1 | n1-1 | NULL | | n2-1 | NULL | n2-1 | n2-1 | NULL | | n3-1 | NULL | n3-1 | n3-1 | NULL | | n1-1 | NULL | n1-1 | n1-1 | NULL | | n2-1 | n2-1 | n2-1 | NULL | NULL | | n3-1 | n3-1 | n3-1 | NULL | NULL | +------+------+------+------+------+ 7 rows in set (0.00 sec) NODE2 MariaDB [testdb]> select * from t; +------+------+------+------+------+ | c1 | c11 | c2 | c3 | c4 | +------+------+------+------+------+ | n1-1 | NULL | n1-1 | n1-1 | NULL | | n1-1 | NULL | n1-1 | n1-1 | NULL | | n2-1 | NULL | n2-1 | n2-1 | NULL | | n3-1 | NULL | n3-1 | n3-1 | NULL | | n1-1 | NULL | NULL | n1-1 | n1-1 | | n2-1 | NULL | n2-1 | n2-1 | NULL | | n3-1 | NULL | n3-1 | n3-1 | NULL | +------+------+------+------+------+ 7 rows in set (0.00 sec) NODE3 MariaDB [testdb]> select * from t; +------+------+------+------+------+ | c1 | c11 | c2 | c3 | c4 | +------+------+------+------+------+ | n1-1 | NULL | n1-1 | n1-1 | NULL | | n1-1 | NULL | n1-1 | n1-1 | NULL | | n2-1 | NULL | n2-1 | n2-1 | NULL | | n3-1 | NULL | n3-1 | n3-1 | NULL | | n1-1 | NULL | NULL | n1-1 | n1-1 | | n2-1 | NULL | n2-1 | n2-1 | NULL | | n3-1 | NULL | n3-1 | n3-1 | NULL | +------+------+------+------+------+ 7 rows in set (0.00 sec)
Data inconsistency is the worst problem that could happen in synchronous cluster configuration.
It could happen without any notice, but sooner or later it will stop reapplication process and failing node will be excluded from the cluster.
REFERENCE
https://dev.mysql.com/doc/refman/5.7/en/replication-features-differing-tables.html
0 Comments:
Post a Comment