Install and deploy MySQL 8 InnoDB Cluster with 3 nodes under CentOS 8 and MySQL Router for HA

This article is going to show how to install a MySQL server and deploy a MySQL 8 InnoDB Cluster with three nodes behind a MySQL router to archive a high availability with MySQL database back-end.

In really simple words, MySQL 8.0 InnoDB Cluster is just MySQL replication on steroids – i.e. a little more additional work between the servers in the group before committing the transactions. It uses MySQL Group Replication plugin, which allows the group to operate in two different modes:

  1. a single-primary mode with automatic primary election. Only one server gets the updates.
  2. a multi-master mode – all servers accept the updates. For advanced setups.

Group Replication is bi-directional, the servers communicate with each other and use row replication to replicate the data. The main limitation is that only the MySQL InnoDB engine is supported, because of the transactions support. So the performance (and most features and caveats) of MySQL InnoDB is not impacted by cluster setup and overhead compared to the MySQL in replication mode (or a single server setups) from the previous MySQL versions. Still, all read-write transactions commit only after they have been approved by the group – a verification process providing consensus between the servers. In fact, most of the features like GUIDs, row-based replication (i.e. different replication modes) are developed and available to older versions. The new part is handled by Group Communication System (GCS) protocols, which provide a failure detection mechanism, a group membership service, and a safe and completely ordered message delivery (more on the subject here https://dev.mysql.com/doc/refman/8.0/en/group-replication-background.html).
In addition to the group replication, MySQL Router 8.0 provides the HAhigh availability. The program, which redirects, fails over, balances to the right server in the group is the MySQL Router. Clients may connect directly to the servers in the group, but only if the clients connect using MySQL router will have HA because Group Replication does not have a built-in method for it. It is worth noting, there could be many MySQL Routers in different servers, they do not need to communicate or synchronize anything with each other. So the router could be installed in the same place, where the application is installed or on a separate dedicated server, or on every MySQL server in the group.

Key points in this article of MySQL InnoDB Cluster deployment:

  • CentOS 8 Stream is used for the operating system
  • SELinux tuning to allow MySQL process to connect the network.
  • CentOS 8 firewall tuning to unblock the nodes traffic between them.
  • Disable mysql package system module to use the official MySQL repository.
  • Three MySQL 8.0.28 server nodes will be installed
  • To create and manage the cluster MySQL Shell 8.0 and dba object in it are used.
  • Three MySQL routers on each MySQL node will be installed.
  • Each server will have the domains of the all three servers in /etc/hosts file – db-cluster-1, db-cluster-2, db-cluster-3.
  • The cluster is in group replication with one primary (i.e. master) and two secondary nodes (i.e. slaves)

STEP 1) Install CentOS 8 Stream.

There is an article with the CentOS 8 – How to do a network installation of CentOS 8 (8.0.1950) – minimal server installation, which installation is essentially the same as CentOS 8 Stream.

STEP 2) Prepare the CentOS 8 Stream to install MySQL 8 server.

At present, the latest MySQL Community edition is 8.0.28. The preferred way to install the MySQL server is to download the RPM repository file from MySQL web site – https://dev.mysql.com/downloads/repo/yum/

[root@db-cluster-1 ~]# wget https://repo.mysql.com/mysql80-community-release-el8-3.noarch.rpm
--2022-02-17 15:56:11--  https://repo.mysql.com/mysql80-community-release-el8-3.noarch.rpm
Resolving repo.mysql.com (repo.mysql.com)... 2.18.233.231
Connecting to repo.mysql.com (repo.mysql.com)|2.18.233.231|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 14100 (14K) [application/x-redhat-package-manager]
Saving to: ‘mysql80-community-release-el8-3.noarch.rpm’

mysql80-community-release-e 100%[=========================================>]  13.77K  --.-KB/s    in 0s      

2022-02-17 15:56:12 (257 MB/s) - ‘mysql80-community-release-el8-3.noarch.rpm’ saved [14100/14100]

[root@db-cluster-1 ~]# dnf install -y ./mysql80-community-release-el8-3.noarch.rpm 
Last metadata expiration check: 1:18:27 ago on Thu 17 Feb 2022 02:37:58 PM UTC.
Dependencies resolved.
==============================================================================================================
 Package                               Architecture       Version              Repository                Size
==============================================================================================================
Installing:
 mysql80-community-release             noarch             el8-3                @commandline              14 k

Transaction Summary
==============================================================================================================
Install  1 Package

Total size: 14 k
Installed size: 7.5 k
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                      1/1 
  Installing       : mysql80-community-release-el8-3.noarch                                               1/1 
  Verifying        : mysql80-community-release-el8-3.noarch                                               1/1 

Installed:
  mysql80-community-release-el8-3.noarch                                                                      

Complete!
[root@db-cluster-1 ~]#

To use this repository – the official MySQL repository, the mysql module of the CentOS 8 package system must be disabled or MySQL Community Server 8 won’t be available for installation.

[root@db-cluster-1 ~]# dnf module disable mysql
MySQL 8.0 Community Server                                                    4.3 MB/s | 2.3 MB     00:00    
MySQL Connectors Community                                                    702 kB/s |  80 kB     00:00    
MySQL Tools Community                                                         3.7 MB/s | 432 kB     00:00    
Dependencies resolved.
==============================================================================================================
 Package                   Architecture             Version                   Repository                 Size
==============================================================================================================
Disabling modules:
 mysql                                                                                                       

Transaction Summary
==============================================================================================================

Is this ok [y/N]: y
Complete!
[root@db-cluster-1 ~]#

STEP 3) Install MySQL and configure the MySQL server.

If it is a clean install a couple of dependencies will be installed:

[root@db-cluster-1 ~]# dnf install -y mysql-community-server
Last metadata expiration check: 0:01:13 ago on Thu 17 Feb 2022 04:03:26 PM UTC.
Dependencies resolved.
==============================================================================================================
 Package                          Arch     Version                                  Repository           Size
==============================================================================================================
Installing:
 mysql-community-server           x86_64   8.0.28-1.el8                             mysql80-community    53 M
Installing dependencies:
 groff-base                       x86_64   1.22.3-18.el8                            baseos              1.0 M
 libaio                           x86_64   0.3.112-1.el8                            baseos               33 k
 mysql-community-client           x86_64   8.0.28-1.el8                             mysql80-community    14 M
 mysql-community-client-plugins   x86_64   8.0.28-1.el8                             mysql80-community   2.4 M
 mysql-community-common           x86_64   8.0.28-1.el8                             mysql80-community   633 k
 mysql-community-icu-data-files   x86_64   8.0.28-1.el8                             mysql80-community   2.1 M
 mysql-community-libs             x86_64   8.0.28-1.el8                             mysql80-community   1.5 M
 net-tools                        x86_64   2.0-0.52.20160912git.el8                 baseos              322 k
 numactl-libs                     x86_64   2.0.12-13.el8                            baseos               36 k
 perl-Carp                        noarch   1.42-396.el8                             baseos               30 k
 perl-Data-Dumper                 x86_64   2.167-399.el8                            baseos               58 k
 perl-Digest                      noarch   1.17-395.el8                             appstream            27 k
 perl-Digest-MD5                  x86_64   2.55-396.el8                             appstream            37 k
 perl-Encode                      x86_64   4:2.97-3.el8                             baseos              1.5 M
 perl-Errno                       x86_64   1.28-421.el8                             baseos               76 k
 perl-Exporter                    noarch   5.72-396.el8                             baseos               34 k
 perl-File-Path                   noarch   2.15-2.el8                               baseos               38 k
 perl-File-Temp                   noarch   0.230.600-1.el8                          baseos               63 k
 perl-Getopt-Long                 noarch   1:2.50-4.el8                             baseos               63 k
 perl-HTTP-Tiny                   noarch   0.074-1.el8                              baseos               58 k
 perl-IO                          x86_64   1.38-421.el8                             baseos              142 k
 perl-MIME-Base64                 x86_64   3.15-396.el8                             baseos               31 k
 perl-Net-SSLeay                  x86_64   1.88-1.module_el8.4.0+517+be1595ff       appstream           379 k
 perl-PathTools                   x86_64   3.74-1.el8                               baseos               90 k
 perl-Pod-Escapes                 noarch   1:1.07-395.el8                           baseos               20 k
 perl-Pod-Perldoc                 noarch   3.28-396.el8                             baseos               86 k
 perl-Pod-Simple                  noarch   1:3.35-395.el8                           baseos              213 k
 perl-Pod-Usage                   noarch   4:1.69-395.el8                           baseos               34 k
 perl-Scalar-List-Utils           x86_64   3:1.49-2.el8                             baseos               68 k
 perl-Socket                      x86_64   4:2.027-3.el8                            baseos               59 k
 perl-Storable                    x86_64   1:3.11-3.el8                             baseos               98 k
 perl-Term-ANSIColor              noarch   4.06-396.el8                             baseos               46 k
 perl-Term-Cap                    noarch   1.17-395.el8                             baseos               23 k
 perl-Text-ParseWords             noarch   3.30-395.el8                             baseos               18 k
 perl-Text-Tabs+Wrap              noarch   2013.0523-395.el8                        baseos               24 k
 perl-Time-Local                  noarch   1:1.280-1.el8                            baseos               34 k
 perl-URI                         noarch   1.73-3.el8                               appstream           116 k
 perl-Unicode-Normalize           x86_64   1.25-396.el8                             baseos               82 k
 perl-constant                    noarch   1.33-396.el8                             baseos               25 k
 perl-interpreter                 x86_64   4:5.26.3-421.el8                         baseos              6.3 M
 perl-libnet                      noarch   3.11-3.el8                               appstream           121 k
 perl-libs                        x86_64   4:5.26.3-421.el8                         baseos              1.6 M
 perl-macros                      x86_64   4:5.26.3-421.el8                         baseos               72 k
 perl-parent                      noarch   1:0.237-1.el8                            baseos               20 k
 perl-podlators                   noarch   4.11-1.el8                               baseos              118 k
 perl-threads                     x86_64   1:2.21-2.el8                             baseos               61 k
 perl-threads-shared              x86_64   1.58-2.el8                               baseos               48 k
Installing weak dependencies:
 perl-IO-Socket-IP                noarch   0.39-5.el8                               appstream            47 k
 perl-IO-Socket-SSL               noarch   2.066-4.module_el8.4.0+517+be1595ff      appstream           298 k
 perl-Mozilla-CA                  noarch   20160104-7.module_el8.3.0+416+dee7bcef   appstream            15 k
Enabling module streams:
 perl                                      5.26                                                              
 perl-IO-Socket-SSL                        2.066                                                             
 perl-libwww-perl                          6.34                                                              

Transaction Summary
==============================================================================================================
Install  51 Packages

Total download size: 88 M
Installed size: 388 M
Downloading Packages:
(1/51): perl-Digest-1.17-395.el8.noarch.rpm                                   368 kB/s |  27 kB     00:00    
(2/51): perl-Digest-MD5-2.55-396.el8.x86_64.rpm                               353 kB/s |  37 kB     00:00    
(3/51): perl-IO-Socket-IP-0.39-5.el8.noarch.rpm                               350 kB/s |  47 kB     00:00    
(4/51): perl-Mozilla-CA-20160104-7.module_el8.3.0+416+dee7bcef.noarch.rpm     366 kB/s |  15 kB     00:00    
(5/51): perl-IO-Socket-SSL-2.066-4.module_el8.4.0+517+be1595ff.noarch.rpm     2.0 MB/s | 298 kB     00:00    
(6/51): perl-Net-SSLeay-1.88-1.module_el8.4.0+517+be1595ff.x86_64.rpm         3.1 MB/s | 379 kB     00:00    
(7/51): perl-URI-1.73-3.el8.noarch.rpm                                        1.0 MB/s | 116 kB     00:00    
(8/51): perl-libnet-3.11-3.el8.noarch.rpm                                     1.2 MB/s | 121 kB     00:00    
(9/51): libaio-0.3.112-1.el8.x86_64.rpm                                       434 kB/s |  33 kB     00:00    
(10/51): numactl-libs-2.0.12-13.el8.x86_64.rpm                                686 kB/s |  36 kB     00:00    
(11/51): groff-base-1.22.3-18.el8.x86_64.rpm                                  5.1 MB/s | 1.0 MB     00:00    
(12/51): net-tools-2.0-0.52.20160912git.el8.x86_64.rpm                        1.9 MB/s | 322 kB     00:00    
(13/51): perl-Carp-1.42-396.el8.noarch.rpm                                    285 kB/s |  30 kB     00:00    
(14/51): perl-Data-Dumper-2.167-399.el8.x86_64.rpm                            591 kB/s |  58 kB     00:00    
(15/51): perl-Exporter-5.72-396.el8.noarch.rpm                                647 kB/s |  34 kB     00:00    
(16/51): perl-Errno-1.28-421.el8.x86_64.rpm                                   672 kB/s |  76 kB     00:00    
(17/51): perl-Encode-2.97-3.el8.x86_64.rpm                                    7.1 MB/s | 1.5 MB     00:00    
(18/51): perl-File-Path-2.15-2.el8.noarch.rpm                                 328 kB/s |  38 kB     00:00    
(19/51): perl-File-Temp-0.230.600-1.el8.noarch.rpm                            524 kB/s |  63 kB     00:00    
(20/51): perl-Getopt-Long-2.50-4.el8.noarch.rpm                               717 kB/s |  63 kB     00:00    
(21/51): perl-HTTP-Tiny-0.074-1.el8.noarch.rpm                                609 kB/s |  58 kB     00:00    
(22/51): perl-IO-1.38-421.el8.x86_64.rpm                                      1.3 MB/s | 142 kB     00:00    
(23/51): perl-MIME-Base64-3.15-396.el8.x86_64.rpm                             289 kB/s |  31 kB     00:00    
(24/51): perl-PathTools-3.74-1.el8.x86_64.rpm                                 874 kB/s |  90 kB     00:00    
(25/51): perl-Pod-Escapes-1.07-395.el8.noarch.rpm                             208 kB/s |  20 kB     00:00    
(26/51): perl-Pod-Perldoc-3.28-396.el8.noarch.rpm                             935 kB/s |  86 kB     00:00    
(27/51): perl-Pod-Simple-3.35-395.el8.noarch.rpm                              2.0 MB/s | 213 kB     00:00    
(28/51): perl-Pod-Usage-1.69-395.el8.noarch.rpm                               334 kB/s |  34 kB     00:00    
(29/51): perl-Scalar-List-Utils-1.49-2.el8.x86_64.rpm                         640 kB/s |  68 kB     00:00    
(30/51): perl-Socket-2.027-3.el8.x86_64.rpm                                   617 kB/s |  59 kB     00:00    
(31/51): perl-Storable-3.11-3.el8.x86_64.rpm                                  1.1 MB/s |  98 kB     00:00    
(32/51): perl-Term-ANSIColor-4.06-396.el8.noarch.rpm                          541 kB/s |  46 kB     00:00    
(33/51): perl-Term-Cap-1.17-395.el8.noarch.rpm                                268 kB/s |  23 kB     00:00    
(34/51): perl-Text-ParseWords-3.30-395.el8.noarch.rpm                         191 kB/s |  18 kB     00:00    
(35/51): perl-Text-Tabs+Wrap-2013.0523-395.el8.noarch.rpm                     249 kB/s |  24 kB     00:00    
(36/51): perl-Time-Local-1.280-1.el8.noarch.rpm                               346 kB/s |  34 kB     00:00    
(37/51): perl-Unicode-Normalize-1.25-396.el8.x86_64.rpm                       831 kB/s |  82 kB     00:00    
(38/51): perl-constant-1.33-396.el8.noarch.rpm                                265 kB/s |  25 kB     00:00    
(39/51): perl-macros-5.26.3-421.el8.x86_64.rpm                                1.1 MB/s |  72 kB     00:00    
(40/51): perl-parent-0.237-1.el8.noarch.rpm                                   333 kB/s |  20 kB     00:00    
(41/51): perl-libs-5.26.3-421.el8.x86_64.rpm                                  7.4 MB/s | 1.6 MB     00:00    
(42/51): perl-podlators-4.11-1.el8.noarch.rpm                                 1.3 MB/s | 118 kB     00:00    
(43/51): perl-interpreter-5.26.3-421.el8.x86_64.rpm                            16 MB/s | 6.3 MB     00:00    
(44/51): perl-threads-2.21-2.el8.x86_64.rpm                                   267 kB/s |  61 kB     00:00    
(45/51): perl-threads-shared-1.58-2.el8.x86_64.rpm                            174 kB/s |  48 kB     00:00    
(46/51): mysql-community-common-8.0.28-1.el8.x86_64.rpm                       5.9 MB/s | 633 kB     00:00    
(47/51): mysql-community-client-plugins-8.0.28-1.el8.x86_64.rpm                11 MB/s | 2.4 MB     00:00    
(48/51): mysql-community-icu-data-files-8.0.28-1.el8.x86_64.rpm                12 MB/s | 2.1 MB     00:00    
(49/51): mysql-community-libs-8.0.28-1.el8.x86_64.rpm                         8.7 MB/s | 1.5 MB     00:00    
(50/51): mysql-community-client-8.0.28-1.el8.x86_64.rpm                        18 MB/s |  14 MB     00:00    
(51/51): mysql-community-server-8.0.28-1.el8.x86_64.rpm                        36 MB/s |  53 MB     00:01    
--------------------------------------------------------------------------------------------------------------
Total                                                                          24 MB/s |  88 MB     00:03     
MySQL 8.0 Community Server                                                    3.0 MB/s | 3.1 kB     00:00    
Importing GPG key 0x3A79BD29:
 Userid     : "MySQL Release Engineering <mysql-build@oss.oracle.com>"
 Fingerprint: 859B E8D7 C586 F538 430B 19C2 467B 942D 3A79 BD29
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
Key imported successfully
MySQL 8.0 Community Server                                                    1.9 MB/s | 1.9 kB     00:00    
Importing GPG key 0x5072E1F5:
 Userid     : "MySQL Release Engineering <mysql-build@oss.oracle.com>"
 Fingerprint: A4A9 4068 76FC BD3C 4567 70C8 8C71 8D3B 5072 E1F5
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                      1/1 
  Installing       : mysql-community-common-8.0.28-1.el8.x86_64                                          1/51 
  Installing       : mysql-community-client-plugins-8.0.28-1.el8.x86_64                                  2/51 
  Installing       : mysql-community-libs-8.0.28-1.el8.x86_64                                            3/51 
  Running scriptlet: mysql-community-libs-8.0.28-1.el8.x86_64                                            3/51 
  Installing       : mysql-community-client-8.0.28-1.el8.x86_64                                          4/51 
  Installing       : mysql-community-icu-data-files-8.0.28-1.el8.x86_64                                  5/51 
  Installing       : numactl-libs-2.0.12-13.el8.x86_64                                                   6/51 
  Running scriptlet: numactl-libs-2.0.12-13.el8.x86_64                                                   6/51 
  Installing       : net-tools-2.0-0.52.20160912git.el8.x86_64                                           7/51 
  Running scriptlet: net-tools-2.0-0.52.20160912git.el8.x86_64                                           7/51 
  Installing       : libaio-0.3.112-1.el8.x86_64                                                         8/51 
  Installing       : groff-base-1.22.3-18.el8.x86_64                                                     9/51 
  Installing       : perl-Digest-1.17-395.el8.noarch                                                    10/51 
....
....                                                              
  perl-threads-shared-1.58-2.el8.x86_64                                                                       

Complete!
[root@db-cluster-1 ~]#

Some of the installation output is trimmed.
Now, MySQL 8 is installed and on the first start, it would create the system databases and some unique IDs in /var/lib/mysql/
First start of the server:

[root@db-cluster-1 ~]# systemctl start mysqld
[root@db-cluster-1 ~]# systemctl enable mysqld
[root@db-cluster-1 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2022-02-11 16:17:18 UTC; 9min 45s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 747 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 817 (mysqld)
   Status: "Server is operational"
    Tasks: 39 (limit: 11381)
   Memory: 590.9M
   CGroup: /system.slice/mysqld.service
           └─817 /usr/sbin/mysqld

Feb 17 16:11:38 db-cluster-1 systemd[1]: Starting MySQL Server...
Feb 17 16:12:37 db-cluster-1 systemd[1]: Started MySQL Server.
[root@db-cluster-1 ~]# ls -altr /var/lib/mysql/
total 188884
drwxr-xr-x. 23 root  root      4096 Feb 17 16:05  ..
-rw-r-----.  1 mysql mysql       56 Feb 17 16:11  auto.cnf
-rw-r-----.  1 mysql mysql 50331648 Feb 17 16:11  ib_logfile1
-rw-r-----.  1 mysql mysql  8585216 Feb 17 16:11 '#ib_16384_1.dblwr'
drwxr-x---.  2 mysql mysql     4096 Feb 17 16:12  performance_schema
-rw-------.  1 mysql mysql     1676 Feb 17 16:12  ca-key.pem
-rw-r--r--.  1 mysql mysql     1112 Feb 17 16:12  ca.pem
-rw-------.  1 mysql mysql     1680 Feb 17 16:12  server-key.pem
-rw-r--r--.  1 mysql mysql     1112 Feb 17 16:12  server-cert.pem
-rw-------.  1 mysql mysql     1680 Feb 17 16:12  client-key.pem
-rw-r--r--.  1 mysql mysql     1112 Feb 17 16:12  client-cert.pem
-rw-r--r--.  1 mysql mysql      452 Feb 17 16:12  public_key.pem
-rw-------.  1 mysql mysql     1680 Feb 17 16:12  private_key.pem
drwxr-x---.  2 mysql mysql     4096 Feb 17 16:12  mysql
drwxr-x---.  2 mysql mysql     4096 Feb 17 16:12  sys
-rw-r-----.  1 mysql mysql     5515 Feb 17 16:12  ib_buffer_pool
drwxr-x---.  2 mysql mysql     4096 Feb 17 16:12 '#innodb_temp'
-rw-r-----.  1 mysql mysql       16 Feb 17 16:12  binlog.index
-rw-r-----.  1 mysql mysql      157 Feb 17 16:12  binlog.000001
-rw-------.  1 mysql mysql        5 Feb 17 16:12  mysql.sock.lock
srwxrwxrwx.  1 mysql mysql        0 Feb 17 16:12  mysql.sock
drwxr-x--x.  6 mysql mysql     4096 Feb 17 16:12  .
-rw-r-----.  1 mysql mysql 12582912 Feb 17 16:12  ibtmp1
-rw-r-----.  1 mysql mysql 25165824 Feb 17 16:12  mysql.ibd
-rw-r-----.  1 mysql mysql 12582912 Feb 17 16:12  ibdata1
-rw-r-----.  1 mysql mysql 16777216 Feb 17 16:14  undo_001
-rw-r-----.  1 mysql mysql   196608 Feb 17 16:14 '#ib_16384_0.dblwr'
-rw-r-----.  1 mysql mysql 16777216 Feb 17 16:14  undo_002
-rw-r-----.  1 mysql mysql 50331648 Feb 17 16:14  ib_logfile0
[root@db-cluster-1 ~]#

STEP 3) Install MySQL shell to create and manage the MySQL 8 InnoDB Cluster

The MySQL Shell 8.0 is the program, with which the user interacts with the cluster – https://dev.mysql.com/doc/mysql-shell/8.0/en/

[root@db-cluster-1 ~]# dnf install -y mysql-shell
Last metadata expiration check: 2:18:03 ago on Thu 17 Feb 2022 05:44:58 PM UTC.
Dependencies resolved.
==============================================================================================================
 Package                       Architecture  Version                               Repository             Size
==============================================================================================================
Installing:
 mysql-shell                   x86_64        8.0.28-1.el8                          mysql-tools-community  17 M
Installing dependencies:
 python39-libs                 x86_64        3.9.7-1.module_el8.6.0+930+10acc06f   appstream              8.2 M
 python39-pip-wheel            noarch        20.2.4-6.module_el8.6.0+930+10acc06f  appstream              1.3 M
 python39-setuptools-wheel     noarch        50.3.2-4.module_el8.6.0+930+10acc06f  appstream              497 k
Installing weak dependencies:
 python39                      x86_64        3.9.7-1.module_el8.6.0+930+10acc06f   appstream               33 k
 python39-pip                  noarch        20.2.4-6.module_el8.6.0+930+10acc06f  appstream              2.0 M
 python39-setuptools           noarch        50.3.2-4.module_el8.6.0+930+10acc06f  appstream              871 k
Enabling module streams:
 python39                                                   3.9                                                                                                           

Transaction Summary
===============================================================================================================
Install  7 Packages

Total download size: 30 M
Installed size: 150 M
Downloading Packages:
(1/7): python39-3.9.7-1.module_el8.6.0+930+10acc06f.x86_64.rpm                      375 kB/s |  33 kB     00:00    
(2/7): python39-pip-wheel-20.2.4-6.module_el8.6.0+930+10acc06f.noarch.rpm           6.7 MB/s | 1.3 MB     00:00    
(3/7): python39-setuptools-50.3.2-4.module_el8.6.0+930+10acc06f.noarch.rpm           10 MB/s | 871 kB     00:00    
(4/7): python39-pip-20.2.4-6.module_el8.6.0+930+10acc06f.noarch.rpm                 4.1 MB/s | 2.0 MB     00:00    
(5/7): python39-setuptools-wheel-50.3.2-4.module_el8.6.0+930+10acc06f.noarch.rpm    2.7 MB/s | 497 kB     00:00    
(6/7): python39-libs-3.9.7-1.module_el8.6.0+930+10acc06f.x86_64.rpm                  11 MB/s | 8.2 MB     00:00    
(7/7): mysql-shell-8.0.28-1.el8.x86_64.rpm                                           27 MB/s |  17 MB     00:00    
---------------------------------------------------------------------------------------------------------------
Total                                                                                21 MB/s |  30 MB     00:01     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                        1/1 
  Installing       : python39-setuptools-wheel-50.3.2-4.module_el8.6.0+930+10acc06f.noarch                  1/7 
  Installing       : python39-pip-wheel-20.2.4-6.module_el8.6.0+930+10acc06f.noarch                         2/7 
  Installing       : python39-libs-3.9.7-1.module_el8.6.0+930+10acc06f.x86_64                               3/7 
  Installing       : python39-3.9.7-1.module_el8.6.0+930+10acc06f.x86_64                                    4/7 
  Running scriptlet: python39-3.9.7-1.module_el8.6.0+930+10acc06f.x86_64                                    4/7 
  Installing       : python39-setuptools-50.3.2-4.module_el8.6.0+930+10acc06f.noarch                        5/7 
  Running scriptlet: python39-setuptools-50.3.2-4.module_el8.6.0+930+10acc06f.noarch                        5/7 
  Installing       : python39-pip-20.2.4-6.module_el8.6.0+930+10acc06f.noarch                               6/7 
  Running scriptlet: python39-pip-20.2.4-6.module_el8.6.0+930+10acc06f.noarch                               6/7 
  Installing       : mysql-shell-8.0.28-1.el8.x86_64                                                        7/7 
  Running scriptlet: mysql-shell-8.0.28-1.el8.x86_64                                                        7/7 
  Verifying        : python39-3.9.7-1.module_el8.6.0+930+10acc06f.x86_64                                    1/7 
  Verifying        : python39-libs-3.9.7-1.module_el8.6.0+930+10acc06f.x86_64                               2/7 
  Verifying        : python39-pip-20.2.4-6.module_el8.6.0+930+10acc06f.noarch                               3/7 
  Verifying        : python39-pip-wheel-20.2.4-6.module_el8.6.0+930+10acc06f.noarch                         4/7 
  Verifying        : python39-setuptools-50.3.2-4.module_el8.6.0+930+10acc06f.noarch                        5/7 
  Verifying        : python39-setuptools-wheel-50.3.2-4.module_el8.6.0+930+10acc06f.noarch                  6/7 
  Verifying        : mysql-shell-8.0.28-1.el8.x86_64                                                        7/7 

Installed:
  mysql-shell-8.0.28-1.el8.x86_64
  python39-3.9.7-1.module_el8.6.0+930+10acc06f.x86_64
  python39-libs-3.9.7-1.module_el8.6.0+930+10acc06f.x86_64                                         
  python39-pip-20.2.4-6.module_el8.6.0+930+10acc06f.noarch                                  
  python39-pip-wheel-20.2.4-6.module_el8.6.0+930+10acc06f.noarch                                   
  python39-setuptools-50.3.2-4.module_el8.6.0+930+10acc06f.noarch                           
  python39-setuptools-wheel-50.3.2-4.module_el8.6.0+930+10acc06f.noarch                           

Complete!

STEP 3.1) Add the domains and IPs in the /etc/hosts

Of course, this step is not mandatory, the domains could be resolvable through the DNS, but domains in the /etc/hosts are faster resolved with only local for the system lookup. In fact, using /etc/hosts for the cluster domain names the IPs may be different in the different nodes, but this setup is more complex is beyond this article’s scope.

192.168.0.11 db-cluster-1
192.168.0.12 db-cluster-2
192.168.0.13 db-cluster-3

STEP 3.2) SELinux configuration.

SELinux tuning is important for the cluster to work properly:

[root@db-cluster-1 ~]# getenforce 
Enforcing
[root@db-cluster-1 ~]# setsebool -P mysql_connect_any 1

If getenforce reports Enforcing the setseboot command is mandatory to run the cluster flawless.

STEP 3.3) Firewall configuration.

The cluster is in the local trusted network, the three IPs or the whole network could be added in the build-in trusted zone with:

firewall-cmd --permanent --zone=trusted --add-source=192.168.0.0/24
firewall-cmd --reload

or only for servers’ IPs assuimg the IPs of the three nodes are 192.168.0.11, 192.168.0.12, 192.168.0.13.

firewall-cmd --permanent --zone=trusted --add-source=192.168.0.11
firewall-cmd --permanent --zone=trusted --add-source=192.168.0.12
firewall-cmd --permanent --zone=trusted --add-source=192.168.0.13
firewall-cmd --reload

STEP 3.4) MySQL generic configuration.

When the SELinux and firewall configuration are ready, tune the MySQL generic and some InnoDB configuration variables for the purpose of the setup. For example, add to the bottom of file /etc/my.cnf (under section “[mysqld]“):

#my configs
skip-external-locking
skip-name-resolve
skip-character-set-client-handshake

key_buffer_size                 = 256M
max_allowed_packet              = 256M
table_open_cache                = 500
open_files_limit                = 5000
sort_buffer_size                = 2048K
net_buffer_length               = 8K
read_buffer_size                = 256K
read_rnd_buffer_size            = 1024K
myisam_sort_buffer_size         = 64M
max_connections                 = 150
max_heap_table_size             = 512M

innodb_buffer_pool_size         = 2048M
innodb_log_buffer_size          = 32M
innodb_log_files_in_group       = 2
innodb_flush_log_at_trx_commit  = 0
innodb_lock_wait_timeout        = 50
innodb_file_per_table
innodb_thread_concurrency       = 0
innodb_flush_method             = O_DIRECT

The innodb_buffer_pool_size is set to 70% the amount of RAM.

STEP 3.5) Set MySQL root password.

The initializing process of MySQL sets a temporary password for the root account, which should be changed with MySQL console. The password is logged in the MySQL log file – /var/log/mysqld.log. Search for it with:

[root@db-cluster-1 ~]# grep -i "temporary password" /var/log/mysqld.log 
2022-02-17T16:12:02.627695Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: Z5+fYVe_s=ZO

And then change the password with MySQL SQL console (the password is need for login):

[root@db-cluster-1 ~]# mysql -uroot -p"Z5+fYVe_s=ZO"
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.28

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'tee9wie2Pa4aevai#g&i';
Query OK, 0 rows affected (0.08 sec)

mysql> ^DBye

It is convenient to add the password to the user’s home .my.cnf file (in this case, root system account – /root/.my.cnf).

[client]
password="tee9wie2Pa4aevai#g&i"

Entering the MySQL console will not require a MySQL root password, because the MySQL console will read it from the file.

STEP 4) Check the configuration for MySQL 8.0 InnoDB Cluster and create a cluster administrative account.

The MySQL Shell command “mysqlsh” is used to configure and manage the MySQL 8.0 InnoDB Cluster using JavaScript syntax. The user accesses MySQL InnoDB Cluster functanality with the “dba” object through “mysqlsh”

First thing to do before creating the MySQL 8.0 InnoDB Cluster is to check whether the current MySQL configuration is ready and compliant for the Cluster setup. Enther the MySQL Shell and execute the following command:

[root@db-cluster-1 ~]# mysqlsh
MySQL Shell 8.0.28

Copyright (c) 2016, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
 MySQL  JS > dba.checkInstanceConfiguration("root@localhost")
Please provide the password for 'root@localhost': ********************
Save password for 'root@localhost'? [Y]es/[N]o/Ne[v]er (default No): No
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...

ERROR: New account(s) with proper source address specification to allow remote connection from all instances must be created to manage the cluster.
Dba.checkInstanceConfiguration: User 'root' can only connect from 'localhost'. (RuntimeError)

A cluster administrative account must be created, which could connect from the network. Then, check the MySQL readiness for the MySQL 8.0 Cluster InnoDB with the cluster’s administrative account. The local root administrative account should be used to create the cluster administrative account with the name clusteradmin (the name may be different):

[root@db-cluster-1 ~]# mysqlsh
MySQL Shell 8.0.28

Copyright (c) 2016, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
 MySQL  JS > dba.configureInstance("root@localhost")
Please provide the password for 'root@localhost': ********************
Save password for 'root@localhost'? [Y]es/[N]o/Ne[v]er (default No): N
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as db-cluster-1:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

ERROR: User 'root' can only connect from 'localhost'. New account(s) with proper source address specification to allow remote connection from all instances must be created to manage the cluster.

1) Create remotely usable account for 'root' with same grants and password
2) Create a new admin account for InnoDB cluster with minimal required grants
3) Ignore and continue
4) Cancel

Please select an option [1]: 2
Please provide an account name (e.g: icroot@%) to have it created with the necessary
privileges or leave empty and press Enter to cancel.
Account Name: clusteradmin@%
Password for new account: ********************
Confirm password: ********************

applierWorkerThreads will be set to the default value of 4.

NOTE: Some configuration options need to be fixed:
+----------------------------------------+---------------+----------------+--------------------------------------------------+
| Variable                               | Current Value | Required Value | Note                                             |
+----------------------------------------+---------------+----------------+--------------------------------------------------+
| binlog_transaction_dependency_tracking | COMMIT_ORDER  | WRITESET       | Update the server variable                       |
| enforce_gtid_consistency               | OFF           | ON             | Update read-only variable and restart the server |
| gtid_mode                              | OFF           | ON             | Update read-only variable and restart the server |
| server_id                              | 1             | <unique ID>    | Update read-only variable and restart the server |
+----------------------------------------+---------------+----------------+--------------------------------------------------+

Some variables need to be changed, but cannot be done dynamically on the server.
Do you want to perform the required configuration changes? [y/n]: y
Do you want to restart the instance after configuring it? [y/n]: y

Cluster admin user 'clusteradmin'@'%' created.
Configuring instance...
The instance 'db-cluster-1:3306' was configured to be used in an InnoDB cluster.
Restarting MySQL...
NOTE: MySQL server at db-cluster-1:3306 was restarted.

The MySQL shell command dba.configureInstance(“root@localhost”) offers:

  1. To create an admin account (i.e. administrative cluster account) for InnoDB cluster with minimal required grants
  2. a check for the MySQL configuration compliance with the MySQL Cluster InnoDB.
  3. Fix any non-compliant configuration and restart the MySQL server.

Using the newly created admin user is easy to check whether the dba.configureInstance fixed the issues reported above:

[root@db-cluster-1 ~]# mysqlsh
MySQL Shell 8.0.28

Copyright (c) 2016, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
 MySQL  JS > dba.checkInstanceConfiguration("clusteradmin@db-cluster-1")
Please provide the password for 'clusteradmin@db-cluster-1': ********************
Save password for 'clusteradmin@db-cluster-1'? [Y]es/[N]o/Ne[v]er (default No): Y
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as db-cluster-1:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected

Checking instance configuration...
Instance configuration is compatible with InnoDB cluster

The instance 'db-cluster-1:3306' is valid to be used in an InnoDB cluster.

{
    "status": "ok"
}
 MySQL  JS >

This server is ready to create the MySQL InnoDB Cluster. It is a good idea to remember the password of clusteradmin@db-cluster-1 for future use in mysqlsh (line 11).

STEP 5) Create the MySQL Cluster.

It is a simple operation and it will create a group replication with the first server master, i.e. primary in the group. Note, first connect to the MySQL with the cluster administrator account and then issue a create command with the logical name of the cluster.

[root@db-cluster-1 ~]# mysqlsh
MySQL Shell 8.0.28

Copyright (c) 2016, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
 MySQL  JS > \connect clusteradmin@db-cluster-1
Creating a session to 'clusteradmin@db-cluster-1'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 12 (X protocol)
Server version: 8.0.28 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  db-cluster-1:33060+ ssl  JS > dba.createCluster("mycluster1")
A new InnoDB cluster will be created on instance 'db-cluster-1:3306'.

Validating instance configuration at db-cluster-1:3306...

This instance reports its own address as db-cluster-1:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'db-cluster-1:33061'. Use the localAddress option to override.

Creating InnoDB cluster 'mycluster1' on 'db-cluster-1:3306'...

Adding Seed Instance...
Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.

<Cluster:mycluster1>
 MySQL  db-cluster-1:33060+ ssl  JS > var cluster = dba.getCluster()
 MySQL  db-cluster-1:33060+ ssl  JS > cluster.status()
{
    "clusterName": "mycluster1", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "db-cluster-1:3306", 
        "ssl": "REQUIRED", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures.", 
        "topology": {
            "db-cluster-1:3306": {
                "address": "db-cluster-1:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.28"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "db-cluster-1:3306"
}
 MySQL  db-cluster-1:33060+ ssl  JS >

The cluster is up and running with only one node, which is the read/write primary node (note the mode: R/W). By default, the cluster mode is with one primary node and multiple (in this case, two nodes) in secondary mode, which executes only reads (i.e. selects).

STEP 6) Install and configure the rest two cluster nodes.

Now, it is time to repeat the above steps two more times without the last “5) Create the cluster”. Here are just the commands without their output for clarity:

wget https://repo.mysql.com/mysql80-community-release-el8-3.noarch.rpm
dnf install -y ./mysql80-community-release-el8-3.noarch.rpm
dnf module disable mysql
dnf install -y mysql-community-server
systemctl start mysqld
dnf install -y mysql-shell
echo "
192.168.0.11 db-cluster-1 
192.168.0.12 db-cluster-2 
192.168.0.13 db-cluster-3" >> /etc/hosts
setsebool -P mysql_connect_any 1
firewall-cmd --permanent --zone=trusted --add-source=192.168.0.0/24
firewall-cmd --reload
#tune the MySQL configuration /etc/my.cnf file with generic variables
grep -i "temporary password" /var/log/mysqld.log
mysql -uroot -p"[temporary password from above command]"
     mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'tee9wie2Pa4aevai#g&i';
     mysql> quit
#add the new root password in <strong>/root/.my.cnf</strong>
mysqlsh
      MySQL JS > dba.configureInstance("root@localhost")
          Please select an option [1]: 2
          Account Name: clusteradmin@%
          Password for new account: ********************
          Confirm password: ********************
          Do you want to perform the required configuration changes? [y/n]: y
          Do you want to restart the instance after configuring it? [y/n]: y
      MySQL JS > dba.checkInstanceConfiguration("clusteradmin@db-cluster-3")
          {
             "status": "ok"
          }

STEP 7) Add the two MySQL nodes in the cluster.

Now, it is time to add the MySQL nodes in the cluster created in STEP 5). When adding a node a recovery process happens and there two different modes – Clone and Incremental. The Clone mode is safer way to add an node and in general, it is used when adding a completely new node.

It’s worth mentioning the cloning process when adding a node (instance) to the cluster is a complex procedure, which may add users and temporary configuration and if interrupted the process may not be able to restart properly!

[root@db-cluster-1 ~]# mysqlsh
MySQL Shell 8.0.28

Copyright (c) 2016, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
 MySQL  JS > \connect clusteradmin@db-cluster-1
Creating a session to 'clusteradmin@db-cluster-1'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 8 (X protocol)
Server version: 8.0.28 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  db-cluster-1:33060+ ssl  JS > var cluster = dba.getCluster()
 MySQL  db-cluster-1:33060+ ssl  JS > cluster.status()
{
    "clusterName": "mycluster1", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "db-cluster-1:3306", 
        "ssl": "REQUIRED", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures.", 
        "topology": {
            "db-cluster-1:3306": {
                "address": "db-cluster-1:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.28"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "db-cluster-1:3306"
}
 MySQL  db-cluster-1:33060+ ssl  JS > cluster.addInstance('clusteradmin@db-cluster-2:3306')

NOTE: The target instance 'db-cluster-2:3306' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether incremental state recovery can correctly provision it.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'db-cluster-2:3306' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the cluster or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.


Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): C
Validating instance configuration at db-cluster-2:3306...

This instance reports its own address as db-cluster-2:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'db-cluster-2:33061'. Use the localAddress option to override.

A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster...

Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Clone based state recovery is now in progress.

NOTE: A server restart is expected to happen as part of the clone process. If the
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.

* Waiting for clone to finish...
NOTE: db-cluster-2:3306 is being cloned from db-cluster-1:3306
** Stage DROP DATA: Completed
** Clone Transfer  
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ############################################################  100%  Completed
    REDO COPY  ############################################################  100%  Completed

NOTE: db-cluster-2:3306 is shutting down...

* Waiting for server restart... ready 
* db-cluster-2:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 72.61 MB transferred in about 1 second (~72.61 MB/s)

State recovery already finished for 'db-cluster-2:3306'

The instance 'db-cluster-2:3306' was successfully added to the cluster.

 MySQL  db-cluster-1:33060+ ssl  JS > cluster.status()
{
    "clusterName": "mycluster1", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "db-cluster-1:3306", 
        "ssl": "REQUIRED", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures.", 
        "topology": {
            "db-cluster-1:3306": {
                "address": "db-cluster-1:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.28"
            }, 
            "db-cluster-2:3306": {
                "address": "db-cluster-2:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.28"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "db-cluster-1:3306"
}
 MySQL  db-cluster-1:33060+ ssl  JS >

And add the third server db-cluster-3 with the same command (resume in the above MySQL shellmysqlsh or connect again):

[root@db-cluster-1 ~]# mysqlsh
MySQL Shell 8.0.28

Copyright (c) 2016, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
 MySQL  JS > \connect clusteradmin@db-cluster-1
Creating a session to 'clusteradmin@db-cluster-1'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 51 (X protocol)
Server version: 8.0.28 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  db-cluster-1:33060+ ssl  JS > var cluster = dba.getCluster()
 MySQL  db-cluster-1:33060+ ssl  JS > cluster.status()
{
    "clusterName": "mycluster1", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "db-cluster-1:3306", 
        "ssl": "REQUIRED", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures.", 
        "topology": {
            "db-cluster-1:3306": {
                "address": "db-cluster-1:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.28"
            }, 
            "db-cluster-2:3306": {
                "address": "db-cluster-2:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.28"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "db-cluster-1:3306"
}
 MySQL  db-cluster-1:33060+ ssl  JS > cluster.addInstance('clusteradmin@db-cluster-3:3306')

NOTE: The target instance 'db-cluster-3:3306' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether incremental state recovery can correctly provision it.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'db-cluster-3:3306' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the cluster or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.


Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): C
Validating instance configuration at db-cluster-3:3306...

This instance reports its own address as db-cluster-3:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'db-cluster-3:33061'. Use the localAddress option to override.

A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster...

Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Clone based state recovery is now in progress.

NOTE: A server restart is expected to happen as part of the clone process. If the
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.

* Waiting for clone to finish...
NOTE: db-cluster-3:3306 is being cloned from db-cluster-2:3306
** Stage DROP DATA: Completed 
** Clone Transfer  
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ############################################################  100%  Completed
    REDO COPY  ############################################################  100%  Completed

NOTE: db-cluster-3:3306 is shutting down...

* Waiting for server restart... ready 
* db-cluster-3:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 72.61 MB transferred in 5 sec (14.52 MB/s)

Incremental state recovery is now in progress.

* Waiting for distributed recovery to finish...
NOTE: 'db-cluster-3:3306' is being recovered from 'db-cluster-2:3306'
* Distributed recovery has finished

The instance 'db-cluster-3:3306' was successfully added to the cluster.

 MySQL  db-cluster-1:33060+ ssl  JS > cluster.status()
{
    "clusterName": "mycluster1", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "db-cluster-1:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "db-cluster-1:3306": {
                "address": "db-cluster-1:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.28"
            }, 
            "db-cluster-2:3306": {
                "address": "db-cluster-2:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.28"
            }, 
            "db-cluster-3:3306": {
                "address": "db-cluster-3:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.28"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "db-cluster-1:3306"
}
 MySQL  db-cluster-1:33060+ ssl  JS >

STEP 8) Install and run MySQL Router

After installation of MySQL Router it should be bootstrapped to generate the current configuration of the cluster.

MySQL Router depends only on the MySQL InnoDB Cluster scheme, so multiple instances on different hosts could be used simultaneously.

First, install the MySQL Router.

[root@db-cluster-1 ~]# dnf install -y mysql-router-community
Last metadata expiration check: 0:44:22 ago on Fri 18 Feb 2022 11:23:25 AM UTC.
Dependencies resolved.
==============================================================================================================
 Package                         Architecture    Version                 Repository                      Size
==============================================================================================================
Installing:
 mysql-router-community          x86_64          8.0.28-1.el8            mysql-tools-community          3.5 M

Transaction Summary
==============================================================================================================
Install  1 Package

Total download size: 3.5 M
Installed size: 14 M
Downloading Packages:
mysql-router-community-8.0.28-1.el8.x86_64.rpm                                5.0 MB/s | 3.5 MB     00:00    
--------------------------------------------------------------------------------------------------------------
Total                                                                         5.0 MB/s | 3.5 MB     00:00     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                      1/1 
  Running scriptlet: mysql-router-community-8.0.28-1.el8.x86_64                                           1/1 
  Installing       : mysql-router-community-8.0.28-1.el8.x86_64                                           1/1 
  Running scriptlet: mysql-router-community-8.0.28-1.el8.x86_64                                           1/1 
  Verifying        : mysql-router-community-8.0.28-1.el8.x86_64                                           1/1 

Installed:
  mysql-router-community-8.0.28-1.el8.x86_64                                                                  

Complete!

Second, bootstrap the configuration to create the initial configuration and the needed user(s).

[root@db-cluster-1 ~]# mysqlrouter --bootstrap clusteradmin@localhost:3306 --user mysqlrouter --conf-use-sockets --account routerfriend1 --account-create if-not-exists
Please enter MySQL password for clusteradmin: 
# Bootstrapping system MySQL Router instance...

Please enter MySQL password for routerfriend1: 
- Creating account(s) (only those that are needed, if any)
- Verifying account (using it to run SQL queries that would be run by Router)
- Storing account in keyring
- Adjusting permissions of generated files
- Creating configuration /etc/mysqlrouter/mysqlrouter.conf

Existing configuration backed up to '/etc/mysqlrouter/mysqlrouter.conf.bak'

# MySQL Router configured for the InnoDB Cluster 'mycluster1'

After this MySQL Router has been started with the generated configuration

    $ /etc/init.d/mysqlrouter restart
or
    $ systemctl start mysqlrouter
or
    $ mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf

InnoDB Cluster 'mycluster1' can be reached by connecting to:

## MySQL Classic protocol

- Read/Write Connections: localhost:6446, /tmp/mysql.sock
- Read/Only Connections:  localhost:6447, /tmp/mysqlro.sock

## MySQL X protocol

- Read/Write Connections: localhost:6448, /tmp/mysqlx.sock
- Read/Only Connections:  localhost:6449, /tmp/mysqlxro.sock

[root@db-cluster-1 ~]#

It will generate the configuration in /etc/mysqlrouter/mysqlrouter.conf and some more files like the keys and certificates needed for the SSL connections:

# File automatically generated during MySQL Router bootstrap
[DEFAULT]
name=system
user=mysqlrouter
keyring_path=/var/lib/mysqlrouter/keyring
master_key_path=/etc/mysqlrouter/mysqlrouter.key
connect_timeout=15
read_timeout=30
dynamic_state=/var/lib/mysqlrouter/state.json
client_ssl_cert=/var/lib/mysqlrouter/router-cert.pem
client_ssl_key=/var/lib/mysqlrouter/router-key.pem
client_ssl_mode=PREFERRED
server_ssl_mode=AS_CLIENT
server_ssl_verify=DISABLED

[logger]
level=INFO

[metadata_cache:mycluster1]
cluster_type=gr
router_id=1
user=routerfriend1
metadata_cluster=mycluster1
ttl=0.5
auth_cache_ttl=-1
auth_cache_refresh_interval=2
use_gr_notifications=0

[routing:mycluster1_rw]
bind_address=0.0.0.0
bind_port=6446
socket=/tmp/mysql.sock
destinations=metadata-cache://mycluster1/?role=PRIMARY
routing_strategy=first-available
protocol=classic

[routing:mycluster1_ro]
bind_address=0.0.0.0
bind_port=6447
socket=/tmp/mysqlro.sock
destinations=metadata-cache://mycluster1/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=classic

[routing:mycluster1_x_rw]
bind_address=0.0.0.0
bind_port=6448
socket=/tmp/mysqlx.sock
destinations=metadata-cache://mycluster1/?role=PRIMARY
routing_strategy=first-available
protocol=x

[routing:mycluster1_x_ro]
bind_address=0.0.0.0
bind_port=6449
socket=/tmp/mysqlxro.sock
destinations=metadata-cache://mycluster1/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=x

[http_server]
port=8443
ssl=1
ssl_cert=/var/lib/mysqlrouter/router-cert.pem
ssl_key=/var/lib/mysqlrouter/router-key.pem

[http_auth_realm:default_auth_realm]
backend=default_auth_backend
method=basic
name=default_realm

[rest_router]
require_realm=default_auth_realm

[rest_api]

[http_auth_backend:default_auth_backend]
backend=metadata_cache

[rest_routing]
require_realm=default_auth_realm

[rest_metadata_cache]
require_realm=default_auth_realm

And the last step is to start the MySQL Router and the ports to the router should be opened:

[root@db-cluster-1 ~]# systemctl start mysqlrouter
[root@db-cluster-1 ~]# systemctl status mysqlrouter
● mysqlrouter.service - MySQL Router
   Loaded: loaded (/usr/lib/systemd/system/mysqlrouter.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2022-02-18 12:13:22 UTC; 3s ago
 Main PID: 2272 (mysqlrouter)
   Status: "running"
   CGroup: /system.slice/mysqlrouter.service
           └─2272 /usr/bin/mysqlrouter

Feb 18 12:13:22 db-cluster-1 systemd[1]: Starting MySQL Router...
Feb 18 12:13:22 db-cluster-1 systemd[1]: Started MySQL Router.
[root@db-cluster-1 ~]# systemctl enable mysqlrouter
Created symlink /etc/systemd/system/multi-user.target.wants/mysqlrouter.service → /usr/lib/systemd/system/mysqlrouter.service.
[root@db-cluster-1 ~]# firewall-cmd --permanent --zone=public --add-port=6446/tcp
success
[root@db-cluster-1 ~]# firewall-cmd --permanent --zone=public --add-port=6447/tcp
success
[root@db-cluster-1 ~]# firewall-cmd --permanent --zone=public --add-port=6448/tcp
success
[root@db-cluster-1 ~]# firewall-cmd --permanent --zone=public --add-port=6449/tcp
success
[root@db-cluster-1 ~]# firewall-cmd --reload
success

Port 6446 is for Read/Write operations and the 6447 is only for Read operations of the classic MySQL protocol, the other two ports are for the MySQL X protocol. Note that, by executing the above firewall rules, all IPs would be allowed to contact the specified ports. An additional limitation may apply. Limit the IPs, which may contact the MySQL Router with:

firewall-cmd --permanent --add-rich-rule="rule family="ipv4" source address="1.1.1.0/24" port protocol="tcp" port="6446" accept"
firewall-cmd --reload

Only the IPs within the range 1.1.1.0/24 could contact the 6446 port.
Repeat this last step on every server, where the MySQL router is installed. It may be a good idea to be a local service for the application to failover properly when a MySQL cluster node fails.

Bonus – Troubleshooting

If MySQL could not connect to 33061 used for communications in the group replication because of firewall (see STEP 3.2) or SELinux misconfiguration (see STEP 3.3).

2022-02-17T09:23:28.266341Z 27 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL '__mysql_innodb_cluster_creating_cluster__' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
2022-02-17T09:23:28.301711Z 27 [System] [MY-013587] [Repl] Plugin group_replication reported: 'Plugin 'group_replication' is starting.'
2022-02-17T09:23:28.305522Z 31 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_applier' executed'. Previous state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.
2022-02-17T09:23:28.451550Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error testing to the local group communication engine instance.'
2022-02-17T09:23:28.520774Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33061'
2022-02-17T09:23:33.697625Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error testing to the local group communication engine instance.'
2022-02-17T09:23:33.775694Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33061'
2022-02-17T09:23:38.885940Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error testing to the local group communication engine instance.'
2022-02-17T09:23:38.971057Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33061'
2022-02-17T09:23:44.140848Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error testing to the local group communication engine instance.'
2022-02-17T09:23:44.211101Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33061'
2022-02-17T09:23:49.318153Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error testing to the local group communication engine instance.'
2022-02-17T09:23:49.390162Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33061'
2022-02-17T09:23:54.563024Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error testing to the local group communication engine instance.'
2022-02-17T09:23:54.632728Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33061'
2022-02-17T09:23:59.744722Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error testing to the local group communication engine instance.'
2022-02-17T09:23:59.815798Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33061'
2022-02-17T09:24:04.906002Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error testing to the local group communication engine instance.'
2022-02-17T09:24:04.997848Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33061'
2022-02-17T09:24:10.060337Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error testing to the local group communication engine instance.'
2022-02-17T09:24:10.128839Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33061'
2022-02-17T09:24:15.256115Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error testing to the local group communication engine instance.'
2022-02-17T09:24:15.321630Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33061'
2022-02-17T09:24:28.351894Z 27 [ERROR] [MY-011640] [Repl] Plugin group_replication reported: 'Timeout on wait for view after joining group'
2022-02-17T09:24:28.351997Z 27 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The member is leaving a group without being on one.'

It is important to tune the default MySQL generic variables because some of the cluster procedures need big buffers like max_allowed_packet. Bigger value is needed in the cloning process.

NOTE: db-cluster-2:3306 is being cloned from db-cluster-1:3306
ERROR: The clone process has failed: Clone Donor Error: 3957 : Clone needs max_allowed_packet value to be 2097152 or more. Current value is 1048576. (3862)
Cluster.addInstance: Clone Donor Error: 3957 : Clone needs max_allowed_packet value to be 2097152 or more. Current value is 1048576. (RuntimeError)

The process of adding an instance is interrupted, which leads to the following error:

[root@db-cluster-1 ~]# mysqlsh
MySQL Shell 8.0.28

Copyright (c) 2016, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
 MySQL  JS > \connect clusteradmin@db-cluster-1
Creating a session to 'clusteradmin@db-cluster-1'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 63 (X protocol)
Server version: 8.0.28 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  db-cluster-1:33060+ ssl  JS > var cluster = dba.getCluster()
 MySQL  db-cluster-1:33060+ ssl  JS > cluster.status()
{
    "clusterName": "mycluster1", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "db-cluster-1:3306", 
        "ssl": "REQUIRED", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures.", 
        "topology": {
            "db-cluster-1:3306": {
                "address": "db-cluster-1:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.28"
            }, 
            "db-cluster-2:3306": {
                "address": "db-cluster-2:3306", 
                "instanceErrors": [
                    "ERROR: GR Recovery channel receiver stopped with an error: error connecting to master 'mysql_innodb_cluster_364432059@db-cluster-1:3306' - retry-time: 60 retries: 1 message: Access denied for user 'mysql_innodb_cluster_364432059'@'192.168.0.1' (using password: YES) (1045) at 2022-02-18 10:44:06.692599", 
                    "NOTE: instance server_id is not registered in the metadata. Use cluster.rescan() to update the metadata."
                ], 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "recovery": {
                    "receiverError": "error connecting to master 'mysql_innodb_cluster_364432059@db-cluster-1:3306' - retry-time: 60 retries: 1 message: Access denied for user 'mysql_innodb_cluster_364432059'@'192.168.0.1' (using password: YES)", 
                    "receiverErrorNumber": 1045, 
                    "state": "CONNECTION_ERROR"
                }, 
                "recoveryStatusText": "Distributed recovery in progress", 
                "role": "HA", 
                "status": "RECOVERING", 
                "version": "8.0.28"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "db-cluster-1:3306"
}
 MySQL  db-cluster-1:33060+ ssl  JS > cluster.removeInstance("clusteradmin@db-cluster-2")
ERROR: The instance db-cluster-2:3306 does not belong to the cluster.
Cluster.removeInstance: Metadata for instance db-cluster-2:3306 not found (MYSQLSH 51104)

Removing the instance reports it does not belong to the cluster, the cloning process could not proceed with the cloning because of a missing user, and so on.

2 thoughts on “Install and deploy MySQL 8 InnoDB Cluster with 3 nodes under CentOS 8 and MySQL Router for HA”

  1. Thank you very much for the comprehensive guide. From my understanding, this is multi-master 3 node setup where the delta data keeps replicating to other two nodes. Another piece of information I was looking for is how do I point an app to this setup for a backend in high available mode. I have a scenario where I can place these servers on a load balancer pool and use a virtual IP on top. If I do that, would Active/Active configuration work? Right now we have two node multi master setup load balanced on F5 and that is active/passive and we are fed up with that because we always have to keep 01 as primary and 02 as slave for the reasons only my DB knows. The cut over to 02 is always manual and the business doesn’t like that dependency as one of the node goes down switching involves everyone jumping on a call. In other words can I take your setup and implement in a fire and forget way on HA side?

    1. What we have done in our organization is to have a local MySQL router on each application server and the application software connects with 127.0.0.1 to the ports 6446 and 6447. So here in the article the MySQL Routers are installed in every MySQL InnoDB Cluster node, but in fact the MySQL Router may be installed everywhere and no limit how many MySQL Routers you may have (I think it is mentioned somewhere in the MySQL Router documentations). So if you install on each node a MySQL Router and a virtual IP on top of it, the Active/Active configuration should work flawlessly. Our setup is slightly different, but it has been working for a whole year and it survived several failure of nodes switching properly every time without any manual intervention.

Leave a Reply to Arun Ghanta Cancel reply

Your email address will not be published. Required fields are marked *