Clustering

How to convert galera node to async slave and vice-versa with MariaDB Galera Cluster

Recently, I was working with one of our customer and this is what their requirement as they want to automate this process for converting galera node to async slave and make async slave to galera node without shutting down any server.

———-

I’ve tested locally with my galera cluster and it’s working smoothly. But with heavy load on the server, you might need to speed up this process.

———-

Here are the steps for how to do that. I assumes that you already have working 3 nodes galera cluster if not, then for the testing purpose you can create it from my previous post.

Btw, there is no matter how many nodes you have. Now, create one test1 table and add 3 records in galera cluster.

MariaDB [nil]> select * from test1;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | nilnandan |
|    2 | joshi     |
|    3 | niljoshi  |
+------+-----------+
3 rows in set (0.00 sec)

Step1: Stop Galera Replication on node3

MariaDB [nil]> SET GLOBAL wsrep_on=0; SET GLOBAL wsrep_cluster_address='dummy://';
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (2.01 sec)

Step2: get the value of wsrep_last_committed

MariaDB [nil]> show global status like '%wsrep_last_committed%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| wsrep_last_committed | 40455 |
+----------------------+-------+

Step3: On node2, find the binlog and check end_log_pos with the help of Xid.

because wsrep_last_committed  == Xid

[nil@centos68 data]$ mysqlbinlog --base64-output=decode-rows --verbose mysql-bin.000012  | grep -i "Xid = 40455"
#180113  5:35:49 server id 112  end_log_pos 803         Xid = 40455
[nil@centos68 data]$

Step4: on node3, start replication from node2

CHANGE MASTER TO MASTER_HOST='127.0.0.1',
MASTER_PORT=19223,
MASTER_USER='repl_user' ,
MASTER_PASSWORD='replica123' ,
MASTER_LOG_FILE='mysql-bin.000012',
MASTER_LOG_POS=803;

Step5: Before start slave, add two more records in test1 table in Galera cluster so we can check after starting slave that aync replication between galera node2 and node3, is working or not. 

MariaDB [nil]> insert into test1 values (4, 'nil');
Query OK, 1 row affected (0.00 sec)
MariaDB [nil]> insert into test1 values (5, 'njoshi');
Query OK, 1 row affected (0.00 sec)

Step6: On node3, start slave;

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
MariaDB [nil]> select * from test1;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | nilnandan |
|    2 | joshi     |
|    3 | niljoshi  |
|    4 | nil       |
|    5 | njoshi    |
+------+-----------+
5 rows in set (0.00 sec)

you can see that the records which were inserted in remaining two nodes galera cluster before start slave, will be now available in this async slave.

Now, if we talk about vice-versa where we want to add this async slave as a 3rd node of galera cluster.

Here are the steps,

Step1: Stop slave

MariaDB [nil]> stop slave;
Query OK, 0 rows affected (0.01 sec)

Step2: collect master log file and position from show slave 

Master_Log_File: mysql-bin.000013
Exec_Master_Log_Pos: 683

Step3: get the relevant xid from binlog.

[nil@centos68 data]$ mysqlbinlog --base64-output=decode-rows --verbose mysql-bin.000013 | grep -i "683"
#180113  5:38:06 server id 112  end_log_pos 683         Xid = 40457
[nil@centos68 data]$

Step4: get the wsrep_cluster_state_uuid and add above xid with it. i.e 

set global wsrep_start_position=wsrep_cluster_state_uuid:Xid

here,

wsrep_cluster_state_uuid     | afdac6cb-f7ee-11e7-b1c5-9e96fe6fb1e1

Step5: add two more records in galera cluster

MariaDB [nil]> insert into test1 values (6, 'niljo');
Query OK, 1 row affected (0.01 sec)
MariaDB [nil]> insert into test1 values (7, 'joshinil');
Query OK, 1 row affected (0.00 sec)

and set these.

MariaDB [nil]> set global wsrep_start_position='afdac6cb-f7ee-11e7-b1c5-9e96fe6fb1e1:40457';
Query OK, 0 rows affected (0.00 sec)
MariaDB [nil]> SET GLOBAL wsrep_on=1; SET GLOBAL wsrep_cluster_address='gcomm://127.0.0.1:4030,127.0.0.1:5030';
Query OK, 0 rows affected (0.00 sec)

after this,

MariaDB [nil]> select * from test1;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | nilnandan |
|    2 | joshi     |
|    3 | niljoshi  |
|    4 | nil       |
|    5 | njoshi    |
|    4 | nil       |
|    5 | njoshi    |
|    6 | niljo     |
|    7 | joshinil  |
+------+-----------+
9 rows in set (0.00 sec)

We can see those records in this table which were inserted after stop slave and before start 3rd node.

I would always suggest to test this on test/stage server before implement it to productions.

Few links to know more about async slave and Galera Cluster.

Source link

Leave a Reply

Your email address will not be published. Required fields are marked *