Convert Galera Node to Async Slave And Vice-versa With Galera Cluster

Recently, I was working with one of our customers and this was their requirement as they wanted to automate this process for converting a galera node to async slave and make async slave to galera node without shutting down any servers. This blog post will provide a step-by-step instruction on how to accomplish this. Here, for the testing purpose, I’ve used a sandbox and installed a 3-node Galera cluster on the same server with different ports.

The following are steps to make a one node to async slave.

Step 1: Stop galera node with wsrep_on=0 and wsrep_cluster_address=’dummy://’.

MariaDB [nil]> SET GLOBAL wsrep_on=0; SET GLOBAL wsrep_cluster_address='dummy://';

Step 2: Collect  the value of wsrep_last_committed which is xid,.

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

Step 3: On the basis of that xid, find binlog file and end log position.

[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]$

Step 4: Start replication with it from Galera Cluster.

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;

DO NOT FORGET to edit my.cnf for these dynamic parameters for permanent effect. i.e 

[mysqld]
GLOBAL wsrep_on=0;
wsrep_cluster_address=’dummy://’;

Meanwhile for the vice-versa process, follow these steps to make an async slave to a Galera node.

Step 1: Stop slave, collect Master_Log_File and Exec_Master_Log_Pos.

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

MariaDB [nil]> show slave status G
...
Master_Log_File: mysql-bin.000013
Exec_Master_Log_Pos: 683

Step 2: On the basis of that information, you can get xid from the 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]$

Step 3: Just combine wsrep_cluster_state_uuid with xid,.

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

so wsrep_start_position = ‘afdac6cb-f7ee-11e7-b1c5-9e96fe6fb1e1:40457’

Step 4: Set it as a wsrep_start_position and add that server as a node of Galera Cluster. 

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)

DO NOT FORGET to edit my.cnf for these dynamic parameters for permanent effect. i.e 

[mysqld]
GLOBAL wsrep_on=1;
wsrep_cluster_address=’gcomm://127.0.0.1:4030,127.0.0.1:5030‘;

In case of heavy loads on the server or slave lagging, you may need to speed up this process.

For a full step-by-step guide, you can check out my original blog post here.