Lvs+Keepalived+MySQL Cluster架設(shè)高可用負(fù)載均衡Mysql集群
一、前言
MySQL Cluster 是MySQL適合于分布式計(jì)算環(huán)境的高實(shí)用、高冗余版本。它采用了NDB Cluster存儲(chǔ)引擎，允許在1個(gè)Cluster中運(yùn)行多個(gè)MySQL服務(wù)器。在MyQL5.0 及以上的二進(jìn)制版本中、以及與***的Linux版本兼容的RPM中提供了該存儲(chǔ)引擎。MySQL Cluster 是一種技術(shù)，該技術(shù)允許在無共享的系統(tǒng)中部署“內(nèi)存中”數(shù)據(jù)庫的 Cluster。通過無共享體系結(jié)構(gòu)，系統(tǒng)能夠使用廉價(jià)的硬件，而且對(duì)軟硬件無特殊要求。此外，由于每個(gè)組件有自己的內(nèi)存和磁盤，不存在單點(diǎn)故障。MySQL Cluster由一組計(jì)算機(jī)構(gòu)成，每臺(tái)計(jì)算機(jī)上均運(yùn)行著多種進(jìn)程，包括MySQL服務(wù)器，NDB Cluster的數(shù)據(jù)節(jié)點(diǎn)，管理服務(wù)器，以及（可能）專門的數(shù)據(jù)訪問程序。
二、MySQL Cluster基本概念
NDB是一種“內(nèi)存中”的存儲(chǔ)引擎，它具有可用性高和數(shù)據(jù)一致性好的特點(diǎn)。MySQL Cluster能夠使用多種故障切換和負(fù)載平衡選項(xiàng)配置NDB存儲(chǔ)引擎，但在Cluster 級(jí)別上的存儲(chǔ)引擎上做這個(gè)最簡單。MySQL Cluster的NDB存儲(chǔ)引擎包含完整的數(shù)據(jù)集，僅取決于Cluster本身內(nèi)的其他數(shù)據(jù)。
目前，MySQL Cluster的Cluster部分可獨(dú)立于MySQL服務(wù)器進(jìn)行配置。在MySQL Cluster中，Cluster
的每個(gè)部分被視為1個(gè)節(jié)點(diǎn)。
管理(MGM)節(jié)點(diǎn)：這類節(jié)點(diǎn)的作用是管理MySQL Cluster內(nèi)的其他節(jié)點(diǎn)，如提供配置數(shù)據(jù)、啟動(dòng)并停止節(jié)點(diǎn)、運(yùn)行備份等。由于這類節(jié)點(diǎn)負(fù)責(zé)管理其他節(jié)點(diǎn)的配置，應(yīng)在啟動(dòng)其他節(jié)點(diǎn)之前首先啟動(dòng)這類節(jié)點(diǎn)。MGM節(jié)點(diǎn)是用命令“ndb_mgmd”啟動(dòng)的。
數(shù)據(jù)(NDB)節(jié)點(diǎn)：這類節(jié)點(diǎn)用于保存Cluster的數(shù)據(jù)。數(shù)據(jù)節(jié)點(diǎn)的數(shù)目與副本的數(shù)目相關(guān)，是片段的倍數(shù)。例如，對(duì)于兩個(gè)副本，每個(gè)副本有兩個(gè)片段，那么就有4個(gè)數(shù)據(jù)節(jié)點(diǎn)。不過沒有必要設(shè)置多個(gè)副本。數(shù)據(jù)節(jié)點(diǎn)是用命令“ndbd”啟動(dòng)的。
客戶(SQL)節(jié)點(diǎn)：這是用來訪問Cluster數(shù)據(jù)的節(jié)點(diǎn)。對(duì)于MySQL Cluster，客戶端節(jié)點(diǎn)是使用NDB Cluster 存儲(chǔ)引擎的傳統(tǒng)MySQL服務(wù)器。通常，SQL節(jié)點(diǎn)是使用命令“mysqld -ndbcluster”啟動(dòng)的，或?qū)?ldquo;ndbcluster”添加到“my.cnf”后使用“mysqld”啟動(dòng)。
注釋：在很多情況下，術(shù)語“節(jié)點(diǎn)”用于指計(jì)算機(jī)，但在討論MySQL Cluster時(shí)，它表示的是進(jìn)程。在單臺(tái)計(jì)算機(jī)上可以有任意數(shù)目的節(jié)點(diǎn)，為此，我們采用術(shù)語“Cluster主機(jī)”。
管理服務(wù)器(MGM節(jié)點(diǎn))負(fù)責(zé)管理Cluster配置文件和Cluster日志。Cluster中的每個(gè)節(jié)點(diǎn)從管理服務(wù)器檢索配置數(shù)據(jù)，并請(qǐng)求確定管理服務(wù)器所在位置的方式。當(dāng)數(shù)據(jù)節(jié)點(diǎn)內(nèi)出現(xiàn)新的事件時(shí)，節(jié)點(diǎn)將關(guān)于這類事件的信息傳輸?shù)焦芾矸?wù)器，然后將這類信息寫入Cluster日志。
三、環(huán)境
系統(tǒng)：CentOS6.4 32位 9臺(tái)
軟件包（可以去mysql官網(wǎng)下載http://dev.mysql.com/downloads/cluster/）：
MGM：
MySQL-Cluster-gpl-management-7.1.31-1.el6.i686.rpm
MySQL-Cluster-gpl-tools-7.1.31-1.el6.i686.rpm
SQL節(jié)點(diǎn)：
MySQL-Cluster-gpl-client-7.1.31-1.el6.i686.rpm
MySQL-Cluster-gpl-server-7.1.31-1.el6.i686.rpm
NDB節(jié)點(diǎn)：
MySQL-Cluster-gpl-storage-7.1.31-1.el6.i686.rpm
拓?fù)鋱D：
IP規(guī)劃：
四、配置（請(qǐng)先卸載掉與mysql有關(guān)的所有RPM包）
1.LB-Master及LB-Backup配置
(1)LB-Master及LB-Backup安裝keepalived和ipvsadm
- # yum groupinstall "Additional Development" //安裝開發(fā)工具
- # yum groupinstall "Development tools"
- # tar -zxvf keepalived-1.2.1.tar.gz -C /usr/local/src/
- # cd /usr/local/src/keepalived-1.2.1
- # ./cnfigure
- Keepalived configuration
- ------------------------
- Keepalived version : 1.2.1
- Compiler : gcc
- Compiler flags : -g -O2
- Extra Lib : -lpopt -lssl -lcrypto
- Use IPVS Framework : No //配置出現(xiàn)錯(cuò)誤
- IPVS sync daemon support : No
- Use VRRP Framework : Yes
- Use Debug flags : No
- 解決方法：
- # yum install kernel-devel ipvsadm
- # ln -s /usr/src/kernels/2.6.32-358.el6.i686/ /usr/src/linux
- # ./cnfigure //再次配置環(huán)境
- # make //編譯
- # make install //安裝
- # cd /usr/local/etc //keepalived默認(rèn)安裝路徑
- # ll
- drwxr-xr-x. 3 root root 4096 May 24 00:37 keepalived
- drwxr-xr-x. 3 root root 4096 May 24 00:29 rc.d
- drwxr-xr-x. 2 root root 4096 May 24 00:29 sysconfig
- 配置以系統(tǒng)方式service啟動(dòng)
- # cp /usr/local/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/
- # cp /usr/local/etc/sysconfig/keepalived /etc/sysconfig/
- # mkdir /etc/keepalived
- # cp /usr/local/etc/keepalived/keepalived.conf /etc/keepalived/
- # cp /usr/local/sbin/keepalived /usr/sbin/
(2)LB-Master及LB-Backup的keepalived主配置文檔
- # cat /etc/keepalived/keepalived.conf
- #guration File for keepalived
- #global define
- global_defs {
- router_id HaMySQL_1
- }
- vrrp_sync_group VGM {
- group {
- VI_MYSQL
- }
- }
- vrrp_instance VI_MYSQL {
- state MASTER //LB-Backup設(shè)置為BACKUP
- interface eth0
- lvs_sync_daemon_inteface eth0
- virtual_router_id 55
- priority 100 //LB-Backup設(shè)置為90
- advert_int 5
- authentication {
- auth_type PASS
- auth_pass 123456
- }
- virtual_ipaddress {
- 192.168.2.200/24 dev eth0
- }
- }
- ########## LVS MySQL Start ###########
- virtual_server 192.168.2.200 3306 {
- delay_loop 6
- lb_algo rr
- lb_kind DR
- persistence_timeout 6
- protocol TCP
- real_server 192.168.2.50 3306 {
- weight 100
- TCP_CHECK {
- connect_timeout 3
- nb_get_retry 3
- delay_before_retry 3
- connect_port 3306
- }
- }
- real_server 192.168.2.60 3306 {
- weight 100
- TCP_CHECK {
- connect_timeout 3
- nb_get_retry 3
- delay_before_retry 3
- connect_port 3306
- }
- }
- }
- ########## LVS MySQL END #############
2.MGM配置
(1)安裝管理節(jié)點(diǎn)
- # rpm -ivh MySQL-Cluster-gpl-management-7.1.31-1.el6.i686.rpm
- # rpm -ivh MySQL-Cluster-gpl-tools-7.1.31-1.el6.i686.rpm
- # mkdir /etc/mysql-cluster
(2)配置管理節(jié)點(diǎn)
- # vim /etc/mysql-cluster/config.ini //添加以下內(nèi)容
- [ndbd default]
- NoOfReplicas=2
- DataMemory=80M
- IndexMemory=18M
- # TCP/IP options:
- [tcp default]
- portnumber=2202
- # Management process options:
- [ndb_mgmd]
- id=1
- hostname=192.168.2.10
- datadir=/var/lib/mysql-cluster
- # Options for data node
- [ndbd]
- id=2
- hostname=192.168.2.30
- datadir=/var/lib/mysql
- [ndbd]
- id=3
- hostname=192.168.2.40
- datadir=/var/lib/mysql
- [mysqld]
- id=4
- hostname=192.168.2.50
- [mysqld]
- id=5
- hostname=192.168.2.60
（3）啟動(dòng)管理節(jié)點(diǎn)
- # mkdir /var/lib/mysql-cluster
- # ndb_mgmd -f /etc/mysql-cluster/config.ini
- MySQL Cluster Management Server mysql-5.1.73 ndb-7.1.31
- # netstat -tupln
- tcp 0 0 0.0.0.0:1186 0.0.0.0:* LISTEN 17629/ndb_mgmd
3.數(shù)據(jù)節(jié)點(diǎn)（NDB1和NDB2）配置
(1)安裝數(shù)據(jù)節(jié)點(diǎn)
- # rpm -ivh MySQL-Cluster-gpl-storage-7.1.31-1.el6.i686.rpm
- # mkdir /var/lib/mysql
(2)配置數(shù)據(jù)節(jié)點(diǎn)
- # vim /etc/my.cnf
- [mysqld]
- datadir=/var/lib/mysql
- socket=/var/lib/mysql/mysql.sock
- user=mysql
- # Disabling symbolic-links is recommended to prevent assorted security risks
- symbolic-links=0
- [mysqld_safe]
- log-error=/var/log/mysqld.log
- pid-file=/var/run/mysqld/mysqld.pid
- [mysql_cluster]
- ndb-connectstring=192.168.2.10
（3）啟動(dòng)數(shù)據(jù)節(jié)點(diǎn)
- # ndbd --initial //NDB1
- 2014-05-28 00:32:17 [ndbd] INFO -- Angel connected to '192.168.2.10:1186'
- 2014-05-28 00:32:17 [ndbd] INFO -- Angel allocated nodeid: 2
- # ndbd --initial //NDB2
- 2014-05-28 00:33:08 [ndbd] INFO -- Angel connected to '192.168.2.10:1186'
- 2014-05-28 00:33:08 [ndbd] INFO -- Angel allocated nodeid: 3
#p#
4.SQL節(jié)點(diǎn)（SQL1和SQL2）配置
網(wǎng)卡及屏蔽ARP設(shè)置
在現(xiàn)有網(wǎng)卡基礎(chǔ)上添加一塊lo:0網(wǎng)卡
- # vim /etc/sysctl.conf //添加以下兩行
- net.ipv4.conf.all.arp_announce = 2
- net.ipv4.conf.all.arp_ignore = 1
- # sysctl -p
(1)安裝SQL節(jié)點(diǎn)
- # rpm -ivh MySQL-Cluster-gpl-client-7.1.31-1.el6.i686.rpm
- # rpm -ivh MySQL-Cluster-gpl-server-7.1.31-1.el6.i686.rpm //如出錯(cuò),卸載與mysql有關(guān)的rpm包
(2)配置SQL節(jié)點(diǎn)
- # The MySQL server
- [mysqld]
- port = 3306
- socket=/var/lib/mysql/mysql.sock
- ndbcluster
- default-storage-engine=NDBCLUSTER
- skip-name-resolve
- [mysql_cluster]
- ndb-connectstring=192.168.2.10
（3）啟動(dòng)SQL節(jié)點(diǎn)
- # service mysql start //SQL1
- Starting MySQL.. SUCCESS!
- # service mysql start //SQL2，如無法啟動(dòng)，執(zhí)行pkill -9 mysql再啟動(dòng)
- Starting MySQL SUCCESS!
- # netstat -tupln |grep mysql
- tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3475/mysqld
（4）配置遠(yuǎn)程連接(SQL1和SQL2配置一致)
- # mysql
- mysql> grant all on *.* to 'nuo'@'%' identified by '123';
- mysql> flush privileges;
啟動(dòng)順序一定要遵循：MGM→NDB→SQL
五、查看狀態(tài)
1.查看MGM狀態(tài)
- # 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.2.30 (mysql-5.1.73 ndb-7.1.31, Nodegroup: 0, *)
- id=3 @192.168.2.40 (mysql-5.1.73 ndb-7.1.31, Nodegroup: 0)
- [ndb_mgmd(MGM)] 1 node(s)
- id=1 @192.168.2.10 (mysql-5.1.73 ndb-7.1.31)
- [mysqld(API)] 2 node(s)
- id=4 @192.168.2.50 (mysql-5.1.73 ndb-7.1.31)
- id=5 @192.168.2.60 (mysql-5.1.73 ndb-7.1.31)
2.查看LB-Master狀態(tài)
- # service keepalived start
- Starting keepalived: [ OK ]
- # ip addr
- 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN
- link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
- inet 127.0.0.1/8 scope host lo
- inet6 ::1/128 scope host
- valid_lft forever preferred_lft forever
- 2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UNKNOWN qlen 1000
- link/ether 00:0c:29:22:3d:01 brd ff:ff:ff:ff:ff:ff
- inet 192.168.2.20/24 brd 192.168.2.255 scope global eth0
- inet 192.168.2.200/24 scope global secondary eth0
- inet6 fe80::20c:29ff:fe22:3d01/64 scope link
- valid_lft forever preferred_lft forever
- # ipvsadm
- IP Virtual Server version 1.2.1 (size=4096)
- Prot LocalAddress:Port Scheduler Flags
- -> RemoteAddress:Port Forward Weight ActiveConn InActConn
- TCP 192.168.2.200:mysql rr persistent 6
- -> 192.168.2.50:mysql Route 100 0 0
- -> 192.168.2.60:mysql Route 100 0 0
3.查看LB-Backup狀態(tài)
- # service keepalived start
- Starting keepalived: [ OK ]
- # ip addr
- 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN
- link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
- inet 127.0.0.1/8 scope host lo
- inet6 ::1/128 scope host
- valid_lft forever preferred_lft forever
- 2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UNKNOWN qlen 1000
- link/ether 00:0c:29:27:e1:98 brd ff:ff:ff:ff:ff:ff
- inet 192.168.2.21/24 brd 192.168.2.255 scope global eth0
- inet6 fe80::20c:29ff:fe27:e198/64 scope link
- valid_lft forever preferred_lft forever
- # ipvsadm
- IP Virtual Server version 1.2.1 (size=4096)
- Prot LocalAddress:Port Scheduler Flags
- -> RemoteAddress:Port Forward Weight ActiveConn InActConn
- TCP 192.168.2.200:mysql rr persistent 6
- -> 192.168.2.50:mysql Route 100 0 0
- -> 192.168.2.60:mysql Route 100 0 0
六、測(cè)試
1.客戶端1（CentOS6.4 32位，IP：192.168.2.80/24）
- # yum install mysql
- # mysql -h 192.168.2.200 -u nuo -p
- Enter password:
- mysql> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | mysql |
- | ndbinfo |
- | test |
- +--------------------+
- mysql> create database t;
- mysql> use t;
- mysql> create table t2(id int);
- mysql> insert into t2 values(10);
- mysql> insert into t2 values(20);
2.LB-Master查看連接狀態(tài)
- # ipvsadm
- IP Virtual Server version 1.2.1 (size=4096)
- Prot LocalAddress:Port Scheduler Flags
- -> RemoteAddress:Port Forward Weight ActiveConn InActConn
- TCP 192.168.2.200:mysql rr persistent 6
- -> 192.168.2.50:mysql Route 100 1 0
- -> 192.168.2.60:mysql Route 100 0 0
- # ipvsadm -lcn
- IPVS connection entries
- pro expire state source virtual destination
- TCP 14:48 ESTABLISHED 192.168.2.80:49993 192.168.2.200:3306 192.168.2.50:3306
- TCP 00:54 NONE 192.168.2.80:0 192.168.2.200:3306 192.168.2.50:3306
3.客戶端2（CentOS6.4 32位，IP：192.168.2.81/24）
- # yum install mysql
- # mysql -h 192.168.2.200 -u nuo -p
- Enter password:
- mysql> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | mysql |
- | ndbinfo |
- | t |
- | test |
- +--------------------+
- mysql> use t;
- mysql> select * from t2;
- +------+
- | id |
- +------+
- | 20 |
- | 10 |
- +------+
4.LB-Master查看連接狀態(tài)
- # ipvsadm -lcn
- IPVS connection entries
- pro expire state source virtual destination
- TCP 00:36 NONE 192.168.2.81:0 192.168.2.200:3306 192.168.2.60:3306
- TCP 14:18 ESTABLISHED 192.168.2.81:42435 192.168.2.200:3306 192.168.2.60:3306
總結(jié)：在客戶端1上插入數(shù)據(jù)后，服務(wù)器顯示客戶端1連接的是SQL1(192.168.2.50),在客戶端2上進(jìn)行查詢，能查詢到客戶端1輸入的數(shù)據(jù)，服務(wù)器顯示客戶端2連接的是SQL2（192.168.2.60），所以，數(shù)據(jù)是同步的，并且是一致性的。
