MySQL Group Replication on Ubuntu 16.04

Scenario, we want to set up mysql group replication in 2 mysql nodes. Both are master and master (can read and write).

We implement the nodes in cloud. Each node has 1 IP Public and 1 IP Private. We set the nodes in same local network (switch).

Mysql Server IP Private Type
Node 1 172.16.0.1 Master
Node 2 172.16.0.2 Master

How to install mysql server in ubuntu you can see in previous post Install-mysql-in-Ubuntu-16.04.

1. Configure my.cnf file in Node 1 and Node 2

Generate unique code by uuidgen command (only in node 1), copy the code and save it in somewhere. The code will be used for loose-group_replication_group_name in my.cnf

1
2
root@abcdefg:~# uuidgen
0dcbd0f2-8e47-4f04-9ae1-2650f5087d7b

Then edit my.cnf in node 1 and node 2

1
root@abcdefg:~# sudo nano /etc/mysql/my.cnf

my.cnf file for node 1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

[mysqld]

# General replication settings
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_bootstrap_group = OFF
loose-group_replication_start_on_boot = ON
loose-group_replication_ssl_mode = REQUIRED
loose-group_replication_recovery_use_ssl = 1

# Shared replication group configuration
loose-group_replication_group_name = "0dcbd0f2-8e47-4f04-9ae1-2650f5087d7b"
loose-group_replication_ip_whitelist = "172.16.0.1,172.16.0.2"
loose-group_replication_group_seeds = "172.16.0.1:33061,172.16.0.2:33061"

# Single or Multi-primary mode? Uncomment these two lines
# for multi-primary mode, where any host can accept writes
loose-group_replication_single_primary_mode = OFF
loose-group_replication_enforce_update_everywhere_checks = ON

# Host specific replication configuration
server_id = 1
bind-address = "172.16.0.1"
report_host = "172.16.0.1"
loose-group_replication_local_address = "172.16.0.1:33061"

my.cnf file for node 2 will be similar with node 1. The difference is only in the #Host specific replication configuration section. So just replace that secton with this line below.

1
2
3
4
5
# Host specific replication configuration
server_id = 2
bind-address = "172.16.0.2"
report_host = "172.16.0.2"
loose-group_replication_local_address = "172.16.0.2:33061"

Restart mysql to apply the changes

1
root@abcdefg:~# sudo systemctl restart mysql

Open up access to these two ports in our firewall

1
2
root@abcdefg:~# sudo ufw allow 33061
root@abcdefg:~# sudo ufw allow 3306

Do not forget to open up access in your external firewall if you have it. If you use the Alibaba cloud instance, you can open the port by following this tutorial https://www.alibabacloud.com/help/doc-detail/25475.htm

2. Configure Replication User and Enable Group Replication Plugin

Do it in node 1 and node 2

1
root@abcdefg:~# mysql -u root -p

Then you will be in mysql console

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'password' REQUIRE SSL;
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
mysql> SHOW PLUGINS;
+----------------------------+----------+--------------------+----------------------+---------+
| Name                       | Status   | Type               | Library              | License |
+----------------------------+----------+--------------------+----------------------+---------+
|                            |          |                    |                      |         |
| . . .                      | . . .    | . . .              | . . .                | . . .   |
|                            |          |                    |                      |         |
| group_replication          | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |
+----------------------------+----------+--------------------+----------------------+---------+
45 rows in set (0.00 sec)

3. Start Group Replication

In node 1:

1
2
3
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

You only need to set ON and OFF for SET GLOBAL group_replication_bootstrap_group in one node only, and for another node you just do START GROUP_REPLICATION.
Run this in node 2:

1
mysql> START GROUP_REPLICATION;

4. Check and Test Group Replication

To check group replication node is working, you just do this in one of node. Check MEMBER_STATE column if it is ONLINE or OFFLINE.

1
2
3
4
5
6
7
8
mysql> SELECT * FROM performance_schema.replication_group_members;
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+----------------+-------------+--------------+
| group_replication_applier | 0dcbd0f2-8e47-4f04-9ae1-2650f5087d7b | 172.16.0.1     |        3306 | ONLINE       |
+---------------------------+--------------------------------------+----------------+-------------+--------------+
| group_replication_applier | 0dcbd0f2-8e47-4f04-9ae1-2650f5087d7b | 172.16.0.2     |        3306 | ONLINE       |
+---------------------------+--------------------------------------+----------------+-------------+--------------+
1 row in set (0.00 sec)

You can test to create a database, insert the data inside the table in one of the node. If it is working, the database will be replicated in another node. We configure the group replication as MASTER and MASTER so both of nodes can WRITE and READ.
Do this in node 1.

1
2
3
4
5
6
7
8
9
10
mysql> CREATE DATABASE playground;
mysql> CREATE TABLE playground.equipment ( id INT NOT NULL AUTO_INCREMENT, type VARCHAR(50), quant INT, color VARCHAR(25), PRIMARY KEY(id));
mysql> INSERT INTO playground.equipment (type, quant, color) VALUES ("slide", 2, "blue");
mysql> SELECT * FROM playground.equipment;
+----+-------+-------+-------+
| id | type  | quant | color |
+----+-------+-------+-------+
|  1 | slide |     2 | blue  |
+----+-------+-------+-------+
1 row in set (0.00 sec)

Check the data also in node 2.

1
2
3
4
5
6
7
8
mysql> SELECT * FROM playground.equipment;
+----+-------+-------+-------+
| id | type  | quant | color |
+----+-------+-------+-------+
|  1 | slide |     2 | blue  |
+----+-------+-------+-------+
1 row in set (0.00 sec)
mysql> INSERT INTO playground.equipment (type, quant, color) VALUES ("swing", 10, "yellow");

You can check the data inside playground.equipment in node 1 and node 2. If it works fine, i will show like this bellow

1
2
3
4
5
6
7
8
mysql> SELECT * FROM playground.equipment;
+----+-------+-------+--------+
| id | type  | quant | color  |
+----+-------+-------+--------+
|  1 | slide |     2 | blue   |
|  2 | swing |    10 | yellow |
+----+-------+-------+--------+
2 row in set (0.00 sec)

Reference:

https://www.digitalocean.com/community/tutorials/how-to-configure-mysql-group-replication-on-ubuntu-16-04

Posted on 
© 2017 - Muh Rizal
Powered by Hexo