mysql high availability | SERHAT CELIK DATABASE BLOG

I have tried to document step by step How to Setup MySQL High Availability InnoDB Cluster with MySQL Router & KeepAliveD. There are total 27 low level steps. I hope it will be useful for those who need it!

High Level Installation and Configuration Steps

1-Check Environment Details
2-Update /etc/hosts Files on All Servers
3-Disable Firewall & Selinux on All Servers
4-Install MySQL Server on MySQL-01 & MySQL-02 & MySQL-03
5-Install MySQL Shell (mysqlsh) on MySQL-01 & MySQL-02 & MySQL-03
6-Install MySQL Router on MySQL-Router01 & MySQL-Router02
7-Set MySQL Shell Password on MySQL-01 & MySQL-02 & MySQL-03
8-Configure MySQL-01 Instance
9-Configure MySQL-02 Instance
10-Configure MySQL-03 Instance
11-Create InnoDB Cluster on MySQL-01 Instance
12-Check InnoDB Cluster Status on MySQL-01 Instance
13-Add MySQL-02 Instances to the Cluster on MySQL-01 Instance
14-Add MySQL-03 Instances to the Cluster on MySQL-01 Instance
15-Check InnoDB Cluster Status on MySQL-01 Instance
16-Setup Router Account on MySQL-01 Instance
17-Configure MySQL Router on MySQL-Router01 & MySQL-Router02
18-Create a Table and Insert Some Data for Test Purpose on MySQL-01 Instance
19-Check Newly Created Table on MySQL-02 & MySQL-03 Instance
20-Ensure MySQL Router Works as Expected
21-Stop MySQL Service on MySQL-01 and Ensure Primary Node is Changed as Expected
22-Install KeepAliveD on MySQL-Router01 & MySQL-Router02
23-Configure KeepAliveD on MySQL-Router01 & MySQL-Router02
24-Enable/Start KeepAliveD Service on MySQL-Router01 & MySQL-Router02
25-Shutdown MySQL Router01 and Ensure DB Connections Work as Expected Using KeepAliveD Virtual IP Address and Ports (RW & RO)
26-Shutdown MySQL Router02 and Ensure DB Connections Work as Expected Using KeepAliveD Virtual IP Address and Ports (RW & RO)
27-KeepAliveD Test Script Using Virtual IP Address and Ports (RW & RO)

Low Level Installation and Configuration Steps

1-Check Environment Details

2-Update /etc/hosts Files on All Servers

vi /etc/hosts

192.168.1.151 rocky95-mysql01
192.168.1.152 rocky95-mysql02
192.168.1.153 rocky95-mysql03
192.168.1.154 rocky95-mysqlrouter01
192.168.1.155 rocky95-mysqlrouter02

3-Disable Firewall & Selinux on All Servers

# Disable & Check Firewall
sudo systemctl status firewalld
sudo systemctl stop firewalld
sudo systemctl disable firewalld
sudo systemctl status firewalld

# Disable & Check Selinux
vi /etc/selinux/config
SELINUX=disabled
reboot
sestatus

4-Install MySQL Server on MySQL-01 & MySQL-02 & MySQL-03

How to Install MySQL for Redhat-Based Linux (Rocky Linux)?

5-Install MySQL Shell (mysqlsh) on MySQL-01 & MySQL-02 & MySQL-03

MySQL Shell is an advanced command-line client and code editor for MySQL. In addition to SQL, MySQL Shell also offers scripting capabilities for JavaScript and Python.

# Installation
wget https://repo.mysql.com/mysql80-community-release-el9-5.noarch.rpm
rpm -ivh mysql80-community-release-el9-5.noarch.rpm
yum install mysql-shell -y

# Check
# mysqlsh --version
mysqlsh   Ver 8.0.40 for Linux on x86_64 - for MySQL 8.0.40 (MySQL Community Server (GPL))

6-Install MySQL Router on MySQL-Router01 & MySQL-Router02

# Installation
wget https://repo.mysql.com/mysql80-community-release-el9-5.noarch.rpm
rpm -ivh mysql80-community-release-el9-5.noarch.rpm
yum install mysql-router -y

# Check
# mysqlrouter --version
MySQL Router  Ver 8.0.40 for Linux on x86_64 (MySQL Community - GPL)

7-Set MySQL Shell Password on MySQL-01 & MySQL-02 & MySQL-03

# mysqlsh --uri root@localhost
Please provide the password for 'root@localhost': *********
Save password for 'root@localhost'? [Y]es/[N]o/Ne[v]er (default No): Y
MySQL Shell 8.0.40

Copyright (c) 2016, 2024, 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.
Creating a session to 'root@localhost'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 8 (X protocol)
Server version: 8.0.36 Source distribution
No default schema selected; type \use <schema> to set one.
 MySQL  localhost:33060+ ssl  JS >

8-Configure MySQL-01 Instance

Run below commands on MySQL-01 instance.

mysqlsh --uri root@localhost
dba.configureInstance()
Select 2
innodbcluster
InnoDBcluster_2025!
y
y
dba.checkInstanceConfiguration('innodbcluster@rocky95-mysql01')
InnoDBcluster_2025!
y

Sample Output

[root@rocky95-mysql01 ~]# mysqlsh --uri root@localhost
MySQL Shell 8.0.40

Copyright (c) 2016, 2024, 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.
Creating a session to 'root@localhost'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 11 (X protocol)
Server version: 8.0.36 Source distribution
No default schema selected; type \use <schema> to set one.
 MySQL  localhost:33060+ ssl  JS > dba.configureInstance()
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as rocky95-mysql01: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: innodbcluster
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

Creating user innodbcluster@%.
Account innodbcluster@% was successfully created.

Configuring instance...

WARNING: '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release. (Code 1287).
The instance 'rocky95-mysql01:3306' was configured to be used in an InnoDB cluster.
Restarting MySQL...
NOTE: MySQL server at rocky95-mysql01:3306 was restarted.
 MySQL  localhost:33060+ ssl  JS > dba.checkInstanceConfiguration('innodbcluster@rocky95-mysql01')
Please provide the password for 'innodbcluster@rocky95-mysql01': *******************
Save password for 'innodbcluster@rocky95-mysql01'? [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 rocky95-mysql01: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 'rocky95-mysql01:3306' is valid to be used in an InnoDB cluster.

{
    "status": "ok"
}
 MySQL  localhost:33060+ ssl  JS >

9-Configure MySQL-02 Instance

Run below commands on MySQL-02 instance.

mysqlsh --uri root@localhost
dba.configureInstance()
Select 2
innodbcluster
InnoDBcluster_2025!
y
y
dba.checkInstanceConfiguration('innodbcluster@rocky95-mysql02')
InnoDBcluster_2025!
y

Sample Output

[root@rocky95-mysql02 ~]# mysqlsh --uri root@localhost
MySQL Shell 8.0.40

Copyright (c) 2016, 2024, 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.
Creating a session to 'root@localhost'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 9 (X protocol)
Server version: 8.0.36 Source distribution
No default schema selected; type \use <schema> to set one.
 MySQL  localhost:33060+ ssl  JS > dba.configureInstance()
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as rocky95-mysql02: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: innodbcluster
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

Creating user innodbcluster@%.
Account innodbcluster@% was successfully created.

Configuring instance...

WARNING: '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release. (Code 1287).
The instance 'rocky95-mysql02:3306' was configured to be used in an InnoDB cluster.
Restarting MySQL...
NOTE: MySQL server at rocky95-mysql02:3306 was restarted.
 MySQL  localhost:33060+ ssl  JS > dba.checkInstanceConfiguration('innodbcluster@rocky95-mysql02')
Please provide the password for 'innodbcluster@rocky95-mysql02': *******************
Save password for 'innodbcluster@rocky95-mysql02'? [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 rocky95-mysql02: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 'rocky95-mysql02:3306' is valid to be used in an InnoDB cluster.

{
    "status": "ok"
}
 MySQL  localhost:33060+ ssl  JS >

10-Configure MySQL-03 Instance

Run below commands on MySQL-03 instance.

mysqlsh --uri root@localhost
dba.configureInstance()
Select 2
innodbcluster
InnoDBcluster_2025!
y
y
dba.checkInstanceConfiguration('innodbcluster@rocky95-mysql03')
InnoDBcluster_2025!
y

Sample Output

[root@rocky95-mysql03 ~]# mysqlsh --uri root@localhost
MySQL Shell 8.0.40

Copyright (c) 2016, 2024, 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.
Creating a session to 'root@localhost'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 9 (X protocol)
Server version: 8.0.36 Source distribution
No default schema selected; type \use <schema> to set one.
 MySQL  localhost:33060+ ssl  JS > dba.configureInstance()
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as rocky95-mysql03: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: innodbcluster
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

Creating user innodbcluster@%.
Account innodbcluster@% was successfully created.

Configuring instance...

WARNING: '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release. (Code 1287).
The instance 'rocky95-mysql03:3306' was configured to be used in an InnoDB cluster.
Restarting MySQL...
NOTE: MySQL server at rocky95-mysql03:3306 was restarted.
 MySQL  localhost:33060+ ssl  JS > dba.checkInstanceConfiguration('innodbcluster@rocky95-mysql03')
Please provide the password for 'innodbcluster@rocky95-mysql03': *******************
Save password for 'innodbcluster@rocky95-mysql03'? [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 rocky95-mysql03: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 'rocky95-mysql03:3306' is valid to be used in an InnoDB cluster.

{
    "status": "ok"
}
 MySQL  localhost:33060+ ssl  JS >

11-Create InnoDB Cluster on MySQL-01 Instance

At least 3 instances are needed for the cluster to be able to withstand up to one server failure.

Run below commands on MySQL-01 instance.

mysqlsh --uri root@localhost
\c innodbcluster@rocky95-mysql01:3306
InnoDBcluster_2025!
Y
var v_cluster= dba.createCluster('INNODB_CLS')

Sample Output

[root@rocky95-mysql01 ~]# mysqlsh --uri root@localhost
MySQL Shell 8.0.40

Copyright (c) 2016, 2024, 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.
Creating a session to 'root@localhost'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 10 (X protocol)
Server version: 8.0.36 Source distribution
No default schema selected; type \use <schema> to set one.
 MySQL  localhost:33060+ ssl  JS > \c innodbcluster@rocky95-mysql01:3306
Creating a session to 'innodbcluster@rocky95-mysql01:3306'
Please provide the password for 'innodbcluster@rocky95-mysql01:3306': *******************
Save password for 'innodbcluster@rocky95-mysql01:3306'? [Y]es/[N]o/Ne[v]er (default No): Y
Fetching schema names for auto-completion... Press ^C to stop.
Closing old connection...
Your MySQL connection id is 12
Server version: 8.0.36 Source distribution
No default schema selected; type \use <schema> to set one.
 MySQL  rocky95-mysql01:3306 ssl  JS > var v_cluster= dba.createCluster('INNODB_CLS')
A new InnoDB Cluster will be created on instance 'rocky95-mysql01:3306'.

Validating instance configuration at rocky95-mysql01:3306...

This instance reports its own address as rocky95-mysql01:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'rocky95-mysql01:3306'. Use the localAddress option to override.

* Checking connectivity and SSL configuration...

Creating InnoDB Cluster 'INNODB_CLS' on 'rocky95-mysql01: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.

12-Check InnoDB Cluster Status on MySQL-01 Instance

“statusText”: “Cluster is NOT tolerant to any failures.”,

 MySQL  rocky95-mysql01:3306 ssl  JS > v_cluster.status()
{
    "clusterName": "INNODB_CLS", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "rocky95-mysql01:3306", 
        "ssl": "REQUIRED", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures.", 
        "topology": {
            "rocky95-mysql01:3306": {
                "address": "rocky95-mysql01:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.36"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "rocky95-mysql01:3306"
}
 MySQL  rocky95-mysql01:3306 ssl  JS > v_cluster.describe()
{
    "clusterName": "INNODB_CLS", 
    "defaultReplicaSet": {
        "name": "default", 
        "topology": [
            {
                "address": "rocky95-mysql01:3306", 
                "label": "rocky95-mysql01:3306", 
                "role": "HA"
            }
        ], 
        "topologyMode": "Single-Primary"
    }
}

13-Add MySQL-02 Instances to the Cluster on MySQL-01 Instance

v_cluster.addInstance('rocky95-mysql02:3306')

Error

MySQL  rocky95-mysql01:3306 ssl  JS > v_cluster.addInstance('rocky95-mysql02:3306')

NOTE: The target instance 'rocky95-mysql02: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 'rocky95-mysql02: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): 
Validating instance configuration at rocky95-mysql02:3306...

This instance reports its own address as rocky95-mysql02:3306

Instance configuration is suitable.
ERROR: RuntimeError: Cannot add an instance with the same server UUID (e5622470-ccd9-11ef-872a-0800278f8491) of an active member of the cluster 'rocky95-mysql01:3306'. Please change the server UUID of the instance to add, all members must have a unique server UUID.
Cluster.addInstance: Cannot add an instance with the same server UUID (e5622470-ccd9-11ef-872a-0800278f8491) of an active member of the cluster 'rocky95-mysql01:3306'. Please change the server UUID of the instance to add, all members must have a unique server UUID. (RuntimeError)
 MySQL  rocky95-mysql01:3306 ssl  JS >

Solution

# Run below commands on MySQL-02 & MySQL-03 instances. 
# I am getting this error because I created MySQL-02 and MySQL-03 server by cloning MySQL-01 server :)

$ sudo systemctl stop mysqld
$ mv /var/lib/mysql/auto.cnf /var/lib/mysql/auto.cnf_backup
$ sudo systemctl start mysqld

Successfully Completed

 MySQL  rocky95-mysql01:3306 ssl  JS > v_cluster.addInstance('rocky95-mysql02:3306')

NOTE: The target instance 'rocky95-mysql02: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 'rocky95-mysql02: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): 
Validating instance configuration at rocky95-mysql02:3306...

This instance reports its own address as rocky95-mysql02:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'rocky95-mysql02:3306'. Use the localAddress option to override.

* Checking connectivity and SSL configuration...
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: rocky95-mysql02:3306 is being cloned from rocky95-mysql01:3306
** Stage DROP DATA: Completed
** Clone Transfer  
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ############################################################  100%  Completed
    REDO COPY  ############################################################  100%  Completed

NOTE: rocky95-mysql02:3306 is shutting down...

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

State recovery already finished for 'rocky95-mysql02:3306'

The instance 'rocky95-mysql02:3306' was successfully added to the cluster.

 MySQL  rocky95-mysql01:3306 ssl  JS >

14-Add MySQL-03 Instances to the Cluster on MySQL-01 Instance

v_cluster.addInstance('rocky95-mysql03:3306')

Sample Output

 MySQL  rocky95-mysql01:3306 ssl  JS > v_cluster.addInstance('rocky95-mysql03:3306')

NOTE: The target instance 'rocky95-mysql03: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 'rocky95-mysql03: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): 
Validating instance configuration at rocky95-mysql03:3306...

This instance reports its own address as rocky95-mysql03:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'rocky95-mysql03:3306'. Use the localAddress option to override.

* Checking connectivity and SSL configuration...
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: rocky95-mysql03:3306 is being cloned from rocky95-mysql01:3306
** Stage DROP DATA: Completed
** Clone Transfer  
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ############################################################  100%  Completed
    REDO COPY  ############################################################  100%  Completed

NOTE: rocky95-mysql03:3306 is shutting down...

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

State recovery already finished for 'rocky95-mysql03:3306'

The instance 'rocky95-mysql03:3306' was successfully added to the cluster.

 MySQL  rocky95-mysql01:3306 ssl  JS >

15-Check InnoDB Cluster Status on MySQL-01 Instance

“statusText”: “Cluster is ONLINE and can tolerate up to ONE failure.”,

 MySQL  rocky95-mysql01:3306 ssl  JS > v_cluster.status()
{
    "clusterName": "INNODB_CLS", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "rocky95-mysql01:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "rocky95-mysql01:3306": {
                "address": "rocky95-mysql01:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.36"
            }, 
            "rocky95-mysql02:3306": {
                "address": "rocky95-mysql02:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.36"
            }, 
            "rocky95-mysql03:3306": {
                "address": "rocky95-mysql03:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.36"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "rocky95-mysql01:3306"
}
 MySQL  rocky95-mysql01:3306 ssl  JS >

16-Setup Router Account on MySQL-01 Instance

v_cluster.setupRouterAccount('mysqlrouter')
InnoDBcluster_2025! ---> Password

Sample Output

 MySQL  rocky95-mysql01:3306 ssl  JS > v_cluster.setupRouterAccount('mysqlrouter')

Missing the password for new account mysqlrouter@%. Please provide one.
Password for new account: *******************
Confirm password: *******************


Creating user mysqlrouter@%.
Account mysqlrouter@% was successfully created.

 MySQL  rocky95-mysql01:3306 ssl  JS >

17-Configure MySQL Router on MySQL-Router01 & MySQL-Router02

# Use Below Command for Default Data and Log Paths
mysqlrouter --bootstrap innodbcluster@rocky95-mysql01 --user mysqlrouter
InnoDBcluster_2025! ---> Password

Conf File Path=/etc/mysqlrouter/mysqlrouter.conf
Log File Path=/var/log/mysqlrouter/mysqlrouter.log

# Use Below Command for Custom Data and Log Paths
mkdir -p /data/mysqlrouter
mysqlrouter --bootstrap innodbcluster@rocky95-mysql01 -d /data/mysqlrouter --user mysqlrouter
InnoDBcluster_2025! ---> Password

Conf File Path=/data/mysqlrouter/mysqlrouter.conf
Log File Path=/data/mysqlrouter/log/mysqlrouter.log

# Enable and Start Service
systemctl enable mysqlrouter
systemctl start mysqlrouter
systemctl status mysqlrouter

Sample Output

[root@rocky95-mysqlrouter01 ~]# mysqlrouter --bootstrap innodbcluster@rocky95-mysql01 --user mysqlrouter
Please enter MySQL password for innodbcluster: 
# Bootstrapping system MySQL Router 8.0.40 (MySQL Community - GPL) instance...

- 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 'INNODB_CLS'

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 'INNODB_CLS' can be reached by connecting to:

## MySQL Classic protocol

- Read/Write Connections: localhost:6446
- Read/Only Connections:  localhost:6447

## MySQL X protocol

- Read/Write Connections: localhost:6448
- Read/Only Connections:  localhost:6449

[root@rocky95-mysqlrouter01 ~]# systemctl enable mysqlrouter
Created symlink /etc/systemd/system/multi-user.target.wants/mysqlrouter.service → /usr/lib/systemd/system/mysqlrouter.service.

[root@rocky95-mysqlrouter01 ~]# systemctl start mysqlrouter
[root@rocky95-mysqlrouter01 ~]# 
[root@rocky95-mysqlrouter01 ~]# 
[root@rocky95-mysqlrouter01 ~]# systemctl status mysqlrouter
● mysqlrouter.service - MySQL Router
     Loaded: loaded (/usr/lib/systemd/system/mysqlrouter.service; disabled; preset: disabled)
     Active: active (running) since Sat 2025-01-11 17:45:45 +03; 2s ago
   Main PID: 2940 (mysqlrouter)
     Status: "running"
      Tasks: 25 (limit: 10961)
     Memory: 16.8M
        CPU: 109ms
     CGroup: /system.slice/mysqlrouter.service
             └─2940 /usr/bin/mysqlrouter

18-Create a Table and Insert Some Data for Test Purpose on MySQL-01 Instance

mysqlsh --uri root@localhost

\sql

create database testdb;

use testdb;

CREATE TABLE replication_test_table (id INT AUTO_INCREMENT PRIMARY KEY, replication_test varchar(50));

INSERT INTO replication_test_table (replication_test) VALUES
('Test01 was inserted by Master-192.168.1.151'),('Test02 was inserted by Master-192.168.1.151'),
('Test03 was inserted by Master-192.168.1.151'),('Test04 was inserted by Master-192.168.1.151'),
('Test05 was inserted by Master-192.168.1.151'),('Test06 was inserted by Master-192.168.1.151');

SELECT * FROM replication_test_table;

select @@hostname;

19-Check Newly Created Table on MySQL-02 & MySQL-03 Instance

mysqlsh --uri root@localhost

\sql

use testdb;

SELECT * FROM replication_test_table;

select @@hostname;

MySQL-02

MySQL-03

20-Ensure MySQL Router Works as Expected

## MySQL Classic protocol
- Read/Write Connections: localhost:6446
- Read/Only Connections:  localhost:6447

Run below command and ensure that all requests go to the MySQL-01 instance as expected. Because we have just one node for Read/Write purpose. The port number is 6446.

mysql -h rocky95-mysqlrouter01 -P 6446 -uinnodbcluster -pInnoDBcluster_2025! -e "select @@hostname;"

Run below command and ensure that all requests go to the MySQL-02 and MySQL-03 instances as expected. Because we have two nodes for Read purpose. The port number is 6447.

mysql -h rocky95-mysqlrouter01 -P 6447 -uinnodbcluster -pInnoDBcluster_2025! -e "select @@hostname;"

21-Stop MySQL Service on MySQL-01 and Ensure Primary Node is Changed as Expected

Check Current Primary Node on MySQL-01

mysqlsh --uri root@localhost
\c innodbcluster@rocky95-mysql01:3306
var v_cluster= dba.getCluster('INNODB_CLS')
v_cluster.status()

Stop MySQL Service on MySQL-01

[root@rocky95-mysql01 ~]# sudo systemctl stop mysqld
[root@rocky95-mysql01 ~]# 
[root@rocky95-mysql01 ~]# sudo systemctl status mysqld

Check Current Primary Node Again on MySQL-02

As you see from SS, MySQL-03 has been promoted as new PRIMARY 🙂

mysqlsh --uri root@localhost
\c innodbcluster@rocky95-mysql02:3306
InnoDBcluster_2025! --> Password
var v_cluster= dba.getCluster('INNODB_CLS')
v_cluster.status()

Start MySQL Service on MySQL-01

[root@rocky95-mysql01 ~]# sudo systemctl start mysqld
[root@rocky95-mysql01 ~]# 
[root@rocky95-mysql01 ~]# sudo systemctl status mysqld

Check Cluster Status Again on MySQL-01

mysqlsh --uri root@localhost
\c innodbcluster@rocky95-mysql01:3306
var v_cluster= dba.getCluster('INNODB_CLS')
v_cluster.status()

Promote MySQL-01 as PRIMARY

v_cluster.setPrimaryInstance(‘rocky95-mysql01:3306’)
v_cluster.status()

22-Install KeepAliveD on MySQL-Router01 & MySQL-Router02

sudo yum -y install keepalived
mv /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.backup

23-Configure KeepAliveD on MySQL-Router01 & MySQL-Router02

Important Points
- state is important.
- Use your own public interface name. Check it via ifconfig command.
- virtual_router_id should be same.
- priority is important.
- Use your own virtual_ipaddress.

MySQL-Router01

vi /etc/keepalived/keepalived.conf

vrrp_script chk_mysqlrouter {
    script "/bin/killall -0 /usr/bin/mysqlrouter"  # Check if mysqlrouter is running
    interval 2          # Check every 2 seconds
    weight 2            # Increase priority by 2 if script succeeds
    fall 2              # Consider the service down after 2 consecutive failures
    rise 2              # Consider the service back up after 2 consecutive successes
}

vrrp_instance VI_1 {
    state MASTER
    interface enp0s3
    virtual_router_id 50
    priority 102
    advert_int 1
    virtual_ipaddress {
        192.168.1.100
    }

    track_script {
        chk_mysqlrouter
    }
}

MySQL-Router02

vi /etc/keepalived/keepalived.conf

vrrp_script chk_mysqlrouter {
    script "/bin/killall -0 /usr/bin/mysqlrouter"  # Check if mysqlrouter is running
    interval 2          # Check every 2 seconds
    weight 2            # Increase priority by 2 if script succeeds
    fall 2              # Consider the service down after 2 consecutive failures
    rise 2              # Consider the service back up after 2 consecutive successes
}

vrrp_instance VI_2 {
    state BACKUP
    interface enp0s3
    virtual_router_id 50
    priority 101
    advert_int 1
    virtual_ipaddress {
        192.168.1.100
    }

    track_script {
        chk_mysqlrouter
    }
}

24-Enable/Start KeepAliveD Service on MySQL-Router01 & MySQL-Router02

systemctl enable keepalived
systemctl start keepalived
systemctl status keepalived
ip addr show

25-Shutdown MySQL Router01 and Ensure DB Connections Work as Expected Using KeepAliveD Virtual IP Address and Ports (RW & RO)

## MySQL Classic protocol
- Read/Write Connections: localhost:6446
- Read/Only Connections:  localhost:6447

-- Read/Write Connection Test
mysql -h 192.168.1.100 -P 6446 -uinnodbcluster -pInnoDBcluster_2025! -e "select @@hostname;"

-- Read/Only Connection Test
mysql -h 192.168.1.100 -P 6447 -uinnodbcluster -pInnoDBcluster_2025! -e "select @@hostname;"

26-Shutdown MySQL Router02 and Ensure DB Connections Work as Expected Using KeepAliveD Virtual IP Address and Ports (RW & RO)

## MySQL Classic protocol
- Read/Write Connections: localhost:6446
- Read/Only Connections:  localhost:6447

-- Read/Write Connection Test
mysql -h 192.168.1.100 -P 6446 -uinnodbcluster -pInnoDBcluster_2025! -e "select @@hostname;"

-- Read/Only Connection Test
mysql -h 192.168.1.100 -P 6447 -uinnodbcluster -pInnoDBcluster_2025! -e "select @@hostname;"

27-KeepAliveD Test Script Using Virtual IP Address and Ports (RW & RO)

# RW Test
while [ 1 ]
do
sleep 1
mysql -h 192.168.1.100 -P 6446 -uinnodbcluster -pInnoDBcluster_2025! -e "select @@hostname;"
done

# RO Test
while [ 1 ]
do
sleep 1
mysql -h 192.168.1.100 -P 6447 -uinnodbcluster -pInnoDBcluster_2025! -e "select @@hostname;"
done

Some Useful Commands

Stopping an InnoDB Cluster

##### 1. Check the Cluster Status #####
-- Before stopping the cluster, check its current status to ensure it is healthy and operational

mysqlsh --uri innodbcluster@rocky95-mysql01:3306 -- cluster status
InnoDBcluster_2025! --> Password

##### 2. Disconnect Applications #####
Disconnect applications or services using the cluster to avoid writing data during the shutdown process

##### 3. Stop MySQL Instances (One at a Time) #####
-- Shut down the MySQL instances participating in the cluster in reverse order of their roles
-- First, stop secondary members
-- Finally, stop the primary member
-- Always shut down secondary members first to ensure the primary member remains active until the end.
-- Use the following command on each node

sudo systemctl stop mysqld
sudo systemctl status mysqld

##### 4. Confirm Shutdown #####

ps aux | grep mysqld

Starting an InnoDB Cluster

##### Start MySQL Instances (One at a Time) #####
-- Start the MySQL instances in the cluster one by one. The recommended order is:
-- Start the primary member first.
-- Then start the secondary members.
-- Use the following command on each node

sudo systemctl status mysqld
sudo systemctl start mysqld

##### Rejoin Instances to the Cluster #####
-- If the instances do not automatically rejoin the cluster, you can manually add them back
-- This ensures that the cluster reconfigures itself and resumes normal operation after a complete shutdown

mysqlsh --uri innodbcluster@rocky95-mysql01:3306
dba.rebootClusterFromCompleteOutage()
\sql
SELECT * FROM performance_schema.replication_group_members;

##### Verify Cluster Status #####
-- Check the cluster status to confirm all nodes are healthy and synchronized:

mysqlsh --uri innodbcluster@rocky95-mysql01:3306 -- cluster status
InnoDBcluster_2025! --> Password

##### Reconnect Applications ##### 
-- Once the cluster is fully operational, reconnect the applications and resume normal operations.

Find Cluster Name

mysqlsh --uri root@localhost
var v_cluster= dba.getCluster()
v_cluster.getName()

Directly Define Variable and Use

mysqlsh --uri innodbcluster@rocky95-mysql01:3306 --cluster
InnoDBcluster_2025! --> Password
cluster.status()

Check Cluster Status

mysqlsh --uri root@localhost
\c innodbcluster@rocky95-mysql01:3306
var v_cluster= dba.getCluster('INNODB_CLS')
v_cluster.status()

Check Cluster Status with Extended Info

mysqlsh --uri innodbcluster@rocky95-mysql01:3306 --cluster
InnoDBcluster_2025! --> Password
cluster.status({'extended':0})
cluster.status({'extended':1})
cluster.status({'extended':2})
cluster.status({'extended':3})

Check Directly Cluster Status

mysqlsh --uri innodbcluster@rocky95-mysql01:3306 -- cluster status
InnoDBcluster_2025! --> Password

Check Cluster Topology

mysqlsh --uri innodbcluster@rocky95-mysql01:3306 --cluster
InnoDBcluster_2025! --> Password
cluster.describe()

Rescan Cluster

mysqlsh --uri innodbcluster@rocky95-mysql01:3306 --cluster
InnoDBcluster_2025! --> Password
cluster.rescan()

Check Specific Object Value

mysqlsh --uri innodbcluster@rocky95-mysql01:3306 --cluster
cluster.status().defaultReplicaSet.topology["rocky95-mysql01:3306"].role
cluster.status().defaultReplicaSet.topology["rocky95-mysql01:3306"].status
cluster.status().defaultReplicaSet.topologyMode

Connect Directly to Primary or Secondary

mysqlsh --uri innodbcluster@rocky95-mysql02:3306 --cluster --redirect-primary
InnoDBcluster_2025! --> Password

mysqlsh --uri innodbcluster@rocky95-mysql01:3306 --cluster --redirect-secondary
InnoDBcluster_2025! --> Password

Switch to Multi-Primary Mode

mysqlsh --uri innodbcluster@rocky95-mysql01:3306 --cluster
InnoDBcluster_2025! --> Password
cluster.switchToMultiPrimaryMode()
cluster.status()

Switch to Single-Primary Mode

mysqlsh --uri innodbcluster@rocky95-mysql01:3306 --cluster
InnoDBcluster_2025! --> Password
cluster.switchToSinglePrimaryMode()
cluster.status()

Change Primary Node

mysqlsh --uri innodbcluster@rocky95-mysql01:3306 --cluster
InnoDBcluster_2025! --> Password
cluster.setPrimaryInstance('innodbcluster@rocky95-mysql01')
cluster.status()

Check Cluster Options

mysqlsh --uri innodbcluster@rocky95-mysql01:3306 --cluster
InnoDBcluster_2025! --> Password
cluster.options()

Remove/Add Instance from Routing

mysqlsh --uri innodbcluster@rocky95-mysql01:3306 --cluster
InnoDBcluster_2025! --> Password

-- Remove Instance from Routing
cluster.setInstanceOption("innodbcluster@rocky95-mysql02:3306", "tag:_hidden", true);

-- Add Instance to Routing
cluster.setInstanceOption("innodbcluster@rocky95-mysql02:3306", "tag:_hidden", false);

Remove/Add Instance from Cluster

mysqlsh --uri innodbcluster@rocky95-mysql01:3306 --cluster
InnoDBcluster_2025! --> Password

-- Remove Instance from Cluster
cluster.removeInstance("innodbcluster@rocky95-mysql02:3306", {force: true})

-- Add Instance to Cluster
cluster.addInstance("innodbcluster@rocky95-mysql02:3306")

-- Check Cluster Status
cluster.status()