mysql真正的高可用性設定組群

基於上面的MYSQL 叢集，可以達到MYSQL資料庫完整的備份及同時可讀可寫，現在我們要實現高可用，通過一個IP可以同時訪問各個節點的MYSQL。實現真正的高可用。

上面是兩台資料節點和SQL節點上做LVS + heartbeat 來實現高可用！分別在兩台伺服器上面安裝ipvsadm heartbeat 。我這裡採用yum源來安裝！

1，配置兩台伺服器/etc/hosts檔案

我的如下

127.0.0.1               localhost.localdomain localhost
192.168.0.118           mgm
192.168.0.146           node1
192.168.0.221           node1

2、叢集軟體安裝
用兩台伺服器安裝:
yum -y install ipvsadm    # ipvs管理器   
yum -y install libnet      # 庫檔案
yum -y install e2fsprogs  # 庫檔案
yum -y install heartbeat   # linux-ha
   
modprobe softdog   ##載入軟體狗驅動程式

2- 拷貝設定檔
cp /usr/share/doc/heartbeat-2.1.*/ha.cf /etc/ha.d/
cp /usr/share/doc/heartbeat-2.1.*/authkeys /etc/ha.d/
cp /usr/share/doc/heartbeat-2.1.*/haresources /etc/ha.d/

DRP1＝192.168.0.180
DIP2＝192.168.0.181
VIP＝192.168.0.224
REAL＝192.168.0.146
REAL＝192.168.0.221

配置ha.cf authkeys haresources

1.ha.cf 檔案內容如下

logfile /var/log/ha-log
logfacility     local0
keepalive 2
deadtime 30
warntime 10
initdead 120
udpport 694
ucast eth0 192.168.0.221  //備份機這個是十是主伺服器的地址
auto_failback on
watchdog /dev/watchdog
node    node1
node    node2
ping 192.168.0.118
respawn hacluster /usr/lib/heartbeat/ipfail
apiauth ipfail gid=haclient uid=hacluster

2，authkeys 內容如下

#
#       Authentication file.  Must be mode 600
#
#
#       Must have exactly one auth directive at the front.
#       auth    send authentication using this method-id
#
#       Then, list the method and key that go with that method-id
#
#       Available methods: crc sha1, md5.  Crc doesn't need/want a key.
#
#       You normally only have one authentication method-id listed in this file
#
#       Put more than one to make a smooth transition when changing auth
#       methods and/or keys.
#
#
#       sha1 is believed to be the "best", md5 next best.
#
#       crc adds no security, except from packet corruption.
#               Use only on physically secure networks.
#
auth 1
1 crc
#2 sha1 HI!
#3 md5 Hello!

許可權改為600

chmod 600 authkeys

3,修改haresources 檔案 ，在最後一行新增內容

node1   IPaddr::192.168.0.224/24/eth0:0 ldirectord

至此，heartbeat 配置完成

配置ldirectord

checktimeout=3
checkinterval=1
#fallback=127.0.0.1:80
autoreload=yes
logfile="/var/log/ldirectord.log"
logfile="local0"
emailalert="liuguan269@163.com"
emailalertfreq=3600
emailalertstatus=all
quiescent=yes
       

#Sample configuration for a MySQL virtual service.
virtual = 192.168.0.224:3306
#       real=master.up.com->slave.up.com:3306 gate 10
        real=192.168.0.146:3306 gate 10
        real=192.168.0.221:3306 gate 10
#       fallback=127.0.0.1:3306
        service=mysql
        scheduler=wrr
#       #persistent=600
#       #netmask=255.255.255.255
        protocol=tcp
        checktype=negotiate
        login="client"
        passwd="password123"
        database="cluster"
        request="SELECT i FROM test"
        receive="233"  //可要可不要

四，開啟ip_forward轉寄

修改/etc/sysctl.conf檔案中的

把0改成1

# Controls IP packet forwarding
net.ipv4.ip_forward = 1

在shell環境下執行

sysctl -p

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

五：關閉arp指令碼,成員機使用

switchdr

內容如下

#!/bin/sh

# description: close lo0 and arp_ignore

VIP=192.168.0.224

. /etc/init.d/functions

case "$1" in

    stop)

        echo 0>/proc/sys/net/ipv4/conf/all/arp_announce   ##允許arp解析虛ip

        ;;

    start)

        echo "start Real Server"

        ifconfig lo:0  $VIP netmask 255.255.255.255 broadcast $VIP up

        /sbin/route add -host $VIP dev lo:0

        echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore

        echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce

        echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore

        echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce

        sysctl -p

        ;;

    *)

        echo "Usage: switchdr {start|stop}"

        exit 1

esac

六： 把所有檔案拷到另一個備份SERVER上

scp switchdr 192.168.0.221:/etc/init.d/
scp -r /etc/ha.d 192.168.0.221:/etc
scp /etc/sysconfig/ipvsadm 192.168.0.221:/etc/sysconfig/

注意備份節點的ha.cf檔案

ucast eth0 192.168.0.181 要改成

ucast eth0 192.168.0.180

我這裡在118電腦上串連資料庫

[root@mgm yum.repos.d]# /usr/local/mysql/bin/mysql -u client -p -h 192.168.0.224
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.1.5-alpha-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cc                 |
| cluster            |
| huzi               |
| mysql              |
| ndb_1_fs           |
| test               |
+--------------------+
7 rows in set (0.01 sec)

mysql>

在開一個shell串連資料庫

[root@mgm yum.repos.d]#  /usr/local/mysql/bin/mysql -u client -p -h 192.168.0.224
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.1.5-alpha-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cluster            |
| huzi               |
| mysql              |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql>

查看串連狀態

[root@huzi ha.d]# ipvsadm -L -n
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  192.168.0.224:3306 wrr
  -> 192.168.0.221:3306           Route   10     0          0         
  -> 192.168.0.146:3306           Route   0      0          0  

IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  192.168.0.224:3306 wrr
  -> 192.168.0.221:3306           Route   10     0          1         
  -> 192.168.0.146:3306           Route   10     0          1         
[root@huzi ha.d]#

[root@stu253 etc]# /usr/local/mysql/bin/mysql -u client -p -h 192.168.0.224
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 620 to server version: 5.1.5-alpha-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show
    -> databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cluster            |
| huzi               |
| mysql              |
| test               |
+--------------------+
5 rows in set (0.01 sec)

mysql> exit
Bye
[root@stu253 etc]# /usr/local/mysql/bin/mysql -u client -p -h 192.168.0.224
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.1.5-alpha-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cluster            |
| huzi               |
| mysql              |
| ndb_1_fs           |
+--------------------+
5 rows in set (0.04 sec)

mysql> exit
Bye
[root@stu253 etc]#

   
[root@node1 ha.d]# ipvsadm -L -n -c
[root@huzi ha.d]# ipvsadm -L -n -c
IPVS connection entries
pro expire state       source             virtual            destination
TCP 14:40  ESTABLISHED 192.168.0.253:8232 192.168.0.224:3306 192.168.0.221:3306
TCP 14:56  ESTABLISHED 192.168.0.253:8234 192.168.0.224:3306 192.168.0.221:3306

已經成功執行：ha+mysql 叢集成功應用

用NDB管理節點查看SQL節點串連狀態

[root@mgm yum.repos.d]# /usr/local/mysql/bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: mgm:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.0.146  (Version: 5.1.5, Nodegroup: 0, Master)
id=2 (not connected, accepting connect from node2)

[ndb_mgmd(MGM)] 1 node(s)
id=3    @192.168.0.118  (Version: 5.1.5)

[mysqld(API)]   3 node(s)
id=4    @192.168.0.221  (Version: 5.1.5)
id=5    @192.168.0.146  (Version: 5.1.5)
id=6 (not connected, accepting connect from any host)

ndb_mgm>

以上經過本人兩天的實踐出來的.完全沒有問題!我全部是用虛擬機器來完成的.之間ndb出現了一點問題!重新初始化了NDB.以前的資料全部沒有了(NDB儲存方式的表).讓我不太敢用NDB..改天實現一下MYSQL 主主同步,這樣同樣前面做成高可用!