MySQL Cluster 7.5.5 on Ubuntu 16.04 — step by step

What is it about? Setting up a MySQL Cluster on Ubuntu 16.04 LTS (Server). First things first, sorry for my spelling and grammar -> This is not my mother tongue.

MySQL Cluster 7.5.5

There are many tutorials on this topic, unfortunately none which I have read have all the steps “correct”. Please comment if you know other working tutorials, thanks.

With MySQL 7.5.5, there were several changes, which were mostly not updated on those. For this reason we now try to install an appropriate MySQL Cluster for testing purposes.

We will use 3 VM’s based on VirtualBox for this task.

  • Management-Node / SQL-Node
  • Data-Node 1
  • Data-Node 2

I assume, that you will use OSx for this tutorial, but most steps should be identical on Linux or even Windows.

Important: This will not be a productive system!

For high availability, it should be at least 4 Data-Nodes, 2 Management-Nodes and 2 SQL-Nodes.

What are the different types of nodes?

  • The Management-Node, as the name suggests, is here to manage the Cluster - Configurations and such stuff.
  • The SQL-Node is the entrance to the Cluster, from here the queries are distributed (consider it as something like an API endpoint).
  • The Data-Nodes, constitute the primary cluster, and here is our data stored.

Installation of the VM’s

To make the installation as simple as possible, i have crafted a small Vagrantfile to fire them up for us.

By the way, the “avahi-daemon” is installed to reach the Servers simply via their hostname. Before we get started, please install Vagrant and VirtualBox on your Workstation.

Next, create a folder on your computer and put the “Vagrantfile” with the following content (if you do not use a MacBook as a workstation, you have to change the “Network” lines, and enter your primary adapter there) into it.

What happens here? Creating 3 VM’s with Ubuntu 16.04, create bridged -Network Set -Up (WLAN Adapter of MacBook), install avahi-daemon and install/download the MySQL Cluster package.

Now change to the folder where your Vagrantfile is located, and start setting up the 3 VM’s. Through the big download it can take some time depending on your connection.

vagrant up
Running “vagrant up”

Set up the management node

By running “vagrant ssh management” you get to the shell of the Management-Node, here we will do the necessary configurations for our Cluster.

sudo mkdir /var/lib/mysql-cluster
sudo nano /var/lib/mysql-cluster/config.ini

Content of the “config.ini”

We tell the Management-Node where he finds his Data-Nodes and the SQL-Node (which is in our example the same as the Management-Node).

Next, we start the service and make sure it starts again at a reboot:

sudo /opt/mysql/server-5.7/bin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini
sudo systemctl enable rc-local.service
sudo nano /etc/rc.local

Content of “rc.local”

As I said, for this example Management-Node and SQL-Node are on the same VM.

We also need to prepare the SQL-Node. To do this, we create a new user without access to the shell, start the MySQL daemon, and initialize the DB.

sudo nano /etc/my.cnf

Next we have to run a couple of commands, to prepare everything to use this node as our SQL-Node..

Important: Write down the generated root password!

Setting Up Data Nodes

Same game, we connect with “vagrant ssh data1” or “vagrant ssh data2” with our data nodes to do the necessary setup (yes, you have to do this on both VM’s)

sudo nano /etc/my.cnf

Content of “my.cnf”

sudo mkdir -p /usr/local/mysql/data
sudo /opt/mysql/server-5.7/bin/ndbd
sudo systemctl enable rc-local.service
sudo nano /etc/rc.local

Content of “rc.local”

Testing our cluster

To find out, if we have made everything halfway right, we will use SSH again with the Management SQL-Node, and will try out the whole thing, using the Cluster Manager tool and the MySQL client.

sudo /opt/mysql/server-5.7/bin/ndb_mgm

With the “SHOW” command, you should see something like this..

Working MySQL-Cluster

To work with our MySQL client, we create a symlink, log in as root (SQL user) and change the password (otherwise we can not issue any commands).

sudo ln -s /opt/mysql/server-5.7/bin/mysql /usr/bin/
mysql -u root -p
ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘newPassw0rd’

Thats it, much more complicated than a PostgreSQL Cluster or ElasticSearch, but ok.. as mentioned before: Please don’t use this as a production environment.

If you have to run such a system in production, I suggest to have a look into this book: MySQL High Availability: Tools for Building Robust Data Centers(* ).

If you prefer a german version of this tutorial, feel free to visit me at the Geek Pub.

Any suggestions? You found some errors? Please comment, thanks m8!

By the way, from time to time I will create some more of those, so follow me on twitter: @ophasnoname