MySQL 8.0 InnoDB Cluster – the quick hands-on manual

I’m just back from a trip in Barcelona where I presented MySQL 8.0 InnoDB Cluster (at dataops and Barcelona MySQL Meetup) and the majority of feedback was great, but I also had some comments on the demos I showed. The first one was:

This is a joke of course (maybe it’s true for some), people found it very easy and they liked it.

But then, the second one was that all I showed wasn’t easy to find, some people who already played with the solution didn’t succeeded in creating a cluster so easily… not because they had errors or encountered bugs, but more because they just didn’t know how to do it.

The goal of this blog post is to illustrate how to create a MySQL 8.0 InnoDB Cluster very quickly using the new MySQL Shell only !

Initial environment

We have 3 MySQL 8.0.11 instances running: mysql1, mysql2 and mysql3

They can all communicate to each others and they have a user with all privileges created on each of them : clusteradmin.

Nothing else has been changed, no configuration file has been modified. The only important thing is that if you have created the admin user on them individually, just run RESET MASTER on all the instances.

Quick Cluster Creation

Let’s connect to one instance (doesn’t matter which one), and let’s create the cluster. I will list all commands needed to create the cluster very quickly and on a next post I will explain them in more details:

Connection using the Shell

MySQL JS> \c clusteradmin@mysql1
Creating a session to 'clusteradmin@mysql1'
Enter password: ****
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 10 (X protocol)
Server version: 8.0.11 MySQL Community Server - GPL
No default schema selected; type \use  to set one.

MySQL [mysql1+ ssl] JS> 

MySQL Configuration

MySQL [mysql1+ ssl] JS> dba.configureInstance('clusteradmin@mysql1',{'restart': true})

You will prompted to validate the changes and if a restart of MySQL is required, it will happen (if you use a default MySQL installation, 3 settings will be changed and a restart will be required).
And you need to configure all the other nodes that you want to be part of the cluster:

MySQL [mysql1+ ssl] JS> dba.configureInstance('clusteradmin@mysql2',{'restart': true})
MySQL [mysql1+ ssl] JS> dba.configureInstance('clusteradmin@mysql3',{'restart': true})

Cluster Creation

When the servers are restarted, just connect again to one of them using the Shell and create the cluster:

MySQL [mysql1+ ssl] JS> \c clusteradmin@mysql1
MySQL [mysql1+ ssl] JS> cluster=dba.createCluster('MyCluster')

Now you already have a cluster but with only one membere, you can verify this using the status() method of the cluster object we created:

MySQL [mysql1+ ssl] JS> cluster.status()
{
    "clusterName": "MyCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "mysql1:3306", 
        "ssl": "REQUIRED", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures.", 
        "topology": {
            "mysql1:3306": {
                "address": "mysql1:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://clusteradmin@mysql1:3306"
}

Add the other members

Now it’s time to add the 2nd and 3rd member to the new cluster:

MySQL [mysql1+ ssl] JS> cluster.addInstance('clusteradmin@mysql2:3306')
MySQL [mysql1+ ssl] JS> cluster.addInstance('clusteradmin@mysql3:3306')

Please, pay attention that here the port for MySQL standard protocol (3306) is required.

Check the cluster

We can verify our cluster using again the status() method:

MySQL [mysql1+ ssl] JS> cluster.status()
{
    "clusterName": "MyCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "mysql1:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "mysql1:3306": {
                "address": "mysql1:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "mysql2:3306": {
                "address": "mysql2:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "mysql3:3306": {
                "address": "mysql3:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://clusteradmin@mysql1:3306"
}

And voilà ! We have a 3 node MySQL 8.0 InnoDB Cluster !

Minimal Commands Summary

• connect to mysql1: \c clusteradmin@mysql1
• configure mysql1: dba.configureInstance('clusteradmin@mysql1',{'restart': true})
• configure mysql2: dba.configureInstance('clusteradmin@mysql2',{'restart': true})
• configure mysql3: dba.configureInstance('clusteradmin@mysql3',{'restart': true})
• connect to mysql1 (after restart): \c clusteradmin@mysql1
• create cluster: dba.createCluster('MyCluster')
• add mysql2 to the cluster: cluster.addInstance('clusteradmin@mysql2:3306')
• add mysql3 to the cluster: cluster.addInstance('clusteradmin@mysql3:3306')