Introduction
MySQL Group Replication is a high-availability solution that enables multiple MySQL server instances (a group) to synchronize data automatically, forming a fault-tolerant, distributed database system. It supports both single-primary and multi-primary modes.
Key Features
- Synchronous replication: Transactions are replicated to all members before commit (using a group communication system).
- Automatic failover: In single-primary mode, if the current primary fails, a new one is elected.
- Conflict detection/resolution: In multi-primary mode, it handles conflicts if two nodes try to update the same data.
Use Cases
- High availability and disaster recovery.
- Multi-datacenter deployments (with care).
- Applications needing consistency and auto failover without external tools.
Architecture
Each MySQL server (node) in the group:
- Maintains its own copy of the data.
- Communicates over the network with other members via group communication.
- Uses write sets and certification to ensure consistency.
Modes
- Single-primary mode (default)
- One node handles writes.
- Others are read-only unless the primary fails.
- Multi-primary mode
- All nodes can handle writes.
- Conflict detection is necessary.
How to Configure
Steps for Configure Group Replication with Multi Master Nodes
- Install Operating System on All Nodes
- Define IP Address for Each Node
- Define Host Name of Each Nodes
- Enter Host Name of All Node in each Node etc/hosts
- Download MYSQL RPM
- Install MYSQL RPM
- Define Table Case sensitive in mysql Configure File
- Start MYSQL services
- Secure MYSQL Database
- Create Users Required for Replication
- Define Configure Parameter on Each Node
- Stop Firewall
- Disable SELINUX
- Restart MYSQL Services
- Define Replication Source
- Start Group Replication on Node 1 with group_replication_bootstrap_group=ON;
- Start Group Replication on Node 2 and Node 3
- Enable Read Only Node on node 2 and Node 3
- Monitor Group Replication
On Each Database Nodes
vi /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
192.168.179.128 mysql1
192.168.179.129 mysql2
192.168.179.130 mysql3
On Node1 Database
vi /etc/hostname
mysql1
On Node2 Database
vi /etc/hostname
mysql2
On Node3 Database
vi /etc/hostname
mysql3
On Each Database Nodes
mkdir -p /software
cd /software
wget https://repo.mysql.com/mysql80-community-release-el8-3.noarch.rpm
rpm -ivh mysql80-community-release-el8-3.noarch.rpm
yum install mysql-server –y
Define Table Case sensitive in mysql Configure File on Each Database Nodes
vi /etc/my.cnf
[mysqld]
lower_case_table_names = 1
systemctl start mysqld
systemctl enable mysqld
Secure MYSQL Database
mysql_secure_installation
Create Users Required for Replication on Each Node
mysql -uroot -pOracle123
CREATE USER 'root'@'%' IDENTIFIED BY 'Oracle123';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
CREATE USER 'rpl_user'@'%' IDENTIFIED WITH mysql_native_password BY 'Oracle123';
GRANT REPLICATION SLAVE ON *.* TO 'rpl_user'@'%';
GRANT CONNECTION_ADMIN ON *.* TO 'rpl_user'@'%';
GRANT BACKUP_ADMIN ON *.* TO 'rpl_user'@'%';
GRANT GROUP_REPLICATION_STREAM ON *.* TO 'rpl_user'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'rpl_user'@'%';
GRANT REPLICATION SLAVE, BACKUP_ADMIN, CONNECTION_ADMIN ON *.* TO 'rpl_user'@'%';
FLUSH PRIVILEGES;
Define Configure Parameter on Each Node
vi /etc/my.cnf
[mysqld]
lower_case_table_names = 1
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=1 -– Change on Each Node
gtid_mode=ON
enforce_gtid_consistency=ON
plugin_load_add = "group_replication.so"
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=OFF
group_replication_local_address= "192.168.179.128:33061" -– Change on Each Node
group_replication_group_seeds= "192.168.179.128:33061,192.168.179.129:33061,192.168.179.130:33061"
group_replication_bootstrap_group=OFF
group_replication_ip_allowlist= "192.168.179.128,192.168.179.129,192.168.179.130"
group_replication_single_primary_mode = OFF -- For Multi Master Nodes
lower_case_table_names = 1
Stop Firewall and Disable SELINUX
systemctl stop firewalld
chkconfig firewalld off
setenforce 0
vi /etc/sysconfig/selinux
ELINUX=disabled
systemctl restart mysqld
Define Replication Source
mysql -uroot -pOracle123
CHANGE REPLICATION SOURCE TO SOURCE_USER='rpl_user', SOURCE_PASSWORD='Oracle123' FOR CHANNEL 'group_replication_recovery';
Start Group Replication on Node 1
mysql -uroot -pOracle123
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
Start Group Replication on Node 2 and Node 3
mysql -uroot -pOracle123
START GROUP_REPLICATION;
Enable Read Only on Node 2 and Node 3
SET GLOBAL super_read_only = ON;
Monitor on Group Replication
SELECT * FROM performance_schema.replication_group_members;