---
title: "Convert Galera Node to Async Slave And Vice-versa With Galera Cluster"
publish_date: 2018-02-14
updated_date: 2021-09-02
author: "Nilnandan Joshi"
channel:
  - name: "Tech Talk"
    url: "/resources/blog/channel/tech-talk.md"
tags:
  - name: "Async Slave"
    url: "/resources/blog/tag/async-slave.md"
  - name: "Galera Cluster"
    url: "/resources/blog/tag/galera-cluster.md"
  - name: "Galera Node"
    url: "/resources/blog/tag/galera-node.md"
  - name: "guide"
    url: "/resources/blog/tag/guide.md"
  - name: "instruction"
    url: "/resources/blog/tag/instruction.md"
  - name: "sandbox"
    url: "/resources/blog/tag/sandbox.md"
  - name: "troubleshooting"
    url: "/resources/blog/tag/troubleshooting.md"
---

# 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**](http://www.nilinfobin.com/linux1/how-to-convert-galera-node-to-async-slave-and-vice-versa-with-mariadb-galera-cluster/ "here").