MySQL Group Replication multi-primary – add new member

MySQLmysql-5.7replication

I am configuring MySQL Group Replication in multi-primary mode to replicate all databases between two members and allowing writes on either.

I have my two members IP addresses configured in the whitelist and seed list:

loose-group_replication_ip_whitelist = "x.x.x.x,y.y.y.y."
loose-group_replication_group_seeds = "x.x.x.x:33061,y.y.y.y:33061"

My understanding is that if in a month or two I want add a third member, I will need to update both member's my.cnf files to add the third member to the whitelist and seed list.
I will then need to restart the mysql services in order for the change to take effect.
What is the best practice here? I see two options:

Option 1: restart the members one at a time such that the group never becomes empty.

  • Restart mysql on member1
  • Re-join the group with START GROUP_REPLICATION;
  • Restart mysql on member2
  • Re-join the group with START GROUP_REPLICATION;
  • Start mysql on member3 with START GROUP_REPLICATION;

Option2: stop all members and rebootstrap

  • Stop mysql on both existing members
  • Bootstrap the group on member1
  • Join the group on member2 and member3

Option 1 should result in no downtime whereas Option 2 would have downtime. Are there any other options I'm missing or is Option 1 best practice?

Best Answer

If you want to add a new member to current online Group Replication, You don't need to stop or restart any of nodes.

For doing this just config new member(s3) like current members(s1,s2) but add new IP at the end of these two variables

loose-group_replication_ip_whitelist = "x.x.x.x,y.y.y.y,z.z.z.z"
loose-group_replication_group_seeds = "x.x.x.x:33061,y.y.y.y:33061,z.z.z.z:33061"
loose-group_replication_bootstrap_group=off
loose-group_replication_start_on_boot=off

After that Start new member and execute these

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';

Then Install group_replication and start group_replication

After the new member joined the group open my.cnf of s1,s2 server and update loose-group_replication_ip_whitelist and loose-group_replication_group_seeds to this:

loose-group_replication_ip_whitelist = "x.x.x.x,y.y.y.y,z.z.z.z"
loose-group_replication_group_seeds = "x.x.x.x:33061,y.y.y.y:33061,z.z.z.z:33061"

But you Don't need to restart these nodes(s1,2) just update my.cnf

Related Question