How to Setup MySql InnoDB Cluster

MySQL InnoDB Cluster provides a complete high availability solution for MySQL. By using the AdminAPI included with MySql Shell, you can easily configure and administer a group of at least three MySQL server instances to function as an InnoDB Cluster. Each MySQL server instance runs MySQL Group Replication, which provides the mechanism to replicate data within InnoDB Clusters, with built-in failover. AdminAPI removes the need to work directly with Group Replication in InnoDB Clusters. In the event of an unexpected failure of a server instance the cluster reconfigures automatically. In the default single-primary mode, an InnoDB Cluster has a single read-write server instance – the primary. Multiple secondary server instances are replicas of the primary. If the primary fails, a secondary is automatically promoted to the role of primary.

You can also configure MySQL Router can automatically configure itself based on the cluster you deploy, connecting client applications transparently to the server instances. In the event of an unexpected failure, MySQL Router detects this and forwards client applications to the new primary.

For this demonstration, we have already configured 3 MySql Instances. Below are the details.

Hostname	IP	Role	Server_ID	Port	Installation Link
test-machine01	192.168.114.177	Member-1 (Master)	1	3306	Click Here
test-machine01	192.168.114.177	Member-2 (Replica)	3	3307	Click Here
test-machine02	192.168.114.176	Member-3 (Replica)	2	3306	Click Here

Member-1 Configurations

mysql>
mysql> prompt Member-1 >
PROMPT set to 'Member-1 >'
Member-1 >
Member-1 >select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+
1 row in set (0.07 sec)
 
Member-1 >select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 13ecba9c-444a-11eb-a397-000c29f9d9e6 |
+--------------------------------------+
1 row in set (0.00 sec)
 
Member-1 >select @@port;
+--------+
| @@port |
+--------+
|   3306 |
+--------+
1 row in set (0.07 sec)
 
Member-1 >
 
[mysqld@inst1]
#General
user = mysql
port = 3306
server_id = 1
socket=/var/lib/mysql/mysql.sock
pid_file=/var/run/mysqld/mysqld.pid
 
# Data Storage
datadir=/var/lib/mysql
innodb_directories="/u01/mysql/"
 
#Logging
log_bin = /var/lib/mysql/binlog
log_error = /var/lib/mysql/mysqld.log
expire_logs_days = 7
relay_log = /var/lib/mysql/relay_bin01
relay_log_index = /var/lib/mysql/relay_bin.index
relay_log_recovery = on
master_info_repository = TABLE
relay_log_info_repository = TABLE
 
# GTID Based Replication Parameter
gtid-mode=on
enforce-gtid-consistency=1
log_slave_updates= 1

Member-2 Configurations

mysql>
mysql> prompt Member-2>
PROMPT set to 'Member-2>'
Member-2>
Member-2>select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           3 |
+-------------+
1 row in set (0.06 sec)
 
Member-2>select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 98411332-6aad-11eb-809a-000c29f9d9e6 |
+--------------------------------------+
1 row in set (0.00 sec)
 
Member-2>select @@port;
+--------+
| @@port |
+--------+
|   3307 |
+--------+
1 row in set (0.02 sec)
 
Member-2>
 
[mysqld@inst2]
#General
user = mysql
port=3307
server_id = 3
socket=/u01/mysql-2/mysql.sock
pid_file=/u01/mysql-2/mysqld.pid
 
# Data Storage
basedir=/u01/mysql-2
datadir=/u01/mysql-2/data
innodb_directories="/u01/mysql/"
plugin_dir=/usr/lib64/mysql/plugin
 
#Logging
log_bin = /u01/mysql-2/data/binlog
log_error = /u01/mysql-2/data/mysqld.log
expire_logs_days = 7
relay_log = /u01/mysql-2/data/relay_bin01
relay_log_index = /u01/mysql-2/data/relay_bin.index
relay_log_recovery = on
master_info_repository = TABLE
relay_log_info_repository = TABLE
 
# GTID Based Replication Parameter
gtid-mode=on
enforce-gtid-consistency=1
log_slave_updates= 1

Member-3 Configuration

mysql> prompt Memer-3 >
PROMPT set to 'Memer-3 >'
Memer-3 >
Memer-3 >select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)
 
Memer-3 >select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 13c84508-5014-11eb-af41-000c2997dedd |
+--------------------------------------+
1 row in set (0.00 sec)
 
Memer-3 >select @@port;
+--------+
| @@port |
+--------+
|   3306 |
+--------+
1 row in set (0.08 sec)
 
Memer-3 >
 
[mysqld@inst1]
#General
user = mysql
port = 3306
server_id = 2
bind-address = 0.0.0.0
socket=/u01/mysql-8/mysql.sock
pid_file=/u01/mysql-8/mysqld.pid
 
# Data Storage
basedir=/u01/mysql-8
datadir=/u01/mysql-8/data
innodb_directories="/u01/mysql-8;/u01/mysql"
 
#Logging
log_bin = /u01/mysql-8/data/binlog
log_error = /u01/mysql-8/data/mysqld.log
expire_logs_days = 7
relay_log = /u01/mysql-8/data/relay_bin01
relay_log_index = /u01/mysql-8/data/relay_bin.index
relay_log_recovery = on
master_info_repository = TABLE
relay_log_info_repository = TABLE
 
# GTID Based Replication Parameter
gtid-mode=on
enforce-gtid-consistency=1
log_slave_updates=1

Step 1. Check Instance Configuration: Use function checkInstanceConfiguration() to verify all 3 MySql instances are suitable to add in InnoDB Cluster. I will connect using mysqlsh to instance ‘test-machine01:3306’.

Member-1: “test-machine01:3306”

[root@test-machine01 ~]# mysqlsh root@localhost:3306
MySQL Shell 8.0.23

Copyright (c) 2016, 2021, 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:3306'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 9
MySQL localhost:3306 ssl JS >
MySQL localhost:3306 ssl JS >

MySQL localhost:3306 ssl JS >
MySQL localhost:3306 ssl JS > dba.checkInstanceConfiguration('test-machine01:3306');
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as test-machine01: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...
WARNING: The following tables do not have a Primary Key or equivalent column:
repl_test.repl_test1, repl_test.repl_test2, repl_test.repl_test3, repl_test.repl_test4, repl_test.repl_test5, repl_test.repl_test6, repl_test.repl_test7, repl_test1.repl_test, repl_test1.repl_test1, repl_test1.repl_test3, repl_test.test, repl_test.repl_test8, repl_test.repl_test9, repl_test.repl_test10, repl_test.repl_test11, repl_test.repl_test12, repl_test1.repl_test2

Group Replication requires tables to use InnoDB and have a PRIMARY KEY or PRIMARY KEY Equivalent (non-null unique key). Tables that do not follow these requirements will be readable but not updateable when used with Group Replication. If your applications make updates (INSERT, UPDATE or DELETE) to these tables, ensure they use the InnoDB storage engine and have a PRIMARY KEY or PRIMARY KEY Equivalent.

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

The instance 'test-machine01:3306' is valid to be used in an InnoDB cluster.
WARNING: Some non-fatal issues were detected in some of the existing tables.
You may choose to ignore these issues, although replicated updates on these tables will not be possible.

{
"status": "ok"
}
MySQL localhost:3306 ssl JS >

Member-2: “test-machine01:3307”

MySQL localhost:3306 ssl JS >
MySQL localhost:3306 ssl JS > dba.checkInstanceConfiguration('test-machine01:3307');
Please provide the password for 'root@test-machine01:3307': *********
Save password for 'root@test-machine01:3307'? [Y]es/[N]o/Ne[v]er (default No): Y
Validating local MySQL instance listening at port 3307 for use in an InnoDB cluster...

This instance reports its own address as test-machine01:3307
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...
WARNING: The following tables do not have a Primary Key or equivalent column:
repl_test.repl_test1, repl_test.repl_test10, repl_test.repl_test11, repl_test.repl_test12, repl_test.repl_test2, repl_test.repl_test3, repl_test.repl_test4, repl_test.repl_test5, repl_test.repl_test6, repl_test.repl_test7, repl_test.repl_test8, repl_test.repl_test9, repl_test.test, repl_test1.repl_test, repl_test1.repl_test1, repl_test1.repl_test2, repl_test1.repl_test3

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

The instance 'test-machine01:3307' is valid to be used in an InnoDB cluster.
WARNING: Some non-fatal issues were detected in some of the existing tables.
You may choose to ignore these issues, although replicated updates on these tables will not be possible.

{
"status": "ok"
}
MySQL localhost:3306 ssl JS >

Member-3: “test-machine02:3306”

MySQL localhost:3306 ssl JS > dba.checkInstanceConfiguration('test-machine02:3306')
Please provide the password for 'root@test-machine02:3306': *********
Save password for 'root@test-machine02:3306'? [Y]es/[N]o/Ne[v]er (default No): Y
Validating MySQL instance at test-machine02:3306 for use in an InnoDB cluster...

This instance reports its own address as test-machine02: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 instance configuration...
Instance configuration is compatible with InnoDB cluster

The instance 'test-machine02:3306' is valid to be used in an InnoDB cluster.
WARNING: Some non-fatal issues were detected in some of the existing tables.
You may choose to ignore these issues, although replicated updates on these tables will not be possible.

{
"status": "ok"
}
MySQL localhost:3306 ssl JS >

Step 2. Configure Instance: Use function configureInstance() to configure all 3 mysql instances.

Member-1: “test-machine01:3306”

 MySQL  localhost:3306 ssl  JS >
 MySQL  localhost:3306 ssl  JS > dba.configureInstance('test-machine01:3306');
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as test-machine01: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.

The instance 'test-machine01:3306' is valid to be used in an InnoDB cluster.
The instance 'test-machine01:3306' is already ready to be used in an InnoDB cluster.
 MySQL  localhost:3306 ssl  JS >

Member-2: “test-machine01:3307”

 MySQL  localhost:3306 ssl  JS >
 MySQL  localhost:3306 ssl  JS > dba.configureInstance('test-machine01:3307');
Configuring local MySQL instance listening at port 3307 for use in an InnoDB cluster...

This instance reports its own address as test-machine01:3307
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.

The instance 'test-machine01:3307' is valid to be used in an InnoDB cluster.
The instance 'test-machine01:3307' is already ready to be used in an InnoDB cluster.
 MySQL  localhost:3306 ssl  JS >

Member-3: “test-machine02:3306”

,9MySQL  localhost:3306 ssl  JS >
 MySQL  localhost:3306 ssl  JS > dba.configureInstance('test-machine02:3306');
Configuring MySQL instance at test-machine02:3306 for use in an InnoDB cluster...

This instance reports its own address as test-machine02: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.

The instance 'test-machine02:3306' is valid to be used in an InnoDB cluster.
The instance 'test-machine02:3306' is already ready to be used in an InnoDB cluster.
 MySQL  localhost:3306 ssl  JS >

Step 3. Create Cluster: Use createCluster() function to create InnoDB cluster. And status() function to check current status of InnoDB cluster.

 MySQL  localhost:3306 ssl  JS >
 MySQL  localhost:3306 ssl  JS >  var cluster = dba.createCluster('innodbclustertest');
A new InnoDB cluster will be created on instance 'localhost:3306'.

Validating instance configuration at localhost:3306...

This instance reports its own address as test-machine01:3306
WARNING: The following tables do not have a Primary Key or equivalent column:
repl_test.repl_test1, repl_test.repl_test2, repl_test.repl_test3, repl_test.repl_test4, repl_test.repl_test5, repl_test.repl_test6, repl_test.repl_test7, repl_test1.repl_test, repl_test1.repl_test1, repl_test1.repl_test3, repl_test.test, repl_test.repl_test8, repl_test.repl_test9, repl_test.repl_test10, repl_test.repl_test11, repl_test.repl_test12, repl_test1.repl_test2

Group Replication requires tables to use InnoDB and have a PRIMARY KEY or PRIMARY KEY Equivalent (non-null unique key). Tables that do not follow these requirements will be readable but not updateable when used with Group Replication. If your applications make updates (INSERT, UPDATE or DELETE) to these tables, ensure they use the InnoDB storage engine and have a PRIMARY KEY or PRIMARY KEY Equivalent.

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

Creating InnoDB cluster 'innodbclustertest' on 'test-machine01:3306'...

Adding Seed Instance...
NOTE: Metadata schema found in target 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.

 MySQL  localhost:3306 ssl  JS >



 MySQL  localhost:3306 ssl  JS > cluster.status();
{
    "clusterName": "innodbclustertest",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "test-machine01:3306",
        "ssl": "REQUIRED",
        "status": "OK_NO_TOLERANCE",
        "statusText": "Cluster is NOT tolerant to any failures.",
        "topology": {
            "test-machine01:3306": {
                "address": "test-machine01:3306",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.22"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "test-machine01:3306"
}
 MySQL  localhost:3306 ssl  JS >

Step 4. Add 1 Instance: Use addInstance() function to add instance to newly created InnoDB Cluster.

MySQL localhost:3306 ssl JS >
MySQL localhost:3306 ssl JS > cluster.addInstance('test-machine01:3307');
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'test-machine01:3307' 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'.

Incremental state recovery was selected because it seems to be safely usable.

Validating instance configuration at test-machine01:3307...

This instance reports its own address as test-machine01:3307
WARNING: The following tables do not have a Primary Key or equivalent column:
repl_test.repl_test1, repl_test.repl_test10, repl_test.repl_test11, repl_test.repl_test12, repl_test.repl_test2, repl_test.repl_test3, repl_test.repl_test4, repl_test.repl_test5, repl_test.repl_test6, repl_test.repl_test7, repl_test.repl_test8, repl_test.repl_test9, repl_test.test, repl_test1.repl_test, repl_test1.repl_test1, repl_test1.repl_test2, repl_test1.repl_test3

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'test-machine01:33071'. 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.
State recovery already finished for 'test-machine01:3307'

The instance 'test-machine01:3307' was successfully added to the cluster.

MySQL localhost:3306 ssl JS >

MySQL localhost:3306 ssl JS >
MySQL localhost:3306 ssl JS > cluster.status();
{
"clusterName": "innodbclustertest",
"defaultReplicaSet": {
"name": "default",
"primary": "test-machine01:3306",
"ssl": "REQUIRED",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures.",
"topology": {
"test-machine01:3306": {
"address": "test-machine01:3306",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.22"
},
"test-machine01:3307": {
"address": "test-machine01:3307",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.22"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "test-machine01:3306"
}
MySQL localhost:3306 ssl JS >

Step 5. Add 2 Instance: Use addInstance() function to add instance to newly created InnoDB Cluster.

 MySQL  localhost:3306 ssl  JS >
 MySQL  localhost:3306 ssl  JS > cluster.addInstance('test-machine02:3306');
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'test-machine02: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'.

Incremental state recovery was selected because it seems to be safely usable.

Validating instance configuration at test-machine02:3306...

This instance reports its own address as test-machine02:3306
WARNING: The following tables do not have a Primary Key or equivalent column:
repl_test.repl_test1, repl_test.repl_test2, repl_test.repl_test3, repl_test.repl_test4, repl_test.repl_test5, repl_test.repl_test6, repl_test.repl_test7, repl_test1.repl_test, repl_test1.repl_test1, repl_test1.repl_test3, repl_test.test, repl_test.repl_test8, repl_test.repl_test9, repl_test.repl_test10, repl_test.repl_test11, repl_test.repl_test12, repl_test1.repl_test2

Group Replication requires tables to use InnoDB and have a PRIMARY KEY or PRIMARY KEY Equivalent (non-null unique key). Tables that do not follow these requirements will be readable but not updateable when used with Group Replication. If your applications make updates (INSERT, UPDATE or DELETE) to these tables, ensure they use the InnoDB storage engine and have a PRIMARY KEY or PRIMARY KEY Equivalent.

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'test-machine02: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.
State recovery already finished for 'test-machine02:3306'

The instance 'test-machine02:3306' was successfully added to the cluster.

 MySQL  localhost:3306 ssl  JS >
 MySQL  localhost:3306 ssl  JS >
 MySQL  localhost:3306 ssl  JS >
 MySQL  localhost:3306 ssl  JS > cluster.status();
{
    "clusterName": "innodbclustertest",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "test-machine01:3306",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "test-machine01:3306": {
                "address": "test-machine01:3306",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.22"
            },
            "test-machine01:3307": {
                "address": "test-machine01:3307",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.22"
            },
            "test-machine02:3306": {
                "address": "test-machine02:3306",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.22"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "test-machine01:3306"
}
 MySQL  localhost:3306 ssl  JS >

 MySQL  localhost:3306 ssl  JS >
 MySQL  localhost:3306 ssl  JS > cluster.describe()
{
    "clusterName": "innodbclustertest",
    "defaultReplicaSet": {
        "name": "default",
        "topology": [
            {
                "address": "test-machine01:3306",
                "label": "test-machine01:3306",
                "role": "HA"
            },
            {
                "address": "test-machine01:3307",
                "label": "test-machine01:3307",
                "role": "HA"
            },
            {
                "address": "test-machine02:3306",
                "label": "test-machine02:3306",
                "role": "HA"
            }
        ],
        "topologyMode": "Single-Primary"
    }
}
 MySQL  localhost:3306 ssl  JS >

Reference: Deploying a New Production InnoDB Cluster: https://dev.mysql.com/doc/mysql-shell/8.0/en/deploying-new-production-cluster.html

This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.

INNODB CLUSTER, MYSQL, REPLICATION

How to Setup MySql InnoDB Cluster

Related Articles