Mysql Cluster Using Mysql NDB

Mysql Cluster using NDB(Network DataBase) provides a self healing mysql Cluster which provides a good performance. Mainly the Mysql Cluster Contains 3 Components ie using Management , SQL and Data parts. 

Here we will be configuring two Management and two Data/SQL (together in One server) for the HA. Once the configuration is completed we will have two end points to connect to te database so we need to keep an Load balancer in front of the SQL end points.

OS used is RHEL7
Selinux Enabed
Firewall Disabled

Management Server## Perform the Following steps in both the Management Server's. 

Install Needed Packages 

=================

yum install glibc.i686  ncurses-libs.i686 libstdc++.i686 libgcc.i686 -y


Make Directories and Download the Cluster Files
====================================

mkdir /usr/src/mysql-mgm
cd /usr/src/mysql-mgm
wget http://cdn.mysql.com/Downloads/MySQL-Cluster-7.4/mysql-cluster-gpl-7.4.7-linux-glibc2.5-i686.tar.gz
tar zxvf mysql-cluster-gpl-7.4.7-linux-glibc2.5-i686.tar.gz

cd mysql-cluster-gpl-7.4.7-linux-glibc2.5-i686
cp bin/ndb_mgm* /usr/bin/
chmod 755 /usr/bin/ndb_mgm*


mkdir /var/lib/mysql-cluster
vi /var/lib/mysql-cluster/config.ini
==========================================
[NDBD DEFAULT]
NoOfReplicas=2
DataMemory=80M
IndexMemory=18M
[MYSQLD DEFAULT]

[NDB_MGMD DEFAULT]
DataDir=/var/lib/mysql-cluster
[TCP DEFAULT]

# Section for the cluster management node
[NDB_MGMD]
NodeId=1
# IP address of the first management node (this system)
HostName=192.168.70.130

[NDB_MGMD]
NodeId=2
#IP address of the second management node
HostName=192.168.70.131

# Section for the storage nodes
[NDBD]
# IP address of the first storage node
HostName=192.168.70.132
DataDir= /var/lib/mysql-cluster
[NDBD]
# IP address of the second storage node
HostName=192.168.70.133
DataDir=/var/lib/mysql-cluster
# one [MYSQLD] per storage node
[MYSQLD]
[MYSQLD]
==========================================

chown mysql. /var/lib/mysql-cluster -R

To start the Management Service
========================
ndb_mgmd -f /var/lib/mysql-cluster/config.ini --configdir=/var/lib/mysql-cluster/

Data And SQL Server#Perform this on both of the Server's
==============================================

Install the needed Packages
====================
yum install libaio.i686 libaio-devel.i686 -y
yum install perl -y
yum -y install perl-Data-Dumper

Download the packages
cd /usr/local/
wget http://cdn.mysql.com/Downloads/MySQL-Cluster-7.4/mysql-cluster-gpl-7.4.7-linux-glibc2.5-i686.tar.gz
tar zxvf mysql-cluster-gpl-7.4.7-linux-glibc2.5-i686.tar.gz
mv /root/mysql-cluster-gpl-7.4.7-linux-glibc2.5-i686.tar.gz mysql
chown mysql. mysql -R
cd mysql

Initializing the database
scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data

cp support-files/mysql.server /etc/init.d/
chmod 755 /etc/init.d/mysql.server

cd /usr/local/mysql/bin
mv * /usr/bin
cd ../

vi /etc/my.cnf
============
[mysqld]
ndbcluster
# IP address of the cluster management node
ndb-connectstring=192.168.70.130,192.168.70.131
[mysql_cluster]
# IP address of the cluster management node
ndb-connectstring=192.168.70.130,192.168.70.131
============

mkdir /var/lib/mysql-cluster

cd /var/lib/mysql-cluster
ndbd --initial
/etc/init.d/mysql.server start

After this, secure the MySQL installation by running the appropriate script:

/usr/local/mysql/bin/mysql_secure_installation


Testing 
In the Management Node check the command ndb_mgm and check the status

Delete a nat rule in iptables

First of all I list all the rules including line numbers like this;

iptables -L -t nat –line-numbers

I then look at the output that will be similar to the below

In this example lets say I want to delete rule number 2 in the PREROUTING chain, I would enter the following;

iptables -t nat -D PREROUTING 2

In English the above line means remove line number 2 from the PREOUTING chain, I would then run the first command again to check my iptables file, then save the iptables file and restart the iptables service.

iptables -L -t nat –line-numbers

service iptables save

service iptables restart

All the above is carried out running on Centos, you may have to edit slightly for your particular distribution.

Linux tune the VM subsystem.

Tuning the memory subsystem in Linux is a powerful but delicate task. The right settings can boost your system’s performance, but incorrect changes may cause instability or slowdowns. Always adjust one parameter at a time and monitor your system before making further changes.

Exploring /proc/sys/vm

The /proc/sys/vm directory contains files that represent kernel parameters for the virtual memory subsystem. You can read and write to these files to tune system behavior.

To view the files, use:
cd /proc/sys/vm
ls -l

Sample output:
-rw-r--r-- 1 root root 0 Oct 16 04:21 block_dump
-rw-r--r-- 1 root root 0 Oct 16 04:21 dirty_background_ratio
-rw-r--r-- 1 root root 0 Oct 16 04:21 dirty_expire_centisecs
-rw-r--r-- 1 root root 0 Oct 16 04:21 dirty_ratio
-rw-r--r-- 1 root root 0 Oct 16 04:21 dirty_writeback_centisecs
-rw-r--r-- 1 root root 0 Oct 16 04:21 drop_caches
-rw-r--r-- 1 root root 0 Oct 16 04:21 swappiness
-rw-r--r-- 1 root root 0 Oct 16 04:21 vfs_cache_pressure
...

Key Parameters and Their Effects

1. dirty_background_ratio
   Purpose: Sets the percentage of system memory filled with “dirty” pages (pages to be written to disk) before the background writeback daemon (pdflush) starts writing them out.

Check current value:
sysctl vm.dirty_background_ratio

Default example:
vm.dirty_background_ratio = 10

Tuning:
Increasing this value (for example, to 20) means less frequent flushes, which may benefit systems with fast disks but can cause larger flushes at once.
sysctl -w vm.dirty_background_ratio=20

2. swappiness
   Purpose: Controls how aggressively the kernel swaps memory pages to disk.

Check current value:
sysctl vm.swappiness

Default example:
vm.swappiness = 60

Tuning:
Lower values reduce swapping (good for desktops), higher values increase swapping (can benefit workloads with long-sleeping processes).
sysctl -w vm.swappiness=100

3. dirty_ratio
   Purpose: Sets the percentage of system memory that can be filled with dirty pages before processes generating writes must themselves start writing data to disk.

Check current value:
sysctl vm.dirty_ratio

Default example:
vm.dirty_ratio = 40

Tuning:
Lowering this value (for example, to 25) causes data to be written to disk more frequently, reducing the risk of large data loss but possibly impacting performance.
sysctl -w vm.dirty_ratio=25

Best Practices for VM Tuning

• Change one setting at a time.

• Monitor system performance after each change using tools like vmstat, top, or free.

• If performance improves, keep the new setting. If not, revert to the previous value.

• Document your changes for future reference and troubleshooting.