MySQL-Cluster group replication

Follow this to setup a MySQL database cluster with group replication. Group replication is where the database across the cluster is constantly synced, so you can write or read from any database and it will be replicated across the other ones.

This increases the redundancy and read throughput.

Assumptions

Following are the assumptions that are made.

Hostname of service DB server 1: service-db1
Hostname of service DB server 2: service-db2
Hostname of service DB server 3: service-db3

IP of service DB server 1: 172.32.0.10
IP of service DB server 2: 172.32.0.20
IP of service DB server 3: 172.32.0.30

MySQL port: 3306
MySQL cluster port: 33061

Install MySQL on all three servers

Add the MySQL community repo

Download the deb file

curl -OL https://repo.mysql.com//mysql-apt-config_0.8.15-1_all.deb

Install using dpkg

sudo dpkg -i mysql-apt-config_0.8.15-1_all.deb

Select mysql-5.7

Update the apt repo

sudo apt update

Install MySQL server

sudo apt install mysql-server

Start MySQL service

sudo systemctl start mysql && sudo systemctl enable mysql

Do secure installation

sudo mysql_secure_installation

Open configuration

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

Edit configuration

Add /etc/hosts entries to make hostnames resolvable

Open hosts file

sudo vim /etc/hosts

Add the following configuration after changing the IPs appropriately

172.32.0.10 service-db1
172.32.0.20 service-db2
172.32.0.30 service-db3

Add firewall configuration

sudo ufw allow from 172.32.0.54 to any port 3306 proto tcp
sudo ufw allow from 172.32.0.175 to any port 3306 proto tcp
sudo ufw allow from 172.32.0.166 to any port 3306 proto tcp

sudo ufw allow out to 172.32.0.54 port 3306 proto tcp
sudo ufw allow out to 172.32.0.175 port 3306 proto tcp
sudo ufw allow out to 172.32.0.166 port 3306 proto tcp

sudo ufw allow out to 172.32.0.54 port 33061 proto tcp
sudo ufw allow out to 172.32.0.175 port 33061 proto tcp
sudo ufw allow out to 172.32.0.166 port 33061 proto tcp

sudo ufw allow from 172.32.0.54 to any port 33061 proto tcp
sudo ufw allow from 172.32.0.175 to any port 33061 proto tcp
sudo ufw allow from 172.32.0.166 to any port 33061 proto tcp

Prepare first MySQL server for group replication

Edit configuration

Open the file

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

Add the following content

disabled_storage_engines=”MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY”
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
log_bin=binlog
binlog_format=ROW
plugin_load_add=’group_replication.so’
transaction_write_set_extraction=XXHASH64
group_replication_group_name=”40d2393e-a59b-11ea-8acd-0242ac110003″
group_replication_start_on_boot=off
group_replication_local_address= “service-db1:33061″
group_replication_group_seeds= “service-db1:33061,service-db2:33061,service-db3:33061″
group_replication_bootstrap_group=off
group_replication_ip_whitelist=”service-db1,service-db2,service-db3”

The server_id changes to 2 and 3 and group_replication_local_address changes to the hostname and make it resolvable using /etc/hosts file of the current instance for all the servers in mysql cluster, and also change the whitelist IP range.

Login to mysql

sudo mysql -u root

Get UUID

SELECT UUID();

Replace UUID in the configuration file

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

The UUID to be changed is at group_replication_group_name section

Restart MySQL server

sudo systemctl restart mysql

Login to mysql

sudo mysql -u root

Run the following commands

Turn off bin log

SET SQL_LOG_BIN=0;

Create replication user

CREATE USER rpl_user@’%’ IDENTIFIED BY ‘123456789’;

Give appropriate permission

GRANT REPLICATION SLAVE ON *.* TO rpl_user@’%’;

Flush permissions

FLUSH PRIVILEGES;

Turn on bin log

SET SQL_LOG_BIN=1;

Enable user for replication

CHANGE MASTER TO MASTER_USER=’rpl_user’, MASTER_PASSWORD=’123456789′ FOR CHANNEL ‘group_replication_recovery’;

Bootstrap the replication group

SET GLOBAL group_replication_bootstrap_group=ON;

Start the replication

START GROUP_REPLICATION;

Stop the bootstrap

SET GLOBAL group_replication_bootstrap_group=OFF;

Check if the node is online

SELECT * FROM performance_schema.replication_group_members;

Change the mysqld.cnf file group_replication_start_on_boot to on

Cluster second MySQL server to the group

Edit configuration

Open the file

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

Add the following content

disabled_storage_engines=”MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY”
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
log_bin=binlog
binlog_format=ROW
plugin_load_add=’group_replication.so’
transaction_write_set_extraction=XXHASH64
group_replication_group_name=”40d2393e-a59b-11ea-8acd-0242ac110003″
group_replication_start_on_boot=off
group_replication_local_address= “service-db2:33061″
group_replication_group_seeds= “service-db1:33061,service-db2:33061,service-db3:33061″
group_replication_bootstrap_group=off
group_replication_ip_whitelist=”service-db1,service-db2,service-db3”

The server_id changes to 2 and 3 and group_replication_local_address changes to the hostname and make it resolvable using /etc/hosts file of the current instance for all the servers in mysql cluster, and also change the whitelist IP range. Change the UUID to be the same as the first server

Restart MySQL server

sudo systemctl restart mysql

Login to mysql on master server (first server)

sudo mysql -u root

Run the following commands

Turn off bin log

SET SQL_LOG_BIN=0;

Create replication user

CREATE USER rpl_user@’%’ IDENTIFIED BY ‘123456789’;

Give appropriate permission

GRANT REPLICATION SLAVE ON *.* TO rpl_user@’%’;

Flush permissions

FLUSH PRIVILEGES;

Turn on bin log

SET SQL_LOG_BIN=1;

Enable user for replication

CHANGE MASTER TO MASTER_USER=’rpl_user’, MASTER_PASSWORD=’123456789′ FOR CHANNEL ‘group_replication_recovery’;

Start the replication

START GROUP_REPLICATION;

Disable read only mode

SET GLOBAL super_read_only=0;

Check if the node is online

SELECT * FROM performance_schema.replication_group_members;

Cluster third MySQL server to the group

Edit configuration

Open the file

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

Add the following content

disabled_storage_engines=”MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY”
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
log_bin=binlog
binlog_format=ROW
plugin_load_add=’group_replication.so’
transaction_write_set_extraction=XXHASH64
group_replication_group_name=”40d2393e-a59b-11ea-8acd-0242ac110003″
group_replication_start_on_boot=off
group_replication_local_address= “service-db3:33061″
group_replication_group_seeds= “service-db1:33061,service-db2:33061,service-db3:33061″
group_replication_bootstrap_group=off
group_replication_ip_whitelist=”service-db1,service-db2,service-db3”

The server_id changes to 2 and 3 and group_replication_local_address changes to a resolvable url or IP address of the current instance for all the servers in mysql cluster, and also change the whitelist IP range. Change the UUID to be the same as the first server

Login to mysql on master server (first server)

sudo mysql -u root

Run the following commands

Turn off bin log

SET SQL_LOG_BIN=0;

Create replication user

CREATE USER rpl_user@’%’ IDENTIFIED BY ‘123456789’;

Give appropriate permission

GRANT REPLICATION SLAVE ON *.* TO rpl_user@’%’;

Flush permissions

FLUSH PRIVILEGES;

Turn on bin log

SET SQL_LOG_BIN=1;

Enable user for replication

CHANGE MASTER TO MASTER_USER=’rpl_user’, MASTER_PASSWORD=’123456789′ FOR CHANNEL ‘group_replication_recovery’;

Start the replication

START GROUP_REPLICATION;

Disable read only mode

SET GLOBAL super_read_only=0;

Check if the node is online

SELECT * FROM performance_schema.replication_group_members;

That’s it. Your MySQL servers are now clustered with redundancy.