Group Replication Setup in MySQL

Share via:

Post Views: 0

Group Replication in MySQL:

MySQL Group Replication and how to install, configure and monitor groups.
MySQL Group Replication enables you to create elastic, highly-available, fault-tolerant replication topologies.

MySQL Group Replication is a MySQL Server plugin that provides distributed state machine replication with strong coordination between servers.
Servers coordinate themselves automatically, when they are part of the same replication group.

Any server in the group can process updates. Conflicts are detected and handled automatically.
There is a built-in membership service that keeps the view of the group consistent and available for all servers at any point in time. Servers can leave and join the group and the view will be updated accordingly.

Group replication has the below abilities:
📍Split Brain Prevention
📍Data Consistency
📍Usability Stability
📍Performance

Step by step configuration method for Group Replication:
We are configuring Group Replication for 3 nodes in below steps

Servers :
📍Server 1
📍Server 2
📍Server 3

My.cnf (add the group replication related parameters to my.cnf)
# group replication pre-requisites & recommendations
log-bin
binlog-format=ROW
gtid-mode=ON
enforce-gtid-consistency=ON
log-slave-updates=ON
master-info-repository=TABLE
relay-log-info-repository=TABLE
binlog-checksum=NONE
slave-parallel-workers=0
# prevent use of non-transactional storage engines
disabled_storage_engines=”MyISAM,BLACKHOLE,FEDERATED,ARCHIVE”
transaction-isolation=”READ-COMMITTED”

#group replication specific options
plugin-load=group_replication.so
group_replication=FORCE_PLUS_PERMANENT
transaction-write-set-extraction=XXHASH64
group_replication_start_on_boot=ON
#For first group member, set group_replication_bootstrap_group=ON
group_replication_bootstrap_group=OFF
group_replication_group_name=70cc79b2-c8d4-4b02-9eb6-e64e1a28850a
group_replication_local_address=’cnocdb-wc-a1q.xxxxxx.com:6606′
group_replication_group_seeds=’host1:port,host2:port,host3:port’
group_replication_ip_whitelist=’all three node ip addresses’
group_replication_single_primary_mode=FALSE
group_replication_enforce_update_everywhere_checks=FALSE

Start the server after mysql installation on server 1:

service mysqld start
Redirecting to /bin/systemctl start mysqld.service

1 2	service mysqld start Redirecting to /bin/systemctl start mysqld.service

Next step alert root user password.
Enable login path for so next time you no need to login with user name and password

[root@cnocdb-wc-a1q ~]# mysql_config_editor set --login-path=mysqlroot --host=localhost --user=root --port=3306 –password

1	[root@cnocdb-wc-a1q ~]# mysql_config_editor set --login-path=mysqlroot --host=localhost --user=root --port=3306 –password

Now you can login with $mysql –login-path=mysqlroot

server 1~]# mysql --login-path=mysqlroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 33
mysql>

server 1~]# mysql --login-path=mysqlroot

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 33

mysql>

Create the Replication_user

mysql> SHOW VARIABLES LIKE '%SQL_LOG%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | ON |
| sql_log_off | OFF |
+---------------+-------+
2 rows in set (0.21 sec)
mysql> SET SQL_LOGIN_BIN=0;
mysql> CREATE USER replication_user@'%' IDENTIFIED BY 'Password!';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication_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)

mysql> SHOW VARIABLES LIKE '%SQL_LOG%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| sql_log_bin | ON |

| sql_log_off | OFF |

+---------------+-------+

2 rows in set (0.21 sec)

mysql> SET SQL_LOGIN_BIN=0;

mysql> CREATE USER replication_user@'%' IDENTIFIED BY 'Password!';

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication_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)

Execute the change master to enable the server for group replication recovery

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

1 2	mysql> CHANGE MASTER TO MASTER_USER='replication_user', MASTER_PASSWORD='Password!' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.02 sec)

We have to boot strap on first node only. If this parameter exists in my.cnf comment in all the nodes
Install the group_replication plugin (In below case I have installed before,so that shows as already exists)

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.01 sec)
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 (2.03 sec)

Check the status from the below query:
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------------------------+-------------+--------------+
| group_replication_applier | dfbd0fa2-51cd-11ea-a757-005056afb141 | server1 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------------------------+-------------+--------------+
1 row in set (0.00 sec)

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

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';

Query OK, 0 rows affected (0.01 sec)

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 (2.03 sec)

Check the status from the below query:

mysql> SELECT * FROM performance_schema.replication_group_members;

+---------------------------+--------------------------------------+-------------------------------+-------------+--------------+

+---------------------------+--------------------------------------+-------------------------------+-------------+--------------+

+---------------------------+--------------------------------------+-------------------------------+-------------+--------------+

1 row in set (0.00 sec)

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

Query OK, 0 rows affected (0.01 sec)

Adding Node 2 to the group:
Configure the my.cnf with group replication parameters
Start the mysql

Add the replication user:
Here you can expect the error as below
ERROR 1290 (HY000): The MySQL server is running with the –super-read-only option so it cannot execute this statement
Follow the below steps to create the user on second node that is server2

mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
SET @@global.super_read_only = 0; #############################
mysql> CREATE USER replication_user@'%' IDENTIFIED BY 'Password!';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication_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)
mysql> CHANGE MASTER TO MASTER_USER='replication_user', MASTER_PASSWORD='Password!' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> SET SQL_LOG_BIN=0;

Query OK, 0 rows affected (0.00 sec)

SET @@global.super_read_only = 0; #############################

mysql> CREATE USER replication_user@'%' IDENTIFIED BY 'Password!';

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication_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)

mysql> CHANGE MASTER TO MASTER_USER='replication_user', MASTER_PASSWORD='Password!' FOR CHANNEL 'group_replication_recovery';

Query OK, 0 rows affected, 2 warnings (0.02 sec)

Install the MySQL group replication plugin on server 2:

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';

1	mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';

Next, add server 2 to the group that we bootstrapped earlier:

Start group_replication

mysql> START GROUP_REPLICATION;

Validate the server 2 added to the group:
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------------------------+-------------+--------------+
| group_replication_applier | 23815d41-9616-11ea-92ed-005056afd73c |server 2 | 3306 | ONLINE |
| group_replication_applier | dfbd0fa2-51cd-11ea-a757-005056afb141 | server 1 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------------------------+-------------+--------------+
2 rows in set (0.00 sec)

mysql> START GROUP_REPLICATION;

Validate the server 2 added to the group:

mysql> SELECT * FROM performance_schema.replication_group_members;

+---------------------------+--------------------------------------+-------------------------------+-------------+--------------+

+---------------------------+--------------------------------------+-------------------------------+-------------+--------------+

+---------------------------+--------------------------------------+-------------------------------+-------------+--------------+

2 rows in set (0.00 sec)

Now proceed to add the node 3 to the group:

mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
******************** SET @@global.super_read_only = 0;
mysql> CREATE USER replication_user@'%' IDENTIFIED BY 'Password!';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication_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)
mysql> CHANGE MASTER TO MASTER_USER='replication_user', MASTER_PASSWORD='Password!' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Next, add server 3 to the group that we bootstrapped earlier:
mysql> start group_replication;
Query OK, 0 rows affected (2.87 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------------------------+-------------+--------------+
| group_replication_applier | 23815d41-9616-11ea-92ed-005056afd73c | server 2 | 3306 | ONLINE |
| group_replication_applier | b847ce45-9622-11ea-8ff9-005056afc359 | server 3 | 3306 | ONLINE |
| group_replication_applier | dfbd0fa2-51cd-11ea-a757-005056afb141 | server 1 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------------------------+-------------+--------------+
3 rows in set (0.00 sec)

mysql> exit

mysql> SET SQL_LOG_BIN=0;

Query OK, 0 rows affected (0.00 sec)

******************** SET @@global.super_read_only = 0;

mysql> CREATE USER replication_user@'%' IDENTIFIED BY 'Password!';

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication_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)

mysql> CHANGE MASTER TO MASTER_USER='replication_user', MASTER_PASSWORD='Password!' FOR CHANNEL 'group_replication_recovery';

Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';

Next, add server 3 to the group that we bootstrapped earlier:

mysql> start group_replication;

Query OK, 0 rows affected (2.87 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;

+---------------------------+--------------------------------------+-------------------------------+-------------+--------------+

+---------------------------+--------------------------------------+-------------------------------+-------------+--------------+

+---------------------------+--------------------------------------+-------------------------------+-------------+--------------+

3 rows in set (0.00 sec)

mysql> exit

Now the three nodes on online with group replication setup.
Validate the data by create database and tables on any node the will be propagated to the other nodes and vice versa.

Thank you for giving your valuable time to read the above information.
Follow us on
Website : www.ktexperts.com
Facebook Page : KTexperts
Linkedin Page : KT EXPERTS