Step by Step Guide – Group Replication

by | Jul 5, 2018 | MySQL

Single Server Group Replication Setup

 
  1. Initialize three mysql instances
  2. Generate UUID for group name
  3. Prepare the config file with group replication and regular replication parameters
  4. Start Mysql for all the three instances
  5. Primary Member ( port = 24801 )
    • Replication user creation
    • Assign the credentials to group_replication_recovery replication channel
    • Install group Replication plugin
    • Enable Group Replication plugin
    • Bootstrap the primary node of the group.
  6. Secondary Members ( port = 24802 and port = 24803 )
    • Replication user creation
    • Assign the credentials to group_replication_recovery replication channel
    • Install group Replication plugin
    • Enable Group Replication plugin
NOTE : Group replication is also referred as Innodb Cluster  

Initialize three mysql instances

Start three instances using below command and this would create three instances. We should have three instances running at below ports mys1.cnf:port = 24801 mys2.cnf:port = 24802 mys3.cnf:port = 24803   
cat > setup_replication.sh
mysqldemon=`which mysqld`
mkdir -p /mysql/s1/data/ /mysql/s2/data/ /mysql/s3/data/
echo -e "------------------------ S1 -----------------------"
$mysqldemon --initialize-insecure --basedir=/usr/sbin --datadir=/mysql/s1/data
echo -e "------------------------ S2 -----------------------"
$mysqldemon --initialize-insecure --basedir=/usr/sbin --datadir=/mysql/s2/data
echo -e "------------------------ S3 -----------------------"
$mysqldemon --initialize-insecure --basedir=/usr/sbin --datadir=/mysql/s3/data
chown -R mysql. /mysql/s*

Prepare my.cnf parameters

The highlighted parameters are mandatorily needed alone with group replication parameters . As you see gtid_mode and enforce-gtid-consistency enables GTID, master_info_repository and relay_log_info_repository is to record slave logs master status and connection information in a table mysql.slave_master_info and mysql.slave_master_info instead of master.info or relay.info files. Rest of the other parameters are regular binlogs and relay logs as needed for traditional replication

 

Replication Parameters

 

#################################################
## Replication related settings
#################################################
server_id = 1
## Enable GTID Mode
gtid_mode = ON
enforce-gtid-consistency = ON
## Replication Repo tables
master_info_repository = TABLE
relay_log_info_repository = TABLE
## Binlog parameters
log_slave_updates = ON
binlog_format = ROW
log-bin = /mysql/s1/mysql-bin
log-bin-index = /mysql/s1/bin-log.index
## Relay log parameters
relay-log = /mysql/s1/mysql-relay-bin
relay-log-index = /mysql/s1/mysql-relay-bin
relay_log_info_file = /mysql/s1/relay-log.info
#################################################

 

Group Replication Parameters

We need to the prefix loose- for the group_replication variables listed above which would allow mysql service to continue to start even if the Group Replication plugin is not loaded.Before that we need to generate a uuid as below to give a name to group replication cluster

 

mysql> select uuid();
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| 6801f2d2-7f38-11e8-88c8-00505681ffc4 |
+--------------------------------------+
1 row in set (0.00 sec)

Substitute the above UUID to loose-group_replication_group_name as below. you can generate your own UUID and have it replaced it here. loose-group_replication_local_address parameter should have different ports for each of the instace if you miss to change that you won’t be able to add the instance to the group.

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name=”6801f2d2-7f38-11e8-88c8-00505681ffc4″
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

 

 

Start Mysql Instances

[root@testdb01 mysql]# cd /mysql
[root@testdb01 mysql]# mysqld --defaults-file=./mys1.cnf --user=mysql &
[1] 5973
[root@testdb01 mysql]# mysqld --defaults-file=./mys2.cnf --user=mysql &
[2] 6016
[root@testdb01 mysql]# mysqld --defaults-file=./mys3.cnf --user=mysql &
[3] 6057

Primary Node Setup

  1. Replication User Creation steps

    • SET SQL_LOG_BIN=0;SET session SQL_LOG_BIN=0;
      CREATE USER ‘replication_user’@’%’ IDENTIFIED by ‘secret_password’;
      GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘replication_user’@’%’;
      flush privileges;
      SET session SQL_LOG_BIN=1;

  2. Assign the credentials to group_replication_recovery replication channel

    • CHANGE MASTER TO MASTER_USER=’replication_user’, MASTER_PASSWORD=’secret_password’ FOR CHANNEL ‘group_replication_recovery’;

  3. Install group Replication plugin

    • INSTALL PLUGIN group_replication SONAME ‘group_replication.so’;

  4. Bootstrap the primary node of the group.

    • SET GLOBAL group_replication_bootstrap_group=ON;

  5. Enable Group Replication plugin

    • START GROUP_REPLICATION; SET GLOBAL group_replication_bootstrap_group=OFF;

  6. Verify through performance_schema.replication_group_members

NOTE : If the above steps went fine you would be able to see a member added to the group we initiated.

SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------------------------+-------------+--------------+
| group_replication_applier | 9426b7a0-7f59-11e8-8d83-00505681ffc4 | testdb01.genexdbs.com | 24801 | ONLINE |
+---------------------------+--------------------------------------+--------------------------------+-------------+--------------+
1 row in set (0.00 sec)

Secondary Nodes Setup

  1. Replication User Creation steps

    • SET SQL_LOG_BIN=0;SET session SQL_LOG_BIN=0;
      CREATE USER ‘replication_user’@’%’ IDENTIFIED by ‘secret_password’;
      GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘replication_user’@’%’;
      flush privileges;
      SET session SQL_LOG_BIN=1;

  2. Assign the credentials to group_replication_recovery replication channel

    • CHANGE MASTER TO MASTER_USER=’replication_user’, MASTER_PASSWORD=’secret_password’ FOR CHANNEL ‘group_replication_recovery’;

  3. Install group Replication plugin

    • INSTALL PLUGIN group_replication SONAME ‘group_replication.so’;

  4. Enable Group Replication plugin

    • START GROUP_REPLICATION;

  5. Verify through performance_schema.replication_group_members

NOTE : If the above steps went fine you would be able to see a member added to the group we initiated.

 

SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST                    | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------------------------+-------------+--------------+
| group_replication_applier | 9426b7a0-7f59-11e8-8d83-00505681ffc4 | testdb01.genexdbs.com          | 24801       | ONLINE       |
| group_replication_applier | a00b9f15-7f59-11e8-90a1-00505681ffc4 | testdb01.genexdbs.com          | 24802       | ONLINE       |
| group_replication_applier | aca37cde-7f59-11e8-9340-00505681ffc4 | testdb01.genexdbs.com          | 24803       | ONLINE       |
+---------------------------+--------------------------------------+--------------------------------+-------------+--------------+
%d