Deploying MySql Group Replication in Single-Primary Mode

Group Replication is provided as a plugin to MySQL Server.

MySQL Group Replication enables you to create elastic, highly-available, fault-tolerant replication topologies and Group Replication guarantees that the database service is continuously available.

Groups can operate in a single-primary mode with the automatic primary election, where only one server accepts updates at a time. Alternatively, groups can be deployed in multi-primary mode, where all servers can accept updates, even if they are issued concurrently.

Group Replication also has a built-in, automatic, split-brain protection mechanism. All of this is powered by Group Communication System (GCS) protocols that provide a failure detection mechanism, a group membership service, and safe and completely ordered message delivery.

Server failures are isolated and independent. They are tracked by a group membership service that relies on a distributed failure detector that is able to signal when any servers leave the group, either voluntarily or due to an unexpected halt. There is a distributed recovery procedure to ensure that when servers join the group they are brought up to date automatically.

There is no need for server failover, and the multi-source update everywhere nature ensures that even updates are not blocked in the event of a single server failure. To summarize, MySQL Group Replication guarantees that the database service is continuously available.

For this demonstration, we have already configured 3 MySql Instances. Below are the details.

Hostname	IP	Role	Server_ID	Port	Installation Link
test-machine01	192.168.114.177	Member-1 (Master)	1	3306	Click Here
test-machine01	192.168.114.177	Member-2 (Replica)	3	3307	Click Here
test-machine02	192.168.114.176	Member-3 (Replica)	2	3306	Click Here

Member-1 Configurations

mysql>
mysql> prompt Member-1 >
PROMPT set to 'Member-1 >'
Member-1 >
Member-1 >select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+
1 row in set (0.07 sec)

Member-1 >select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 13ecba9c-444a-11eb-a397-000c29f9d9e6 |
+--------------------------------------+
1 row in set (0.00 sec)

Member-1 >select @@port;
+--------+
| @@port |
+--------+
|   3306 |
+--------+
1 row in set (0.07 sec)

Member-1 >

[mysqld@inst1]
#General
user = mysql
port = 3306
server_id = 1
socket=/var/lib/mysql/mysql.sock
pid_file=/var/run/mysqld/mysqld.pid

# Data Storage
datadir=/var/lib/mysql
innodb_directories="/u01/mysql/"

#Logging
log_bin = /var/lib/mysql/binlog
log_error = /var/lib/mysql/mysqld.log
expire_logs_days = 7
relay_log = /var/lib/mysql/relay_bin01
relay_log_index = /var/lib/mysql/relay_bin.index
relay_log_recovery = on
master_info_repository = TABLE
relay_log_info_repository = TABLE

# GTID Based Replication Parameter
gtid-mode=on
enforce-gtid-consistency=1
log_slave_updates= 1

Member-2 Configuration

mysql>
mysql> prompt Member-2>
PROMPT set to 'Member-2>'
Member-2>
Member-2>select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           3 |
+-------------+
1 row in set (0.06 sec)

Member-2>select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 98411332-6aad-11eb-809a-000c29f9d9e6 |
+--------------------------------------+
1 row in set (0.00 sec)

Member-2>select @@port;
+--------+
| @@port |
+--------+
|   3307 |
+--------+
1 row in set (0.02 sec)

Member-2>

[mysqld@inst2]
#General
user = mysql
port=3307
server_id = 3
socket=/u01/mysql-2/mysql.sock
pid_file=/u01/mysql-2/mysqld.pid

# Data Storage
basedir=/u01/mysql-2
datadir=/u01/mysql-2/data
innodb_directories="/u01/mysql/"
plugin_dir=/usr/lib64/mysql/plugin

#Logging
log_bin = /u01/mysql-2/data/binlog
log_error = /u01/mysql-2/data/mysqld.log
expire_logs_days = 7
relay_log = /u01/mysql-2/data/relay_bin01
relay_log_index = /u01/mysql-2/data/relay_bin.index
relay_log_recovery = on
master_info_repository = TABLE
relay_log_info_repository = TABLE

# GTID Based Replication Parameter
gtid-mode=on
enforce-gtid-consistency=1
log_slave_updates= 1

Member-3 Configuration

mysql> prompt Memer-3 >
PROMPT set to 'Memer-3 >'
Memer-3 >
Memer-3 >select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

Memer-3 >select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 13c84508-5014-11eb-af41-000c2997dedd |
+--------------------------------------+
1 row in set (0.00 sec)

Memer-3 >select @@port;
+--------+
| @@port |
+--------+
|   3306 |
+--------+
1 row in set (0.08 sec)

Memer-3 >

[mysqld@inst1]
#General
user = mysql
port = 3306
server_id = 2
bind-address = 0.0.0.0
socket=/u01/mysql-8/mysql.sock
pid_file=/u01/mysql-8/mysqld.pid

# Data Storage
basedir=/u01/mysql-8
datadir=/u01/mysql-8/data
innodb_directories="/u01/mysql-8;/u01/mysql"

#Logging
log_bin = /u01/mysql-8/data/binlog
log_error = /u01/mysql-8/data/mysqld.log
expire_logs_days = 7
relay_log = /u01/mysql-8/data/relay_bin01
relay_log_index = /u01/mysql-8/data/relay_bin.index
relay_log_recovery = on
master_info_repository = TABLE
relay_log_info_repository = TABLE

# GTID Based Replication Parameter
gtid-mode=on
enforce-gtid-consistency=1
log_slave_updates=1

Step 1. Configuring an Instance for Group Replication: Add the below Group Replication Settings and restart mysql instance. Use command SELECT UUID() to get the uuid for group replication. Also will use Port 33061 for member-to-member communication.

Member-1

Member-1 >SELECT UUID();
+--------------------------------------+
| UUID()                               |
+--------------------------------------+
| 81646864-9069-11eb-bb04-000c293ab022 |
+--------------------------------------+
1 row in set (0.00 sec)

Member-1 >
Member-1 >
[root@test-machine01 mysql]# vi /etc/my.cnf
#Group Replication Based Parameter
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
plugin_load_add='group_replication.so'
group_replication_group_name="81646864-9069-11eb-bb04-000c293ab022"
group_replication_start_on_boot=off
group_replication_local_address= "test-machine01:33061"
group_replication_group_seeds= "test-machine01:33061,test-machine02:33061"
group_replication_bootstrap_group=off
group_replication_recovery_get_public_key=ON

[root@test-machine01 etc]#
[root@test-machine01 etc]# systemctl restart mysqld@inst1
[root@test-machine01 etc]#

Member-2

[root@test-machine01 mysql]# vi /etc/my.cnf
#Group Replication Based Parameter
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
plugin_load_add='group_replication.so'
group_replication_group_name="81646864-9069-11eb-bb04-000c293ab022"
group_replication_start_on_boot=off
group_replication_local_address= "127.0.0.1:24901"
group_replication_group_seeds= "test-machine01:33061,test-machine02:33061"
group_replication_bootstrap_group=off
group_replication_recovery_get_public_key=ON
[root@test-machine01 mysql]#
[root@test-machine01 etc]#
[root@test-machine01 etc]# systemctl restart mysqld@inst2
[root@test-machine01 etc]#

Member-3

[root@test-machine02 data]# vi /etc/my.cnf
# GTID Based Replication Parameter
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
plugin_load_add='group_replication.so'
group_replication_group_name="81646864-9069-11eb-bb04-000c293ab022"
group_replication_start_on_boot=off
group_replication_local_address= "test-machine02:33061"
group_replication_group_seeds= "test-machine01:33061,test-machine02:33061"
group_replication_bootstrap_group=off

[root@test-machine02 data]#
[root@test-machine02 data]# systemctl restart mysqld@inst1
[root@test-machine02 data]#

Step 2. Create User For Distributed Recovery: Login to Member-1 mysql instance and create a user which will be used by group replication for recovery purposes.

Member-1

Member-1 >
Member-1 >SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

Member-1 >CREATE USER rpl_user@'%' IDENTIFIED BY 'Root@1234';
Query OK, 0 rows affected (0.08 sec)

Member-1 >ALTER USER 'rpl_user'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@1234';
Query OK, 0 rows affected (0.00 sec)

Member-1 >GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)

Member-1 >GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)

Member-1 >FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

Member-1 > SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

Member-1 >CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Root@1234' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.07 sec)

Member-1 >
Member-1 >

Step 3. Bootstrapping the Group: Make sure the group replication plugins are active and start the group replication process using the below commands. With these commands Group Replication will be bootstrap and Member-1 mysql instance will be added as Primary instance. Please find the below snippet from mysqld.log file. Once the process is done create some dummy database for testing purposes.

Member-1

Member-1 >
Member-1 >SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

Member-1 >START GROUP_REPLICATION;
Query OK, 0 rows affected (2.85 sec)

Member-1 > SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

Member-1 >
Member-1 >


Member-1 >
Member-1 >SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 13ecba9c-444a-11eb-a397-000c29f9d9e6 | test-machine01 |        3306 | ONLINE       | PRIMARY     | 8.0.22         |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
1 row in set (0.01 sec)

Member-1 >
Member-1 >

2021-03-29T08:36:04.949598Z 92 [System] [MY-013587] [Repl] Plugin group_replication reported: 'Plugin 'group_replication' is starting.'
2021-03-29T08:36:04.950246Z 103 [System] [MY-011565] [Repl] Plugin group_replication reported: 'Setting super_read_only=ON.'
2021-03-29T08:36:05.009755Z 104 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_applier' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.
2021-03-29T08:36:07.808635Z 0 [System] [MY-013471] [Repl] Plugin group_replication reported: 'Distributed recovery will transfer data using: Incremental recovery from a group donor'
2021-03-29T08:36:08.814954Z 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to test-machine01:3306 on view 16170069668043119:1.'
2021-03-29T08:36:08.815053Z 0 [System] [MY-011490] [Repl] Plugin group_replication reported: 'This server was declared online within the replication group.'
2021-03-29T08:36:09.816345Z 0 [System] [MY-011507] [Repl] Plugin group_replication reported: 'A new primary with address test-machine01:3306 was elected. The new primary will execute all previous group transactions before allowing writes.'
2021-03-29T08:36:09.817184Z 122 [System] [MY-011566] [Repl] Plugin group_replication reported: 'Setting super_read_only=OFF.'
2021-03-29T08:36:09.817509Z 122 [System] [MY-011510] [Repl] Plugin group_replication reported: 'This server is working as primary member.'


Member-1 >
Member-1 >CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)

Member-1 >USE test;
Database changed
Member-1 >CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
Query OK, 0 rows affected (0.09 sec)

Member-1 >INSERT INTO t1 VALUES (1, 'Luis');
Query OK, 1 row affected (0.16 sec)

Member-1 >

Step 4. Adding a Second Instance: Login to Member-2 mysql instance and perform the below steps. Once all steps are completed you will notice Member-2 mysql instance added as a replica with read-only status.

Member-2

Member-2 >
Member-2 >SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

Member-2 >CREATE USER rpl_user@'%' IDENTIFIED BY 'Root@1234';
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
Member-2 >
Member-2 >SET @@global.super_read_only = 0;
Query OK, 0 rows affected (0.00 sec)

Member-2 >CREATE USER rpl_user@'%' IDENTIFIED BY 'Root@1234';
Query OK, 0 rows affected (0.01 sec)

Member-2 >ALTER USER 'rpl_user'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@1234';
Query OK, 0 rows affected (0.00 sec)

Member-2 >GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.06 sec)

Member-2 >GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)

Member-2 >FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Member-2 >SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

Member-2 >CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Root@1234' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.10 sec)

Member-2 >START GROUP_REPLICATION;
Query OK, 0 rows affected (3.30 sec)

Member-2 >


Member-2 >select * from performance_schema.replication_group_members ;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 13c84508-5014-11eb-af41-000c2997dedd | test-machine02 |        3306 | ONLINE       | SECONDARY   | 8.0.22         |
| group_replication_applier | 13ecba9c-444a-11eb-a397-000c29f9d9e6 | test-machine01 |        3306 | ONLINE       | PRIMARY     | 8.0.22         |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)

Member-2 >

Step 5. Adding a third Instance: Login to Member-3 mysql instance and perform the below steps. Once all steps are completed, you will notice Member-3 mysql instance added as a replica with read-only status.

Member-3

Memer-3 >
Memer-3 > SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

Memer-3 > CREATE USER rpl_user@'%' IDENTIFIED BY 'Root@1234';
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

Memer-3 > SET @@global.super_read_only = 0;
Query OK, 0 rows affected (0.00 sec)

Memer-3 > CREATE USER rpl_user@'%' IDENTIFIED BY 'Root@1234';
Query OK, 0 rows affected (0.00 sec)

Memer-3 > ALTER USER 'rpl_user'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@1234';
Query OK, 0 rows affected (0.00 sec)

Memer-3 >GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)

Memer-3 >GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)

Memer-3 >FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

Memer-3 >SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

Memer-3 >CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Root@1234' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.06 sec)

Memer-3 >START GROUP_REPLICATION;
Query OK, 0 rows affected (4.40 sec)


Memer-3 >select * from performance_schema.replication_group_members ;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 13c84508-5014-11eb-af41-000c2997dedd | test-machine02 |        3306 | ONLINE       | SECONDARY   | 8.0.22         |
| group_replication_applier | 13ecba9c-444a-11eb-a397-000c29f9d9e6 | test-machine01 |        3306 | ONLINE       | PRIMARY     | 8.0.22         |
| group_replication_applier | 98411332-6aad-11eb-809a-000c29f9d9e6 | test-machine01 |        3307 | ONLINE       | SECONDARY   | 8.0.22         |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
3 rows in set (0.01 sec)

Memer-3 >

Step 6. Test Group Replication Configuration: Perform some transactions on Primary instance (Memer-1 mysql instance) and check the same transaction replicated in the others two replicas.

Member-1

Member-1 >
Member-1 >create database grouprepl_test;
Query OK, 1 row affected (0.06 sec)

Member-1 >use grouprepl_test;
Database changed
Member-1 >CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
Query OK, 0 rows affected (0.01 sec)

Member-1 >INSERT INTO t1 VALUES (1, 'Test');
Query OK, 1 row affected (0.00 sec)

Member-1 >commit;
Query OK, 0 rows affected (0.00 sec)

Member-1 >

Member-2

Member-2 >
Member-2 >use grouprepl_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
Member-2 >
Member-2 >select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Test |
+----+------+
1 row in set (0.00 sec)

Member-2 >

Member-3

Member-3 >
Member-3 >use grouprepl_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
Member-3 >
Member-3 >select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Test |
+----+------+
1 row in set (0.00 sec)

Member-3 >

Step 7. Stop Primary MySql Instance: Let stops PRIMARY MySql instance running on test-machine01. Group Replication will automatically will select another suitable Mysql Instance as Primary.

Member-1 >
Member-1 >
Member-1 >  select * from performance_schema.replication_group_members ;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 13c84508-5014-11eb-af41-000c2997dedd | test-machine02 |        3306 | ONLINE       | SECONDARY   | 8.0.22         |
| group_replication_applier | 13ecba9c-444a-11eb-a397-000c29f9d9e6 | test-machine01 |        3306 | ONLINE       | PRIMARY     | 8.0.22         |
| group_replication_applier | 98411332-6aad-11eb-809a-000c29f9d9e6 | test-machine01 |        3307 | ONLINE       | SECONDARY   | 8.0.22         |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

Member-1 >
Member-1 >


[root@test-machine01 mysql]#
[root@test-machine01 mysql]# systemctl stop mysqld@inst1
[root@test-machine01 mysql]#



Member-3 >
Member-3 >  select * from performance_schema.replication_group_members ;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 13c84508-5014-11eb-af41-000c2997dedd | test-machine02 |        3306 | ONLINE       | PRIMARY     | 8.0.22         |
| group_replication_applier | 98411332-6aad-11eb-809a-000c29f9d9e6 | test-machine01 |        3307 | ONLINE       | SECONDARY   | 8.0.22         |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)

Member-3 >
Member-3 >

Reference : Group Replication : https://dev.mysql.com/doc/refman/8.0/en/group-replication-getting-started.html

This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.

Recent articles

MYSQL, REPLICATION