Follow us on Social Media:

MySQL Database Administrator

Boost Your Quick Knowledge in MySQL DBA Work, Concepts, Tips and Trick

Friday, 30 March 2018

MySQL Group Replication

In this article I will explain you just basic introduction of Group Replication. Why you use it and how to configure Group replication on your environment.

To achieve and providing highly available replica sets , MySQL introduce Group Replication Beta release0.8 for MySQL 5.7.14 available for download on labs.mysql.com, Group Replication will be supported on all MySQL 5.7 supported platforms.

Group Replication is a plugin for the standard MySQL 5.7 Server. This plugin provides virtually synchronous replication, with built-in conflict detection/handling and consistency guarantees, all of which supports multi-master write anywhere usage. It allows you to move from a stand-alone instance of MySQL, which is a single point of failure, to a natively distributed highly available MySQL service (the Group Replication set) that's made up of N MySQL instances (the group members). Then individual machines and/or MySQL instances can fail or be taken offline for maintenance while the distributed MySQL service continues to operate and handle application traffic.

Why you use Group Replication>>

If you need to avoid MySQL downtime for a serious impact of your business or its operations, then you should use Group Replication.
This means that most user-facing production systems should leverage Group Replication as it provides a native means for making a MySQL database highly available.

How to configure Group Replication >>

In this article , Considering 3 Server to setup Group Replication. We have tested Group replication within 3 Machine with single instance. You can configure and test in different-2 scenario as per you convenience.

First of all you need to install MySQL 5.7 packages including the Group Replication plugin (the group_replication.so file located in the @@global.plugin_dir directory) on the machines that you want to participate in Group Replication.

Below is showing our configuration command and testing result:
Group Replication with 3 Server 
 
 Note in my Testing :
* 1: Group replication supported mysql server 5.7.__ and later     
* 2: When member is online in group then Query browser for that memebr not functioning. 
* 3: loose_group_replication_allow_local_disjoint_gtids_join=True (in my.ini )
***************************************************************************************
 
Group replication settings for server1 with 3 server in group
In my.ini
[mysqld] port=3306 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 # *******Group replication section******** 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= "192.168.200.178:3306" loose-group_replication_group_seeds= "192.168.200.178:3306,192.168.200.72:3306,192.168.200.198:3306" loose-group_replication_bootstrap_group= off **************************************************************************************** Command line mysql> SET SQL_LOG_BIN=0; Query OK, 0 rows affected (0,00 sec) mysql> CREATE USER replalok@'%'; Query OK, 0 rows affected (0,00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO replalok@'%' IDENTIFIED BY 'replalok'; Query OK, 0 rows affected, 1 warning (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> SET sql_log_bin=0; # disable GTID generation and binary logging for our session mysql> CHANGE MASTER TO MASTER_USER='replalok', MASTER_PASSWORD='replalok' FOR CHANNEL 'group_replication_recovery'; mysql> SET sql_log_bin=1; # re-enable GTID generation and binary logging for our session cmd>> INSTALL PLUGIN group_replication SONAME 'group_replication.dll'; mysql> SHOW PLUGINS; +----------------------------+----------+--------------------+----------------------+-------------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+----------------------+-------------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY | (...) | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | PROPRIETARY | +----------------------------+----------+--------------------+----------------------+-------------+ mysql> SET GLOBAL group_replication_bootstrap_group=ON; mysql> START GROUP_REPLICATION; mysql> SET GLOBAL group_replication_bootstrap_group=OFF; mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+---------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+---------------+ | group_replication_applier | ce9be252-2b71-11e6-b8f4-00212844f856 | myhost | 24801 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+---------------+ 1 row in set (0,00 sec)
Group replication settings for Server2 with 3 server in group 
In my.ini
[mysqld] port=3306 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 # *******Group replication section******** 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= "192.168.200.72:3306" loose-group_replication_group_seeds= "192.168.200.178:3306,192.168.200.72:3306,192.168.200.198:3306" loose-group_replication_bootstrap_group= off ************************************************************************************************** Command line mysql> SET SQL_LOG_BIN=0; Query OK, 0 rows affected (0,00 sec) mysql> CREATE USER replalok@'%'; Query OK, 0 rows affected (0,00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO replalok@'%' IDENTIFIED BY 'replalok'; Query OK, 0 rows affected, 1 warning (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> SET sql_log_bin=0; # disable GTID generation and binary logging for our session mysql> CHANGE MASTER TO MASTER_USER='replalok', MASTER_PASSWORD='replalok' FOR CHANNEL 'group_replication_recovery'; mysql> SET sql_log_bin=1; # re-enable GTID generation and binary logging for our session cmd>> INSTALL PLUGIN group_replication SONAME 'group_replication.dll'; mysql> SHOW PLUGINS; +----------------------------+----------+--------------------+----------------------+-------------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+----------------------+-------------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY | (...) | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | PROPRIETARY | +----------------------------+----------+--------------------+----------------------+-------------+ mysql> ##Here no need to issue this command because group is already bootstraped on member1 ( SET GLOBAL group_replication_bootstrap_group=ON;) mysql> START GROUP_REPLICATION; mysql> SET GLOBAL group_replication_bootstrap_group=OFF; mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-----------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-----------------+-------------+--------------+ | group_replication_applier | da66ff48-03d1-11e7-a2c9-0a0027000002 | DESKTOP-EI5QAB5 | 3306 | ONLINE | | group_replication_applier | f3bc827f-f8cf-11e6-b744-b870f47692c7 | DESKTOP-M6CGSJ4 | 3306 | ONLINE | +---------------------------+--------------------------------------+-----------------+-------------+--------------+ 2 rows in set (0.00 sec)
Group replication settings for Server3 with 3 server in group
In my.ini
[mysqld] port=3306 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 # *******Group replication section******** 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= "192.168.200.198:3306" loose-group_replication_group_seeds= "192.168.200.178:3306,192.168.200.72:3306,192.168.200.198:3306" loose-group_replication_bootstrap_group= off **************************************************************************************************** Command line mysql> SET SQL_LOG_BIN=0; Query OK, 0 rows affected (0,00 sec) mysql> CREATE USER replalok@'%'; Query OK, 0 rows affected (0,00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO replalok@'%' IDENTIFIED BY 'replalok'; Query OK, 0 rows affected, 1 warning (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> SET sql_log_bin=0; # disable GTID generation and binary logging for our session mysql> CHANGE MASTER TO MASTER_USER='replalok', MASTER_PASSWORD='replalok' FOR CHANNEL 'group_replication_recovery'; mysql> SET sql_log_bin=1; # re-enable GTID generation and binary logging for our session cmd>> INSTALL PLUGIN group_replication SONAME 'group_replication.dll'; mysql> SHOW PLUGINS; +----------------------------+----------+--------------------+----------------------+-------------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+----------------------+-------------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY | (...) | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | PROPRIETARY | +----------------------------+----------+--------------------+----------------------+-------------+ mysql> ##Here no need to issue this command because group is already bootstraped on member1 ( SET GLOBAL group_replication_bootstrap_group=ON;) mysql> START GROUP_REPLICATION; mysql> SET GLOBAL group_replication_bootstrap_group=OFF; mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-----------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-----------------+-------------+--------------+ | group_replication_applier | da66ff48-03d1-11e7-a2c9-0a0027000002 | DESKTOP-EI5QAB5 | 3306 | ONLINE | | group_replication_applier | f3bc827f-f8cf-11e6-b744-b870f47692c7 | DESKTOP-M6CGSJ4 | 3306 | ONLINE | | group_replication_applier | c9ae915l-l9dg-03h8-s469-x637h18420m1 | DESKTOP-A69GSJ4 | 3306 | ONLINE | +---------------------------+--------------------------------------+-----------------+-------------+--------------+ 2 rows in set (0.00 sec)
Now You can see all 3 Servers are Online.These servers are now added in group replication.You can perform transactions on any servers as Master and same will be propogated to other one.

For any further query Click Here!

Your Feedback is highly appreciated.

No comments:

Please Like and Subscribe us ....

Please visit the website on daily basis , Your New articles will publish at any moment. Please Subscribe us for getting Latest Updates...