MySQL InnoDB Cluster offers a comprehensive high availability solution for MySQL databases. It utilizes the AdminAPI, which is included with MySql Shell, to easily configure and manage a group of at least three MySQL server instances that function as an InnoDB Cluster. Each MySQL server instance runs MySQL Group Replication, which handles data replication within the InnoDB Cluster and provides built-in failover capabilities. With AdminAPI, you don't need to work directly with Group Replication in InnoDB Clusters, making it user-friendly. In case of an unexpected server failure, the cluster automatically reconfigures itself. In the default single-primary mode, the InnoDB Cluster has a single read-write server instance, known as the primary, with multiple secondary server instances acting as replicas of the primary. If the primary fails, a secondary server instance is automatically promoted to the role of the primary to ensure continuous operation.
Furthermore, MySQL Router can be configured to automatically adjust itself based on the deployed cluster, transparently connecting client applications to the appropriate server instances. In case of a server failure, MySQL Router detects the change and redirects client applications to the new primary server instance, ensuring uninterrupted access to the database.
In conclusion, MySQL InnoDB Cluster, along with MySQL Router, provides a robust and reliable high availability solution for MySQL databases, ensuring seamless failover and continuous operation in the event of unexpected server failures. The AdminAPI simplifies the configuration and management of InnoDB Clusters, making it an efficient and user-friendly solution for businesses requiring high availability and data replication in their MySQL database environment.
For setup, we installed 3 MySQL Instances listed below:
|
test-machine01
|
192.168.114.177
|
Member-1 (Master)
|
|
|
|
test-machine01
|
192.168.114.177
|
Member-2 (Replica) | |
|
|
test-machine02
|
192.168.114.176
|
Member-3 (Replica)
| |
|
Instance-1 Configurations
Login to MySQL:
mysql>
mysql> prompt Instance-1 >
PROMPT set to 'Instance-1 >'
Instance-1 >
Instance-1 >select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
1 row in set (0.07 sec)
Instance-1 >select @@server_uuid;
+--------------------------------------+
| @@server_uuid |
+--------------------------------------+
| 13ecba9c-444a-11eb-a397-000c29f9d9e6 |
+--------------------------------------+
1 row in set (0.00 sec)
Instance-1 >select @@port;
+--------+
| @@port |
+--------+
| 3306 |
+--------+
1 row in set (0.07 sec)
Instance-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
Instance-2 Configurations
mysql>
mysql> prompt Instance-2>
PROMPT set to 'Instance-2>'
Instance-2>
Instance-2>select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 3 |
+-------------+
1 row in set (0.06 sec)
Instance-2>select @@server_uuid;
+--------------------------------------+
| @@server_uuid |
+--------------------------------------+
| 98411332-6aad-11eb-809a-000c29f9d9e6 |
+--------------------------------------+
1 row in set (0.00 sec)
Instance-2>select @@port;
+--------+
| @@port |
+--------+
| 3307 |
+--------+
1 row in set (0.02 sec)
Instance-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
Instance-3 Configurations
mysql> prompt Instance-3 >
PROMPT set to 'Instance-3 >'
Instance-3 >
Instance-3 >select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
Instance-3 >select @@server_uuid;
+--------------------------------------+
| @@server_uuid |
+--------------------------------------+
| 13c84508-5014-11eb-af41-000c2997dedd |
+--------------------------------------+
1 row in set (0.00 sec)
Instance-3 >select @@port;
+--------+
| @@port |
+--------+
| 3306 |
+--------+
1 row in set (0.08 sec)
Instance-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: Verify Instance Configuration
Before setting up MySQL Group Replication and creating an InnoDB Cluster, it's important to check the configuration of all the MySQL instances that you plan to add to the cluster. This can be done using the checkInstanceConfiguration() function in MySQL Shell. Let's start by connecting to the MySQL instance 'test-machine01:3306' using MySQL Shell.
$ mysqlsh
MySQL Shell 8.0.27
Type '\help' or '\?' for help; '\quit' to exit.
JS > \connect test-machine01:3306
Creating a session to 'test-machine01:3306'
Please provide the password for 'user@test-machine01:3306': ********
Once connected to the MySQL instance, you can use the checkInstanceConfiguration() function to verify if the instance is suitable to be added to the InnoDB Cluster.
JS > dba.checkInstanceConfiguration()
This function will perform various checks on the MySQL instance, including checking the MySQL version, the configuration parameters, and the required plugins, to ensure that the instance meets the requirements for adding it to the InnoDB Cluster. If there are any issues, the function will provide feedback on what needs to be fixed before proceeding.
Make sure to repeat this step for all the MySQL instances that you plan to add to the InnoDB Cluster. It's important to ensure that all instances meet the necessary requirements to avoid any potential issues during the cluster setup process. Once all instances pass the configuration check, you can proceed to the next step of setting up MySQL Group Replication and creating the InnoDB Cluster.
Instance-1: “test-machine01:3306”
Instance-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.
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 >
Instance-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.
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: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 >
Instance-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.
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-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.
Instance-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 >
Instance-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 >
Instance-3: “test-machine02:3306”
MySQL 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
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
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: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
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 >
