mysql真正的高可用性集群
博客已经搬家，请访问如下地址：http://www.czhphp.com
基于上面的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 主主同步,这样同样前面做成高可用!
