LXC Host Details

hostname: infrabase1
Network: 10.1.65.0/24
IP Address : 10.1.65.9
Subnet Mask: 255.255.255.0
Gateway: 10.1.65.1
DNS: 8.8.8.8
Reverse Proxy setup: Nginx


Server OS: Ubuntu 20.04
Edition: LTS, server 
sudo user: kedar

FTP Client: Filezilla
ssh client: terminal, reminna
Text editors: sublime-text

User PC Details
PC type: Desktop
OS: Ubuntu Desktop
IP Address: 10.1.65.173
sudo user:kedar 

LXC Containers
- db1 (MySQL8.0) (10.0.3.202)
- db2 (MySQL8.0) (10.0.3.246)
- db3 (MySQL8.0) (10.0.3.175)
- db4 (MySQL8.0) (10.0.3.26)
- web1 (Apps)    (10.0.3.159)

Introduction

This article explains how to create a MySQL8.0 HA cluster using group replication and MySQL router. It is important to note that I am using an Ubuntu 20.04 LTS server edition as the LXC host OS and have created 4 Ubuntu 20.04 containers in it and installed MySQL 8.0 from the repositories. The containers have private IPs and they are not reachable on public network. An nginx reverse proxy is setup on the LXC host server so that application setup on the containers are accessible on port 443 or port 80. 'How to setup Nginx as a reverse Proxy' can be found here.

Basic preparation

Container details are mentioned below. Host entries have been made into respective servers so that the servers can reach every other server using a hostname instead of an IP.

#	Container/Host	Hostname	IP Address	Software installed
1	LXC Host	infrabase1	10.1.65.9	lxc, nginx, mysql-shell
2	Container 1	db1	10.0.3.202	mysql server 8.0
3	Container 2	db2	10.0.3.246	mysql server 8.0
4	Container 3	db3	10.0.3.175	mysql server 8.0
5	Container 4	db4	10.0.3.26	mysql server 8.0
6	Container 5	web1	10.0.3.159	mysql-client, apache2, php7.4, wordpress, mysqlrouter

Wordpress will be setup on web1 container and mysqlrouter will also be placed on the same application server. Wordpress will connect to the mysqlrouter like a local mysql db instance. Mysqlrouter will route it to the necessary mysql server in the cluster.

The lxc host server (10.1.65.9) will be used to connect to the db servers using mysql-shell. Group replications steps will be done using the mysql-shell only. No files will be manually edited. Installing mysql-shell will be explained in the below sections.

Preparing MySQL Servers

The below steps have to be done on all three servers without exception

Install mysql server 8.0

Install from the default repositories

root@db1:/# apt install mysql-server nano

Run secure installation

root@db1:/# mysql_secure_installation

If you are doing a POC, answer 'N' for the first question. It asks if password complexity should be enabled. You can keep short passwords so that it is simpler to try out in a POC. For a production environment you should enable password complexity. Answer 'Y' for all further questions. This will disable remote 'root login' on the server and also remove anonymous users and test databases from the MySql server.

Create user for cluster management

Login to sql server and create a user and grant all permissions. Since this is a POC, you can be a bit liberal with the permissions, otherwise checkout the documentation on permissions for group replication user on MySQL website.

root@db1:/# mysql -u root -p
mysql> CREATE USER 'clustermgr'@'%' IDENTIFIED BY '123';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'clustermgr'@'%'WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
mysql> EXIT;

Allow remote database connection

root@db1:/# nano /etc/mysql/mysql.conf.d/mysqld.cnf

Comment the bind address directive so that this database can be connected from other sources.

.
#bind-address = 127.0.0.1
.

Repeat the above steps in the other servers, exactly in the same manner. Do not change password of the user or the username. In POC keep it same otherwise they can be different.

Setting up MySQL Shell

We shall be setting up the mysql shell on the LXC host server (10.1.65.9) from where the lxc containers running the mysql servers are reachable. Group replication will be setup using the mysql-shell program. Group replication can also be setup without the mysql-shell, however several modifications are required in the configuration files. Mysql-shell automates the configuration and helps in managing the cluster as well.

It is important to note that Ubuntu 20.04 does not have a mysql-shell binary in the default apt repository. However, it is available as a snap. If you install mysql-shell snap, then javascript is not enabled by default for the mysql-shell installed through snap. For setting up mysql cluster mysql-shell needs javascript. We therefore download the mysql-shell deb file from mysql website and manually install it on LXC host server.

kedar@infrabase1:~$ wget https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell_8.0.20-1ubuntu20.04_amd64.deb
kedar@infrabase1:~$ sudo dpkg -i mysql-shell_8.0.20-1ubuntu20.04_amd64.deb

Once it is installed, you should be able to connect to the db servers using 'clustermgr' credentials. Use the following command

kedar@infrabase1:~$ mysqlsh

It should look like below

Setting up the cluster

Prepare servers to add in cluster

Log into the first db1 through the shell.

kedar@infrabase1:~$ mysqlsh

At the shell enter the following

MySQL-js> dba.checkInstanceConfiguration('clustermgr@db1:3306')
MySQL-js> dba.configureLocalInstance('clustermgr@db1:3306')
By pressing ^D, you can exit the mysql-shell

Ssh into the mysql server and restart the server

root@db1:/# systemctl restart mysql

Repeat these steps for all of the remaining servers

Create the cluster

First DB Server

Connect to the first db server using the mysql shell from the lxc host server

kedar@infrabase1:~$ mysqlsh
MySQL-js> shell.connect('clustermgr@db1:3306')
MySQL| db1:3306 ssl -js> cluster = dba.createCluster('myCluster');
MySQL| db1:3306 ssl -js> cluster.status()

It will appear as below

{
    "clusterName": "myCluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "db1:3306",
        "ssl": "REQUIRED",
        "status": "OK_NO_TOLERANCE",
        "statusText": "Cluster is NOT tolerant to any failures.",
        "topology": {
            "db1:3306": {
                "address": "db1:3306",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.20"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "db1:3306"
}

Second DB Server

Connect to the second db server using the mysql shell from the lxc host server

MySQL|db1:3306 ssl|JS> cluster.addInstance('clustermgr@db2:3306');
MySQL| db1:3306 ssl -js> cluster.status()

The cluster status will appear as below

{
    "clusterName": "myCluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "db1:3306",
        "ssl": "REQUIRED",
        "status": "OK_NO_TOLERANCE",
        "statusText": "Cluster is NOT tolerant to any failures.",
        "topology": {
            "db1:3306": {
                "address": "db1:3306",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.20"
            },
            "db2:3306": {
                "address": "db2:3306",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.20"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "db1:3306"
}

Third DB Server

Connect to the third db server using the mysql shell from the lxc host server

MySQL|db1:3306 ssl|JS> cluster.addInstance('clustermgr@db3:3306');
MySQL| db1:3306 ssl -js> cluster.status()

Check cluster status

The cluster status will appear as below

{
    "clusterName": "myCluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "db1:3306",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "db1:3306": {
                "address": "db1:3306",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.20"
            },
            "db2:3306": {
                "address": "db2:3306",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.20"
            },
            "db3:3306": {
                "address": "db3:3306",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.20"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "db1:3306"
}

All three servers are added in the cluster. You can add the fourth one too in the same manner. At any given point in time, only one server will be primary and the remaining would be in read-only mode. In case of a failure, one of the two servers will become primary and will be in read/write mode and the other one will be in a read-only mode.

MySql Router & App Server

Setting up MySQL router

Install mysql router on the application server container (10.0.3.159)

root@web1:/# apt install mysql-router
root@web1:/# /bin/mysqlrouter --user=root --bootstrap cluster@db1:3306 -d myrouter

After some time, mysqlrouter will have been installed and a folder created by the name of myrouter. In our case, we running mysqlrouter with the user root, hence the folder will be created in /

root@web:/# cd myrouter
root@web1:/myrouter# ls -l

Following content will be shown

root@web1:/myrouter# ls -l
total 28
drwx------ 2 root root 4096 Jun  8 17:48 data
drwx------ 2 root root 4096 Jun  8 17:48 log
-rw------- 1 root root 1211 Jun  8 17:48 mysqlrouter.conf
-rw------- 1 root root   80 Jun  8 17:48 mysqlrouter.key
drwx------ 2 root root 4096 Jun  8 17:48 run
-rwx------ 1 root root  277 Jun  8 17:48 start.sh
-rwx------ 1 root root  137 Jun  8 17:48 stop.sh

Start the mysqlrouter with the following command

root@web1:/myrouter# ./start.sh

Mysql router has now been set and is ready to accept connections

Setting up App Server

As a sample, we shall be setting up a wordpress site that will be served using an apache web server installed on this container in addition to the mysql-router.
There is a detailed howto on 'Setting up Wordpress' that can be found here.
When setting the database for wordpress, mention the database host as

Database host: 10.0.3.159:6446

All other remaining settings remain the same for configuring the wordpress instance.

Important Commands

Shutting down the cluster

Shut down the read-only databases first. Assuming that db1 is the primary (read/write database)

root@db3:/# systemctl stop mysql
root@db2:/# systemctl stop mysql
root@db1:/# systemctl stop mysql

Starting cluster after a shut down

If the cluster is started after a clean shutdown, ensure that mysql service is running on all the cluster servers. Login to any server you want to to make primary using the mysql-shell. In this case, will connect to db1 using mysql-shell from the lxc host server (10.1.65.9)

kedar@infrabase1:$ mysqlsh
MySQL -js> shell.connect('clustermgr@db1:3306')
MySQL db1:3306 ssl |JS> cluster = dba.rebootClusterFromCompleteOutage()
MySQL db1:3306 ssl |JS> cluster.status()

Switching between Single-Primary & Multi-primary

By default only one primary will be present in the cluster while all will be read-only. To make all other servers primary use the following command

MySQL db1:3306 ssl |JS> cluster.switchToMultiPrimaryMode()
MySQL db1:3306 ssl |JS> cluster.status()

The status should be seen as below

{
    "clusterName": "myCluster",
    "defaultReplicaSet": {
        "name": "default",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "db1:3306": {
                "address": "db1:3306",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.20"
            },
            "db2:3306": {
                "address": "db2:3306",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.20"
            },
            "db3:3306": {
                "address": "db3:3306",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.20"
            }
        },
        "topologyMode": "Multi-Primary"
    },
    "groupInformationSourceMember": "db1:3306"
}

In this case if the application connects to port 6447, the connection will be load balanced, however if the application connects to port 6446, it will work only with the first primary server.

To switch back to the single-primary mode, and make db1 as the primary database

MySQL db1:3306 ssl |JS> cluster.switchToSinglePrimaryMode("db1:3306");

Add a node to the cluster

This will add db4 to the existing cluster of three servers

MySQL db1:3306 ssl |JS> cluster.addInstance("db4:3306")

Remove a node from the cluster

MySQL db1:3306 ssl |JS> cluster - dba.getCluster()
MySQL db1:3306 ssl |JS> cluster.removeInstance("db4:3306")

Add slave server

In our case, we shall add db5 as slave of db2 server which is the cluster. It is assumed that db5 is a ubuntu 20.04 server with the same mysql server (8.0.20) installed on it. This server should not be part of the cluster when this activity is started. Add the configuration to the below file

root@db5:/# nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add the following lines at the end of the file

server-id = 25 # this should be unique
gtid-mode = ON
enforce-gtid-consistency = ON
log-slave-updates = OFF
read-only = ON
super-read-only = ON
expire-logs-days = 15

Save the file and

root@db5:/# systemctl restart mysql

On one of the cluster servers, create a replication slave user and take a full backup of all the databases and move it to the db5 server. We shall be using db2 for this purpose. ssh into db2

root@db2:/# mysql -u root -p
mysql> CREATE USER  'repl'@'%' IDENTIFIED BY '123';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
mysql> EXIT;
root@db2:/# mysqldump - uroot-p --single-transaction --master-data=1 --all-databases --triggers --routines --events > backup.sql

Move the file to the db5 server. On db5 server restore the backup file

root@db5:/# mysql -uroot -p < backup.sql

Initiate the replication and start the slave

mysql> CHANGE MASTER TO MASTER_HOST = '10.0.3.246', MASTER_USER = 'repl', MASTER_PASSWORD = '123', MASTER_AUTO_POSITION = 1;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;

References

Website references

Video references

- Part 1: https://youtu.be/CbmNWAxgMf0
- Part 2: https://youtu.be/ZPJ1KiYN4bQ
- Part 3: https://youtu.be/wT66wDpsJbE
- Part 4: https://youtu.be/s1Gm_CTdRuI

Closing

MySQL router should be installed on all application servers so that required redundancy can be achieved.

MySQL Group Replication

Contents

Introduction

Basic preparation

Preparing MySQL Servers

Install mysql server 8.0

Create user for cluster management

Allow remote database connection

Setting up MySQL Shell

Setting up the cluster

Prepare servers to add in cluster

Create the cluster

First DB Server

Second DB Server

Third DB Server

Check cluster status

MySql Router & App Server

Setting up MySQL router

Setting up App Server

Important Commands

Shutting down the cluster

Starting cluster after a shut down

Switching between Single-Primary & Multi-primary

Add a node to the cluster

Remove a node from the cluster

Add slave server

References

Website references

Video references

Closing

Navigation menu

MySQL Group Replication

Introduction

Basic preparation

Preparing MySQL Servers

Install mysql server 8.0

Create user for cluster management

Allow remote database connection

Setting up MySQL Shell

Setting up the cluster

Prepare servers to add in cluster

Create the cluster

First DB Server

Second DB Server

Third DB Server

Check cluster status

MySql Router & App Server

Setting up MySQL router

Setting up App Server

Important Commands

Shutting down the cluster

Starting cluster after a shut down

Switching between Single-Primary & Multi-primary

Add a node to the cluster

Remove a node from the cluster

Add slave server

References

Website references

Video references

Closing

Navigation menu

Search