How to migrate from Galera Cluster to MySQL Group Replication

In this article, I will show you how it’s possible to perform an online migration from a 3 members Galera cluster setup (in this case I’m using PXC 5.7.14) to a 3 members MySQL Group Replication cluster setup (MySQL Community 5.7.17).

Don’t forget that before adopting Group Replication as database backend, you should validate that your application do match GR requirements and limitations. When this is validated, you can start !

So first, let’s have a look at the current situation:

 

We have an application (sysbench 0.5), reading and writing to a Galera Cluster (Percona XtraDB Cluster 5.7.14) via ProxySQL. We write on all the nodes (Multi-Master) and we will do the same on our MySQL Group Replication Cluster, we will use a Multi-Primary Group.

This is the command used to simulate our application:

while true; do sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua \
     --mysql-host=127.0.0.1 --mysql-port=6033 --mysql-password=fred \
     --mysql-table-engine=innodb --mysql-user=fred --max-requests=0 \
     --tx-rate=20 --num-threads=2  --report-interval=1 run ; done;

And this an overview of the machines used:

Hostname	OS	Software	IP	Server_id
mysql1	CentOS 7	PXC 5.7.14	192.168.90.10	1
mysql2	CentOS 7	PXC 5.7.14	192.168.90.11	2
mysql3	CentOS 7	PXC 5.7.14	192.168.90.12	3
app	CentOS 7	ProxySQL sysbench 0.5	192.168.90.13	n/a

So the goal will be to replace all those PXC nodes by MySQL 5.7.17 with Group Replication one by one and avoid downtime.

For those familiar with ProxySQL this is how we see the Galera nodes in the proxy:

ProxySQL Admin> select hostname, status, hostgroup_id from runtime_mysql_servers;
+---------------+--------+--------------+
| hostname      | status | hostgroup_id |
+---------------+--------+--------------+
| 192.168.90.12 | ONLINE | 1            |
| 192.168.90.10 | ONLINE | 1            |
| 192.168.90.11 | ONLINE | 1            |
+---------------+--------+--------------+

For the other ones, you can find more info in the previous post: HA with MySQL Group Replication and ProxySQL.

To be able to proceed as we planned, we need to have binary logs enabled on every PXC nodes and also use MySQL GTIDs.
So in my.cnf you must have:

enforce_gtid_consistency = on
gtid_mode  = on
log_bin 
log_slave_updates

First Step: remove one node and migrate it to MySQL 5.7.17

Our first step in this section will be to stop mysqld and remove the PXC packages on mysql3:

[root@mysql3]# systemctl stop mysql

ProxySQL Admin> select hostname, status from runtime_mysql_servers;
+---------------+---------+
| hostname      | status  |
+---------------+---------+
| 192.168.90.11 | ONLINE  |
| 192.168.90.12 | SHUNNED |
| 192.168.90.10 | ONLINE  |
+---------------+---------+

Our application is of course still running (it might of course be disconnected), so in this case sysbench runs in a loop.

As all our nodes are running CentOS 7, we will use the mysql57 community repo for el7.

[root@mysql3 ~]# yum install http://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm

Now we can change the packages:

[root@mysql3 ~]# yum -y swap Percona-XtraDB-Cluster* mysql-community-server mysql-community-libs-compat
...
=========================================================================================================
 Package                                   Arch     Version              Repository                 Size
=========================================================================================================
Installing:
 mysql-community-libs-compat               x86_64   5.7.17-1.el7         mysql57-community         2.0 M
 mysql-community-server                    x86_64   5.7.17-1.el7         mysql57-community         162 M
Removing:
 Percona-XtraDB-Cluster-57                 x86_64   5.7.14-26.17.1.el7   @percona-release-x86_64   0.0  
 Percona-XtraDB-Cluster-client-57          x86_64   5.7.14-26.17.1.el7   @percona-release-x86_64    37 M
 Percona-XtraDB-Cluster-server-57          x86_64   5.7.14-26.17.1.el7   @percona-release-x86_64   227 M
 Percona-XtraDB-Cluster-shared-57          x86_64   5.7.14-26.17.1.el7   @percona-release-x86_64   3.7 M
 Percona-XtraDB-Cluster-shared-compat-57   x86_64   5.7.14-26.17.1.el7   @percona-release-x86_64   6.7 M
Installing for dependencies:
 mysql-community-client                    x86_64   5.7.17-1.el7         mysql57-community          24 M
 mysql-community-common                    x86_64   5.7.17-1.el7         mysql57-community         271 k
 mysql-community-libs                      x86_64   5.7.17-1.el7         mysql57-community         2.1 M

Transaction Summary
=========================================================================================================
Install  2 Packages (+3 Dependent packages)
Remove   5 Packages

After that step, it’s time to modify my.cnf and comment all wsrep and pxc related variables and add some extra that are mandatory:

binlog_checksum = none
master_info_repository = TABLE
relay_log_info_repository = TABLE
transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name="afb80f36-2bff-11e6-84e0-0800277dd3bf"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.90.12:3406"
loose-group_replication_group_seeds= "192.168.90.10:3406,192.168.90.11:3406"
loose-group_replication_bootstrap_group= off
loose-group_replication_single_primary_mode= off

Then we move that server to another hostgroup in ProxySQL:

ProxySQL Admin> update mysql_servers set hostgroup_id =2 where hostname ="192.168.90.12";
ProxySQL Admin> load mysql servers to runtime;
ProxySQL Admin> select hostname, status, hostgroup_id from runtime_mysql_servers;
+---------------+---------+--------------+
| hostname      | status  | hostgroup_id |
+---------------+---------+--------------+
| 192.168.90.11 | ONLINE  | 1            |
| 192.168.90.10 | ONLINE  | 1            |
| 192.168.90.12 | SHUNNED | 2            |
+---------------+---------+--------------+

It’s time now to start mysqld:

[root@mysql3 ~]# systemctl start mysqld
[root@mysql3 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.17-log MySQL Community Server (GPL)
...

Step 2: create Group Replication Cluster of 1 node

Now we need to bootstrap our group.

This is a very easy step:

mysql3> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
mysql3> SET GLOBAL group_replication_bootstrap_group=ON;
mysql3> START GROUP_REPLICATION;
mysql3> SET GLOBAL group_replication_bootstrap_group=OFF;

Now the Group is started:

mysql3> select * from performance_schema.replication_group_members\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: 9e8416d7-b1c6-11e6-bc10-08002718d305
 MEMBER_HOST: mysql3.localdomain
 MEMBER_PORT: 3306
MEMBER_STATE: ONLINE

It’s not needed now, but it’s the best time to also setup the credentials for the recovery process. You might forget it.

mysql1> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='password' 
             FOR CHANNEL 'group_replication_recovery';

This user is not yet created but we will created directly during the next step (and it will be replicated to all nodes).

Step 3: make this MySQL 5.7.17 replicate from PXC

So now we need to create a replication user on the Galera cluster that we will use for this new MySQL 5.7 server (and later for the Group Replication’s recovery process):

mysql1> CREATE USER 'repl'@'192.168.90.%' IDENTIFIED BY 'password';
mysql1> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.90.%';

And we can start asynchronous replication from the Galera Cluster to this new MySQL server:

mysql3> CHANGE MASTER TO MASTER_HOST="192.168.90.10", MASTER_USER="repl", 
        MASTER_PASSWORD='password', MASTER_AUTO_POSITION=1;
mysql3> START SLAVE;

Now we have the following environment:

 

Step 4: migrate an extra node to the Group

Now we will almost do the same with mysql2:

1. stop mysql
2. install mysql community repository
3. swap the packages
4. modify my.cnf
5. put mysql2 in hostgroup_id 2 in ProxySQL
6. start mysqld
7. join the group

Let’s skip points 1 to 4.

Unlike Galera, it’s mandatory with MySQL Group Replication that all the nodes have a unique server_id. We must then be careful, in this case we will set it to 2.

Don’t forget to also swap the addresses for mysql3 and mysql2 between group_replication_local_address and group_replication_group_seeds:

loose-group_replication_local_address= "192.168.90.11:3406"
loose-group_replication_group_seeds= "192.168.90.10:3406,192.168.90.12:3406"

Put mysql2 in hostgroup_id 2 in ProxySQL, so you have:

ProxySQL Admin> select hostname, status, hostgroup_id from runtime_mysql_servers;
+---------------+---------+--------------+
| hostname      | status  | hostgroup_id |
+---------------+---------+--------------+
| 192.168.90.10 | ONLINE  | 1            |
| 192.168.90.12 | ONLINE  | 2            |
| 192.168.90.11 | SHUNNED | 2            |
+---------------+---------+--------------+

Start mysqld and it’s time to join the group (point 7) !

mysql2> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
mysql2> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='password' 
        FOR CHANNEL 'group_replication_recovery';
mysql2> START GROUP_REPLICATION;

So this is the new situation:

mysql3> select * from performance_schema.replication_group_members\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: 5221ffcf-c1e0-11e6-b1f5-08002718d305
 MEMBER_HOST: pxc2
 MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: 5a2d38db-c1e0-11e6-8bf6-08002718d305
 MEMBER_HOST: pxc3
 MEMBER_PORT: 3306
MEMBER_STATE: ONLINE

Step 5: move the application to our new Cluster

It’s time now to let the application connect to our new MySQL Group Replication Cluster. In ProxySQL, we change the hostgroup_id for mysql2 and mysql3 to 1 and to 2 for mysql1, then we load it runtime and we stop mysql on mysql1:

ProxySQL Admin> select hostname, status, hostgroup_id from runtime_mysql_servers;
+---------------+--------+--------------+
| hostname      | status | hostgroup_id |
+---------------+--------+--------------+
| 192.168.90.10 | ONLINE | 1            |
| 192.168.90.12 | ONLINE | 2            |
| 192.168.90.11 | ONLINE | 2            |
+---------------+--------+--------------+
ProxySQL Admin> update mysql_servers set hostgroup_id =2 where hostname ="192.168.90.10";
ProxySQL Admin> update mysql_servers set hostgroup_id =1 where hostname ="192.168.90.11";
ProxySQL Admin> update mysql_servers set hostgroup_id =1 where hostname ="192.168.90.12";

ProxySQL Admin> load mysql servers to runtime;

ProxySQL Admin> select hostname, status, hostgroup_id from runtime_mysql_servers;
+---------------+--------+--------------+
| hostname      | status | hostgroup_id |
+---------------+--------+--------------+
| 192.168.90.12 | ONLINE | 1            |
| 192.168.90.11 | ONLINE | 1            |
| 192.168.90.10 | ONLINE | 2            |
+---------------+--------+--------------+

In this case as we are using ProxySQL, as soon as mysql1 (192.168.90.10) changes group, all the connection to it are killed and they will reconnect to the new nodes that are now part of the MySQL Group Replication.

[root@mysql1 ~]# systemctl stop mysql

To finish, we have two options, or we configure the remaining PXC node to be slave for a while, so we can still decide to rollback the migration (I would then consider to add an extra node to the current MySQL Group Replication Cluster, as with 2 nodes, the cluster is not tolerant to any failure). Or we can directly migrate the last Galera node to Group Replication.

Conclusion

As you could see, migrate your current Galera environment to MySQL Group Replication is not complicated and can be done with really minimal impact.

Don’t hesitate to leave your comments or questions like usual.

 

Note:

It’s also possible to swap Setp 2 and Step3, this means that the asynchronous replication is started before the bootstrap of the Group Replication. In that case, it might happen that the asynchronous replication fails while starting Group Replication, as the replication recovery is started and therefore no transaction can be executed.

You can see the following in SHOW SLAVE STATUS:

Last_SQL_Error: Error in Xid_log_event: Commit could not be completed, 
'Error on observer while running replication hook 'before_commit'.'

The error log also gives you information about it:

[Note] Plugin group_replication reported: 'Starting group replication recovery 
       with view_id 14817109352506883:1'
[Note] Plugin group_replication reported: 'Only one server alive. 
       Declaring this server as online within the replication group'
[ERROR] Plugin group_replication reported: 'Transaction cannot be executed while Group Replication 
        is recovering. Try again when the server is ONLINE.'
[ERROR] Run function 'before_commit' in plugin 'group_replication' failed
[ERROR] Slave SQL for channel '': Error in Xid_log_event: Commit could not be completed, 
        'Error on observer while running replication hook 'before_commit'.', Error_code: 3100
[Warning] Slave: Error on observer while running replication hook 'before_commit'. Error_code: 3100
[ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart 
        the slave SQL thread with "SLAVE START". We stopped at log 'pxc1-bin.000009' position 76100764
[Note] Plugin group_replication reported: 'This server was declared online within the replication group'

Restarting again replication solves the problem:

mysql3> STOP SLAVE;
mysql3> START SLAVE;