首页 > 代码库 > mysql高可用架构方案之二(keepalived+lvs+读写分离+负载均衡)

mysql高可用架构方案之二(keepalived+lvs+读写分离+负载均衡)

mysql主从复制与lvs+keepalived实现负载高可用




目录
1、前言    4
2、原理    4
2.1、概要介绍    4
2.2、工作原理    4
2.3、实际作用    4
3方案    4
3.1、环境    4
3.2、架构图    5
3.3、设计原理    6
4、相关软件安装    6
4、配置mysql的主从    7
5、通过lvs+keepalived实现负载与热备,并实现读写分离    8

 

1、前言
最近研究了下高可用的东西,这里总结一下mysql主从复制读写分离度的高可用方案,可以提高服务器的使用效率,也可以提高提高维护效率。同时应用的效率也会有一定的提升,如果改造需要应用修改读取的ip地址与写入的ip地址,改造起来还算容易。
2、原理      
2.1、概要介绍
如果将TCP/IP划分为5层,则Keepalived就是一个类似于3~5层交换机制的软件,具有3~5层交换功能,其主要作用是检测web服务器的状 态,如果某台web服务器故障,Keepalived将检测到并将其从系统中剔除,当该web服务器工作正常后Keepalived自动将其加入到服务器 群中,这些工作全部自动完成,而不需要人工干预,只需要人工修复故障的web服务器即可。
2.2、工作原理
Keepalived基于VRRP协议来实现高可用解决方案,利用其避免单点故障,通常这个解决方案中,至少有2台服务器运行Keepalived,即一 台为MASTER,另一台为BACKUP,但对外表现为一个虚拟IP,MASTER会发送特定消息给BACKUP,当BACKUP收不到该消息时,则认为 MASTER故障了,BACKUP会接管虚拟IP,继续提供服务,从而保证了高可用性,3层机理是发送ICMP数据包即PING给某台服务器,如果不痛,则认为其故障,并从服务器群中剔除。4层机理是检测TCP端口号状态来判断某台服务器是否故障,如果故障,则从服务器群中剔除。5层机理是根据用户的设定检查某个服务器应用程序是否正常运行,如果不正常,则从服务器群中剔除。3、
2.3、实际作用
Keepalived+lvs主要用作RealServer的健康检查,以及负载均衡设备MASTER和BACKUP之间failover的实现。
3方案
本案例先使用两台linux做双机MASTER-SLAVE高可用,实现都写分离,用于提高查询性能),采用MYSQL5.6.x的半同步实现数据复制和同步,使用keepalived来监控MYSQL和提供读写VIP浮动。Keepalived在这里主要用作RealServer的健康状态检查以及LoadBalance主机和BackUP主机之间failover的实现
任何一台主机宕机都不会影响对外提供服务(读写vip可以浮动),保持服务的高可用。

3.1、环境
主机A:192.168.150.171
主机B:192.168.150.172
W-VIP:192.168.150.173  (负责写入)
R-VIP:192.168.150.174   (负责读取)
Client:任意,只要能访问以上三个IP即可

3.2、架构图
具体架构图如下:
 
3.3、设计原理(异常情况)
1、    服务器A和B,通过mysql的slave进程是用binlog同步数据。
2、    通过keepalived启用两个虚IP:W-VIP/R-VIP,一个负责写入,一个负责读取,实现读写分离。
3、    A和B都存在时,W-VIP下将请求转发至主机A,R-VIP将请求转发给A和B,实现负载均衡。
4、    当主机A异常时,B接管服务,W-VIP/R-VIP此时漂到了主机B上,此时这两个虚IP下都是主机B,实现高可用
5、    当主机B异常时,R-VIP会将B踢出,其他不变

具体实现后的效果
正常状态
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  192.168.150.173:3306 wrr persistent 60
  -> 192.168.150.171:3306           Local   3      0          0         
TCP  192.168.150.174:3306 wrr persistent 60
  -> 192.168.150.172:3306           Route   3      0          0         
  -> 192.168.150.171:3306           Local   1      0          0         

A故障后,B的状态
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  192.168.150.173:3306 wrr persistent 60
  -> 192.168.150.172:3306           Local   3      0          0         
TCP  192.168.150.174:3306 wrr persistent 60
  -> 192.168.150.172:3306           Local   3      0          0  
架构图

4、相关软件安装
1、    mysql  可以根据需要进行安装,此处省略
2、    lvs+keepalived的安装

关联lvs与keepalived的ipvs所需的内核信息
ln -s /usr/src/kernels/2.6.18-194.el5-x86_64/ /usr/src/linux

安装lvs
下载:wget http://www.linuxvirtualserver.org/software/kernel-2.6/ipvsadm-1.24.tar.gz
tar -zxvf ipvsadm-1.24.tar.gz
cd tar -zxvf ipvsadm-1.24
make
make install
yum install ipv* 安装
验证
ipvsadm –v
ipvsadm v1.24 2003/06/07 (compiled with getopt_long and IPVS v1.2.0)说明安装成功

安装keepalived
tar –zxvf keepalived-1.2.12.tar.gz
cd keepalived-1.2.12
./configure --prefix=/usr/local/keepalived/
make
make install
ln -s /usr/local/keepalived/etc/keepalived /etc/
ln -s /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/
ln -s /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
ln -s /usr/local/keepalived/bin/genhash /bin/
ln -s /usr/local/keepalived/sbin/keepalived /sbin/

configure时注意Use IPVS Framework、IPVS sync daemon support 、Use VRRP Framework要返回yes,否则无法关联ipvs功能

4、配置mysql的主从

Master(210.171)的配置
vi /etc/my.cnf

添加如下内容:
server-id = 1    ##master ID
binlog-do-db = ppl  ##允许同步的库
binlog-ignore-db = mysql  ##忽略同步的库,也就是不能同步的库

##配置文件中还需开启log-bin,例如log-bin = mysql-bin

mysql –uroot –p
以下内容在mysql中执行
mysql> grant replication slave on *.* to ‘repdb01’@’%’ identified by ‘123456‘;
mysql>create database db01;
mysql>flush logs;
mysql>show master status;
mysql>use db01
mysql> create table test(name char);



返回一表格如下,记住File的内容,等下slave的配置中要用到
 

Slave的配置
vi /etc/my.cnf

添加如下内容:
server-id = 2  ##slave ID
master-host = 192.168.150.171  ##指定master的地址
master-user = repdb01    ##同步所用的账号
master-password = 123456   ##同步所用的密码
master-port = 3306     ##master上mysql的端口
replicate-do-db = db01   ##要同步的库名
replicate-ignore-db = mysql  ##忽略的库名
slave-skip-errors = 1062   ##当同步异常时,那些错误跳过,本例为1062错误
#log-slave-updates  ##同步的同时,也记录自己的binlog日志,如果还有台slave是通过这台机器进行同步,那需要增加此项,
#skip-slave-start  ##启动时不自动开启slave进程
#read-only  ##将库设为只读模式,只能从master同步,不能直接写入(避免自增键值冲突)

mysql –uroot –p
以下内容在mysql中执行
mysql>create database db01;
mysql>change master to master_log_file=’mysql-bin.000007’,master_log=106;
mysql>slave start;
mysql>show slave status \G
在返回值中查看,如果slave_IO_Runing与slave_SQL_Runing的值都为Yes说明同步成功
 

5、通过lvs+keepalived实现负载与热备,并实现读写分离

Master上的配置
vi /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
   router_id MySQL-HA
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 90
    priority 100
    advert_int 1
    notify_master "/usr/local/mysql/bin/remove_slave.sh"
    nopreempt
    authentication {
        auth_type PASS
        auth_pass abcd1234
    }
    virtual_ipaddress {
        192.168.150.173 label eth0:1
        192.168.150.174 label eth0:2
    }
}

virtual_server 192.168.150.173 3306 {
    delay_loop 2
lb_algo wrr
lb_kind DR
    persistence_timeout 60
    protocol TCP
    real_server 192.168.150.171 3306 {
    weight 3
    notify_down /usr/local/mysql/bin/mysql.sh
    TCP_CHECK {
            connect_timeout 10
            nb_get_retry 3
            delay_before_retry 3
        connect_port 3306
        }
    }
}

virtual_server 192.168.150.174 3306 {
    delay_loop 2
    lb_algo wrr
    lb_kind DR
    persistence_timeout 60
    protocol TCP
    real_server 192.168.150.171 3306 {
    weight 1
    notify_down /usr/local/mysql/bin/mysql.sh
    TCP_CHECK {
            connect_timeout 10
            nb_get_retry 3
            delay_before_retry 3
        connect_port 3306
        }
    }
    real_server 192.168.150.172 3306 {
    weight 3
    TCP_CHECK {
            connect_timeout 10
            nb_get_retry 3
            delay_before_retry 3
        connect_port 3306
        }
    }
}

vi /usr/local/mysql/bin/remove_slave.sh

#!/bin/bash
user=root
password=123456
log=/root/mysqllog/remove_slave.log
#--------------------------------------------------------------------------------------
echo "`date`" >> $log
/usr/bin/mysql -u$user -p$password -e "set global read_only=OFF;reset master;stop slave;change master to master_host=‘localhost‘;" >> $log
/bin/sed -i ‘s#read-only#\#read-only#‘ /etc/my.cnf

chomd 755 /usr/local/mysql/bin/remove_slave.sh

vi /usr/local/mysql/bin/mysql.sh

#!/bin/bash
/etc/init.d/keepalived stop

Slave上的配置

vi /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
   router_id MySQL-HA
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 90
    priority 99
    advert_int 1
    notify_master "/usr/local/mysql/bin/remove_slave.sh"
    authentication {
        auth_type PASS
        auth_pass ppl.com
    }
    virtual_ipaddress {
        192.168.150.173 label eth0:1
        192.168.150.174 label eth0:2
    }
}

virtual_server 192.168.150.173 3306 {
    delay_loop 2
    lb_algo wrr
    lb_kind DR
    persistence_timeout 60
    protocol TCP
    real_server 192.168.150.172 3306 {
        weight 3
    notify_down /usr/local/mysql/bin/mysql.sh
    TCP_CHECK {
            connect_timeout 10
            nb_get_retry 3
            delay_before_retry 3
        connect_port 3306
        }
    }
}

virtual_server 192.168.150.174 3306 {
    delay_loop 2
    lb_algo wrr
    lb_kind DR
    persistence_timeout 60
    protocol TCP
    real_server 192.168.150.172 3306 {
        weight 3
    notify_down /usr/local/mysql/bin/mysql.sh
    TCP_CHECK {
            connect_timeout 10
            nb_get_retry 3
            delay_before_retry 3
        connect_port 3306
        }
    }
#    real_server 192.168.150.172 3306 {
#        weight 3
#        TCP_CHECK {
#            connect_timeout 10
#            nb_get_retry 3
#            delay_before_retry 3
#            connect_port 3306
#        }
#    }
}

vi /usr/local/mysql/bin/remove_slave.sh

#!/bin/bash
user=root
password=123456
log=/root/mysqllog/remove_slave.log
#--------------------------------------------------------------------------------------
echo "`date`" >> $log
/usr/bin/mysql -u$user -p$password -e "set global read_only=OFF;reset master;stop slave;change master to master_host=‘localhost‘;" >> $log
/bin/sed -i ‘s#read-only#\#read-only#‘ /etc/my.cnf

chomd 755 /usr/local/mysql/bin/remove_slave.sh

vi /usr/local/mysql/bin/mysql.sh

#!/bin/bash
/etc/init.d/keepalived stop

vi /usr/local/keepalived/bin/lvs-rs.sh

#!/bin/bash
WEB_VIP=192.168.150.174

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

case "$1" in
start)
       ifconfig lo:0 $WEB_VIP netmask 255.255.255.255 broadcast $WEB_VIP
       /sbin/route add -host $WEB_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 $WEB_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"
       ;;
status)
        # Status of LVS-DR real server.
        islothere=`/sbin/ifconfig lo:0 | grep $WEB_VIP`
        isrothere=`netstat -rn | grep "lo:0" | grep $web_VIP`
        if [ ! "$islothere" -o ! "isrothere" ];then
            # Either the route or the lo:0 device
            # not found.
            echo "LVS-DR real server Stopped."
        else
            echo "LVS-DR Running."
        fi
;;
*)
        # Invalid entry.
        echo "$0: Usage: $0 {start|status|stop}"
        exit 1
;;
esac
exit 0

chmod 755 /usr/local/keepalived/bin/lvs-rs.sh
echo “/usr/local/keepalived/bin/lvs-rs.sh start” >>/etc/rc.local

vi /etc/my.cnf
将这两个参数前边的 # 去掉,重启mysql
#skip-slave-start  
#read-only
登陆mysql,手动将slave进程启动
mysql>slave start;

先启动master上的keepalived,正常后再启动slave上的。
启动后 主库可以查看ip a
[root@rac3 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue
    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 qlen 1000
    link/ether 00:50:56:95:06:1f brd ff:ff:ff:ff:ff:ff
    inet 192.168.150.171.171/24 brd 192.168.0.255 scope global eth0
    inet 192.168.150.173/32 scope global eth0:1
    inet 192.168.150.174/32 scope global eth0:2
    inet6 fe80::250:56ff:fe95:61f/64 scope link
       valid_lft forever preferred_lft forever
3: sit0: <NOARP> mtu 1480 qdisc noop
link/sit 0.0.0.0 brd 0.0.0.0
slave上查看
[root@rac1 keepalive]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet 192.168.150.174/32 brd 192.168.150.174 scope global lo:0
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast qlen 1000
    link/ether 00:50:56:95:5e:b4 brd ff:ff:ff:ff:ff:ff
    inet 192.168.150.188/24 brd 192.168.0.255 scope global eth0
    inet 192.168.150.252/24 brd 192.168.0.255 scope global secondary eth0:1
    inet 192.168.150.186/24 brd 192.168.0.255 scope global secondary eth0:4
    inet6 fe80::250:56ff:fe95:5eb4/64 scope link
       valid_lft forever preferred_lft forever
3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast qlen 1000
    link/ether 00:50:56:95:11:ba brd ff:ff:ff:ff:ff:ff
    inet 10.10.10.188/24 brd 10.10.10.255 scope global eth1
    inet 169.254.157.163/16 brd 169.254.255.255 scope global eth1:1
    inet6 fe80::250:56ff:fe95:11ba/64 scope link
       valid_lft forever preferred_lft forever
4: sit0: <NOARP> mtu 1480 qdisc noop
link/sit 0.0.0.0 brd 0.0.0.0

发现210.174 读的vip 在主备机上都可以看到
210.173 写入vip在主上才能看到

后续多台实验进行中,敬请等待

mysql高可用架构方案之二(keepalived+lvs+读写分离+负载均衡)