MySQL Group Replication

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) - db2 (MySQL8.0) - db3 (MySQL8.0) - db4 (MySQL8.0) - web1 (MySQL Router+Apache)

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. A reverse proxy is setup on the LXC host server so that application setup on the containers are accessible on port 443 or port 80. It 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	Hostname	IP Address	Software installed
1	Container 1	db1	10.0.3.202	mysql server 8.0
2	Container 2	db2	10.0.3.246	mysql server 8.0
3	Container 3	db3	10.0.3.175	mysql server 8.0
4	Container 4	db4	10.0.3.26	mysql server 8.0
5	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.

Preparing MySQL Servers

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

• Install mysql server 8.0

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 all the four 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.