Group multi master replication is a master-master replication setup in MySQL.
Follow the below steps to configure group multi replication:
1.Considering 3 servers in the group multi master replication, install mysql-community-server on all the 3 servers.
2.Consider one of the server to be the bootstrap server .
3.Run uuidgen command on the bootstrap server to generate a hexadecimal unique key:
uuidgen
5c200097-3e7e-4616-a045-25e871df7cb6
4.Add the below entry in /etc/my.cnf file of all the 3 servers to be configured:
# 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 = OFF
loose-group_replication_ssl_mode = REQUIRED
loose-group_replication_recovery_use_ssl = 1
# 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
5. Add below entry in /etc/my.cnf file with replication_group_name as the uuid generated in
step 3.Also add ip addresses of all the three servers involved ip ip_whitelist and group_seeds entry
# Shared replication group configuration
loose-group_replication_group_name = "5c200097-3e7e-4616-a045-25e871df7cb6"
loose-group_replication_ip_whitelist = "192.168.197.131,192.168.197.130,192.168.197.129"
loose-group_replication_group_seeds = "192.168.197.131:33061,192.168.197.130:33061,192.168.197.129:33061"
6.Add ip address of each server specifically for the below entries and provide a seperate server_id for each
# Host specific replication configuration
server_id=1
bind-address = "192.168.197.131"
report_host = "192.168.197.131"
loose-group_replication_local_address = "192.168.197.131:33061"
7. After adding all the entries, do a restart of mysqld
systemctl restart mysqld
8.On the all three nodes run the below queries on MySQL prompt:
SET SQL_LOG_BIN=0;
CREATE USER 'repl'@'%' IDENTIFIED BY '!@#Ilg007' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
9.Also, run the below statement on all three nodes to enable the created user for replication for channel group replication recovery
change master to master_user='repl',master_password='!@#Ilg007' for channel 'group_replication_recovery';
10.Install group_replication plugin on all three nodes
Install plugin group_replication soname 'group_replication.so';
11.Check if the plugin is active
show plugins
12.We would require SSL support for group replication, so first check on the bootstrap node if ssl is available
show variables like "%ssl%"; or \s
13.If ssl is disabled , we can install the ssl using mysql_ssl_rsa_setup utility on the bootstrap node
mysql_ssl_rsa_setup --uid=mysql
14.Confirm if ssl has been installed correctly by checking for .pem extention files in /var/lib/mysql and restart mysqld to make the ssl change in effect.
15.Now that the configuration setup is completed for all the nodes, start the group replication on the bootstrap node first as below
set global group_replication_bootstrap_group=ON;
start group_replication;
set global group_replication_bootstrap_group=OFF;
16. Now if we check the below query, we would be able to see our bootstrap node as online in the query result
select * from performance_schema.replication_group_members;
17.In the other two nodes, simply start the group replication
start group_replication;
18. Now if we check the query on step 16 on any of the nodes, we would be able to see all the three nodes as online in the query result.
select * from performance_schema.replication_group_members;
19.Now the multi replication setup is completed, we can try adding data in bootstrap node and check if it is getting replicated in other two nodes.
20.By any chance , if any of the nodes is not listed in the step 18 query or is in recovery mode, check logs /var/log/mysqld.log for any possible errors.
21.One of the common error is of data in any of the nodes being greater than that of bootstrap node, follow the below steps to rectify such errors:
a. On all three nodes run the below command
SELECT @@global.gtid_executed;
b.Check for any missing GTID in bootstrap which are present in other two nodes
c.load those GTIDs in bootstrap node on by one
SET GTID_NEXT='a1c5e25e-2715-11e7-bbe4-0800273fb9a2:1';
begin;
commit;
SET GTID_NEXT='a1c5e25e-2715-11e7-bbe4-0800273fb9a2:2';
begin;
commit;
SET GTID_NEXT='AUTOMATIC';
d. Now run start group replication on the failed nodes
start group_replication;
e.Check again if all nodes are online
select * from performance_schema.replication_group_members;
22. For enabling multi write in the multi group replication, uncomment the following lines in /etc/my.cnf file and restart the group replication
loose-group_replication_single_primary_mode = OFF
loose-group_replication_enforce_update_everywhere_checks = ON
Follow the below steps to configure group multi replication:
1.Considering 3 servers in the group multi master replication, install mysql-community-server on all the 3 servers.
2.Consider one of the server to be the bootstrap server .
3.Run uuidgen command on the bootstrap server to generate a hexadecimal unique key:
uuidgen
5c200097-3e7e-4616-a045-25e871df7cb6
4.Add the below entry in /etc/my.cnf file of all the 3 servers to be configured:
# 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 = OFF
loose-group_replication_ssl_mode = REQUIRED
loose-group_replication_recovery_use_ssl = 1
# 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
5. Add below entry in /etc/my.cnf file with replication_group_name as the uuid generated in
step 3.Also add ip addresses of all the three servers involved ip ip_whitelist and group_seeds entry
# Shared replication group configuration
loose-group_replication_group_name = "5c200097-3e7e-4616-a045-25e871df7cb6"
loose-group_replication_ip_whitelist = "192.168.197.131,192.168.197.130,192.168.197.129"
loose-group_replication_group_seeds = "192.168.197.131:33061,192.168.197.130:33061,192.168.197.129:33061"
6.Add ip address of each server specifically for the below entries and provide a seperate server_id for each
# Host specific replication configuration
server_id=1
bind-address = "192.168.197.131"
report_host = "192.168.197.131"
loose-group_replication_local_address = "192.168.197.131:33061"
7. After adding all the entries, do a restart of mysqld
systemctl restart mysqld
8.On the all three nodes run the below queries on MySQL prompt:
SET SQL_LOG_BIN=0;
CREATE USER 'repl'@'%' IDENTIFIED BY '!@#Ilg007' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
9.Also, run the below statement on all three nodes to enable the created user for replication for channel group replication recovery
change master to master_user='repl',master_password='!@#Ilg007' for channel 'group_replication_recovery';
10.Install group_replication plugin on all three nodes
Install plugin group_replication soname 'group_replication.so';
11.Check if the plugin is active
show plugins
12.We would require SSL support for group replication, so first check on the bootstrap node if ssl is available
show variables like "%ssl%"; or \s
13.If ssl is disabled , we can install the ssl using mysql_ssl_rsa_setup utility on the bootstrap node
mysql_ssl_rsa_setup --uid=mysql
14.Confirm if ssl has been installed correctly by checking for .pem extention files in /var/lib/mysql and restart mysqld to make the ssl change in effect.
15.Now that the configuration setup is completed for all the nodes, start the group replication on the bootstrap node first as below
set global group_replication_bootstrap_group=ON;
start group_replication;
set global group_replication_bootstrap_group=OFF;
16. Now if we check the below query, we would be able to see our bootstrap node as online in the query result
select * from performance_schema.replication_group_members;
17.In the other two nodes, simply start the group replication
start group_replication;
18. Now if we check the query on step 16 on any of the nodes, we would be able to see all the three nodes as online in the query result.
select * from performance_schema.replication_group_members;
19.Now the multi replication setup is completed, we can try adding data in bootstrap node and check if it is getting replicated in other two nodes.
20.By any chance , if any of the nodes is not listed in the step 18 query or is in recovery mode, check logs /var/log/mysqld.log for any possible errors.
21.One of the common error is of data in any of the nodes being greater than that of bootstrap node, follow the below steps to rectify such errors:
a. On all three nodes run the below command
SELECT @@global.gtid_executed;
b.Check for any missing GTID in bootstrap which are present in other two nodes
c.load those GTIDs in bootstrap node on by one
SET GTID_NEXT='a1c5e25e-2715-11e7-bbe4-0800273fb9a2:1';
begin;
commit;
SET GTID_NEXT='a1c5e25e-2715-11e7-bbe4-0800273fb9a2:2';
begin;
commit;
SET GTID_NEXT='AUTOMATIC';
d. Now run start group replication on the failed nodes
start group_replication;
e.Check again if all nodes are online
select * from performance_schema.replication_group_members;
22. For enabling multi write in the multi group replication, uncomment the following lines in /etc/my.cnf file and restart the group replication
loose-group_replication_single_primary_mode = OFF
loose-group_replication_enforce_update_everywhere_checks = ON
Comments
Post a Comment