Tuesday, April 26, 2011

MySQL cluster - Load Balancing

MySQL Cluster
Purpose:
    cluster for Mysql
    load balancing cho Mysql
    scalability in future
Model: 4 nodes,  2 node cluster, 2 node load balancing ( 1 node master, one node backup)
    1 virtual ip (VIP) listen connection
Db1    192.168.1.11
Db2    192.168.1.12
Lb1    192.168.1.21
Lb2    192.168.1.22
Db    192.168.1.20

Sychronize host file:
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1       localhost.localdomain   localhost
192.168.1.10    db1.hbn.local           db1
::1             localhost6.localdomain6 localhost6
192.168.1.11    db2.hbn.local           db2
192.168.1.21    lb1.hbn.local           lb1
192.168.1.22    lb2.hbn.local           lb2
192.168.1.20    db.hbn.local            db

Download MySQL cluster at: https://dev.mysql.com/downloads/cluster/

On lb1 and lb2, manager node:
[root@lb1 ~]# rpm -Uvh MySQL-Cluster-gpl-*
Preparing...                ########################################### [100%]
   1:MySQL-Cluster-gpl-tools########################################### [ 50%]
   2:MySQL-Cluster-gpl-manag########################################### [100%]
#mkdir –p /var/lib/mysql-cluster    ; chứa cluster log
#mkdir -p /usr/local/mysql-cluster    ; chứa file cấu hình
#cd /usr/local/mysql-cluster
#vi config.ini
[ndb_mgmd]
Id=1
HostName=192.168.1.21        # IP address  của máy manager
DataDir=/var/lib/mysql-cluster

[ndb_mgmd]
Id=2
HostName=192.168.1.22        # IP address  của máy manager
DataDir=/var/lib/mysql-cluster


[ndbd default]
NoOfReplicas=2
DataDir=/var/lib/mysql-cluster

[ndbd]
Id=3
HostName=192.168.1.11

[ndbd]
Id=4
HostName=192.168.1.12

[mysqld]
HostName=192.168.1.11

[mysqld]
HostName=192.168.1.12

#ndb_mgmd --config-file=/usr/local/mysql-cluster/config.ini
Test:
#ndb_mgm
    ndb_mgm> SHOW
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=3 (not connected, accepting connect from 192.168.1.11)
id=4 (not connected, accepting connect from 192.168.1.12)

[ndb_mgmd(MGM)] 2 node(s)
id=1 (not connected, accepting connect from 192.168.1.21)
id=2    @192.168.1.22  (mysql-5.1.47 ndb-7.1.5)

[mysqld(API)]   2 node(s)
id=5 (not connected, accepting connect from 192.168.1.11)
id=6 (not connected, accepting connect from 192.168.1.12)

2 node show only 2 storage node ( in this lab is mysql node) started

On storage nodes và sql nodes, db1 and db2
Install: MySQL-Cluster-gpl-storage-7.1.5-1.rhel5.i386.rpm
    MySQL-Cluster-gpl-tools-7.1.5-1.rhel5.i386.rpm
    MySQL-Cluster-gpl-server-7.1.5-1.rhel5.i386.rpm
    MySQL-Cluster-gpl-client-7.1.5-1.rhel5.i386.rpm
#vi  /etc/my.cnf
[mysqld]
ndbcluster
ndb-connectstring = "host=192.168.1.21,host=192.168.1.22"

[ndb_mgm]
connect-string = "host=192.168.1.21,host=192.168.1.22"

[ndbd]
connect-string = "host=192.168.1.21,host=192.168.1.22"
#mkdir -p /var/lib/mysql-cluster
#ndbd --initial
# /etc/init.d/mysql start
#mysqladmin -u root password '123456'

test on manager node:
#ndb_mgm
ndb_mgm> show
ndb_mgm> ALL STATUS
Node 3: started (mysql-5.1.47 ndb-7.1.5)
Node 4: started (mysql-5.1.47 ndb-7.1.5)
ndb_mgm> SHOW
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=3    @192.168.1.11  (mysql-5.1.47 ndb-7.1.5, Nodegroup: 0, Master)
id=4    @192.168.1.12  (mysql-5.1.47 ndb-7.1.5, Nodegroup: 0)

[ndb_mgmd(MGM)] 2 node(s)
id=1    @192.168.1.21  (mysql-5.1.47 ndb-7.1.5)
id=2    @192.168.1.22  (mysql-5.1.47 ndb-7.1.5)

[mysqld(API)]   2 node(s)
id=5    @192.168.1.11  (mysql-5.1.47 ndb-7.1.5)
id=6    @192.168.1.12  (mysql-5.1.47 ndb-7.1.5)

create database:
mysql -p
mysql> CREATE DATABASE nhansu;
mysql> use nhansu;
Database changed
mysql> CREATE TABLE  phongban (tenp char(12)) ENGINE=NDBCLUSTER;
Query OK, 0 rows affected (0.52 sec)

mysql> INSERT INTO phongban () VALUES ("Ke toan");
Query OK, 1 row affected (0.19 sec)

mysql> INSERT INTO phongban () VALUES ("Kinh doanh");
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO phongban () VALUES ("Ky thuat");
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM phongban ;
+----------------------+
| tenp                    |
+----------------------+
| Ky thuat              |
| Kinh doanh         |
| Ke toan               |
+----------------------+
3 rows in set (0.04 sec)

Trên node thứ 2 (192.168.1.12)
mysql
mysql> show databases;
+---------------------------+
| Database                 |
+---------------------------+
| information_schema |
| mysql                        |
| ndbinfo                     |
| nhansu                     |
| test                           |
+---------------------------+
5 rows in set (0.01 sec)

mysql> use nhansu;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT * FROM phongban;
+----------------------+
| tenp                    |
+----------------------+
| Ky thuat              |
| Kinh doanh         |
| Ke toan               |
+----------------------+
3 rows in set (0.00 sec)

mysql>

MySQL load balancing:
On db1, db2
#vi /etc/sysconfig/network-scripts/ifcfg-lo:0
DEVICE=lo:0
IPADDR=192.168.1.20
NETMASK=255.255.255.255
NETWORK=192.168.1.0
BROADCAST=192.168.104.255
ONBOOT=yes
NAME=loopback

#ifup lo

Install arptable to disable arp broadcast
arptables_jf-0.0.8-8.i386.rpm
arptables-noarp-addr-0.99.2-1.rh.el.um.1.noarch.rpm

/etc/init.d/arptables_jf stop
/usr/sbin/arptables-noarp-addr 192.168.1.20 start
/etc/init.d/arptables_jf save
/etc/init.d/arptables_jf start
/sbin/chkconfig --level 2345 arptables_jf on

On lb1, lb2
yum install  libnet heartbeat-*  ipvsadm -y
cp /usr/share/doc/heartbeat-2.1.3/authkeys /etc/ha.d/
    cp /usr/share/doc/heartbeat-2.1.3/ha.cf /etc/ha.d/
    cp /usr/share/doc/heartbeat-2.1.3/haresources /etc/ha.d

vi /etc/ha.d/authkeys
auth 1
1 sha1 123456


vi /etc/ha.d/ha.cf

logfacility        local0
bcast        eth0
mcast eth0 225.0.0.1 694 1 0
auto_failback off
respawn hacluster /usr/lib/heartbeat/ipfail
apiauth ipfail gid=haclient uid=hacluster
node lb1.hbn.local
node lb2.hbn.local
+ vi /etc/ha.d/ldirectord.cf
checktimeout=10
checkinterval=2
autoreload=no
logfile="local0"
quiescent=yes
virtual = 192.168.1.20:3306
        service = mysql
        real = 192.168.104.21:3306 gate
        real = 192.168.104.22:3306 gate
        checktype = negotiate
        login = "admin"
        passwd = "123456"
        database = "nhansu"
        request = "SELECT * FROM phongban"
        scheduler = wrr
+ vi /etc/ha.d/haresources
on lb1:

Lb1.hbn.local \
        ldirectord::ldirectord.cf \
        LVSSyncDaemonSwap::master \
        IPaddr2::192.168.1.20/24/eth0/192.168.1.255
on lb2:
Lb2.hbn.local \
        ldirectord::ldirectord.cf \
        LVSSyncDaemonSwap::master \
        IPaddr2::192.168.1.20/24/eth0/192.168.1.255

#chmod 600 /etc/ha.d/authkeys
# service heartbeat Start
#service ldirectord stop
#ipvsadm -L –n

Txt tutorial: http://www.mediafire.com/?5klhsf1zrs8gykn
------------------------------------------------------------
Thanks for reading
--------------------------------------------------------------------------
All my Lab:
Linux Lab -- window and Cisco Lab
to be continued - I will update more.  

No comments:

Install Xposed Inspector and Frida on Genymotion

Today i had some work with android. So i need trace application. I found 2 nice tool can help me: Xposed Inspector and Frida. To setup ther...