mysql主从复制 lvs+ keepalived


一、环境
Master(主机A):192.168.1.1
Slave(主机B) :192.168.1.2  
W-VIP(写入)  :192.168.1.3 
R-VIP(读取)  :192.168.1.4 
Client(测试) :192.168.1.100

操作系统版本:CentOS release 6.5
MySQL数据库版本:5.5.13
keepalived版本:1.2.7
LVS版本:1.26

所有环境均为虚拟机
 

二、设计思路

1. 服务器A和B,通过mysql的slave进程同步数据。
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踢出,其他不变
 

三、架构图

 

四、软件安装
主从两个主机都要装以下软件:
1. MySQL的安装(略)
2. keepalived安装
yum install keepalived
2. LVS安装
yum install ipvsadm

 

五、配置
1. 配置MySQL的主从复制(略)
2. 配置keepalived
Master上的配置
vi /etc/keepalived/keepalived.conf

 

  1. ! Configuration File for keepalived 
  2.  
  3. global_defs { 
  4.      router_id MySQL-ha 
  5.  
  6. vrrp_instance VI_1 { 
  7.      state BACKUP 
  8.      interface eth1 
  9.      virtual_router_id 90 
  10.      priority 100 
  11.      advert_int 1 
  12. notify_master "/usr/local/mysql/bin/remove_slave.sh"
  13.      nopreempt 
  14.      authentication { 
  15.      auth_type PASS 
  16.      auth_pass 1111 
  17.      } 
  18.      virtual_ipaddress { 
  19.      192.168.1.3 label eth1:1 
  20.      192.168.1.4 label eth1:2 
  21.      } 
  22.  
  23. virtual_server 192.168.1.3 6603 {   
  24.      delay_loop 2 
  25.      lb_algo wrr 
  26.      lb_kind DR 
  27.      persistence_timeout 60 
  28.      protocol TCP 
  29.      real_server 192.168.1.1 6603 { 
  30.      weight 3 
  31.      notify_down /usr/local/mysql/bin/mysql.sh 
  32.      TCP_CHECK { 
  33.      connect_timeout 10 
  34.      nb_get_retry 3 
  35.      delay_before_retry 3 
  36.      connect_port 6603 
  37.      } 
  38.      } 
  39.  
  40. virtual_server 192.168.1.4 6603 { 
  41.      delay_loop 2 
  42.      lb_algo wrr 
  43.      lb_kind DR 
  44.      persistence_timeout 60 
  45.      protocol TCP 
  46.      real_server 192.168.1.1 6603 { 
  47.      weight 1 
  48.      notify_down /usr/local/mysql/bin/mysql.sh 
  49.      TCP_CHECK { 
  50.      connect_timeout 10 
  51.      nb_get_retry 3 
  52.      delay_before_retry 3 
  53.      connect_port 6603 
  54.      } 
  55.      } 
  56.      real_server 192.168.1.2 6603 { 
  57.      weight 3 
  58.      TCP_CHECK { 
  59.      connect_timeout 10 
  60.      nb_get_retry 3 
  61.      delay_before_retry 3 
  62.      connect_port 6603 
  63.      } 
  64.      } 


! Configuration File for keepalived global_defs { router_id MySQL-ha } vrrp_instance VI_1 { state BACKUP interface eth1 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 1111 } virtual_ipaddress { 192.168.1.3 label eth1:1 192.168.1.4 label eth1:2 } } virtual_server 192.168.1.3 6603 { delay_loop 2 lb_algo wrr lb_kind DR persistence_timeout 60 protocol TCP real_server 192.168.1.1 6603 { 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 6603 } } } virtual_server 192.168.1.4 6603 { delay_loop 2 lb_algo wrr lb_kind DR persistence_timeout 60 protocol TCP real_server 192.168.1.1 6603 { 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 6603 } } real_server 192.168.1.2 6603 { weight 3 TCP_CHECK { connect_timeout 10 nb_get_retry 3 delay_before_retry 3 connect_port 6603 } } }

keepalived配置成服务并开机启动

  1. cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/ 
  2. cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/ 
  3. cp /usr/local/keepalived/sbin/keepalived /usr/sbin/ 
  4. chkconfig --add keepalived  
  5. chkconfig --level 345 keepalived on 


cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/ cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/ cp /usr/local/keepalived/sbin/keepalived /usr/sbin/ chkconfig --add keepalived chkconfig --level 345 keepalived on

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

  1. #!/bin/bash 
  2. user=u1 
  3. password=12345 
  4. log=/usr/local/mysql/log/remove_slave.log 
  5. echo "`date`" >> $log 
  6. /usr/local/mysql/bin/mysql -u$user -p$password -e "set global read_only=OFF;reset master;stop slave;change master to master_host='localhost';" >> $log 
  7. /bin/sed -i 's#read-only#\#read-only#' /etc/my.cnf 


#!/bin/bash user=u1 password=12345 log=/usr/local/mysql/log/remove_slave.log echo "`date`" >> $log /usr/local/mysql/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

 

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

 

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


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

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

 

 

[plain]view plaincopyprint?

  1. ! Configuration File for keepalived 
  2.  
  3. global_defs { 
  4.      router_id MySQL-ha 
  5.  
  6. vrrp_instance VI_1 { 
  7.      state BACKUP 
  8.      interface eth1 
  9.      virtual_router_id 90 
  10.      priority 99 
  11.      advert_int 1 
  12.      notify_master "/usr/local/mysql/bin/remove_slave.sh" 
  13.      authentication { 
  14.      auth_type PASS 
  15.      auth_pass 1111 
  16.      } 
  17.      virtual_ipaddress { 
  18.      192.168.1.3 label eth1:1 
  19.      192.168.1.4 label eth1:2 
  20.      } 
  21.  
  22. virtual_server 192.168.1.3 6603 { 
  23.      delay_loop 2 
  24.      lb_algo wrr 
  25.      lb_kind DR 
  26.      persistence_timeout 60 
  27.      protocol TCP 
  28.      real_server 192.168.1.2 6603 { 
  29.      weight 3 
  30.      notify_down /usr/local/mysql/bin/mysql.sh 
  31.      TCP_CHECK { 
  32.      connect_timeout 10 
  33.      nb_get_retry 3 
  34.      delay_before_retry 3 
  35.      connect_port 6603 
  36.      } 
  37.      } 
  38.  
  39. virtual_server 192.168.1.4 6603 { 
  40.      delay_loop 2 
  41.      lb_algo wrr 
  42.      lb_kind DR 
  43.      persistence_timeout 60 
  44.      protocol TCP 
  45.      real_server 192.168.1.2 6603 { 
  46.      weight 3 
  47.      notify_down /usr/local/mysql/bin/mysql.sh 
  48.      TCP_CHECK { 
  49.      connect_timeout 10 
  50.      nb_get_retry 3 
  51.      delay_before_retry 3 
  52.      connect_port 6603 
  53.      } 
  54.      } 


! Configuration File for keepalived global_defs { router_id MySQL-ha } vrrp_instance VI_1 { state BACKUP interface eth1 virtual_router_id 90 priority 99 advert_int 1 notify_master "/usr/local/mysql/bin/remove_slave.sh" authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.1.3 label eth1:1 192.168.1.4 label eth1:2 } } virtual_server 192.168.1.3 6603 { delay_loop 2 lb_algo wrr lb_kind DR persistence_timeout 60 protocol TCP real_server 192.168.1.2 6603 { 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 6603 } } } virtual_server 192.168.1.4 6603 { delay_loop 2 lb_algo wrr lb_kind DR persistence_timeout 60 protocol TCP real_server 192.168.1.2 6603 { 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 6603 } } }
keepalived配置成服务并开机启动

 

 

[plain]view plaincopyprint?

  1. cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/ 
  2. cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/ 
  3. cp /usr/local/keepalived/sbin/keepalived /usr/sbin/ 
  4. chkconfig --add keepalived  
  5. chkconfig --level 345 keepalived on 


cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/ cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/ cp /usr/local/keepalived/sbin/keepalived /usr/sbin/ chkconfig --add keepalived chkconfig --level 345 keepalived on
vi /usr/local/mysql/bin/remove_slave.sh

 

 

[plain]view plaincopyprint?

  1. #!/bin/bash 
  2. user=u1 
  3. password=12345 
  4. log=/usr/local/mysql/log/remove_slave.log 
  5. echo "`date`" >> $log 
  6. /usr/local/mysql/bin/mysql -u$user -p$password -e "set global read_only=OFF;reset master;stop slave;change master to master_host='localhost';" >> $log 
  7. /bin/sed -i 's#read-only#\#read-only#' /etc/my.cnf 


#!/bin/bash user=u1 password=12345 log=/usr/local/mysql/log/remove_slave.log echo "`date`" >> $log /usr/local/mysql/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

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

 

 

[plain]view plaincopyprint?

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


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

3. 配置LVS
Master与Slave上的配置相同:
vi /usr/local/bin/lvs_real.sh

 

 

[plain]view plaincopyprint?

  1. #!/bin/bash 
  2. # description: Config realserver lo and apply noarp 
  3.   
  4. SNS_VIP=192.168.1.3 
  5. SNS_VIP2=192.168.1.4 
  6. source /etc/rc.d/init.d/functions 
  7. case "$1" in 
  8.   
  9. start) 
  10.        ifconfig lo:0 $SNS_VIP netmask 255.255.255.255 broadcast $SNS_VIP 
  11.        ifconfig lo:1 $SNS_VIP2 netmask 255.255.255.255 broadcast $SNS_VIP2 
  12.        /sbin/route add -host $SNS_VIP dev lo:0 
  13.        /sbin/route add -host $SNS_VIP2 dev lo:1 
  14.        echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore 
  15.        echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce 
  16.        echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore 
  17.        echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce 
  18.        sysctl -p >/dev/null 2>&1 
  19.        echo "RealServer Start OK" 
  20.        ;; 
  21.   
  22. stop) 
  23.        ifconfig lo:0 down 
  24.        ifconfig lo:1 down 
  25.        route del $SNS_VIP >/dev/null 2>&1 
  26.        route del $SNS_VIP2 >/dev/null 2>&1 
  27.        echo "0" >/proc/sys/net/ipv4/conf/lo/arp_ignore 
  28.        echo "0" >/proc/sys/net/ipv4/conf/lo/arp_announce 
  29.        echo "0" >/proc/sys/net/ipv4/conf/all/arp_ignore 
  30.        echo "0" >/proc/sys/net/ipv4/conf/all/arp_announce 
  31.        echo "RealServer Stoped" 
  32.        ;; 
  33.   
  34. *) 
  35.   
  36.        echo "Usage: $0 {start|stop}" 
  37.        exit 1 
  38. esac 
  39. exit 0 


#!/bin/bash # description: Config realserver lo and apply noarp SNS_VIP=192.168.1.3 SNS_VIP2=192.168.1.4 source /etc/rc.d/init.d/functions case "$1" in start) ifconfig lo:0 $SNS_VIP netmask 255.255.255.255 broadcast $SNS_VIP ifconfig lo:1 $SNS_VIP2 netmask 255.255.255.255 broadcast $SNS_VIP2 /sbin/route add -host $SNS_VIP dev lo:0 /sbin/route add -host $SNS_VIP2 dev lo:1 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 ifconfig lo:1 down route del $SNS_VIP >/dev/null 2>&1 route del $SNS_VIP2 >/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

[plain]view plaincopyprint?

  1. chmod 755 /usr/local/bin/lvs_real.sh 
  2. echo "/usr/local/bin/lvs_real.sh start" >> /etc/rc.local 


chmod 755 /usr/local/bin/lvs_real.sh echo "/usr/local/bin/lvs_real.sh start" >> /etc/rc.local

五、Master和Slave的启动
1. 启动Master上的MySQL
service mysql start
2. 启动Slave上的MySQL
service mysql start
3. 启动Master上的realserver脚本
/usr/local/bin/lvs_real.sh start
4. 启动Slave上的realserver脚本
/usr/local/bin/lvs_real.sh start
5. 启动Master上的keepalived
service keepalived start
6. 启动Slave上的keepalived
service keepalived start


六、测试
1. 查看lvs能否进行负载均衡转发
在Master和Slave上分别执行:
ipvsadm -ln

2. 在Client上验证连通性:
ping 192.168.1.3
ping 192.168.1.4
mysql -u u1 -p12345 -P 6603 -h 192.168.1.3 -e "show variables like 'server_id'"
mysql -u u1 -p12345 -P 6603 -h 192.168.1.4 -e "show variables like 'server_id'"

3. 停掉Master上的MySQL,看写IP否自动切换到Slave,看读IP是否去掉了Master的MySQL
在Master和Slave上分别执行:
ipvsadm -ln
在Client上执行:
mysql -u u1 -p12345 -P 6603 -h 192.168.1.3 -e "show variables like 'server_id'"
mysql -u u1 -p12345 -P 6603 -h 192.168.1.4 -e "show variables like 'server_id'"

4. 停掉Master上的keepalived,看读写VIP是否会迁移到Slave上。
在Master和Slave上分别执行:
ipvsadm -ln
在Client上执行:
mysql -u u1 -p12345 -P 6603 -h 192.168.1.3 -e "show variables like 'server_id'"



在Master和Slave上分别执行:
ipvsadm -ln
在Client上执行:
mysql -u u1 -p12345 -P 6603 -h 192.168.1.3 -e "show variables like 'server_id'"
mysql -u u1 -p12345 -P 6603 -h 192.168.1.4 -e "show variables like 'server_id'"

6. 重启Master的系统,看看切换过程是否正常



七、参考
http://wenku.baidu.com/link?url=17K0UDApzfK18A8F5Of9wl0lmGx6AD_0PQ8GyVqmCsBMSz16xjt2JI88lPAeID2qECs7piUxkzQLyatTtDuEJxwZEy6joIBfBmT3vgTfdUS
http://blog.chinaunix.net/uid-23500957-id-3781918.html
http://blog.chinaunix.net/uid-23500957-id-3781919.html
http://blog.chinaunix.net/uid-20639775-id-3337471.html

转载于:https://my.oschina.net/u/2350399/blog/715737

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值