Sunday, 7 July 2013

How To Setup MySql Cluster High Availability


Hi, We are going to setup Mysql Cluster over source code compilation And first we are going to install some dependence of Mysql.

root@machine01: ~#apt-get install bison libncurses5-dev libtool g++ gcc make cmake; 

#Extracting mysql

root@machine01: ~# tar xvfz mysql-cluster-gpl-7.2.8.tar.gz -C /usr/local/src
root@machine01: ~# chown -R root.root /usr/local/src//mysql-cluster-gpl-7.2.8/
root@machine01: ~# cd /usr/local/src//mysql-cluster-gpl-7.2.8

#Configuring  Mysql Cluster 

root@machine01:/usr/local/src/mysql-cluster-gpl-7.2.8# cmake .

#Installing Mysql cluster

root@machine01:/usr/local/src/mysql-cluster-gpl-7.2.8# make clean install 

# Adding mysql user and group

root@machine01:/usr/local/src/mysql-cluster-gpl-7.2.8# groupadd mysql; useradd -g mysql mysql

root@machine01:/usr/local/src/mysql-cluster-gpl-7.2.8# cd /usr/local/mysql 
root@machine01:/usr/local/mysql# cp support-files/my-medium.cnf /etc/my.cnf
root@machine01:/usr/local/mysql# chown -R mysql . ; chgrp -R mysql .

#Dumping essential data in mysql 

root@machine01:/usr/local/mysql./scripts/mysql_install_db --user=mysql
root@machine01:/usr/local/mysqlchown -R root . ; chown -R mysql data

#Putting mysql executables in PATH

root@machine01:/usr/local/mysql# cd /usr/local/mysql/bin/ 
root@machine01:/usr/local/mysql/bin/#  for file in *; do rm /usr/bin/$file; ln -s /usr/local/mysql/bin/$file /usr/bin/$file; done;

#Starting Mysql..

root@machine01:/usr/local/mysql/bin/# ./mysqld_safe --user=mysql

#Securing Mysql Cluster installation 

root@machine01:/usr/local/mysql/bin/# ./mysql_secure_installation

Note : After execution above cmd do the on screen instruction set root password, 
remove anonymous user, etc. [ i have set "y" for every instruction ]   

#Now log into mysql database
root@machine02:/usr/local/mysql/bin/# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.5.27-ndb-7.2.8-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

Note : Installation of the management node does not require the mysqld binary.
but i have installed on machine01. 
--------------------------------------------------------------------------------------------------------------------------------

#Configuration of management node on machine01.

root@machine01:~# mkdir /var/lib/mysql-cluster
root@machine01:~# cd  /var/lib/mysql-cluster
root@machine01:/var/lib/mysql-cluster# vim config.ini

 for our setup, The config.ini file should read as follows: 
---------------------------------------------------------------------------------------------------------------------------------
# Example Ndbcluster storage engine config file.
#
[ndbd default]
NoOfReplicas= 3
MaxNoOfConcurrentOperations= 10000
DataMemory= 2048M
IndexMemory= 1024M
#TimeBetweenWatchDogCheck= 10000
DataDir= /usr/local/mysql
MaxNoOfAttributes=5000
MaxNoOfOrderedIndexes= 500
#ServerPort=2200
#FragmentLogFileSize=1073741824
[ndb_mgmd default]
DataDir= /var/lib/mysql-cluster

[ndb_mgmd]
HostName=192.168.28.28
[ndbd]
HostName=192.168.28.2
[ndbd]
HostName=192.168.28.3
[ndbd]
HostName=192.168.28.4

[mysqld]
HostName=192.168.28.2
[mysqld]
HostName=192.168.28.3
[mysqld]
HostName=192.168.28.4

# choose an unused port number
# in this configuration 63132, 63133, and 63134
# will be used
[tcp default]
#PortNumber= 63132


#Configuration of Data/SQL Nodes on machine02, machine02 and machine03

 For each data node and SQL node in our setup, my.cnf should look like this:

-------------------------------------------------------------------------------------------------------------------------------
 # Example MySQL config file for medium systems.


# The following options will be passed to all MySQL clients
[client]
#password    = your_password
port        = 3306
socket        = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port        = 3306
socket        = /tmp/mysql.sock

# Options for to access mysql cluster datanode
ndbcluster                                 # run NDB storage engine
ndb-connectstring=192.168.28.28           # location of management server


skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

server-id    = 1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysql_cluster]
# Options for ndbd process:
ndb-connectstring=192.168.28.28
-------------------------------------------------------------------------------------------------------------------------------

#Starting the MySQL Cluster

1 ) On the management host(machine01), issue the following command from the system shell to start the management node process

-------------------------------------------------------------------------------------------------------------------------------

root@machine01:~# /usr/local/mysql/bin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini --configdir=/var/lib/mysql-cluster --initial

-------------------------------------------------------------------------------------------------------------------------------

2 ) On each of the Data/SQL node hosts, run these commands to start the ndbd and mysql server process

--------------------------------------------------------------------------------------------------------------------------------
 root@machine02:~# /usr/local/mysql/bin/ndbd  --initial

root@machine02:~# /usr/local/mysql/support-files/mysql.server start

--------------------------------------------------------------------------------------------------------------------------------

#Now login into machine01 (Management Node )
--------------------------------------------------------------------------------------------------------------------------------

root@machine01:~#ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]    3 node(s)
id=2    @192.168.28.2 (mysql-5.5.27 ndb-7.2.8, Nodegroup: 0, Master)
id=3    @192.168.28.3 (mysql-5.5.27 ndb-7.2.8, Nodegroup: 0)
id=4    @192.168.28.4 (mysql-5.5.27 ndb-7.2.8, Nodegroup: 0)

[ndb_mgmd(MGM)]    1 node(s)
id=1    @192.168.28.28  (mysql-5.5.27 ndb-7.2.8)

[mysqld(API)]    3 node(s)
id=5 @192.168.28.2 (mysql-5.5.27 ndb-7.2.8)
id=6 @192.168.28.3  (mysql-5.5.27 ndb-7.2.8)
id=7 @192.168.28.4  (mysql-5.5.27 ndb-7.2.8)
--------------------------------------------------------------------------------------------------------------------------------

#######################################################################

Testing the mysql Cluster Setup

####################################################################### 

On either server machine01 , machine02 and machine03 enter the following commands:
----------------------------------------------------------------------------------------------------------------------------- 
root@machine02:~#mysql -uroot -p 
Enter password: 

mysql >  create database testdb;

mysql > use test;
mysql > CREATE TABLE cluster_test (i INT) ENGINE=NDBCLUSTER;
mysql > INSERT INTO cluster_test (i) VALUES (1);
mysql > SELECT * FROM cluster_test;
----------------------------------------------------------------------------------------------------------------------------

If this works, now go to the other server and run the same SELECT and see what you will get. Insert from that host and go back to previous host and see if it works. If it works then congratulations! 

1 comment:

  1. How does application connect to mysql cluster?

    ReplyDelete