Install MySQL Cluster 7.3 on CentOS 6

MySQL cluster provides massive scalibility and allows for high availability.

When migrating an existing database to MySQL cluster, be prepared to spend some time making database optimisation tweaks. Depending on the budget available to your project, you may want to take advantage of the MySQL Manager, which will show you exactly where your slowdowns are.

AQL is also worth investigating.

Node Types

There are three types of Nodes in a MySQL cluster - A management node, a data node and an SQL node.

The management node is used to control and push configs onto all of the other nodes. Once the cluster has been started, it can continue to run even after all of the management nodes have failed, however, you will be unable to manage the cluster until the management node(s) has/have been restored.

The data nodes store the database and replicate it between the other data nodes.

The SQL node is the gateway between the MySQL client and the cluster.

Node Configuration

In this example, we will have two of each kind of node, so that any single node failure shouldn't result in an emergency.

Management-Node1 - 10.10.1.10
Management-Node2 - 10.10.1.11

Data-Node1 - 10.10.1.20
Data-Node2 - 10.101.1.21

SQL-Node1 - 10.10.1.30
SQL-Node 2 - 10.10.1.31

Let's begin by disabling SELinux on all nodes. I highly recommend re-enabling and configuring SELinux correctly, once you've finished testing.

nano /etc/selinux/config

SELINUX=permissive

Configure Management Nodes

All of these steps must be performed on BOTH management nodes

Configure IPTables

iptables -A INPUT -p tcp --dport 1186 -j ACCEPT

Download the MySQL cluster packages

wget http://cdn.mysql.com/Downloads/MySQL-Cluster-7.3/MySQL-Cluster-gpl-7.3.6-2.el6.x86_64.rpm-bundle.tar

Install MySQL cluster server package

yum remove -y mysql-libs

mv http://cdn.mysql.com/Downloads/MySQL-Cluster-7.3/MySQL-Cluster-gpl-7.3.6-2.el6.x86_64.rpm-bundle.tar /usr/local/src

cd /usr/local/src

tar xvf MySQL-Cluster-gpl-7.3.6-2.el6.x86_64.rpm-bundle.tar

rpm -Uvh MySQL-Cluster-server-gpl-7.3.6-2.el6.x86_64.rpm

Create Management Config File

mkdir -p /var/lib/mysql-cluster/

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

[tcp default]
SendBufferMemory=128M
ReceiveBufferMemory=128M

# Increasing the sizes of these 2 buffers beyond the default values
# helps prevent bottlenecks due to slow disk I/O.

# MANAGEMENT NODE PARAMETERS

[ndb_mgmd default]
DataDir=/var/lib/mysql-cluster

# It is possible to use a different data directory for each management
# server, but for ease of administration it is preferable to be
# consistent.


[ndb_mgmd]
HostName=10.10.1.10

[ndb_mgmd]
HostName=10.10.1.11

# DATA NODE PARAMETERS

[ndbd default]
NoOfReplicas=2

# Using 2 replicas is recommended to guarantee availability of data;
# using only 1 replica does not provide any redundancy, which means
# that the failure of a single data node causes the entire cluster to
# shut down. We do not recommend using more than 2 replicas, since 2 is
# sufficient to provide high availability, and we do not currently test
# with greater values for this parameter.

LockPagesInMainMemory=1

# On Linux and Solaris systems, setting this parameter locks data node
# processes into memory. Doing so prevents them from swapping to disk,
# which can severely degrade cluster performance.

DataMemory=10G
IndexMemory=2G

# The values provided for DataMemory and IndexMemory assume 4 GB RAM
# per data node. However, for best results, you should first calculate
# the memory that would be used based on the data you actually plan to
# store (you may find the ndb_size.pl utility helpful in estimating
# this), then allow an extra 20% over the calculated values. Naturally,
# you should ensure that each data node host has at least as much
# physical memory as the sum of these two values.

# ODirect=1

# Enabling this parameter causes NDBCLUSTER to try using O_DIRECT
# writes for local checkpoints and redo logs; this can reduce load on
# CPUs. We recommend doing so when using MySQL Cluster on systems running
# Linux kernel 2.6 or later.

NoOfFragmentLogFiles=300
DataDir=/var/lib/mysql-cluster
MaxNoOfConcurrentOperations=1000000

SchedulerSpinTimer=400
SchedulerExecutionTimer=100
RealTimeScheduler=1
# Setting these parameters allows you to take advantage of real-time scheduling
# of NDBCLUSTER threads to get higher throughput.

TimeBetweenGlobalCheckpoints=1000
TimeBetweenEpochs=200
DiskCheckpointSpeed=10M
DiskCheckpointSpeedInRestart=100M
RedoBuffer=32M

# CompressedLCP=1
# CompressedBackup=1
# Enabling CompressedLCP and CompressedBackup causes, respectively, local
#checkpoint files and backup files to be compressed, which can result in a space
#savings of up to 50% over noncompressed LCPs and backups.

# MaxNoOfLocalScans=64
MaxNoOfTables=1024
MaxNoOfOrderedIndexes=256

[ndbd]
HostName=10.10.1.20

LockExecuteThreadToCPU=1
LockMaintThreadsToCPU=0
# On systems with multiple CPUs, these parameters can be used to lock NDBCLUSTER
# threads to specific CPUs

[ndbd]
HostName=10.10.1.21

LockExecuteThreadToCPU=1
LockMaintThreadsToCPU=0

# SQL NODE / API NODE PARAMETERS

[mysqld]
HostName=10.10.1.30

[mysqld]
HostName=10.10.1.31

Configure Data Nodes

These steps must be performed on BOTH data nodes.

Configure the firewall

iptables -A INPUT -p tcp --dport 2200 -j ACCEPT

Download the MySQL cluster packages

wget http://cdn.mysql.com/Downloads/MySQL-Cluster-7.3/MySQL-Cluster-gpl-7.3.6-2.el6.x86_64.rpm-bundle.tar

Install MySQL cluster server package

yum remove -y mysql-libs

mv http://cdn.mysql.com/Downloads/MySQL-Cluster-7.3/MySQL-Cluster-gpl-7.3.6-2.el6.x86_64.rpm-bundle.tar /usr/local/src

cd /usr/local/src

tar xvf MySQL-Cluster-gpl-7.3.6-2.el6.x86_64.rpm-bundle.tar

rpm -Uvh MySQL-Cluster-server-gpl-7.3.6-2.el6.x86_64.rpm

Create data node config file

nano /etc/my.cnf

[mysqld]

ndbcluster
ndb-connectstring=10.10.1.10
ndb-connectstring=10.10.1.11


[mysql_cluster]

ndb-connectstring=10.10.1.10         
ndb-connectstring=10.10.1.11        

Configure SQL Nodes

These steps must be performed on BOTH SQL nodes

Configure the firewall (If you're running a local webserver, this step isn't necessary)

iptables -A INPUT -p tcp --dport 3306 -j ACCEPT

Download the MySQL cluster packages

wget http://cdn.mysql.com/Downloads/MySQL-Cluster-7.3/MySQL-Cluster-gpl-7.3.6-2.el6.x86_64.rpm-bundle.tar

Install MySQL cluster packages

yum remove -y mysql-libs

yum install libaio -y

mv http://cdn.mysql.com/Downloads/MySQL-Cluster-7.3/MySQL-Cluster-gpl-7.3.6-2.el6.x86_64.rpm-bundle.tar /usr/local/src

cd /usr/local/src

tar xvf MySQL-Cluster-gpl-7.3.6-2.el6.x86_64.rpm-bundle.tar

rpm -Uvh MySQL-Cluster-server-gpl-7.3.6-2.el6.x86_64.rpm MySQL-Cluster-shared-compat-gpl-7.3.6-2.el6.x86_64.rpm MySQL-Cluster-shared-gpl-7.3.6-2.el6.x86_64.rpm MySQL-Cluster-client-gpl-7.3.6-2.el6.x86_64.rpm

Create SQL node config file

nano /etc/my.cnf

[mysqld]

ndbcluster
ndb-connectstring=10.10.1.10
ndb-connectstring=10.10.1.11
default_storage_engine=ndbcluster

 
[mysql_cluster]

ndb-connectstring=10.10.1.10         
ndb-connectstring=10.10.1.11        

Start the Cluster

Start the management nodes - This must be done to BOTH management nodes

ndb_mgmd -f /var/lib/mysql-cluster/config.ini

Start the data nodes - This must be done to both data nodes

ndbd

Start the SQL nodes - This must be done to both SQL nodes

service mysql start

Find the MySQL password on each SQL node

cat ~/.mysql_secret

Run MySQL secure installation on each SQL node

mysql_secure_installation

Show Cluster Status

Overall cluster status

ndb_mgm -e show

Check memory usage

ndb_mgm -e "all report memory"

Shutting Down / Restarting the Cluster

The cluster will need to be restarted in order for changes made to the configuration file to come into effect. The SQL nodes must be shutdown prior to shutting down the data and management nodes.

Shutdown the SQL nodes - Ths command should be performed on both SQL nodes

service mysql stop

Shutdown the remainder of the cluster - This command only needs to be performed on one of the management nodes

ndb_mgm -e shutdown

Now you can make your configuration changes and start up the cluster again. We will be using the --initial paramater to make sure that the cluster reads the updated configuration file

Make sure that the config files on both management servers are matching, then run the following command on both management nodes

ndbd_mgmd -f /var/lib/mysql-cluster/config.ini --initial

Start the SQL nodes

service mysql start

Import Existing Database

The SQL engine will need to be set to ndbcluster. If you are importing an existing database, export it using mysqldump, then perform the following commands.

vim YourDB.sql

:%s/engine=InnoDB/engine=ndbcluster/g

:%s/engine=MyISAM/engine=ndbcluster/g

:wq

Now import your edited database backup onto one of your SQL nodes

mysql -uroot -p [YourDB] < YourDB.sql

Tips

Make sure your data nodes have lots of RAM. I gave both of mine 16GB. This guide is based on the premise that both data nodes have at least 16GB of RAM.

Consolidating the SQL node and data node onto the one server will improve performance

If you have nodes split between different networks, do your best to limit the number of hops between these networks

Deploy multiple SQL nodes, install your webserver onto each one and setup your website, then use round-robin DNS to split the load between each webserver

Investigate adaptive query localisation