Installing and Configuring InnoDB Cluster

Now a days HA(High Availability) and DR(Disaster Recovery) Solutions are mandatory and required for mission critical databases. In MySQL world , HA solution are provided using the InnoDB or NDB Clustering Solution.

In this blog , We will discuss about the Installation and Configuration of the InnoDB Cluster.

Installation Standards and Prerequisites

Before installing a production deployment of InnoDB cluster, ensure that the server instances you intend to use meet the following requirements.

InnoDB cluster uses Group Replication and therefore your server instances must meet the same requirements. AdminAPI provides the dba.checkInstanceConfiguration() method to verify that an instance meets the Group Replication requirements, and the dba.configureLocalInstance() method to configure an instance to meet the requirements
Group Replication members can contain tables using a storage engine other than Innodb, for example MyISAM. Such tables cannot be written to by Group Replication, and therefore when using InnoDB cluster. To be able to write to such tables with InnoDB cluster, convert all such tables to Innodb before using the instance in a InnoDB cluster.
The Performance Schema must be enabled on any instance which you want to use with InnoDB cluster
The provisioning scripts that MySQL Shell uses to configure servers for use in InnoDB cluster require access to Python version 2.7. For a sandbox deployment Python is required on the single machine used for the deployment, production deployments require Python on each server instance.
On Unix Python must be found as part of the shell environment. To check that your system has Python configured correctly issue following command : $/usr/bin/env python

If a Python interpreter starts ,no further action is required. And if previous command failed ,create soft link between /usr/bin/python and your chosen Python binay.
Downloading and installing following components:
1) MySQL Server
2) MySQL Shell
3) MySQL Router

Installation

1) Install MySQL Server on all three node of the cluster.

2) Install MySQL Shell on all three node of the cluster.

apt-get install mysql-shell

3) Install MySQL Router on Application servers and DB server(if needed).

4) Create cluster administration user on all three node of the Cluster.

create user 'clusteradmin'@'%' identified by '********';
grant all privileges on *.* to 'clusteradmin'@'%' with grant option;

GRANT SELECT ON mysql_innodb_cluster_metadata.* TO clusteradmin@'%';
GRANT SELECT ON performance_schema.global_status TO clusteradmin@'%';
GRANT SELECT ON performance_schema.replication_applier_configuration TO clusteradmin@'%';
GRANT SELECT ON performance_schema.replication_applier_status TO clusteradmin@'%';
GRANT SELECT ON performance_schema.replication_applier_status_by_coordinator TO clusteradmin@'%';
GRANT SELECT ON performance_schema.replication_applier_status_by_worker TO clusteradmin@'%';
GRANT SELECT ON performance_schema.replication_connection_configuration TO clusteradmin@'%';
GRANT SELECT ON performance_schema.replication_connection_status TO clusteradmin@'%';
GRANT SELECT ON performance_schema.replication_group_member_stats TO clusteradmin@'%';
GRANT SELECT ON performance_schema.replication_group_members TO clusteradmin@'%';
GRANT SELECT ON performance_schema.threads TO clusteradmin@'%' WITH GRANT OPTION;

5)Check and verify instance configuration.

mysqlsh --log-level=DEBUG3

mysql-js> dba.verbose=2

dba.checkInstanceConfiguration('mysqlhost01@mysqlhost01-1:3306')

root@mysqlhost01:~# hostname
mysqlhost01
root@mysqlhost01:~# mysqlsh --log-level=DEBUG3
MySQL Shell 8.0.17

Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved.
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.verbose=2
2
MySQL JS > dba.checkInstanceConfiguration('clusteradmin@mysqlhost01:3306')
Please provide the password for 'clusteradmin@mysqlhost01:3306': ************************
Save password for 'clusteradmin@mysqlhost01:3306'? [Y]es/[N]o/Ne[v]er (default No):
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...

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

Some variables need to be changed, but cannot be done dynamically on the server.
NOTE: Please use the dba.configureInstance() command to repair these issues.

{
    "config_errors": [
        {
            "action": "server_update",
            "current": "CRC32",
            "option": "binlog_checksum",
            "required": "NONE"
        },
        {
            "action": "server_update+restart",
            "current": "OFF",
            "option": "enforce_gtid_consistency",
            "required": "ON"
        },
        {
            "action": "server_update+restart",
            "current": "OFF",
            "option": "gtid_mode",
            "required": "ON"
        },
        {
            "action": "server_update+restart",
            "current": "1",
            "option": "server_id",
            "required": "<unique ID>"
        }
    ],
    "status": "error"
}
MySQL JS >

Follow the above steps on three node of the cluster.

4) Configure all three node of the cluster.

mysqlsh --log-level=DEBUG3
mysql-js> dba.verbose=2
dba.configureLocalInstance('clusteradmin@mysqlhost01:3306')

MySQL JS > dba.configureLocalInstance('clusteradmin@mysqlhost01:3306')
Please provide the password for 'clusteradmin@mysqlhost01:3306': ************************
Save password for 'clusteradmin@mysqlhost01:3306'? [Y]es/[N]o/Ne[v]er (default No):
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

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
Configuring instance...
The instance 'mysqlhost01:3306' was configured for InnoDB cluster usage.
Restarting MySQL...
NOTE: MySQL server at mysqlhost01:3306 was restarted.

Verify configuration again.

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

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 'mysqlhost01:3306' is valid for InnoDB cluster usage.

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

Similarly, Configure all other MySQL nodes.

5) Create Cluster

mysqlsh --uri clusteradmin@mysqlhost01:3306
var cluster = dba.createCluster('MysqlCluster')

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

Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'clusteradmin@mysqlhost01:3306'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 11
Server version: 8.0.17 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL mysqlhost01:3306 ssl JS >
MySQL mysqlhost01:3306 ssl JS >
MySQL mysqlhost01:3306 ssl JS > var cluster = dba.createCluster('MysqlCluster')
A new InnoDB cluster will be created on instance 'mysqlhost01:3306'.

Validating instance at mysqlhost01:3306...

This instance reports its own address as mysqlhost01:3306

Instance configuration is suitable.
Creating InnoDB cluster 'MysqlCluster' on 'mysqlhost01: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 created, Please verify cluster using below commands :

var cluster = dba.getCluster()
cluster.status()
cluster.describe();

MySQL mysqlhost01:3306 ssl JS > var cluster = dba.getCluster()
MySQL mysqlhost01:3306 ssl JS > cluster.status()
{
    "clusterName": "MysqlCluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "mysqlhost01:3306",
        "ssl": "REQUIRED",
        "status": "OK_NO_TOLERANCE",
        "statusText": "Cluster is NOT tolerant to any failures.",
        "topology": {
            "mysqlhost01:3306": {
                "address": "mysqlhost01:3306",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.17"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "mysqlhost01:3306"
}
MySQL mysqlhost01:3306 ssl JS > cluster.describe();
{
    "clusterName": "MysqlCluster",
    "defaultReplicaSet": {
        "name": "default",
        "topology": [
            {
                "address": "mysqlhost01:3306",
                "label": "mysqlhost01:3306",
                "role": "HA",
                "version": "8.0.17"
            }
        ],
        "topologyMode": "Single-Primary"
    }
}

6) Add nodes to the cluster.

cluster.addInstance('clusteradmin@mysqlhost02:3306');

MySQL mysqlhost01:3306 ssl JS > cluster.addInstance('clusteradmin@mysqlhost02:3306');
Please provide the password for 'clusteradmin@mysqlhost02:3306': ************************
Save password for 'clusteradmin@mysqlhost02:3306'? [Y]es/[N]o/Ne[v]er (default No):

NOTE: The target instance 'mysqlhost02:3306' has not been pre-provisioned (GTID set
is empty). The Shell is unable to decide whether incremental distributed 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
'mysqlhost02:3306' with a physical snapshot from an existing cluster member.
To use this method by default, set the 'recoveryMethod' option to 'clone'.

The incremental distributed 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 at mysqlhost02:3306...

This instance reports its own address as mysqlhost02:3306

Instance configuration is suitable.
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...

WARNING: User 'mysql_innodb_cluster_1397898975'@'%' already existed at instance 'mysqlhost01:3306'. It will be deleted and created again with a new password.
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: mysqlhost02:3306 is being cloned from mysqlhost01:3306
** Stage DROP DATA: Completed
** Clone Transfer
    FILE COPY ############################################################ 100% Completed
    PAGE COPY ############################################################ 100% Completed
    REDO COPY ############################################################ 100% Completed
** Stage RECOVERY: \
NOTE: mysqlhost02:3306 is shutting down...

* Waiting for server restart... ready
* mysqlhost02:3306 has restarted, waiting for clone to finish...
* Clone process has finished: 58.50 MB transferred in about 1 second (~inf TB/s)

Incremental distributed state recovery is now in progress.

* Waiting for distributed recovery to finish...
NOTE: 'mysqlhost02:3306' is being recovered from 'mysqlhost01:3306'
* Distributed recovery has finished

The instance 'mysqlhost02:3306' was successfully added to the cluster.

Similarly , Add other nodes to the cluster.

7) Configure MySQL Router on all the application and DB Servers (if needed).

8) Check Cluster status post configuration.

MySQL mysqlhost02:3306 ssl JS > cluster.status()
{
    "clusterName": "MySQLCLUSTER",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "mysqlhost02:3306",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "mysqlhost01:3306": {
                "address": "mysqlhost01:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.17"
            },
            "mysqlhost02:3306": {
                "address": "mysqlhost02:3306",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.17"
            },
            "mysqlhost03:3306": {
                "address": "mysqlhost03:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.17"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "mysqlhost02:3306"
}

9) Perform connectivity Test.

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

Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'clusteradmin@localhost:6446'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 1033
Server version: 8.0.17 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL localhost:6446 ssl JS > \sql
Switching to SQL mode... Commands end with ;
MySQL localhost:6446 ssl SQL > show databases;
+-------------------------------+
| Database                      |
+-------------------------------+
| information_schema            |
| mysql                         |
| mysql_innodb_cluster_metadata |
| performance_schema            |
| sys                           |
+-------------------------------+
5 rows in set (0.0013 sec)
MySQL localhost:6446 ssl SQL > select @@port;
+--------+
| @@port |
+--------+
|   3306 |
+--------+
1 row in set (0.0007 sec)
MySQL localhost:6446 ssl SQL > select @@hostname;
+--------------+
| @@hostname   |
+--------------+
| mysqlhost01 |
+--------------+
1 row in set (0.0006 sec)
MySQL localhost:6446 ssl SQL >

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

Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'clusteradmin@localhost:6446'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 3213
Server version: 8.0.17 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL localhost:6446 ssl JS > \sql
Switching to SQL mode... Commands end with ;
MySQL localhost:6446 ssl SQL > show databases;
+-------------------------------+
| Database                      |
+-------------------------------+
| information_schema            |
| mysql                         |
| mysql_innodb_cluster_metadata |
| performance_schema            |
| sys                           |
+-------------------------------+
5 rows in set (0.0011 sec)
MySQL localhost:6446 ssl SQL > select @@port;
+--------+
| @@port |
+--------+
|   3306 |
+--------+
1 row in set (0.0006 sec)
MySQL localhost:6446 ssl SQL > select @@hostname;
+--------------+
| @@hostname   |
+--------------+
| mysqlhost01 |
+--------------+
1 row in set (0.0007 sec)
MySQL localhost:6446 ssl SQL >

you can learn more about MySql and InnoDB from below books:

MySQL Replication Simplified: Easy step-by-step examples to establish, troubleshoot and monitor replication

Introducing InnoDB Cluster: Learning the MySQL High Availability Stack

MySQL Cookbook: Solutions for Database Developers and Administrators

Effective MySQL Backup and Recovery

=================================================================

You can also , Visit our Blogs for related topic on MySQL InnoDB Cluster:
1)MySQL InnoDB Cluster node Addition Issue: - Cluster.addInstance: : MySQL server has gone away (RuntimeError)
2)Install & Configure MySQL Router - MySQL InnoDB Cluster

3)Restoring a Failed member in InnoDB Cluster