part 1 mysqlcluster ndbcluster on centos 7

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.

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.

Install MySQL Cluster 7.3 on CentOS 6
Posted by Knowledge Base on 01 April 2016 03:40 PM
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