Convert Galera Node to Async Slave and Vice-Versa With Galera Cluster

Recently, I was working with one of our customers and they wanted to automate the process for converting a Galera node to async slave and async slave to Galera node without shutting down any servers. This blog post will provide step-by-step instructions on how to accomplish this.

Here, for testing purposes, 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.