In this article, We are going to perform How to do MySQL InnoDB Cluster Setup step by step for MySQL 8.0 on Ubuntu 18.04 LTS or any Cloud Instance like Amazon EC2, Azure VM, Google Compute Engine,etc.

Table of Contents

Introdution

MySQL InnoDB Cluster provides a complete high availability solution for MySQL with Single, Multi-master capabilities and automatic failover detection.

High Availability solution consists of 3 Components,

Prerequisites

Atleast 3 Ubuntu 18.04 LTS System
SSH access with sudo privileges
Firewall Port: 3306

MySQL InnoDB Cluster Environment Details

In this article, We are performing brand new MySQL InnoDB Cluster setup with MySQL Community Server 8.0

IP	Hostname	Description
192.168.100.10	MYSQL-IDC-01	Primary/Master DB Server
192.168.100.11	MYSQL-IDC-02	Read Only DB Server 1
192.168.100.12	MYSQL-IDC-02	Read Only DB Server 2

IP and Hostname Mapping

Open /etc/hosts file , copy and paste IP and hostname.

Output:

$ sudo nano /etc/hosts
192.168.100.10 MYSQL-IDC-01

192.168.100.11 MYSQL-IDC-02

192.168.100.12 MYSQL-IDC-03

Step 1: Add MySQL APT Repository

add the MySQL APT repository to your system’s software repository list

# sudo wget https://dev.mysql.com/get/mysql-apt-config_0.8.14-1_all.deb

Install downloaded MySQL apt repository,

$ sudo dpkg -i mysql-apt-config_0.8.14-1_all.deb

Update the system package

$ sudo apt-get update

Step 2: Install MySQL Server and MySQL Shell

Install MySQL Server and MySQL Shell using below command,

$ sudo apt-get install mysql-server mysql-shell

once you enter above command, it will ask for set root password and confirm password.

Step 3: Creating MySQL Admin User and Allow Remote Connections.

We have to create admin user in all 3 Database servers. Enter following commands to create user and grant admin privileges.

Create User:

$ mysql –u root –p

Create the user.

$ CREATE USER 'fosstechnix'@'%' IDENTIFIED BY 'fosstechnix@123';

Grant Permission to all databases to connect remotely with admin permission.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'fosstechnix'@'%' WITH GRANT OPTION;

Perform flush-privileges operation to tell the server to reload the grant tables.

mysql> FLUSH PRIVILEGES;

Change Bind Address:

Open /etc/mysql/mysql.conf.d/mysqld.conf and add bind address,

bind-address = 0.0.0.0

To take effect, restart MySQL Service.

$ sudo systemctl restart mysql

Step 3: MySQL InnoDB Cluster Setup

1. Connect to MySQL Shell and configure the host for InnoDB cluster usage.

Before creating cluster, we have to configure host for InnoDB cluster usage.Run below command to on 3 hosts.

On MYSQL-IDC-01 Host

$ mysqlsh

MySQL  JS > dba.configureInstance('fosstechnix@MYSQL-IDC-01:3306')

On MYSQL-IDC-02 Host

$ mysqlsh

MySQL  JS > dba.configureInstance('fosstechnix@MYSQL-IDC-02:3306')

On MYSQL-IDC-03 Host

$ mysqlsh

MySQL  JS > dba.configureInstance('fosstechnix@MYSQL-IDC-03:3306')

2. Create an InnoDB Cluster

Once you configured host for innodb cluster , next is to create cluster on Master/First instance (MYSQL-IDC-01). Run below command to login mysql shell and create cluster.

First login to MySQL Shell on MYSQL-IDC-01 instance.

$ mysqlsh

Connect to first instance,

MySQL  JS > shell.connect('fosstechnix@MYSQL-IDC-01:3306')

Now create a cluster assigning the return value to a variable.

MYSQL-IDC-01:33060+ ssl JS > var cluster =dba.createCluster('ProdCluster')

Next add MYSQL-IDC-02 and MYSQL-IDC-03 to ProdCluster. Run below commands on MYSQL-IDC-01

MySQL JS > cluster.addInstance('fosstechnix@MYSQL-IDC-02:3306')

MySQL JS > cluster.addInstance('fosstechnix@MYSQL-IDC-03:3306')

Check the Cluster status

MYSQL-IDC-01:33060+ ssl JS > cluster.status()

Output:

cluster.status()

{

    "clusterName": "ProdCluster",

    "defaultReplicaSet": {

        "name": "default",

        "primary": "MYSQL-IDC-01:3306",

        "ssl": "REQUIRED",

        "status": "OK",

        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",

        "topology": {

            "MYSQL-IDC-01:3306": {

                "address": "MYSQL-IDC-01:3306",

                "mode": "R/W",

                "readReplicas": {},

                "role": "HA",

                "status": "ONLINE",

                "version": "8.0.17"

            },

            "MYSQL-IDC-02:3306": {

                "address": "MYSQL-IDC-02:3306",

                "mode": "R/O",

                "readReplicas": {},

                "role": "HA",

                "status": "ONLINE",

                "version": "8.0.17"

            },

            "MYSQL-IDC-03:3306": {

                "address": "MYSQL-IDC-03:3306",

                "mode": "R/O",

                "readReplicas": {},

                "role": "HA",

                "status": "ONLINE",

                "version": "8.0.17"

            }

        },

        "topologyMode": "Single-Primary"

    },

    "groupInformationSourceMember": "MYSQL-IDC-01:3306"

}

We have done successfully MySQL InnoDB Cluster Setup.

Next is to Install and Configure MySQL Router on Application Instance.

InnoDB Cluster Administration:

Below are common functions for administration InnoDB Cluster .

1. Get information about the structure of the InnoDB cluster

Cluster.describe()

2. To check Primary Master in Group Replication. By Default Group Replication runs on Single Primary Node

mysql> SELECT member_host, member_port, member_state, member_role FROM performance_schema.replication_group_members;

Troubleshooting:

ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.

Conslusion:

In this article, We have performed, How to do MySQL InnoDB Cluster Setup for MySQL on Ubuntu 19.04,18.04/16.04 LTS and any other cloud platform like Azure, EC2, Compute Engine.

MySQL InnoDB Cluster Setup – Complete High Availability Solution for MySQL 8.0