HOWTO: Distrubuted MySQL Cluster on Ubuntu 11.04 (cluster between 3 Ubuntuserver)

There are a few guides out for setting up a MySQL Cluster already; unfortunately the large majority of them aren’t geared towards the beginner, and those that are generally involve a single-server setup.

This guide will aim to explain the purpose of each choice and will get you up and running with a basic 3 server setup with a single load-balancing server.

 

Preliminary Requirements

There are a few things you will need first; 3 servers (or VM’s) for the cluster and 1 server for load-balancing. They should all be running Ubuntu 11.04 Server Edition.

Each node should have a minimum of 1GB of RAM and 16GB of hard drive space. The management node can work with 512MB of ram and the default 8GB of hard drive space that VMWare allocates.

Package Installation

These packages will be installed on all 3 servers (management node, data node 1 and data node 2). There is a bug in the installation for the MySQL Cluster packages on Ubuntu, you will need to install MySQL Server first then install the Cluster packages like so:

sudo apt-get install mysql-server

The mysql-server package installs some crucial configuration files and scripts that are skipped and cause dpkg to get hung up during configuration.  


The root password you select here WILL NOT be overwritten in the mysql-cluster-server installation, remember this password.

sudo apt-get install mysql-cluster-server

Accept any extra packages these installations need, it will take about 200MB total for both.

Configuration

This is the bread and butter of a MySQL Cluster installation. In a production environment you would run with more than 2 data nodes across more than one physical machine on the same network. There should always be as little latency between nodes as possible. If you do choose to run VMs on a physical host you should never overallocate RAM to the nodes, the database is mainly stored in RAM and overallocation means some data will be placed into the hosts swap space which increases latency in orders of 10′s, 100′s or even 1000′s in the worst cases.

The Management Node

This node is the brain of the cluster. Without the data nodes can lose sync and cause all sorts of inconsistencies. In a production environment you have at least 2 management nodes (the configuration changes slightly and will be noted in the files here). Here is the configuration file (/etc/mysql/ndb_mgmd.cnf):

[NDBD DEFAULT]
NoOfReplicas=2
DataMemory=256M    # How much memory to allocate for data storage
IndexMemory=18M   # How much memory to allocate for index storage
# For DataMemory and IndexMemory, we have used the
# default values. Since the “world” database takes up
# only about 500KB, this should be more than enough for
# this example Cluster setup.
[MYSQLD DEFAULT]
[NDB_MGMD DEFAULT]
[TCP DEFAULT]
# Section for the cluster management node
[NDB_MGMD]
# IP address of the management node (this system)
HostName=172.16.59.134

#For multiple management nodes we just create more [NDB_MGMD] sections for each node

# Section for the storage nodes
[NDBD]
# IP address of the first storage node
HostName=172.16.59.132
DataDir=/var/lib/mysql-cluster
BackupDataDir=/var/lib/mysql-cluster/backup
DataMemory=512M
[NDBD]
# IP address of the second storage node
HostName=172.16.59.133
DataDir=/var/lib/mysql-cluster
BackupDataDir=/var/lib/mysql-cluster/backup
DataMemory=512M
# one [MYSQLD] per storage node
#These do not require any configuration, they are the “front-end” access to our data
#Their addresses can be assigned when they connect
[MYSQLD]
[MYSQLD]

This configuration assumes 2 things; first that your nodes are isolated on their own network and all the machines on it are trusted (VLAN them onto their own network damnit). Second, it assumes you are going to run two mysqld instances (I run them on the data nodes themselves and balance the load with a 4th server using mysql-proxy).

The Data Nodes

The data nodes are much easier to configure, we can use the configuration that was installed and add 4 lines. Here are the changes that need to be made (/etc/mysql/my.cnf):

We add this to the existing section

[mysqld]
ndbcluster
ndb-connectstring=172.16.59.134  #This is the management node
#ndb-connectstring=”host=172.16.59.134, host=172.16.59.135″ This is if we hade TWO management nodes, one on 172.16.59.134 and one on 172.16.59.135

We add this section at the bottom of the file
[mysql_cluster]
ndb-connectstring=172.16.59.134
#ndb-connectstring=”host=172.16.59.134, host=172.16.59.135″ If we had two management nodes

One thing missing on the data nodes is the backup directory I have referenced in the ndb_mgmd.cnf file. The following commands will create them and set their permisisons (do this on each data node):

sudo mkdir /var/lib/mysql-cluster/backup

sudo chown mysql:mysql -R /var/lib/mysql-cluster

Bringing it Online

Bringing the whole arrangement online involves a very specific ordering:
On the management node:

sudo /etc/init.d/mysql-ndb-mgm restart

On the data nodes (do this on all of the data nodes first):

sudo /etc/init.d/mysql-ndb restart

Once all the data nodes have their ndb daemons restarted:

sudo /etc/init.d/mysql restart

This last one will start the mysql daemons and assumes you are running them on the data nodes.

Testing the Cluster

The Nodes are Connected

First off we want to verify that the cluster is running properly; run the following on the management node:

sudo ndb_mgm
mgm> show

You should see at least 5 separate nodes, the first two are the data nodes, middle ones are the management nodes and lastly you will see the mysql daemons. If the data nodes are stuck in the starting state a quick restart should fix them, DO NOT JUST TYPE REBOOT.

From ndb_mgm
mgm> shutdown

Issuing the shutdown command from within ndb_mgm will bring the cluster down. You can then safely reboot the data nodes, however make sure to restart the management node first as the data nodes will come up without it otherwise (should probably just reboot the management node(s) then the data nodes for good measure). If everything goes well you should be set.

Test Databases

Connect to the first data node and run the following commands:

mysql -u root -p
mysql> show databases;

You should see a few databases, lets create a test database and add a table to it:

mysql> create databases test_db;
mysql> use test_db;
mysql> create table test_table (ival int(1)) engine=ndbcluster;
mysql> insert into test_table values(1);
mysql> select * from test_table;

You should see one value 1 in the table. Now connect to one of the other data nodes and you should be able to do the following:

sudo mysql -u root -p
mysql> show databases;

This should show the database we created on the first node test_db;

mysql> use test_db;
mysql> select * from test_table;

If all is well this should show the same value as we had before, congratulations your cluster is working.

Advanced Setup: A Load Balancer

This is actually the easier part of the guide. On a 4th server install mysql-proxy:

sudo apt-get install mysql-proxy

Next lets start the proxy and have it connect to our two data nodes:

screen -S proxy
mysql-proxy –proxy-backend-addresses=172.16.59.132:3306 –proxy-backend-addresses=172.16.59.133:3306
CTRL+A D

This starts the proxy and allows it to balance across the two nodes I specified in my configuration. If you want to specify a node as read-only substitute –proxy-backend-addresses= with –proxy-read-only-backend-addresses=
Let’s connect to the proxy and see if it works:

mysql -u root -p -h 127.0.0.1 -P 4040
mysql> use test_db;
mysql> select * from test_table;

If all is working well you will see the same things as if you were connected to your actual mysql instances.