lvs配合keepalived实现mysql高可用负载均衡及读写分离

ewqw

拓扑结构：


实验规划：
把后端的mysql做成“一主多从”复制架构，主的mysql将提供“写”操作，从的mysql将提供“读”操作，两台服务器做lvs-keepalived实现高可用，正常情况下，一台服务器获得对mysql“写”操作的vip，另一台服务器获得对mysql"读"操作的vip，当用户请求连接“写”vip操作时，将被lvs调度到主的mysql上，当用户请求连接“读”vip操作时，将被lvs调度到从的mysql上。

实验环境：
ipvsadm-1.26-4.el6.x86_64
keepalived-1.2.13-4.el6.x86_64
Linux centos-server 2.6.32-504.el6.x86_64
mysql-5.1


首先配置主从复制：

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 47 48 49 50 51

配置主mysql [iyunv@centos-server ~]# vim/etc/my.cnf  #修改my.cnf [mysqld] port            = 3306 binlog-do-db=dragon    #同步的数据库 replicate-ignore-db=mysql    #不同步的数据库 replicate-ignore-db=information_schema server-id= 1      #server-id要唯一 log-bin=mysql-bin mysql> show master status; +------------------+----------+--------------+------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000037 |      106 | dragon       |                  | +------------------+----------+--------------+------------------+ 1 row in set (0.08 sec) mysql> grant replication slave on *.* to 'back'@'192.168.150.%' identified by 'qwe123'; 1 row in set (0.01 sec)        #添加用户"back"，SLAVE用于登录本机复制数据库日志文件 [iyunv@centos-server ~]# cd /usr/local/mysql/var/ [iyunv@centos-server var]# tar zcf dragon.tar.gz dragon/ scp dragon.tar.gz root@192.168.150.137:/usr/local/mysql/var/  #把数据库复制到SLAVE上 配置从mysql： 修改my.cnf的server-id为2 [iyunv@drbd1 ~]# cd /usr/local/mysql/var/ [iyunv@drbd1 var]#tar xf dragon.tar.gz    #先解压缩复制过来的数据库文件 [iyunv@drbd1 var]#mysql -uroot -pqwe123 mysql> slave stop; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql>  change master to master_host='192.168.150.128',master_user='back',masaster_log_pos=106; #使用用户"back"复制MASTER的"mysql-bin.000025"日志      Query OK, 0 rows affected (0.03 sec) mysql> slave start; Query OK, 0 rows affected (0.01 sec) mysql> exit [iyunv@drbd1 var]# service mysqld restart    #重启SLAVE的mysql [iyunv@drbd1 var]# mysql -uroot -pqwe123 mysql> show slave status\G *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.150.128                   Master_User: back                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000037           Read_Master_Log_Pos: 106                Relay_Log_File: drbd1-relay-bin.000003                 Relay_Log_Pos: 251         Relay_Master_Log_File: mysql-bin.000037              Slave_IO_Running: Yes             Slave_SQL_Running: Yes

在从mysql配置提供“读”操作的vip，并且禁止apr响应和添加路由条目：

1 2 3 4 5 6 7 8 9 10

[iyunv@centos-server ~]# vim /etc/sysctl.conf net.ipv4.conf.all.arp_ignore=1 net.ipv4.conf.all.arp_announce=2 net.ipv4.conf.default.arp_ignore=1 net.ipv4.conf.default.arp_announce=2 net.ipv4.conf.lo.arp_ignore=1 net.ipv4.conf.lo.arp_announce=2 [iyunv@centos-server ~]# sysctl -p [iyunv@centos-server ~]# ifconfig lo:0 192.168.150.201/32 [iyunv@centos-server ~]# route add -host 192.168.150.201 dev lo:0

在从mysql配置提供“写”操作的vip，并且禁止apr响应和添加路由条目：

1 2 3 4 5 6 7 8 9 10

[iyunv@centos-server ~]# vim /etc/sysctl.conf net.ipv4.conf.all.arp_ignore=1 net.ipv4.conf.all.arp_announce=2 net.ipv4.conf.default.arp_ignore=1 net.ipv4.conf.default.arp_announce=2 net.ipv4.conf.lo.arp_ignore=1 net.ipv4.conf.lo.arp_announce=2 [iyunv@centos-server ~]# sysctl -p [iyunv@centos-server ~]# ifconfig lo:0 192.168.150.200/32 [iyunv@centos-server ~]# route add -host 192.168.150.200 dev lo:0

利用lvs把两台服务器做成HA：

1 2 3 4 5 6 7 8 9 10 11

[iyunv@centos-server ~]#  sysctl -w net.ipv4.conf.all.send_redirects=0 net.ipv4.conf.all.send_redirects = 0 [iyunv@centos-server ~]# sysctl -w net.ipv4.conf.default.send_redirects=0 net.ipv4.conf.default.send_redirects = 0 [iyunv@centos-server ~]# sysctl -w net.ipv4.conf.eth3.send_redirects=0 net.ipv4.conf.eth2.send_redirects = 0 [iyunv@centos-server ~]# ipvsadm -A -t 192.168.150.200:3306 -s rr [iyunv@centos-server ~]# ipvsadm -a -t 192.168.150.200:3306 -r 192.168.150.129 -g -w 1 [iyunv@centos-server ~]# ipvsadm -A -t 192.168.150.201:3306 -s rr [iyunv@centos-server ~]# ipvsadm -a -t 192.168.150.201:3306 -r 192.168.150.128 -g -w 1 [iyunv@centos-server ~]# service ipvsadm save

配置keepalived实现高可用：

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 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77

global_defs {    notification_email {      acassen@firewall.loc      failover@firewall.loc      sysadmin@firewall.loc    }    notification_email_from Alexandre.Cassen@firewall.loc    smtp_server 220.181.12.11    smtp_connect_timeout 30    router_id haproxy_DEVEL } vrrp_instance VI_1 {     state mastert    #另一端keepalived配置为slave     interface eth3    #挂载vip的本地网卡     virtual_router_id 51     priority 100    #另一配置文件为“80”     advert_int 1     authentication {         auth_type PASS         auth_pass 1234     }    virtual_ipaddress {         192.168.150.200/24 dev eth3     } } virtual_server 192.168.150.200/24 3306 {  #后端mysql的健康检测       delay_loop 6        lb_algo rr        lb_kind DR        persistence_timeout 50        protocol TCP        real_server 192.168.150.128 3306 {            weight 3            TCP_CHECK {            connect_timeout 10            nb_get_retry 3            delay_before_retry 3            connect_port 3306         }    } } vrrp_instance VI_2 {     state slave    #另一端keepalived配置为“master”     interface eth3     virtual_router_id 52     priority 80 #另一端keepalived配置为“100”     advert_int 1     authentication {         auth_type PASS         auth_pass 1234     }    virtual_ipaddress {         192.168.150.201/24 dev eth3     } } virtual_server 192.168.150.201/24 3306 {     delay_loop 6     lb_algo rr     lb_kind DR     persistence_timeout 50     protocol TCP     real_server 192.168.150.129 3306 {         weight 3         TCP_CHECK {         connect_timeout 10         nb_get_retry 3         delay_before_retry 3         connect_port 3306         }         } } [iyunv@centos-server ~]# service keepalived restart  #两端HA重启

连接vip测试：
  请求“写”操作的vip时，能够被正常调度

1 2 3 4 5 6 7 8 9

[iyunv@drbd2 var]# mysql -uback -pqwe123 -h 192.168.150.200 Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 161 Server version: 5.1.55-log Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>

  请求“读”操作时，能够被正常调度

1 2 3 4 5 6 7 8 9

[iyunv@drbd2 var]# mysql -uread -pqwe123 -h 192.168.150.201 Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 12 Server version: 5.1.55-log Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>

查看lvs当前处理的调度：

1 2 3 4 5 6 7 8 9 10

[iyunv@centos-server ~]#  ipvsadm -Lnc IPVS connection entries pro expire state       source             virtual            destination TCP 14:56  ESTABLISHED 192.168.150.129:53464 192.168.150.200:3306 192.168.150.128:3306 TCP 00:46  NONE        192.168.150.129:0  192.168.150.200:3306 192.168.150.128:3306 [iyunv@centos-server ~]# ipvsadm -Lnc IPVS connection entries pro expire state       source             virtual            destination TCP 01:25  FIN_WAIT    192.168.150.128:49274 192.168.150.201:3306 192.168.150.129:3306 TCP 14:41  ESTABLISHED 192.168.150.128:49275 192.168.150.201:3306 192.168.150.129:3306

keepalived实现故障转移：

1 2	停掉其中一台HA的网卡 [iyunv@centos-server ~]# ifdown eth3

虚拟IP被转移