Load balanced mysql cluster installation

MySQL Cluster is a real-time open source transactional database designed for fast, always-on access to data under high throughput conditions.

Here I will explain how to setup a mysql cluster environment in load balanced scenario. For that I am using 3 nodes.
One Management node and Two Data/SQL nodes. I am also using this management node for load balancing.

Management node – 192.168.26.1
SQL nodes – 192.168.26.2/192.168.26.3

Download latest stable linux Generic MySQL Cluster 7.2.12 from the link below.
mysql-cluster-gpl-7.2.12-linux2.6-i686.tar.gz

First we can setup cluster Management node [ server(ndb_mgmd) / client (ndb_mgm) ]

mkdir /usr/src/
cd /usr/src
tar -zxvf mysql-cluster-gpl-7.2.12-linux2.6-i686.tar.gz
cd mysql-cluster-gpl-7.2.12-linux2.6-i686
mv bin/ndb_mgm /usr/bin
mv bin/ndb_mgmd /usr/bin
chmod 755 /usr/bin/ndb_mg*

Now create cluster configuration file ..

vim /usr/local/mysql/mysql-cluster/config.ini
[NDBD DEFAULT]
NoOfReplicas=2

[MYSQLD DEFAULT]

[NDB_MGMD DEFAULT]

[TCP DEFAULT]

# Section for the cluster management node
[NDB_MGMD]
# IP address of the management node (this system)
HostName=192.168.26.1

# Section for the storage nodes
[NDBD]
# IP address of the first storage node
HostName=192.168.26.2
DataDir= /usr/local/mysql/mysql-cluster

[NDBD]
# IP address of the second storage node
HostName=192.168.26.3
DataDir= /usr/local/mysql/mysql-cluster
# one [MYSQLD] per storage node
[MYSQLD]
[MYSQLD]

Now we can start the cluster management server by issuing the command,

ndb_mgmd -f /usr/local/mysql/mysql-cluster/config.ini

For automatically starting the management server at system boot

echo ‘ndb_mgmd -f /usr/local/mysql/mysql-cluster/config.ini’ > /etc/init.d/ndb_mgmd
chmod 755 /etc/init.d/ndb_mgmd
update-rc.d ndb_mgmd defaults

Now setup the mysql Data/SQL nodes

Follow the below procedure for the two nodes

groupadd mysql
useradd -g mysql mysql

apt-get install libaio1

cd /usr/local/
tar -zxvf mysql-cluster-gpl-7.2.12-linux2.6-i686.tar.gz
ln -s mysql-cluster-gpl-7.2.12-linux2.6-i686 mysql
chown mysql.mysql mysql-cluster-gpl-7.2.12-linux2.6-i686 mysql
cd mysql/scripts
./mysql_install_db –user=mysql
chown -R root:mysql .
chown -R mysql data
cp support-files/mysql.server /etc/init.d/mysqld
chmod 755 /etc/init.d/mysqld
cd /usr/local/mysql/bin
mv * /usr/bin
cd ../
rm -fr /usr/local/mysql/bin
ln -s /usr/bin /usr/local/mysql/bin

Configure MySQL configuration file /etc/my.cnf

vim /etc/my.cnf
[mysqld]
ndbcluster
# IP address of the cluster management node
ndb-connectstring=192.168.26.1

[mysql_cluster]
# IP address of the cluster management node
ndb-connectstring=192.168.26.1

Now we can create data directories and start the MySQL server on both cluster nodes, for that

mkdir /usr/local/mysql/mysql-cluster
cd /usr/local/mysql/mysql-cluster
ndbd –initial
/etc/init.d/mysqld start

Note: Run ndbd –initial for the first time and only if we changed the config.ini of management node.

For automatically starting the cluster nodes at system boot, issue the below commands ..

echo ‘ndbd’ > /etc/init.d/ndbd
chmod 755 /etc/init.d/ndbd
update-rc.d ndbd defaults

We can check the cluster nodes from the cluster management client (ndb_mgm)

For that issue this command at management node,

ndb_mgm

— NDB Cluster — Management Client —
ndb_mgm> show;
Connected to Management Server at: localhost:1186
Cluster Configuration
———————
[ndbd(NDB)] 2 node(s)
id=2 @192.168.26.2 (Version: 5.5.06, Nodegroup: 0, Master)
id=3 @192.168.26.3 (Version: 5.5.06, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.26.1 (Version: 5.5.06)

[mysqld(API)] 2 node(s)
id=4 @192.168.26.2 (Version: 5.5.06)
id=5 @192.168.26.3 (Version: 5.5.06)

ndb_mgm> quit;

Now create a database at any of the two data/SQL node , It will reflect at the other.

For clustering to work, those tables should use ENGINE=NDBCLUSTER;

For restarting the cluster, issue this command from management client

shutdown;
quit;

ndb_mgmd -f /usr/local/mysql/mysql-cluster/config.ini

For a load balanced environment , what I use here is mysql-proxy. It does automatic load-balancing and the client applications won’t notice if any node goes down.

For installing mysql-proxy,

apt-get install mysql-proxy
mkdir /etc/mysql-proxy
vim /etc/mysql-proxy/mysql-proxy.conf

[mysql-proxy]
daemon = true
keepalive = true
proxy-address = 192.168.26.1:3306

# data/SQL nodes 1 and 2
proxy-backend-addresses = 192.168.26.2:3306,192.168.26.3:3306

Start mysql-proxy by,
mysql-proxy –defaults-file=/etc/mysql-proxy/mysql-proxy.conf

Now we can point to load balanced high available mysql cluster server from anywhere by issuing the below command,

mysql -u username -ppassword -h 192.168.26.1

cheers /-

Advertisements
Leave a comment

Leave your opinion

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: