MySQL 8.0 Group Replication – Three-server installation

MySQL InnoDB Cluster was introduced in MySQL version 5.7. MySQL InnoDB Cluster consists of three parts – Group Replication, MySQL Shell and MySQL Router. MySQL InnoDB Cluster provides a complete high availability solution for MySQL. I am not going to go into the details of InnoDB Cluster and how it works, as there are enough manual pages and blogs to cover these topics.

MySQL InnoDB Cluster manual
Blog sites: mysqlhighavailability.com and mysqlserverteam.com.

Instead, I will be showing you how to install Group Replication on three new installations of mysql 8.0 manually, without using the MySQL Shell.

These instructions should enable you to setup Group Replication in less than an hour. I am doing this on a Mac running 10.13, but most of these commands can easily be translated over to Linux or Windows. I will try to supply the correct commands for all three operating systems.

I will be installing Group Replication on three new installations of MySQL (without any data) with the IP addresses (host names) of 192.168.1.151 (ic-1), 192.168.1.152 (ic-2) and 192.168.1.153 (ic-3). It is important that you don’t run any other commands on the server, and that you start with a fresh install of MySQL. If you already have a server with data, you will need to export the data and import it into the other servers before you go any further here. Starting Group Replication with a server with data requires a different set of commands, and this blog might not work in that situation.

Group Replication may be setup as either a single-primary (one server to handle the writes and two servers for reads), or multi-primary (read/write to any of the servers). This post covers setting up a single-primary configuration. The server with the IP address of 192.168.1.151 will be our single-primary (read/write server) and the other two servers will be read-only.

Let’s begin.

Edit your /etc/hosts file, and add the IP addresses and host names for the three servers.

192.168.1.151 ic-1
192.168.1.152 ic-2
192.168.1.153 ic-3

Flush the directory service cache by running this as root:

Mac - dscacheutil -flushcache
Linux - /etc/rc.d/init.d/nscd restart
Windows - ipconfig /flushdns

You will need to add some variables to your MySQL options file, which is located in these directories: (see Using Option Files)

Mac and Linux - /etc/my.cnf
Windows - C:\ProgramData\MySQL\MySQL Server X (where X is the version number of MySQL)

NOTE: On Windows, the my.ini file may be hidden.

The following variables need to be in the options file under the [mysqld] section: (you may remove the # comment lines if you want, but read each line before deleting it)

You will need to change the server_id value for each server so each has a unique ID. I simply used 1, 2 and 3 for the three servers.

# All members in the group requires a unique server_id greater than zero
server_id=1

# this is the default port for MySQL - you can change it if you want, but it should be the same on all servers
port=3306

# you may specify a name for the binary log, or leave it blank to use the default name
# however, binary logging is required
log_bin=mysql-bin  

# these settings are required
log_slave_updates=ON
binlog_checksum=NONE
enforce_gtid_consistency=ON
gtid_mode=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64

# Group Replication specific options

# this is the name of the plugin
# load the plugin - in Windows, the plugin will be named group_replication.dll

plugin_load_add ="group_replication.so"

# this is required
group_replication = FORCE_PLUS_PERMANENT

# you will turn this on and back off during Group Replication setup
group_replication_bootstrap_group = OFF

# the group_replication_group_name is the UUID for the entire group
# Each server has their own UUID in the file auto.cnf located in the MySQL data directory
# You can generate your own group_replication_group_name on Linux with `uuidgen -t`, 
# on a Mac use "uuidgen" 
# for Windows - The Windows SDK comes with a tool called uuidgen
# all members use this value as group_replication_group_name

# PLEASE NOTE: this group_replication_group_name must be unique for each Group Replication (GR) group
# so each server within a group will have the same value
# Example: if you have two GR groups with three nodes in each group, then three nodes in one group
# will have the same group_replication_group_name, and the other three nodes in the different
# group will have a different group_replication_group_name
group_replication_group_name = 8E2F4761-C55C-422F-8684-D086F6A1DB0E

In the config file make sure group_replication_start_on_boot is set to OFF or add a # (comment) to the line with the value of ON as shown below. You will want to uncomment this line after you setup and start Group Replication, so if the server is rebooted, Group Replication will begin automatically.

# uncomment this line AFTER you have started Group Replication
# so that Group Replication will start after a reboot

#group_replication_start_on_boot = ON

You will need to change this line to match each of the servers, and while you can change the group_replication_local_address (or port number – the recommended default value is 33061), it doesn’t matter what port you use as long as that port isn’t being used by another application.

# change this to be the local address for each server
# the port number can be anything except ports already in use
# and do not use 3306

group_replication_local_address = '192.168.1.151:33061'

This line must contain all of the servers that will be in your group. The group_replication_group_seeds is a list of group members used to establish the connection from the new member to the group.

# add all of the members of the group here, along with the same port numbers

group_replication_group_seeds = '192.168.1.151:33061,192.168.1.152:33061,192.168.1.153:33061'

That is all you need for the configuration file. After you have made the changes, reboot the MySQL instance.

---

NOTE: If you installed MySQL as the root user, be sure that the OS user “mysql” owns all of the mysql directories. You will need to change the directory name (in this example it is /usr/local) to be the directory where you installed the MySQL Server.

# be sure that the mysql user own the mysql directory
# if you install MySQL via root on a Mac or Linux, there is a good chance that root owns the directory

$ cd /usr/local
$ chown -R mysql mysql*

---

Now we are ready to install Group Replication. Be sure to restart the mysqld processes to make the /etc/my.cnf changes permanent.

Let’s start with Server #1, which will be our read-write primary server.

On Server #1 (IP 192.168.1.151)

Since we included the variable group_replication_group_seeds in the options file, each instance has already been added to the group.

The table performance_schema.replication_group_members shows network and status information for replication group members. The network addresses shown are the addresses used to connect clients to the group, and should not be confused with the member’s internal group communication address specified by group_replication_local_address. (source: https://dev.mysql.com/doc/refman/8.0/en/replication-group-members-table.html)

You can run this command from a mysql prompt on each of the servers to see if they have joined the group successfully:

# command to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members;

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST      | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 60889f20-48ed-11e8-b6e2-0998e2a48fe0 | MacVM151-2.local |        3306 | OFFLINE      |             |                |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)

Or, you can run the command with the \G at the end instead of the semi-colon (;)

# command to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members\G

mysql> SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: 60889f20-48ed-11e8-b6e2-0998e2a48fe0
   MEMBER_HOST: MacVM151-2.local
   MEMBER_PORT: 3306
  MEMBER_STATE: OFFLINE
   MEMBER_ROLE: 
MEMBER_VERSION: 
1 row in set (0.00 sec)

The MEMBER_ID 60889f20-48ed-11e8-b6e2-0998e2a48fe0 is the UUID for this particular MySQL instance. The UUID is located in the auto.cnf file in the MySQL data directory.

# cat /usr/local/mysql/data/auto.cnf
[auto]
server-uuid=60889f20-48ed-11e8-b6e2-0998e2a48fe0

You can take a look at the MASTER STATUS of the primary server, and it should be relatively blank.

# command to run from MySQL prompt

SHOW MASTER STATUS\G

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 151
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

The active binary log for the server is “mysql-bin.000001“. You can take a look at the events in the log and see nothing has happened on the server.

# command to run from MySQL prompt

SHOW BINLOG EVENTS in 'mysql-bin.000001'\G

mysql> SHOW BINLOG EVENTS in 'mysql-bin.000001'\G
*************************** 1. row ***************************
   Log_name: mysql-bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 151
End_log_pos: 124
       Info: Server ver: 8.0.11, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000001
        Pos: 124
 Event_type: Previous_gtids
  Server_id: 151
End_log_pos: 151
       Info: 
2 rows in set (0.00 sec)

---

Now go to Server #2 (IP 192.168.1.152)

Confirm that Server #2 is part of the group.

# command to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members;

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | f4112942-48ed-11e8-86c4-f57773daeb0a | MacVM152.local |        3306 | OFFLINE      |             |                |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)

OPTIONAL: If you want, you can run the same informational commands for Server #2 as you did on Server #1. (SHOW MASTER STATUS, SHOW BINLOG EVENTS, cat auto.cnf)

---

Now go to Server #3 (IP 192.168.1.153)

Confirm that Server #3 is part of the group.

# command to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members;

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | bedc9968-48ee-11e8-9735-0a5899f91373 | MacVM153.local |        3306 | OFFLINE      |             |                |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
1 row in set (0.01 sec)

OPTIONAL: If you want, you can run the same informational commands on Server #3 as you did on Server #1. (SHOW MASTER STATUS, SHOW BINLOG EVENTS, cat auto.cnf)

---

Make sure that the “group_replication” plugin is active on all three servers. You value of PLUGIN_STATUS should be ACTIVE. Run this command on all three servers.

# command to run from MySQL prompt

select * from information_schema.plugins where PLUGIN_NAME = 'group_replication'\G

mysql> select * from information_schema.plugins where PLUGIN_NAME = 'group_replication'\G
*************************** 1. row ***************************
           PLUGIN_NAME: group_replication
        PLUGIN_VERSION: 1.1
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: GROUP REPLICATION
   PLUGIN_TYPE_VERSION: 1.2
        PLUGIN_LIBRARY: group_replication.so
PLUGIN_LIBRARY_VERSION: 1.9
         PLUGIN_AUTHOR: ORACLE
    PLUGIN_DESCRIPTION: Group Replication (1.1.0)
        PLUGIN_LICENSE: GPL
           LOAD_OPTION: FORCE_PLUS_PERMANENT
1 row in set (0.00 sec)

You should see the same output on the other two servers (IP 192.168.1.152 and 192.168.1.152) as on Server #1.

---

Next you will want to create the replication users. Since we will be turning on replication, we don’t want to write this to the binary logs. Execute this command on all of the servers.

# run this on all three servers
# commands to run from MySQL prompt

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'R3plic4tion!';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

NOTE: If you are using SHA-256 for your passwords, you need to specify sha256_password in your CREATE USER statement (see below). See: https://dev.mysql.com/doc/refman/8.0/en/sha256-pluggable-authentication.html

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED WITH sha256_password BY 'R3plic4tion!';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

Once the users have been created, we can start execute our CHANGE MASTER statement and start Group Replication. Execute these two commands on all of the servers.

# run this on all three servers

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='R3plic4tion!' FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;

We have to create the replication users, but we don’t want to write this to the binary log, as it would get replicated to the other servers and cause an error.

On Server #1 (IP 192.168.1.151)

# commands to run from MySQL prompt

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'R3plic4tion!';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'R3plic4tion!';
Query OK, 0 rows affected (0.04 sec)

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

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

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

Since we are only running single-primary mode, we don’t need to do a CHANGE MASTER on Server #1. In replication, the slave is responsible for connecting to the master to get the write statements. Since the secondary nodes won’t be accepting any writes, then the primary won’t need to connect to them. But, since the primary could fail, and be brought back into the group as a read-only slave, we need to go ahead and run the following CHANGE MASTER statement.

The CHANGE MASTER statement will produce two warnings – we can look at the warnings and ignore them.

# commands to run from MySQL prompt

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='R3plic4tion!' FOR CHANNEL 'group_replication_recovery';

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='R3plic4tion!' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                              |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure.                                                                                                                                                                                                               |
| Note  | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Now we will need to turn group_replication_bootstrap_group to ON, and then we can start Group Replication (but do this only for on Server #1). We will want to turn group_replication_bootstrap_group to OFF after we have started Group Replication for the first time.

# commands to run from MySQL prompt

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

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

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (3.10 sec)

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

Check the status of the MASTER server:

# commands to run from MySQL prompt

SHOW MASTER STATUS\G

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 458
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1
1 row in set (0.00 sec)

There has only been one GTID executed on the MASTER – 8e2f4761-c55c-422f-8684-d086f6a1db0e:1. Check the binlog to see what has been written to it.

# commands to run from MySQL prompt

SHOW BINLOG EVENTS in 'mysql-bin.000001';

mysql> SHOW BINLOG EVENTS in 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                              |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |       151 |         124 | Server ver: 8.0.11, Binlog ver: 4                                 |
| mysql-bin.000001 | 124 | Previous_gtids |       151 |         151 |                                                                   |
| mysql-bin.000001 | 151 | Gtid           |       151 |         229 | SET @@SESSION.GTID_NEXT= '8e2f4761-c55c-422f-8684-d086f6a1db0e:1' |
| mysql-bin.000001 | 229 | Query          |       151 |         291 | BEGIN                                                             |
| mysql-bin.000001 | 291 | View_change    |       151 |         390 | view_id=15247058086370221:1                                       |
| mysql-bin.000001 | 390 | Query          |       151 |         458 | COMMIT                                                            |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
6 rows in set (0.00 sec)

We still only have one member of the Group Replication, and we can see which server is a member:

# commands to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members;

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST      | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 60889f20-48ed-11e8-b6e2-0998e2a48fe0 | MacVM151-2.local |        3306 | ONLINE       | PRIMARY     | 8.0.11         |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)

# commands to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members\G

mysql> SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: 60889f20-48ed-11e8-b6e2-0998e2a48fe0
   MEMBER_HOST: MacVM151-2.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.11
1 row in set (0.00 sec)

You can see one member of the group (Server #1) is online. Group Replication has been started on Server #1.

Now we can add Server #2. We will do the same steps as we did for Server #1, except we don’t need to bootstrap Group Replication, as it has already been started. Oon Server #2 (IP 192.168.1.152):

# commands to run from MySQL prompt

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'R3plic4tion!';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'R3plic4tion!';
Query OK, 0 rows affected (0.03 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

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

# commands to run from MySQL prompt

SHOW MASTER STATUS\G

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 151
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

Run the CHANGE MASTER statement:

# commands to run from MySQL prompt

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='R3plic4tion!' FOR CHANNEL 'group_replication_recovery';

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='R3plic4tion!' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                              |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure.                                                                                                                                                                                                               |
| Note  | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

The CHANGE MASTER statement produced two warnings – we can look at the warnings and ignore them.
# commands to run from MySQL prompt

SHOW WARNINGS\G

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1759
Message: Sending passwords in plain text without SSL/TLS is extremely insecure.
*************************** 2. row ***************************
  Level: Note
   Code: 1760
Message: Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2 rows in set (0.00 sec)

Start Group Replication on Server #2:

# commands to run from MySQL prompt

START GROUP_REPLICATION;

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (3.10 sec)

---

NOTE: If you put in the wrong password in your CHANGE MASTER statement, the server will be in the group, but will be in a MEMBER_STATE of RECOVERING and it will never join the group.

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST      | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 60889f20-48ed-11e8-b6e2-0998e2a48fe0 | MacVM151-2.local |        3306 | ONLINE       | PRIMARY     | 8.0.11         |
| group_replication_applier | f4112942-48ed-11e8-86c4-f57773daeb0a | MacVM152.local   |        3306 | RECOVERING   | SECONDARY   | 8.0.11         |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)

---

Check to see that Server #2 has joined the group. You can execute this command on Server #1 or #2. (This was executed from Server #1)

# commands to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members;

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST      | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 60889f20-48ed-11e8-b6e2-0998e2a48fe0 | MacVM151-2.local |        3306 | ONLINE       | PRIMARY     | 8.0.11         |
| group_replication_applier | f4112942-48ed-11e8-86c4-f57773daeb0a | MacVM152.local   |        3306 | ONLINE       | SECONDARY   | 8.0.11         |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)

Or you can run it with the \G at the end.

# commands to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members\G

mysql> SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: 60889f20-48ed-11e8-b6e2-0998e2a48fe0
   MEMBER_HOST: MacVM151-2.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.11
*************************** 2. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: f4112942-48ed-11e8-86c4-f57773daeb0a
   MEMBER_HOST: MacVM152.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.11
2 rows in set (0.00 sec)

Running the same commands from Server #2 gets the same results, as you can query the status of the Group from any member of the group.

This was executed from Server #2

# commands to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members;

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST      | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 60889f20-48ed-11e8-b6e2-0998e2a48fe0 | MacVM151-2.local |        3306 | ONLINE       | PRIMARY     | 8.0.11         |
| group_replication_applier | f4112942-48ed-11e8-86c4-f57773daeb0a | MacVM152.local   |        3306 | ONLINE       | SECONDARY   | 8.0.11         |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)

And the option with \G at the end:
# commands to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members\G

mysql> SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: 60889f20-48ed-11e8-b6e2-0998e2a48fe0
   MEMBER_HOST: MacVM151-2.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.11
*************************** 2. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: f4112942-48ed-11e8-86c4-f57773daeb0a
   MEMBER_HOST: MacVM152.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.11
2 rows in set (0.00 sec)

Group replication now has Server #1 and Server #2. Now we can add Server #3 (IP 192.168.1.153)

We run the same commands as above. Note:I only show the commands to run – I do not show the screen output here.

# commands to run from MySQL prompt

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'R3plic4tion!';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='R3plic4tion!' FOR CHANNEL 'group_replication_recovery';

START GROUP_REPLICATION;

If you didn’t see any errors, Group Replication is ready to go. As before, you can check the status of the group from any server.

# commands to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members\G

mysql> SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: 60889f20-48ed-11e8-b6e2-0998e2a48fe0
   MEMBER_HOST: MacVM151-2.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.11
*************************** 2. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: bedc9968-48ee-11e8-9735-0a5899f91373
   MEMBER_HOST: MacVM153.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.11
*************************** 3. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: f4112942-48ed-11e8-86c4-f57773daeb0a
   MEMBER_HOST: MacVM152.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.11
3 rows in set (0.00 sec)

Or you can run:

# commands to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members;

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST      | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 60889f20-48ed-11e8-b6e2-0998e2a48fe0 | MacVM151-2.local |        3306 | ONLINE       | PRIMARY     | 8.0.11         |
| group_replication_applier | bedc9968-48ee-11e8-9735-0a5899f91373 | MacVM153.local   |        3306 | ONLINE       | SECONDARY   | 8.0.11         |
| group_replication_applier | f4112942-48ed-11e8-86c4-f57773daeb0a | MacVM152.local   |        3306 | ONLINE       | SECONDARY   | 8.0.11         |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

NOTICE: Be sure to change group_replication_start_on_boot to ON in your my.ini or my.cnf configuration file for all of the servers as shown below.

(in the my.cnf or my.ini file)
group_replication_start_on_boot = ON

If the MEMBER_STATE of three servers is ONLINE, then you are finished and Group Replication has been installed!

---

Creating an InnoDB Cluster

If you have an existing deployment of Group Replication and you want to use it to create a cluster, pass the adoptFromGR option to the dba.createCluster() function. The created InnoDB cluster matches whether the replication group is running as single-primary or multi-primary. See: https://dev.mysql.com/doc/refman/8.0/en/mysql-innodb-cluster-from-group-replication.html

---

	Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
	Tony is the author of Twenty Forty-Four: The League of Patriots  Visit http://2044thebook.com for more information.
	Tony is the editor/illustrator for NASA Graphics Standards Manual Remastered Edition  Visit https://amzn.to/2oPFLI0 for more information.

Advertisement