Group Replication Setup in MySQL

Share via:
Group Replication in MySQL:

MySQL Group Replication and how to install, configure and monitor groups.
MySQL Group Replication enables you to create elastic, highly-available, fault-tolerant replication topologies.

MySQL Group Replication is a MySQL Server plugin that provides distributed state machine replication with strong coordination between servers.
Servers coordinate themselves automatically, when they are part of the same replication group.

Any server in the group can process updates. Conflicts are detected and handled automatically.
There is a built-in membership service that keeps the view of the group consistent and available for all servers at any point in time. Servers can leave and join the group and the view will be updated accordingly.

Group replication has the below abilities:
📍Split Brain Prevention
📍Data Consistency
📍Usability Stability
📍Performance

Step by step configuration method for Group Replication:
We are configuring Group Replication for 3 nodes in below steps

Servers :
📍Server 1
📍Server 2
📍Server 3

My.cnf (add the group replication related parameters to my.cnf)
# group replication pre-requisites & recommendations
log-bin
binlog-format=ROW
gtid-mode=ON
enforce-gtid-consistency=ON
log-slave-updates=ON
master-info-repository=TABLE
relay-log-info-repository=TABLE
binlog-checksum=NONE
slave-parallel-workers=0
# prevent use of non-transactional storage engines
disabled_storage_engines=”MyISAM,BLACKHOLE,FEDERATED,ARCHIVE”
transaction-isolation=”READ-COMMITTED”

#group replication specific options
plugin-load=group_replication.so
group_replication=FORCE_PLUS_PERMANENT
transaction-write-set-extraction=XXHASH64
group_replication_start_on_boot=ON
#For first group member, set group_replication_bootstrap_group=ON
group_replication_bootstrap_group=OFF
group_replication_group_name=70cc79b2-c8d4-4b02-9eb6-e64e1a28850a
group_replication_local_address=’cnocdb-wc-a1q.xxxxxx.com:6606′
group_replication_group_seeds=’host1:port,host2:port,host3:port’
group_replication_ip_whitelist=’all three node ip addresses’
group_replication_single_primary_mode=FALSE
group_replication_enforce_update_everywhere_checks=FALSE

Start the server after mysql installation on server 1:

Next step alert root user password.
Enable login path for so next time you no need to login with user name and password

Now you can login with  $mysql –login-path=mysqlroot

Create the Replication_user

Execute the change master to enable the server for group replication recovery

We have to boot strap on first node only. If this parameter exists in my.cnf comment in all the nodes
Install the group_replication plugin (In below case I have installed before,so that shows as already exists)

Adding Node 2 to the group:
Configure the my.cnf with group replication parameters
Start the mysql

Add the replication user:
Here you can expect the error as below
ERROR 1290 (HY000): The MySQL server is running with the –super-read-only option so it cannot execute this statement
Follow the below steps to create the user on second node that is  server2

Install the MySQL group replication plugin on server 2:

Next, add server 2 to the group that we bootstrapped earlier:

Start group_replication

Now proceed to add the node 3 to the group:

Now the three nodes on online with group replication setup.
Validate the data by create database and tables on any node the will be propagated to the other nodes and vice versa.

 

 

Thank you for giving your valuable time to read the above information.
Follow us on 
Website  www.ktexperts.com
Facebook Page KTexperts
Linkedin Page : KT EXPERTS

Follow Me
Linkedin : SRINIVAS REDDY A

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Add Comment