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.
———-
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.
DO NOT FORGET to edit this dynamic parameter changes in my.cnf for permanent effect. i.e 
[mysqld]
GLOBAL wsrep_on=0;
wsrep_cluster_address=’dummy://’;
———-
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 status \G
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
so wsrep_start_position = ‘afdac6cb-f7ee-11e7-b1c5-9e96fe6fb1e1:40457’
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.
DO NOT FORGET to edit this dynamic parameters changes in my.cnf for permanent effect. i.e 
[mysqld]
GLOBAL wsrep_on=1;
wsrep_cluster_address=’gcomm://127.0.0.1:4030,127.0.0.1:5030‘;
———-
Few links to know more about async slave and Galera Cluster.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.