Single Server Group Replication Setup
- Initialize three mysql instances
- Generate UUID for group name
- Prepare the config file with group replication and regular replication parameters
- Start Mysql for all the three instances
- Primary Member ( port = 24801 )
- Replication user creation
- Assign the credentials to group_replication_recovery replication channel
- Install group Replication plugin
- Enable Group Replication plugin
- Bootstrap the primary node of the group.
- Secondary Members ( port = 24802 and port = 24803 )
- Replication user creation
- Assign the credentials to group_replication_recovery replication channel
- Install group Replication plugin
- Enable Group Replication plugin
Initialize three mysql instances
Start three instances using below command and this would create three instances. We should have three instances running at below ports mys1.cnf:port = 24801 mys2.cnf:port = 24802 mys3.cnf:port = 24803cat > setup_replication.sh mysqldemon=`which mysqld` mkdir -p /mysql/s1/data/ /mysql/s2/data/ /mysql/s3/data/ echo -e "------------------------ S1 -----------------------" $mysqldemon --initialize-insecure --basedir=/usr/sbin --datadir=/mysql/s1/data echo -e "------------------------ S2 -----------------------" $mysqldemon --initialize-insecure --basedir=/usr/sbin --datadir=/mysql/s2/data echo -e "------------------------ S3 -----------------------" $mysqldemon --initialize-insecure --basedir=/usr/sbin --datadir=/mysql/s3/data chown -R mysql. /mysql/s*
Prepare my.cnf parameters
The highlighted parameters are mandatorily needed alone with group replication parameters . As you see gtid_mode and enforce-gtid-consistency enables GTID, master_info_repository and relay_log_info_repository is to record slave logs master status and connection information in a table mysql.slave_master_info and mysql.slave_master_info instead of master.info or relay.info files. Rest of the other parameters are regular binlogs and relay logs as needed for traditional replication
Replication Parameters
################################################# ## Replication related settings ################################################# server_id = 1 ## Enable GTID Mode gtid_mode = ON enforce-gtid-consistency = ON ## Replication Repo tables master_info_repository = TABLE relay_log_info_repository = TABLE ## Binlog parameters log_slave_updates = ON binlog_format = ROW log-bin = /mysql/s1/mysql-bin log-bin-index = /mysql/s1/bin-log.index ## Relay log parameters relay-log = /mysql/s1/mysql-relay-bin relay-log-index = /mysql/s1/mysql-relay-bin relay_log_info_file = /mysql/s1/relay-log.info #################################################
Group Replication Parameters
We need to the prefix loose-
for the group_replication
variables listed above which would allow mysql service to continue to start even if the Group Replication plugin is not loaded.Before that we need to generate a uuid as below to give a name to group replication cluster
mysql> select uuid(); +--------------------------------------+ | uuid() | +--------------------------------------+ | 6801f2d2-7f38-11e8-88c8-00505681ffc4 | +--------------------------------------+ 1 row in set (0.00 sec)
Substitute the above UUID to loose-group_replication_group_name as below. you can generate your own UUID and have it replaced it here. loose-group_replication_local_address parameter should have different ports for each of the instace if you miss to change that you won’t be able to add the instance to the group.
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name=”6801f2d2-7f38-11e8-88c8-00505681ffc4″
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= “127.0.0.1:24901”
loose-group_replication_group_seeds= “127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903” loose-group_replication_bootstrap_group=off
Start Mysql Instances
[root@testdb01 mysql]# cd /mysql [root@testdb01 mysql]# mysqld --defaults-file=./mys1.cnf --user=mysql & [1] 5973 [root@testdb01 mysql]# mysqld --defaults-file=./mys2.cnf --user=mysql & [2] 6016 [root@testdb01 mysql]# mysqld --defaults-file=./mys3.cnf --user=mysql & [3] 6057
Primary Node Setup
- Replication User Creation steps
-
SET SQL_LOG_BIN=0;SET session SQL_LOG_BIN=0;
CREATE USER ‘replication_user’@’%’ IDENTIFIED by ‘secret_password’;
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘replication_user’@’%’;
flush privileges;
SET session SQL_LOG_BIN=1;
-
- Assign the credentials to group_replication_recovery replication channel
-
CHANGE MASTER TO MASTER_USER=’replication_user’, MASTER_PASSWORD=’secret_password’ FOR CHANNEL ‘group_replication_recovery’;
-
- Install group Replication plugin
-
INSTALL PLUGIN group_replication SONAME ‘group_replication.so’;
-
- Bootstrap the primary node of the group.
-
SET GLOBAL group_replication_bootstrap_group=ON;
-
- Enable Group Replication plugin
-
START GROUP_REPLICATION; SET GLOBAL group_replication_bootstrap_group=OFF;
-
- Verify through performance_schema.replication_group_members
NOTE : If the above steps went fine you would be able to see a member added to the group we initiated.
SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+--------------------------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+--------------------------------+-------------+--------------+ | group_replication_applier | 9426b7a0-7f59-11e8-8d83-00505681ffc4 | testdb01.genexdbs.com | 24801 | ONLINE | +---------------------------+--------------------------------------+--------------------------------+-------------+--------------+ 1 row in set (0.00 sec)
Secondary Nodes Setup
- Replication User Creation steps
-
SET SQL_LOG_BIN=0;SET session SQL_LOG_BIN=0;
CREATE USER ‘replication_user’@’%’ IDENTIFIED by ‘secret_password’;
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘replication_user’@’%’;
flush privileges;
SET session SQL_LOG_BIN=1;
-
- Assign the credentials to group_replication_recovery replication channel
-
CHANGE MASTER TO MASTER_USER=’replication_user’, MASTER_PASSWORD=’secret_password’ FOR CHANNEL ‘group_replication_recovery’;
-
- Install group Replication plugin
-
INSTALL PLUGIN group_replication SONAME ‘group_replication.so’;
-
- Enable Group Replication plugin
-
START GROUP_REPLICATION;
-
- Verify through performance_schema.replication_group_members
NOTE : If the above steps went fine you would be able to see a member added to the group we initiated.
SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+--------------------------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+--------------------------------+-------------+--------------+ | group_replication_applier | 9426b7a0-7f59-11e8-8d83-00505681ffc4 | testdb01.genexdbs.com | 24801 | ONLINE | | group_replication_applier | a00b9f15-7f59-11e8-90a1-00505681ffc4 | testdb01.genexdbs.com | 24802 | ONLINE | | group_replication_applier | aca37cde-7f59-11e8-9340-00505681ffc4 | testdb01.genexdbs.com | 24803 | ONLINE | +---------------------------+--------------------------------------+--------------------------------+-------------+--------------+