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

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 and any other cloud platform like Azure, EC2, Compute Engine.

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 https://dev.mysql.com/get/mysql-apt-config_0.8.13-1_all.deb

Install downloaded MySQL apt repository,

$ sudo dpkg -i mysql-apt-config_0.8.13-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:

Login into Database with user name and password

$ mysql –u root –p 

Create the user.

$ CREATE USER 'fosstechnix'@'%' IDENTIFIED BY '[email protected]';

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('[email protected]:3306')

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

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

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.

Related Articles

How To Install MySQL 8.0 on Ubuntu 18.04

 

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe To Our Newsletter

Subscribe To Our Newsletter

Join our mailing list to receive the latest news and updates from our team.

We promise not to spam you, and we don't usually send more than one email a week.

 

You have Successfully Subscribed!

0 Shares
Share via
Copy link