Step-by-Step Guide to Setting Up MySQL 5.7 Group Replication on One Server

Introduction

Previously we briefly introduced the role and features of MySQL 5.7.17 Group Replication; now we will configure it to better understand the replication concept.

Practice steps:

Install three MySQL instances (s1, s2, s3) on one server.

Configure s1 and start Group Replication.

Add s2 to the group.

Add s3 to the group.

Test the setup.

The process is lengthy; a PDF version is also available—send “gr” to receive the download link.

Detailed Configuration Process

(1) Download MySQL 5.7.17

https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz

(2) Install

Extract:

tar zxf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz
mv mysql-5.7.17-linux-glibc2.5-x86_64 /usr/local/mysql-5.7
cd /usr/local

Initialize three MySQL data directories (s1, s2, s3):

mkdir data
mysql-5.7/bin/mysqld --initialize-insecure --basedir=$PWD/mysql-5.7 --datadir=$PWD/data/s1
mysql-5.7/bin/mysqld --initialize-insecure --basedir=$PWD/mysql-5.7 --datadir=$PWD/data/s2
mysql-5.7/bin/mysqld --initialize-insecure --basedir=$PWD/mysql-5.7 --datadir=$PWD/data/s3

(3) Create MySQL system user

groupadd mysql5.7
useradd -g mysql5.7 mysql5.7
chown -R mysql5.7:mysql5.7 /usr/local/mysql-5.7
chown -R mysql5.7:mysql5.7 /usr/local/data
su mysql5.7

(4) Configure s1 and start Group Replication

Create configuration file data/s1/s1.cnf with the following content:

[mysqld]

datadir=/usr/local/data/s1
basedir=/usr/local/mysql-5.7/

port=24801
socket=/usr/local/data/s1/s1.sock

server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="127.0.0.1:24901"
loose-group_replication_group_seeds="127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks=TRUE

Start s1:

nohup mysql-5.7/bin/mysqld --defaults-file=data/s1/s1.cnf >data/s1/nohup.out 2>&1 &

Login to s1:

mysql-5.7/bin/mysql -uroot -h127.0.0.1 -P24801 --skip-password

Set root password:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'A123456';

Create replication user:

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';

Install Group Replication plugin:

INSTALL PLUGIN group_replication SONAME 'group_replication.so';

Verify plugin installation: SHOW PLUGINS; If successful, the result will contain a record for group_replication.

Start Group Replication:

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

Check group members:

SELECT * FROM performance_schema.replication_group_members;

Create a test database and table on s1 to verify replication:

CREATE DATABASE test;
USE test;
CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
INSERT INTO t1 VALUES (1, 'Luis');
SELECT * FROM t1;

(5) Add s2 to the replication group

Create s2 configuration file data/s2/s2.cnf (datadir points to s1 data for simplicity) with similar settings, changing port, socket, server_id, and local address to 127.0.0.1:24902.

[mysqld]

datadir=/usr/local/data/s1
basedir=/usr/local/mysql-5.7/

port=24802
socket=/usr/local/data/s1/s1.sock

server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="127.0.0.1:24902"
loose-group_replication_group_seeds="127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks=TRUE

Start s2:

nohup mysql-5.7/bin/mysqld --defaults-file=data/s2/s2.cnf >data/s2/nohup.out 2>&1 &

Login to s2 and create the same replication user as for s1.

Install and start Group Replication on s2:

INSTALL PLUGIN group_replication SONAME 'group_replication.so';
START GROUP_REPLICATION;

Check members (now two):

SELECT * FROM performance_schema.replication_group_members;

Verify the test table data is replicated.

(6) Add third MySQL instance s3

Create configuration file data/s3/s3.cnf with its own datadir, port 24803, socket, server_id=3, and appropriate replication settings.

[mysqld]

datadir=/usr/local/data/s3
basedir=/usr/local/mysql-5.7/

port=24803
socket=/usr/local/data/s3/s3.sock

server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="127.0.0.1:24903"
loose-group_replication_group_seeds="127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks=TRUE

Start s3, login, create replication user, install plugin, and start replication.

Check members (now three):

SELECT * FROM performance_schema.replication_group_members;

Verify test data is present on s3.

(7) Test data insertion on s3

Insert a new row into the test table on s3:

USE test;
INSERT INTO t1 VALUES (2, 's3 test');
SELECT * FROM t1;

Query s1 to confirm the row was replicated:

USE test;
SELECT * FROM t1;

Replication works; Group Replication configuration is complete.