InnoDB Cluster – MySQL 8.0.11

Introduction

InnoDB Cluster is one the best HA solutions in mysql world for mysql 8.0 and MySQL Shell 8.0 is also amazing for the features and flexibility it has like the number of languages it supports and setting up group replication / InnoDB Cluster is so easy using mysqlshell.

In this blog I have tried to cover setting up a three nodes InnoDB Cluster (group Replication with MySQL router)  with 1 router and 1 app server . You can also have both app and router hosted on the same server. App Server is just needed to validate DB connections through mysqlrouter. This setup was performed in enterprise MySQL 8.0 and mysqlrouter, complete version details can be found below.

These instructions are only for brand new cluster setup not for migrating an already existing master-slave setup.

InnoDB cluster Environment Setup

HostName	Description
test-server01 [ 127.7.10.1:3306 ]	Primary DB Server
test-server02 [ 127.7.10.2:3306 ]	Read Only Server 1
test-server03 [ 127.7.10.3:3306 ]	Read Only Server 2
test-router	Server for mysqlrouter
test-appserver	Server to test connectivity to DB servers through router

Softwares version

mysqlsh --version
mysqlsh Ver 8.0.12 for Linux on x86_64 - for MySQL 8.0.12 (MySQL Community Server (GPL))

mysqlrouter --version
MySQL Router v8.0.12 on Linux (64-bit) (Commercial)

mysql --version
mysql Ver 8.0.11-commercial for Linux on x86_64 (MySQL Enterprise Server - Commercial)

NOTE : Though I have performed this setup with enterprise mysql I am pretty sure that the same sequence of steps will work on community version as well. I will try to test it out by myself. Please feel free to leave your feedback if something doesn’t work with community version.

Group Replication Specific Parameters

The first step would be to get these below parameters added to mysql config file under mysqld section in all the three mysql DB servers.

# default binlog_checksum is CRC32 which should be disabled for group Replication 
binlog_checksum                 = NONE

# this is the name of the plugin
plugin_load_add ="group_replication.so" 

# change this to be the local address for each server the port number can be anything except ports already in use and do not use 3306
loose-group_replication_local_address = '127.7.10.1:33061' 

# add all of the members of the group here, along with the same port numbers
loose-group_replication_group_seeds = '127.7.10.1:33061,127.7.10.2:33061,127.7.10.3:33061'

# Specifies which hosts are permitted to connect to the group
loose-group_replication_ip_whitelist  = 127.7.10.1,127.7.10.2,127.7.10.3

Shell User to connect DB servers

Create the below user in all the three DB servers and make sure you turn the binlog off to avoid creating any GTIDs before starting the group replication and if you forget to do that make sure you do reset master in each node to keep them in consistent starting position.

set session sql_log_bin=0;
set global validate_password_policy=LOW;
CREATE USER 'shelluser'@'test-router' identified by 'shellpass';
GRANT USAGE ON *.* TO 'shelluser'@'test-router';
GRANT ALL PRIVILEGES ON `mysql_innodb_cluster_metadata`.* TO 'shelluser'@'test-router';
GRANT SELECT ON `performance_schema`.* TO 'shelluser'@'test-router';
GRANT CREATE USER ON *.* TO 'shelluser'@'test-router';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'shelluser'@'test-router' WITH GRANT OPTION;
RESET MASTER;

NOTE :  I do reset master to make sure we don’t have any excess GTIDs in any servers else it could cause issues while we add them to the cluster.

InnoDB Cluster Setup

This is the step where we will be setting up group replication between these three nodes from mysql shell.

MySQL Shell from Router

I used mysql shell for setting this innodb cluster setup from my router server not necessarily it has to be from router it can be done from any DB server as well. All that matters is the upcoming sequence of steps not where we actually have shell. I am assuming that you were able to install both mysqlrouter and mysqlshell rpms or packages as instructed in mysql documentation.

[admin@test-router linuxuser]# mysqlsh
MySQL Shell 8.0.12

Copyright (c) 2016, 2018, 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 >

Validate Instance for being in Innodb Cluster

Check Instance Configuration to validate if Innodb Cluster can be set on this instance. We need to perform this check on all the three mysql database nodes where we want to setup innodb cluster

dba.checkInstanceConfiguration("shelluser@test-server01:3306");
dba.checkInstanceConfiguration("shelluser@test-server02:3306");
dba.checkInstanceConfiguration("shelluser@test-server03:3306");

NOTE: Do Not proceed if you run into configuration issues and not able to get them fixed.

Instance Check output [ OK ]

 MySQL  JS > dba.checkInstanceConfiguration("shelluser@test-server02:3306");
Validating MySQL instance at test-server02:3306 for use in an InnoDB cluster...

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

The instance 'test-server02:3306' is valid for InnoDB cluster usage.

{
    "status": "ok"
}

Instance Check output [ NOT OK ]

Default binlog_checksum is CRC32 which should be disabled for group Replication

MySQL  JS > dba.checkInstanceConfiguration("shelluser@test-server01:3306");
Validating MySQL instance at test-server02:3306 for use in an InnoDB cluster...

This instance reports its own address as test-server01
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 configuration options need to be fixed:
+-----------------+---------------+----------------+----------------------------+
| Variable        | Current Value | Required Value | Note                       |
+-----------------+---------------+----------------+----------------------------+
| binlog_checksum | CRC32         | NONE           | Update the server variable |
+-----------------+---------------+----------------+----------------------------+

Please use the dba.configureInstance() command to repair these issues

{
    "config_errors": [
        {
            "action": "server_update",
            "current": "CRC32",
            "option": "binlog_checksum",
            "required": "NONE"
        }
    ],
    "errors": [],
    "status": "error"
}

Connect to the Primary Node of the cluster

This will prompt you for the password for the shelluser and it would ask for you to share the credentials for future purposes and you can give Yes (Y) for that.

MySQL  JS > shell.connect("shelluser@test-server01:3306");
Creating a session to 'shelluser@test-server01:3306'
Please provide the password for 'shelluser@test-server01:3306': ***********
Save password for 'shelluser@test-server01:3306'? [Y]es/[N]o/Ne[v]er (default No): Y
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 1820508
Server version: 8.0.11-commercial MySQL Enterprise Server - Commercial
No default schema selected; type \use  to set one.
<ClassicSession:shelluser@test-server01:3306>
MySQL  test-server01:3306 ssl  JS >

Create Cluster on the Primary Node

Create the cluster using below command and it would create this mysql_innodb_cluster_metadata database with necessary metadata information for the cluster to start which will get synced to the other nodes once they get added to the cluster

MySQL  JS > cluster = dba.createCluster("innodbcluster1");
A new InnoDB cluster will be created on instance 'shelluser@test-server01:3306'.

Validating instance at test-server01:3306...

This instance reports its own address as test-server01

Instance configuration is suitable.
Creating InnoDB cluster 'innodbcluster1' on 'shelluser@test-server01: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.

Adding 2nd Node to the cluster

MySQL  JS > cluster.addInstance("shelluser@test-server02:3306");
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 ...

Validating instance at test-server02:3306...

This instance reports its own address as test-server02

Instance configuration is suitable.
The instance 'shelluser@test-server02:3306' was successfully added to the cluster.

Cluster Status After adding the 2nd Node

MySQL  JS > cluster.status();
{
    "clusterName": "innodbcluster1",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "test-server01:3306",
        "ssl": "REQUIRED",
        "status": "OK_NO_TOLERANCE",
        "statusText": "Cluster is NOT tolerant to any failures.",
        "topology": {
            "test-server01:3306": {
                "address": "test-server01:3306",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "test-server02:3306": {
                "address": "test-server02:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            }
        }
    },
    "groupInformationSourceMember": "mysql://shelluser@test-server01:3306"
}

Adding 3rd Node to the cluster

MySQL  JS > cluster.addInstance("shelluser@test-server03:3306");
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 ...

Validating instance at test-server03:3306...

This instance reports its own address as test-server03

Instance configuration is suitable.
The instance 'shelluser@test-server03:3306' was successfully added to the cluster.

Cluster Status After adding the 3rd Node

JS > cluster.status();
{
    "clusterName": "innodbcluster1",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "test-server01:3306",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "test-server01:3306": {
                "address": "test-server01:3306",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "test-server02:3306": {
                "address": "test-server02:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "test-server03:3306": {
                "address": "test-server03:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            }
        }
    },
    "groupInformationSourceMember": "mysql://shelluser@test-server01:3306"
}

Verify the group members on any DB server in the cluster

mysql> SELECT * FROM performance_schema.replication_group_members; 
+---------------------------+--------------------------------------+--------------------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+--------------------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 73bdf757-bd42-11e8-b4ca-00505681763c | test-server01 | 3306 | ONLINE | PRIMARY | 8.0.11 |
| group_replication_applier | 87bfd3f0-bd43-11e8-bc2a-00505681e9b4 | test-server02 | 3306 | ONLINE | SECONDARY | 8.0.11 |
| group_replication_applier | b2556b0c-bd3e-11e8-9787-00505681a4ff | test-server03 | 3306 | ONLINE | SECONDARY | 8.0.11 |
+---------------------------+--------------------------------------+--------------------------------+-------------+--------------+-------------+----------------+

Router Setup

Bootstrap mysqlrouter with the cluster

We can have router hosted on a separate server or on the same server where application is hosted. During bootstraping the primary node gets bootstrap with the router at base ports by default it is 3306 but in the example I chose 3310 as I already had another cluster running at 3306/3307.

bash# mysqlrouter --bootstrap shelluser@test-server01:3306 --directory  /etc/innodbcluster1 --conf-base-port 3310 --user=mysql --force
Please enter MySQL password for shelluser:

Reconfiguring MySQL Router instance at '/etc/innodbcluster1'...
Checking for old Router accounts
Found old Router accounts, removing
Creating account mysql_router2_2jvij0zi8g6t@'%'
MySQL Router  has now been configured for the InnoDB cluster 'innodbcluster1'.

The following connection information can be used to connect to the cluster.

Classic MySQL protocol connections to cluster 'innodbcluster1':
- Read/Write Connections: localhost:3310
- Read/Only Connections: localhost:3311
X protocol connections to cluster 'innodbcluster1':
- Read/Write Connections: localhost:3312
- Read/Only Connections: localhost:3313

Existing configurations backed up to '/etc/innodbcluster1/mysqlrouter.conf.bak'

Start Router Service

The above step creates innodbcluster1 directory in /etc with the files listed below where you can find the mysqlrouter.key , mysqlrouter.conf and start/stop scripts for innodbcluster1 cluster.

[admin@test-router etc]# cd /etc/innodbcluster1
[admin@test-router innodbcluster1]# ls -ltrh
total 20K
drwx------ 2 mysql mysql    6 Sep 20 19:18 run
-rw------- 1 mysql mysql   87 Sep 20 19:18 mysqlrouter.key
drwx------ 2 mysql mysql   20 Sep 20 19:18 data
drwx------ 2 mysql mysql   28 Sep 20 19:18 log
-rwx------ 1 mysql mysql  158 Sep 20 19:19 stop.sh
-rwx------ 1 mysql mysql  278 Sep 20 19:19 start.sh
-rw------- 1 mysql mysql 1.4K Sep 20 19:19 mysqlrouter.conf.bak
-rw------- 1 mysql mysql 1.4K Sep 20 19:19 mysqlrouter.conf

[admin@test-router innodbcluster1]# ./start.sh

Verify Router Process after starting

[admin@test-router innodbcluster1]# ps -ef| grep router | grep -v grep | grep innodbcluster1
root      7044     1  0 19:19 pts/4    00:00:00 sudo ROUTER_PID=/etc/innodbcluster1/mysqlrouter.pid /bin/mysqlrouter -c /etc/innodbcluster1/mysqlrouter.conf --user=mysql
mysql     7045  7044  6 19:19 pts/4    00:00:00 /bin/mysqlrouter -c /etc/innodbcluster1/mysqlrouter.conf --user=mysql

Validate MySQL DB Connections from App Server

So application would need to connect using 3310 and 3311 port on test-router to connect to RW and RO servers respectively . Technically we can keep router and application on the same server that would reduce the number of servers we need need for a three node cluster to run. Router load balances the Read-Only connections between test-server02/test-server03 read-only servers in round robin fashion

admin@test-appserver linuxuser]# mysql --login-path=appuser --enable-cleartext-plugin -h test-router -P 3310 -e "select @@hostname"
+--------------------------------+
| @@hostname                     |
+--------------------------------+
| test-server01                  |
+--------------------------------+
[admin@test-appserver linuxuser]# mysql --login-path=appuser --enable-cleartext-plugin -h test-router -P 3311 -e "select @@hostname"
+--------------------------------+
| @@hostname                     |
+--------------------------------+
| test-server02                  |
+--------------------------------+
[admin@test-appserver linuxuser]# mysql --login-path=appuser --enable-cleartext-plugin -h test-router -P 3311 -e "select @@hostname"
+--------------------------------+
| @@hostname                     |
+--------------------------------+
| test-server03                  |
+--------------------------------+

Possible Errors

I came across these below errors and it was only because config parameters were not set right in /etc/my.cnf. So please make sure we have all the group replication related parameters right.

Error 1:

MySQL  JS > cluster.addInstance("shelluser@test-server03:3306");
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 ...

Validating instance at test-server03:3306...

This instance reports its own address as test-server03

Instance configuration is suitable.

Cluster.addInstance: WARNING: Not running locally on the server and can not access its error log.
ERROR:
Group Replication join failed.
ERROR: Error joining instance to cluster: 'test-server03:3306' - Query failed. MySQL Error (3092): ClassicSession.query: The server is not configured properly to be an active member of the group. Please see more details on error log.. Query: START group_replication: MySQL Error (3092): ClassicSession.query: The server is not configured properly to be an active member of the group. Please see more details on error log. (RuntimeError)

MySQL  test-server01:3306 ssl  JS >

Error 2:

MySQL  JS > cluster.addInstance("shelluser@test-server03:3306");
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 ...

Validating instance at test-server03:3306...

This instance reports its own address as test-server03

Some configuration options need to be fixed:
+-----------------+---------------+----------------+----------------------------+
| Variable        | Current Value | Required Value | Note                       |
+-----------------+---------------+----------------+----------------------------+
| binlog_checksum | CRC32         | NONE           | Update the server variable |
+-----------------+---------------+----------------+----------------------------+

Please use the dba.configureInstance() command to repair these issues.

ERROR: Instance must be configured and validated with dba.checkInstanceConfiguration() and dba.configureInstance() before it can be used in an InnoDB cluster.
Cluster.addInstance: Instance check failed (RuntimeError)

I hope this blog helps you to setup Innodb cluster with mysqlrouter using mysqlshell. Thanks for your time reading this !!. Your feedback is highly appreciated !!!