配置mysql+lvs+keeplived实现Mysql读操作的负载均衡
环境：
test1192.168.46.131master
test2192.168.46.130slave备份test库
test3调度器
1、安装与配置Keepalived
首先在节点test1、test2上安装Keepalived软件，软件安装非常简单。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
[root@test1 ]# yum install -y openssl-devel[root@test1 ~]# wget http://www.keepalived.org/software/keepalived-1.2.1.tar.gz
[root@test1 ~]# tar zxvf keepalived-1.2.1.tar.gz[root@test1 ~]# cd keepalived-1.2.1[root@test1 keepalived-1.2.1]# ./configure --prefix=/usr/local/keepalived[root@test1 keepalived-1.2.1]# make && make install[root@test1 ~]# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/[root@test1 ~]# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/[root@test1 ~]# mkdir /etc/keepalived[root@test1 ~]# cp /usr/local/keepalived/etc/keepalived /keepalived.conf /etc/keepalived/[root@test1 ~]# ln -s /usr/local/keepalived/sbin/keepalived /usr/sbin/[root@test1 ~]# service keepalived startStarting keepalived: [ OK ] |
Keepalived的配置也非常简单，仅仅需要一个配置文件即可完成HAcluster和lvs服务节点监控功能，在通过Keepalived搭建高可用的LVS集群实例中，主、备DirectorServer都需要安装Keepalived软件，安装成功后，默认的配置文件路径为/etc/Keepalived/Keepalived.conf。
一个完整的keepalived配置文件，有三个部分组成，分别是全局定义部分、vrrp实例定义部分以及虚拟服务器定义部分，配置好的文件内容如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
|
! Configuration File for keepalived
global_defs {notification_email {root@localhost}notification_email_from root@localhostsmtp_server 192.168.46.131
smtp_connect_timeout 30
router_id LVS_MASTER}vrrp_instance VI_1 {state MASTER //备库为BACKUP
interface eth2 //通过的网卡
virtual_router_id 51
priority 100 //备库为99
advert_int 1
authentication {auth_type PASSauth_pass 1111
}virtual_ipaddress {192.168.46.100
}}virtual_server 192.168.46.100 3306 {
delay_loop 30
lb_algo rrlb_kind DRpersistence_timeout 50
protocol TCPreal_server 192.168.46.131 3306 {
weight 1
MISC_CHECK{misc_path "/etc/keepalived/check_slave.pl 192.168.46.131"
misc_dynamic}}real_server 192.168.46.130 3306 {
weight 1
MISC_CHECK{misc_path "/etc/keepalived/check_slave.pl 192.168.46.130"
misc_dynamic}}}} |
2，check_slave.pl检测mysql
check_slave.pl是用perl写的一个检测脚本，定时在slave机器上执行showslavestatus\G命令，检查Slave_IO_Running、Slave_SQL_Running、Seconds_Behind_Master三个值。Slave_IO_Running和Slave_SQL_Running有一个值为No就自动从LVS的realserver列表去掉，不再对外提供服务，如果这两个值为Yes，检查Seconds_Behind_Master大于设定的值也会自动从对外服务机器列表里面去掉。三个值同时满足时又会被加入到服务列表，对外提供服务。这个检测脚本内容如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
#!/usr/bin/perl -wuse DBI;
use DBD::mysql;
# CONFIG VARIABLES$SBM = 120;
$db = "test";
$host = $ARGV[0];
$port = 3306;
$user = "root";
$pw = "mysql";
# SQL query$query = "show slave status";
$dbh = DBI->connect("DBI:mysql:$db:$host:$port", $user, $pw, { RaiseError => 0,PrintError => 0 });
if (!defined($dbh)) {
exit 1;
}$sqlQuery = $dbh->prepare($query);
$sqlQuery->execute;
$Slave_IO_Running = "";
$Slave_SQL_Running = "";
$Seconds_Behind_Master = "";
while (my $ref = $sqlQuery->fetchrow_hashref()) {
$Slave_IO_Running = $ref->{'Slave_IO_Running'};
$Slave_SQL_Running = $ref->{'Slave_SQL_Running'};
$Seconds_Behind_Master = $ref->{'Seconds_Behind_Master'};
}$sqlQuery->finish;
$dbh->disconnect();
if ( $Slave_IO_Running eq "No" || $Slave_SQL_Running eq "No" ) {
exit 1;
} else {
if ( $Seconds_Behind_Master > $SBM ) {
exit 1;
} else {
exit 0;
}} |
3、安装配置lvs
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
[root@test1 ~]# yum install -y ipvsadm[root@test1 ~]# lsmod |grep ip_vs[root@test1 ~]# modprobe ip_vs[root@test1 ~]# lsmod |grep ip_vsip_vs 122241 0
这里不能靠进程来判断是不是加载了ip_vs模块，因为这里就是一个命令，没有产生进程。vim /etc/init.d/lvsdrrip#!/bin/bash#DR serverVIP=192.168.46.100
case "$1" in
start)echo "start LVS of DR"
/sbin/ifconfig lo:0 $VIP broadcast $VIP netmask 255.255.255.255 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
;;stop)echo "stop LVS of DR"
/sbin/ifconfig lo:0 down
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 "Usage:$0 {start|stop}"
esacexit1 |
4，test1和test2上安装mysql服务，test1为master，test2为slave
参考http://wolfword.blog.51cto.com/4892126/1290938
5，test3调度器安装lvsdr
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
[root@test3 ~]# vim /etc/init.d/lvsdr#!/bin/bashVIP=192.168.46.100
RIP1=192.168.46.131
RIP2=192.168.46.130
case "$1" in
start)echo "start LVS of DirectorServer DR"
/sbin/iptables -F/sbin/ipvsadm -C/sbin/ifconfig eth0:0 $VIP broadcast $VIP netmask 255.255.255.0 up
/sbin/ipvsadm -A -t $VIP:3306 -s rr
/sbin/ipvsadm -a -t $VIP:3306 -r $RIP1 -g
/sbin/ipvsadm -a -t $VIP:3306 -r $RIP2 -g
/sbin/ipvsadm;;stop)echo "stop LVS of DirectorServer DR"
echo "0" >/proc/sys/net/ipv4/ip_forward
/sbin/ipvsadm -C/sbin/ifconfig eth0:0 down
;;*)[root@test3 ~]#chown +x /etc/init.d/lvsdr |
本文转自陈仲阳0 51CTO博客，原文链接:http://blog.51cto.com/wolfword/1291868