MySQL Cluster 7 Set up with Ubuntu

If you are accessing this page, it is possible that you want to figure out how difficult is to create a MySQL Cluster. I can tell you that the installation process is not that complicated, the most complicated part is to try to move current databases to this environment , since MySQL cluster engine (NDB) has some differences with other engines (MyISAM, InoDB, ..)

First, we need to understand that MySQL uses different storage engines. Each storage engine has their own advantage and disadvantage. However, NDB is the only engine that according to MySQL can guarantee HA.

Different MySQL configurations can guarantee some degree of HA. The most popular options are the following:

1- Master - Slave Replication : If your Master Server is down you can promote the Slave to master. This configuration is considered active - passive . Moreover, some data can be lost.

2- Master - Master Replication (Asynchronous) : This configuration is great for HA however you would have to have some considerations to avoid conflict. This configuration is considered active - active.

3- MySQL HA with DRBD: DRBD is a process that keep two server file system in sync. The synchronization process is at data block level, so database corruption can be seen. This configuration is active - passive.

4- MySQL Cluster: Basically, MySQL introduces a new storage engine that can help to maintain redundancy and HA.

MySQL Cluster

First, we need to recognize the different processes and facts for MySQL Cluster.

The NDB engine stores Indexes and Data on memory by default. However, if you define a tablespace on disk, Data can be stored on disk.

1- ndbd or ndbmt : Single or Multi thread process for data nodes. This process reserves the memory and stores the data on memory. (Data Nodes)

2- ndb_mgmd : This process provides the configuration file to all nodes (Data Nodes and MySQL Nodes).

3- Mysqld (API): This process provides all the storage engine available plus access to the NDB engine.

Starting the Set UP

Although you can run MySQL cluster in one machine , it does not make sense to install it since you do not have any redundancy. I suggest a basic setup of 5 nodes ( 2 data nodes, 2 mysql nodes and 1 management). Also the following specs will help you to get the HW.

1- Data Nodes:

- A lot of memory. Index and Data are mainly stored in memory.

- Disk space. If you store Data tables on disk.

- CPU. Data nodes are not CPU intensive. Moreover , if you use multiple cpu, you should use ndbmtd process.

2- MySQL Nodes:

- CPU. These Nodes are CPU intensive.

- Memory. As required.
3- Management Node.
- Any small machine can perform this task.

Moreover, it is suggested to have a different subnet for data communication between the data and sql nodes (security and traffic). According to this, sql nodes must have two interfaces, one for access from external apps and one for data communications to data nodes (NDB).

datanode01 192.168.0.1
datanode02 192.168.0.2

sqlnode01 192.168.0.3 172.1616.1
sqlnode02 192.168.0.4 172.16.16.2

mnode01 192.168.0.5

Installing Mysql Cluster.

You are probably tempted to install Mysql using apt-get , however the repos do not contain the latest version of Mysql, so I suggest you to use the binary installation of MySQL. Go to MySQL website and chose the Linux Generic Download, then arch 32 or 64 and finally the mirror.

# wget http://www.mysql.com/get/Downloads/MySQL-Cluster-7.0/mysql-cluster-gpl-7.0.13-linux-x86_64-glibc23.tar.gz/from/http://mysql.he.net/

# tar xzvf mysql-cluster-gpl-7.0.13-linux-x86_64-glibc23.tar.gz

Then
# cd mysql-cluster-gpl-7.0.13-linux-x86_64-glibc23

Read the INSTALL-BINARY file ...so you can install it.

shell> groupadd mysql
shell> useradd -g mysql mysql
shell> cd /usr/local
shell> gunzip < /path/to/mysql-VERSION-OS.tar.gz | tar xvf - shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> chown -R mysql .
shell> chgrp -R mysql .
shell> scripts/mysql_install_db --user=mysql
shell> chown -R root .
shell> chown -R mysql data

Wait ...This is the basic installation for all the nodes and we do not want to have mysqld running on all the nodes, only sql nodes.

This command will be executed on sql nodes when all configuration files are ready:

shell> bin/mysqld_safe --user=mysql &


Setting Up Management Node.

Copy the template configuration file for management node.

#cp /usr/local/mysql/support-files/ndb-config-2-node.ini /usr/local/mysql/config.ini

This configuration contains pretty basic stuff that you would have to tweak for your cluster.
However, I can suggest you a couple of changes depending on the amount of memory that you have available.
Remember mysql cluster stores tables on memory, so if we have more memory available adjust the values.
For example, I have 32GB of memory on each data node.

DataMemory=25634M
IndexMemory=3205M

There are more changes that you can tweak so you can improve the behaviour of the cluster or you can try this website to help you configure the cluster. http://www.severalnines.com/config/

Modify the IP for the management node.

[ndb_mgmd] Id=1 HostName= 192.168.0.5


Then modify the IPs for data nodes under [ndbd]

[ndbd] Id= 2 HostName= 192.168.0.1 [ndbd] Id= 3 HostName= 192.168.0.2
Then the IPs for sql nodes under [mysqld]

[mysqld] Id= 4 HostName= 192.168.0.3 [mysqld] Id= 5 HostName= 192.168.0.4
Save the file.

Create the mysql-cluster directory.

# mkdir /var/lib/mysql-cluster

then Start the management node.

# /usr/local/mysql/bin/ndb_mgmd -f /usr/local/mysql/config.ini

If we wan to check the cluster use the following command.

# /usr/local/mysql/bin/ndb_mgm -- NDB Cluster -- Management Client -- ndb_mgm> show


Setting Up Data node

(I assume you have installed MySQL Binary)

Basically we need to tell the ndbd daemon to use configuration from management node.

Create configuration file on /etc/mysql/

#mkdir /etc/mysql
#vi /etc/mysql/my.cnf

[mysqld]
ndbcluster
# IP address of the cluster management node
ndb-connectstring=192.168.0.5

[MYSQL_CLUSTER]
ndb-connectstring=192.168.0.5

Because it is the first time we are going to start ndbd daemon we need to initialize it.

# /usr/local/mysql/bin/ndbd --initial

After both data nodes are up, you can check the status on the management node using the show command.

If you want to stop a data node , you can run the following command on the management node:

ndb_mgm> 2 stop

It will stop the datanode with ID=2 (see management node configuration)


Setting Up SQL Node or API.

(I assume you have installed MySQL Binary)

Create a my.cnf file.

#vi /etc/my.cnf

[mysqld]
ndbcluster
# IP address of the cluster management node
ndb-connectstring=192.168.0.5
[mysql_cluster]
# IP address of the cluster management node
ndb-connectstring=192.168.0.5

Finally start the SQL nodes.

# /usr/local/mysql/support-files/mysql.server start

After all the nodes are running you can monitor the nodes from the management nodes.

Note: Remember that you must create tables with ENGINE=NDB so they can be running on the cluster.

Enjoy it ...