MySQL InnoDB Cluster – Real-World Cluster Tutorial for Ubuntu and Debian

In this tutorial, we are going to guide you through the whole process of configuring Debian based distributions for InnoDB cluster usage; the most popular being Ubuntu. We will address the steps from the initial configurations, to the cluster creation, and finally the MySQL Router configuration to map the data traffic.

A real world setup

In a real world setup (as opposed to one using sandboxes), one would use actual server hosts, however for explanatory purposes we will use Virtual machines to simulate a real server farm as described in the previous tutorial.

Prerequisites

sudo

To properly configure the hosts for InnoDB cluster we need sudo to execute commands with super-user privileges.

To install it run the following commands

To configure it, open the sudoers file and add your user and set the required permissions:

Press ctrl+o and then enter to save the file. Press ctrl+x to close the file.

Python

Python is required to use MySQL Shell, please open a terminal and install it.

Hostname mapping

For this tutorial, we assume that the hostname mapping is already done. If not, then please consider configuring the host file before continuing. Hostname mapping is required in order to map a valid hostname to an IP.

Ubuntu’s default hostname looks like the following:

To configure the host mapping, edit the hosts file:

Add the IP(s) of your host(s) and the name(s). Press ctrl+o and then enter to save the file. Press ctrl+x to close the file.

The file should have the following entrances:

Note: Ubuntu will configure a loopback interface (127.0.1.1) for the hostname by default. Make sure to remove the loopback interface entry as it can’t be used to connect from other hosts.

In case you don’t have the sudo package installed in Debian, please install it and configure it properly.

Install the MySQL APT repository

Open a terminal and use wget to download the official APT repository and then install the package:

Once the installation of the APT repository completes, update the repositories:

Install MySQL Server and MySQL Shell

Type the following command in the terminal to install MySQL Server and MySQL Shell:

During the installation, you need to set a password for MySQL root user.

Ubuntu screenshot

When the installation finish start MySQL Shell using root user, type the password for root when asked for it:

Configure the local instance calling the following function, and type the password for the user when prompted:

MySQL Shell will find the default configuration file and ask you if it is ok to modify it, type “Y”. Since root cannot do remote logins, you have three options to continue with the configuration: enable the remote connections for root, create a new user or not enable remote connections for root neither create a new user.

Ubuntu screenshot

In this tutorial, we choose to create a new user.

You will see a report with the changes made by MySQL Shell and a message saying that you need to restart the MySQL service to apply them.

Ubuntu screenshot

Quit MySQL Shell:

mysql-js> \q

Then restart the MySQL Service

At this point, the host is ready to be part of an InnoDB cluster.

Install MySQL Router

The next step is to install MySQL Router, which provides you the ability to hide your network configuration behind a proxy and map the data requests to the cluster.

Normally, MySQL Router is installed in the client machine. However, for this tutorial we will install it on one of the machines.

In a terminal run the following command:

Once the installation of MySQL Router finish, it’s time to create a cluster.

Create an InnoDB cluster

Open a terminal and start MySQL Shell:

Then create a classic session to the host using the user created in the configuration step and the hostname of the host:

 

Ubuntu screenshot

Now create a cluster assigning the return value to a variable for later usage:

You will see a couple of messages with information about the cluster creation and the function required to add instances to it:

Ubuntu screenshot

Once the cluster creation is complete, you can see the status of the cluster calling the following function:

Ubuntu screenshot

To add new instances use the following command, be sure to use a valid user and ip of an already configured host:

 

Type the password for the user when prompted. Add as many hosts as you want in your cluster, and take in mind that at least three are required to have tolerance to one failure.

Here you can see that all the instances in the cluster are online:

Persist cluster configuration

In order to persist the cluster configuration of each instance, so if a restart happens the instances automatically rejoin the cluster, we must use the dba.configureLocalInstance() again on each instance. The command will update the my.cnf files with the parameters necessary for the automatic rejoin on the cluster on startup.

Run the following commands, locally on each instance:

Using MySQL Router

Now, it’s time to bootstrap our Router. Open a new terminal and type the following command, and type the password for the user when requested:


Ubuntu screenshot

With the previous command the following it’s done:

  • A specific configuration for the cluster “myCluster” it’s created, MySQL Router got connected to the cluster and extracted the metadata to run by itself
  • A directory named “myrouter” is created in “home” and it contains the configuration required by MySQL Router to run
  • Four  TCP ports are generated to get connected to the cluster: rean only and read-write for classic protocol and X ptrotocol.

To start MySQL Router run the following command:


Ubuntu screenshot

To stop MySQL Router, in a terminal run the stop script generated:

Remote Connection

Now we can get connected to the cluster using the IP generated by MySQL Router. The following screenshot is from a Windows host that is connected to the cluster using the read/write port:


Windows screenshot

And the following screenshot is from a windows host that it’s connected to the cluster using the read-only port:

 

Remember that to be able to connect to a remote host using its name, you should configure the host mapping in Windows as well. The file to edit it’s in the directory “C:\Windows\System32\drivers\etc\hosts”, once you configure the host mapping the file should looks like:

Conclusion

You’ve acquired the knowledge to configure hosts for cluster usage, as well as create cluster and add instances to it. Also, you have learned the basics to bootstrap a cluster and to create a proxy for remote connections to map the data traffic using MySQL Router. The environment can be tested as described in a previous tutorial.

You’ve certainly realized how simple and easy to use is the collection of products provided by MySQL to create a high availability environment.

See you in the next blog post!

5 thoughts on “MySQL InnoDB Cluster – Real-World Cluster Tutorial for Ubuntu and Debian

  1. Hi, Thank you very much for this well written tutorial.
    When I try to create the cluster using “var cluster = dba.createCluster(‘devCluster’)” command, it gives me an error saying that : “Query failed – START GROUP_REPLICATION …” command failed.
    Above is the logs from MySQL Shell :
    2017-04-28 10:13:10: Error: Requested session assumes MySQL X Protocol but ‘mysql-primary:3306’ seems to speak the classic MySQL protocol
    2017-04-28 10:13:10: Warning: Closing session: root@mysql-primary:3306
    2017-04-28 10:13:53: Info: Joining ‘mysql-primary:3306′ to group using account root@mysql-primary:3306
    2017-04-28 10:13:53: Info: DBA: mysqlprovision: Executing /usr/bin/mysqlprovision start-replicaset –instance=root@mysql-primary:3306 –replication-user=mysql_innodb_cluster_r1718518156@’%’ –ssl-mode=REQUIRED –stdin –log-format=json -xV 2.0
    2017-04-28 10:13:53: Error: DBA: mysqlprovision exited with error code (1) : {“type”: “INFO”, “msg”: “”, “time”: “2017-04-28 11:13:53 AM”}{“type”: “STEP”, “msg”: “Running start command on ‘mysql-primary@3306′.”, “time”: “2017-04-28 11:13:53 AM”}{“type”: “STEP”, “msg”: “Checking Group Replication prerequisites.”, “time”: “2017-04-28 11:13:53 AM”}{“type”: “INFO”, “msg”: “* Comparing options compatibility with Group Replication… FAIL”, “time”: “2017-04-28 11:13:53 AM”}{“type”: “INFO”, “msg”: “Incompatible server configuration has been updated.”, “time”: “2017-04-28 11:13:53 AM”}{“type”: “INFO”, “msg”: “* Checking server version… PASS”, “time”: “2017-04-28 11:13:53 AM”}{“type”: “INFO”, “msg”: “Server is 5.7.18”, “time”: “2017-04-28 11:13:53 AM”}{“type”: “INFO”, “msg”: “”, “time”: “2017-04-28 11:13:53 AM”}{“type”: “INFO”, “msg”: “* Checking that server_id is unique… PASS”, “time”: “2017-04-28 11:13:53 AM”}{“type”: “INFO”, “msg”: “The server_id is valid.”, “time”: “2017-04-28 11:13:53 AM”}{“type”: “INFO”, “msg”: “”, “time”: “2017-04-28 11:13:53 AM”}{“type”: “INFO”, “msg”: “”, “time”: “2017-04-28 11:13:53 AM”}{“type”: “INFO”, “msg”: “The user root@’mysql-primary’ does not exists on ‘mysql-primary@3306’ and requires to be created.”, “time”: “2017-04-28 11:13:53 AM”}{“type”: “INFO”, “msg”: “* Checking user privileges… PASS”, “time”: “2017-04-28 11:13:53 AM”}{“type”: “INFO”, “msg”: “* Checking compliance of existing tables… PASS”, “time”: “2017-04-28 11:13:53 AM”}{“type”: “INFO”, “msg”: “”, “time”: “2017-04-28 11:13:53 AM”}{“type”: “INFO”, “msg”: “* Verifying Group Replication plugin for server ‘mysql-primary@3306’ …”, “time”: “2017-04-28 11:13:53 AM”}{“type”: “INFO”, “msg”: “Initializing group_replication plugin on ‘mysql-primary@3306′”, “time”: “2017-04-28 11:13:53 AM”}{“type”: “STEP”, “msg”: “Group Replication group name: ’61b58746-2bfb-11e7-a949-080027a01fee'”, “time”: “2017-04-28 11:13:53 AM”}{“type”: “INFO”, “msg”: “* Running change master command”, “time”: “2017-04-28 11:13:53 AM”}{“type”: “STEP”, “msg”: “Attempting to start the Group Replication group…”, “time”: “2017-04-28 11:13:53 AM”}{“type”: “ERROR”, “msg”: “Error starting cluster: ‘mysql-primary@3306’ – Query failed. 3096 (HY000): The START GROUP_REPLICATION command failed as there was an error when initializing the group communication layer.. Query: START group_replication”, “time”: “2017-04-28 11:13:53 AM”}
    2017-04-28 10:13:53: Error: ERROR: Error starting cluster: ‘mysql-primary@3306’ – Query failed. 3096 (HY000): The START GROUP_REPLICATION command failed as there was an error when initializing the group communication layer.. Query: START group_replication
    2017-04-28 10:13:53: Error: Dba.createCluster: ERROR: Error starting cluster: ‘mysql-primary@3306’ – Query failed. 3096 (HY000): The START GROUP_REPLICATION command failed as there was an error when initializing the group communication layer.. Query: START group_replication

    What’s wrong?
    Thanks

    1. Hello.

      Seeing the error log, this message it’s the one that seems is causing the problem:

      “The user root@’mysql-primary’ does not exists on ‘mysql-primary@3306’ and requires to be created.”

      Verify that in your server the user root@mysql-primary or root@% exists.
      Also if you can share as well the log from MySQL Server to complement the MySQL Shell log will be great, we will have a better view to try to find the issue.

      Thanks for your time.

  2. Hi,
    it is possible to have multiple router instances in separate machines routing tough the cluster?
    The thing is that having a single MySQL Router instance creates a single point of failure, so Im planning to create multiple application instances each with their own local router instance. Is it possible?

    Another question: What about “SELECT FOR UPDATE” statement, I understand that read operations (SELECT`s) are load balanced across read replicas… but what about “SELECT FOR UPDATE”? I think that should be routed to master or propagated to its slaves as this statement is used to avoid race conditions.

    Regards!

    1. While you could spin a server solely for the purpose of running the router and run all your connections through it, that doesn’t scale too well as the router can only sustain 500 connections. And as you said, from a HA perspective you would now have to spin at least two and stick a load balancer in front of them which makes the deployment more complicated.

      The recommendation from the MySQL folks is that the router gets installed on the client machine accessing the database. That’s your App server or the workstation used by a developer. So if you have multiple App servers accessing the databases, each would get a router installed on them. Then on your app you define the connection to 127.0.0.1 and the read/write port that router spits out when it is bootstrapped, usually 6446.

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter * Time limit is exhausted. Please reload CAPTCHA.