MySQL 5.7 Group Replication

Install MySQL 5.7 Server on CentOS 7
rpm -ivh https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
yum install -y mysql-community-client mysql-community-server

# start
systemctl start mysqld

# get temporary root password
grep -i temporary /var/log/mysqld.log

# login
mysql -uroot -p

# change default password
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('[email protected]');

# save you creds to .my.cnf
cat << EOF >> ~/.my.cnf
[client]
user=root
[email protected]
EOF

Configure replication
And then create a user and save the credentials for the recovery channel.

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


mysql> GRANT REPLICATION SLAVE ON *.* TO [email protected]'%' IDENTIFIED BY 'rep';
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> CHANGE MASTER TO MASTER_USER='rep', MASTER_PASSWORD='rep' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.01 sec)

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

 

To start the group, the only thing left to do is to instruct server s1 to bootstrap the group and then start group replication.

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

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

mysql> SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: a85673dc-c5c1-11e6-9455-005056972476
 MEMBER_HOST: db01
 MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
1 row in set (0.00 sec)

Join servers: db02, db03
Basic operation is the same as the db01

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

mysql> SELECT * FROM performance_schema.replication_group_members;
+—————————+————————————–+————-+————-+————–+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+—————————+————————————–+————-+————-+————–+
| group_replication_applier | a85673dc-c5c1-11e6-9455-005056972476 | db01        |        3306 | ONLINE       |
| group_replication_applier | b767c453-c5c1-11e6-9a47-005056972476 | db02        |        3306 | ONLINE       |
| group_replication_applier | c1c473db-c5c1-11e6-9d3a-005056972476 | db03        |        3306 | ONLINE       |
+—————————+————————————–+————-+————-+————–+
3 rows in set (0.00 sec)
Configs
##################### db01 cnf #########################################################

#REP
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
binlog_format=ROW
log-bin=mysql-bin.log

#MGR
plugin-load = group_replication.so
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="550fa9ee-a1f8-4b6d-9bfe-c03c12cd1c72"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "10.0.0.11:24901"
loose-group_replication_group_seeds= "10.0.0.11:24901,10.0.0.12:24901,10.0.0.13:24901"
loose-group_replication_bootstrap_group= off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks= TRUE
loose-group_replication_recovery_user=rep
loose-group_replication_recovery_password=rep
loose-group_replication_recovery_retry_count= 3
loose-group_replication_recovery_reconnect_interval= 120

 
##################### db02 cnf #########################################################

#REP
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
binlog_format=ROW
log-bin=mysql-bin.log

#MGR
plugin-load = group_replication.so
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="550fa9ee-a1f8-4b6d-9bfe-c03c12cd1c72"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "10.0.0.12:24901"
loose-group_replication_group_seeds= "10.0.0.11:24901,10.0.0.12:24901,10.0.0.13:24901"
loose-group_replication_bootstrap_group= off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks= TRUE
loose-group_replication_recovery_user=rep
loose-group_replication_recovery_password=rep
loose-group_replication_recovery_retry_count= 3
loose-group_replication_recovery_reconnect_interval= 120

 
##################### db03 cnf #########################################################

#REP
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
binlog_format=ROW
log-bin=mysql-bin.log

#MGR
plugin-load = group_replication.so
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="550fa9ee-a1f8-4b6d-9bfe-c03c12cd1c72"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "10.0.0.13:24903"
loose-group_replication_group_seeds= "10.0.0.11:24901,10.0.0.12:24901,10.0.0.13:24901"
loose-group_replication_bootstrap_group= off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks= TRUE
loose-group_replication_recovery_user=rep
loose-group_replication_recovery_password=rep
loose-group_replication_recovery_retry_count= 3
loose-group_replication_recovery_reconnect_interval= 120

Hints
# to support tables with multi-level foreign key dependencies
# on real multi-master (siml inserts into different nodes) use:

group_replication_enforce_update_everywhere_checks=ON