MySQL InnoDB Cluster – Quick Guide

Modern database engines like MongoDB provide built-in cluster solutions, making deployment simpler. While MySQL offers clustering products such as MySQL HA and MySQL NDB Cluster, the widely-used InnoDB engine lacked this capability for some time. Recently, MySQL introduced the InnoDB Cluster, allowing users to establish clusters that can handle node outages effectively.

MySQL also provides tools like MySQL-shell and MySQL-router to streamline management.

Initial Setup Tasks

To create a MySQL cluster, three hosts are required. Depending on the host setup, some preliminary tasks must be completed. For clarity, this example will use three virtual machines labeled MySQL-rep-1, MySQL-rep-2, and MySQL-rep-3, all running on the RH7 operating system. Each host will undergo the following initial configuration steps.

Step 1: Verify Hostnames

Each host in the cluster must resolve the hostname to an IP address that is not a loopback address. Editing the /etc/hosts file and removing loopback entries is recommended. Add an entry for each cluster host. Example configurations are shown below:

Initial /etc/hosts Configuration

After editing, your /etc/hosts file should look like this:

Step 2: Disable SELinux

For smooth communication between nodes in a controlled environment, disable SELinux and the firewall. This can be achieved with the following commands:

Component Installation

Each host requires several MySQL components. Follow these steps to install the necessary packages.

Install the MySQL YUM Repository

First, download and install the MySQL YUM repository RPM:

Install MySQL Components

Using yum, install MySQL-community-server, MySQL-community-client, and MySQL-shell:

Update the Root Password

After installation, start MySQL-server and retrieve the temporary password:

Log into MySQL using the temporary password and update it to a strong password following MySQL’s password policy.

Configuring the Cluster Nodes

With MySQL-shell, configure each node to be ready for clustering:

1. Start MySQL-shell.
2. Use dba.configureLocalInstance() to verify and configure each node.
3. MySQL-shell can automatically configure compatible versions (MySQL 8.0.x) for clustering.

Creating and Managing the Cluster

Step 1: Create the Seed Instance

To initiate the cluster, designate MySQL-rep-1 as the seed node:

Step 2: Adding Additional Instances

With the seed instance active, add MySQL-rep-2 and MySQL-rep-3 as replicas:

To check the cluster status, use:

Testing

Once the cluster is live, log into each instance as root and verify that all instances reflect changes made on the R/W node (MySQL-rep-1). Here’s a quick example:

Run a SELECT query on each node to confirm the replication.

Conclusion

With your MySQL InnoDB cluster configured, you can treat it as a standard MySQL instance. Remember, only designated R/W nodes can handle write operations, while R/O nodes receive replication updates. MySQL InnoDB Cluster supports automatic failover, electing a new R/W node if the primary fails. MySQL-router can facilitate automatic failover handling for applications, a feature detailed in an upcoming post.