MySQL InnoDB Cluster Tutorial 1 ( Group Replication + MySQL Shell )

MySQL InnoDB Cluster has introduced by the MySQL team for the High Availability ( HA ) purpose . It provides a complete high availability solution for MySQL.

Alright, I am planning to write the series of the blogs about the InnoDB Cluster configurations / Management with MySQL Shell / Monitoring etc …

In this blog I am going to show the InnoDB Cluster configuration with three nodes .

What is InnoDB Cluster ?

MySQL InnoDB Cluster is the Combination of,

• MySQL shell
• Group Replication ( GR )
• MySQL Router

Lab Environment :

I have prepared my lab with three servers,

• OS : Centos 7.7
• MySQL 8.0.18 ( latest version )

The server details are ,

• 192.168.91.11 ( hostname : sakthilabs11 )
• 192.168.91.12 ( hostname : sakthilabs12 )
• 192.168.91.13 ( hostname : sakthilabs13 )

Step 1 :

Need to allow the complete communication between the cluster nodes based on the hostname and IP . The below entry needs to be made on all the cluster nodes individually .

[root@sakthilabs11 ~]# cat /etc/hosts | grep 192
192.168.33.11 sakthilabs11 sakthilabs11
192.168.33.12 sakthilabs12 sakthilabs12
192.168.33.13 sakthilabs13 sakthilabs13

Step 2 :

In this step, we need to prepare the MySQL server for the InnoDB Cluster . The below step needs to be individually executed on all the cluster nodes .

cmd : dba.configureLocalInstance("username@userhost:3306"); 

When executing the above command , it will print the informations and ask the actions to configure the InnoDB Cluster . I just highlight them in the below output section .

output ;

MySQL localhost:33060+ ssl JS > dba.configureLocalInstance("root@localhost:3306");
Please provide the password for 'root@localhost:3306': *
Save password for 'root@localhost:3306'? [Y]es/[N]o/Ne[v]er (default No): y
Configuring local MySQL instance listening at port 3306 for use in an InnoDB 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: ***

NOTE: Some configuration options need to be fixed:
+--------------------------+---------------+----------------+--------------------------------------------------+
| Variable | Current Value | Required Value | Note |
+--------------------------+---------------+----------------+--------------------------------------------------+
| binlog_checksum | CRC32 | NONE | 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 | | Update read-only variable and restart the server |
+--------------------------+---------------+----------------+--------------------------------------------------+

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

Cluster admin user 'InnoDBCluster'@'%' created.
Configuring instance…
The instance 'localhost:3306' was configured for InnoDB cluster usage.
Restarting MySQL…
NOTE: MySQL server at localhost:3306 was restarted.

Step 3 :

After prepare all the nodes , need to login any one of the MySQL Shell with the InnoDB Cluster account ( which was created during the preparing phase ) .

cmd : shell.connect('InnoDBCluster@192.168.33.23:3306');

output :

MySQL localhost:33060+ ssl JS > shell.connect('InnoDBCluster@192.168.33.11:3306');
Creating a session to 'InnoDBCluster@192.168.33.11:3306'
Please provide the password for 'InnoDBCluster@192.168.33.11:3306': ***
Save password for 'InnoDBCluster@192.168.33.11:3306'? [Y]es/[N]o/Ne[v]er (default No): y
Fetching schema names for autocompletion… Press ^C to stop.
Closing old connection…
Your MySQL connection id is 9
Server version: 8.0.18 MySQL Community Server - GPL
No default schema selected; type \use to set one.

MySQL 192.168.33.11:3306 ssl JS >

Step 4 :

Create your first node of the InnoDB Cluster .

cmd : cluster = dba.createCluster('first_InnoDB_cluster');

output :

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

Validating instance at 192.168.33.11:3306…

This instance reports its own address as sakthilabs11:3306

Instance configuration is suitable.
Creating InnoDB cluster 'first_InnoDB_cluster' on '192.168.33.11: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.
MySQL 192.168.33.11:3306 ssl JS >

Step 5 :

Now we have successfully created the single node cluster , Have to add the other nodes as well . When adding the other nodes, it will ask the recovery method, we need to choose them . Clone plugin is the default one .

cmd : cluster.addInstance('InnoDBCluster@192.168.33.12:3306');

output :

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

Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): clone
Validating instance at 192.168.33.12:3306…

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

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: 192.168.33.12:3306 is being cloned from sakthilabs11:3306
** Stage DROP DATA: Completed
** Clone Transfer
FILE COPY ############################################################ 100% Completed
PAGE COPY ############################################################ 100% Completed
REDO COPY ############################################################ 100% Completed
** Stage RECOVERY: \
NOTE: 192.168.33.12:3306 is shutting down…

Waiting for server restart… ready

sakthilabs12:3306 has restarted, waiting for clone to finish…

Clone process has finished: 59.55 MB transferred in about 1 second (~59.55 MB/s)

Incremental distributed state recovery is now in progress.

Waiting for distributed recovery to finish…
NOTE: '192.168.33.12:3306' is being recovered from 'sakthilabs11:3306'

Distributed recovery has finished

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

Similarly , I have added the third node as well .

Finally ,

We can check the cluster status with the below command .

cmd : cluster.status();

output :

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

Hope this blog will helps someone who is trying to learning the MySQL InnoDB Cluster . I will be coming back with new blog soon .

Thanks !!