lvs centos mysql 读负载均衡_keepalived+lvs+mysql cluster架构实现负载均衡

一、环境

用途

ip

os

memory

调度器

172.16.48.204

CentOS 5.5 64bit

32G

sql节点1

172.16.48.206

CentOS 5.5 64bit

32G

sql节点2

172.16.48.207

CentOS 5.5 64bit

32G

sql节点3

172.16.48.208

CentOS 5.5 64bit

32G

sql节点4

172.16.48.211

CentOS 5.5 64bit

32G

VIP设置为：172.100.100.251 (注：不能和调度器、sql节点在一个网段上)

在调度器172.16.48.204电脑上，用route add default dev eth1

命令添加路由，使得172.16.48.204节点有对100网段的访问权限。

二、架构图

这里调度器只用了一个节点，没有设置Backup

三、软件下载

lvs:    http://www.linuxvirtualserver.org/software/kernel-2.6/ipvsadm-1.24.tar.gz

keepalived:     http://www.keepalived.org/download.html

四、软件安装

编辑lvs的时候需要操作系统的内核，不然会报错

configure: error:

!!! OpenSSL is not properly installed on your system. !!!

!!! Can not include OpenSSL headers files. !!!

解决方法：yum install openssl*

可能 还会报错：libipvs.c:253: 错误：提领指向不完全类型的指针

解决方法：yum -y install kernel-devel    可参考：http://692344.blog.51cto.com/blog/682344/804278

如果还是不行，可能是由于lvs keepalived的版本过高

1.在调度器172.16.48.204上安装lvs

ln -s /usr/src/kernels/2.6.18-164.el5-i686/ /usr/src/linux

tar zxvf ipvsadm-1.24.tar.gz

cd ipvsadm-1.24

make && make install

2.在调度器172.16.48.204上安装keepalived

tar zxvf keepalived-1.1.19.tar.gz

cd keepalived-1.1.19

./configure --prefix=/usr/local/keepalived

make

make install

cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/

cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/

mkdir /etc/keepalived

3.配置keepalived.conf

vi /etc/keepalived/keepalived.conf

添加如下内容：

global_defs {

router_id HaMySQL_1

}

vrrp_sync_group VGM {

group {

VI_MYSQL

}

}

vrrp_instance VI_MYSQL {

state MASTER

interface eth1

virtual_router_id 100

priority 100

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

172.100.100.251/24  etho label eth0:1

}

}

virtual_server 172.100.100.251 3306 {

delay_loop 6

lb_algo rr

lb_kind DR

# nat_mask 255.255.0.0

#persistence_timeout 50

protocol TCP

real_server 172.16.48.206 3306 {

weight 3

TCP_CHECK {

connect_timeout 3

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

}

real_server 172.16.48.207 3306 {

weight 3

TCP_CHECK {

connect_timeout 3

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

}

real_server 172.16.48.208 3306 {

weight 3

TCP_CHECK {

connect_timeout 3

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

}

real_server 172.16.48.211 3306 {

weight 3

TCP_CHECK {

connect_timeout 3

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

}

}

4.sql服务节点的配置

vi /etc/rc.d/init.d/realserver.sh 添加如下配置：

#!/bin/bash

# description: Config realserver lo and apply noarp

SNS_VIP=172.100.100.251

/etc/rc.d/init.d/functions

case "$1" in

start)

ifconfig lo:0 $SNS_VIP netmask 255.255.255.255 broadcast $SNS_VIP

/sbin/route add -host $SNS_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 >/dev/null 2>&1

echo "RealServer Start OK"

;;

stop)

ifconfig lo:0 down

route del $SNS_VIP >/dev/null 2>&1

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

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

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

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

echo "RealServer Stoped"

;;

*)

echo "Usage: $0 {start|stop}"

exit 1

esac

exit 0

添加加可执行的权限chmod +x /etc/rc.d/init.d/realserver.sh

注：四台sql节点都要进行此步操作

五、启动LVS节点(mysql cluster中的sql节点)

/etc/rc.d/init.d/realserver.sh start

六、启动调度器(keepalived的服务器端启动)

/etc/rc.d/init.d/keepalived start

启动后可以用命令 tail -f /var/log/messages 进行查看

若看到如下提示，则表示配置成功。

[root@sg204 mysql-cluster]# tail -f /var/log/messages

May 29 17:21:01 sg204 Keepalived_vrrp: Opening file '/etc/keepalived/keepalived.conf'.

May 29 17:21:01 sg204 Keepalived_vrrp: Configuration is using : 63921 Bytes

May 29 17:21:01 sg204 Keepalived_healthcheckers: Using LinkWatch kernel netlink reflector...

May 29 17:21:01 sg204 Keepalived_vrrp: Using LinkWatch kernel netlink reflector...

May 29 17:21:01 sg204 Keepalived_healthcheckers: Activating healtchecker for service [172.16.48.206:3306]

May 29 17:21:01 sg204 Keepalived_healthcheckers: Activating healtchecker for service [172.16.48.207:3306]

May 29 17:21:01 sg204 Keepalived_healthcheckers: Activating healtchecker for service [172.16.48.208:3306]

May 29 17:21:01 sg204 Keepalived_healthcheckers: Activating healtchecker for service [172.16.48.211:3306]

May 29 17:21:01 sg204 Keepalived_vrrp: VRRP sockpool: [ifindex(3), proto(112), fd(10,11)]

May 29 17:21:02 sg204 Keepalived_vrrp: VRRP_Instance(VI_MYSQL) Transition to MASTER STATE

May 29 17:21:03 sg204 Keepalived_vrrp: VRRP_Instance(VI_MYSQL) Entering MASTER STATE

May 29 17:21:03 sg204 Keepalived_vrrp: VRRP_Instance(VI_MYSQL) setting protocol VIPs.

May 29 17:21:03 sg204 Keepalived_healthcheckers: Netlink reflector reports IP 172.100.100.251 added

May 29 17:21:03 sg204 Keepalived_vrrp: VRRP_Instance(VI_MYSQL) Sending gratuitous ARPs on eth1 for 172.100.100.251

May 29 17:21:03 sg204 Keepalived_vrrp: VRRP_Group(VGM) Syncing instances to MASTER state

May 29 17:21:03 sg204 avahi-daemon[4768]: Registering new address record for 172.100.100.251 on eth1.

May 29 17:21:03 sg204 Keepalived_vrrp: Netlink reflector reports IP 172.100.100.251 added

May 29 17:21:08 sg204 Keepalived_vrrp: VRRP_Instance(VI_MYSQL) Sending gratuitous ARPs on eth1 for 172.100.100.251

在调度器上用ipvsadm命令查看，可以看到如下信息

[root@sg204 mysql-cluster]# ipvsadm

IP Virtual Server version 1.2.1 (size=4096)

Prot LocalAddress:Port Scheduler Flags

-> RemoteAddress:Port           Forward Weight ActiveConn InActConn

TCP  172.100.100.251:mysql rr

-> 172.16.48.211:mysql          Route   3      0          0

-> 172.16.48.208:mysql          Route   3      0          0

-> 172.16.48.207:mysql          Route   3      0          0

-> 172.16.48.206:mysql          Route   3      0          0

七、远程应用通过vip访问mysql数据库要远程应用程序访问mysql数据库，还需要一个远程访问的账号和密码，在mysql刚创建后，mysql是没有远程访问账号的，

在sql节点上增加远程认证客户：

grant 权限1,权限2,…权限n on 数据库名称.表名称 to 用户名@用户地址 identified by ‘连接口令’

Mysql>:select user,host,password from mysql.user;

Mysql>: grant all privileges on *.* to admin@"%"  identified by '111111' with grant option;

八、测试

在其他 节点上(调度器和sql节点之外的电脑)，ping 172.100.100.251发现可以ping通

这里选用的是172.16.48.201节点：

[root@sg201 ~]# ping 172.100.100.251

PING 172.100.100.251 (172.100.100.251) 56(84) bytes of data.

64 bytes from 172.100.100.251: icmp_seq=1 ttl=64 time=0.093 ms

64 bytes from 172.100.100.251: icmp_seq=2 ttl=64 time=0.164 ms

64 bytes from 172.100.100.251: icmp_seq=3 ttl=64 time=0.162 ms

在172.16.48.201上安装mysql客户端后，用命令mysql -u admin -p -h 172.100.100.251测试发现可以 连接数据库，

将172.16.48.208上的mysql服务停掉后，这时在高度器上tail -f var/log/messages会看到调度器自动将无效sql节点移除lvs了

May 30 11:04:11 sg204 Keepalived_healthcheckers: TCP connection to [172.16.48.208:3306] failed !!!

May 30 11:04:11 sg204 Keepalived_healthcheckers: Removing service [172.16.48.208:3306] from VS [172.100.100.251:3306]

再将172.16.48.208上的mysql服务启动起来，这时

May 30 11:06:53 sg204 Keepalived_healthcheckers: TCP connection to [172.16.48.208:3306] success.

May 30 11:06:53 sg204 Keepalived_healthcheckers: Adding service [172.16.48.208:3306] to VS [172.100.100.251:3306]

哈哈！成功搞定