Convert Galera Node to Async Slave and Vice-Versa With Galera Cluster
Learn how to automate the process for converting a Galera node to async slave and async slave to Galera node without shutting down any servers.
Join the DZone community and get the full member experience.
Join For FreeRecently, 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.
Published at DZone with permission of Nilnandan Joshi, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments