ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > MySQL > mysql主从复制 lvs+ keepalived

mysql主从复制 lvs+ keepalived

MySQL 作者：紫翼龙王夜 时间：2015-02-07 11:16:28 0 删除 编辑
最近在网上认识一大牛，目前就知道叫王工。

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

! 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]print
  1. ! Configuration File for keepalived 
  2.  
  3. global_defs { 
  4.      router_id MySQL-ha 
  5. } 
  6.  
  7. vrrp_instance VI_1 { 
  8.      state BACKUP 
  9.      interface eth1 
  10.      virtual_router_id 90 
  11.      priority 99 
  12.      advert_int 1 
  13.      notify_master "/usr/local/mysql/bin/remove_slave.sh" 
  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.  
  24. virtual_server 192.168.1.3 6603 { 
  25.      delay_loop 2 
  26.      lb_algo wrr 
  27.      lb_kind DR 
  28.      persistence_timeout 60 
  29.      protocol TCP 
  30.      real_server 192.168.1.2 6603 { 
  31.      weight 3 
  32.      notify_down /usr/local/mysql/bin/mysql.sh 
  33.      TCP_CHECK { 
  34.      connect_timeout 10 
  35.      nb_get_retry 3 
  36.      delay_before_retry 3 
  37.      connect_port 6603 
  38.      } 
  39.      } 
  40. } 
  41.  
  42. virtual_server 192.168.1.4 6603 { 
  43.      delay_loop 2 
  44.      lb_algo wrr 
  45.      lb_kind DR 
  46.      persistence_timeout 60 
  47.      protocol TCP 
  48.      real_server 192.168.1.2 6603 { 
  49.      weight 3 
  50.      notify_down /usr/local/mysql/bin/mysql.sh 
  51.      TCP_CHECK { 
  52.      connect_timeout 10 
  53.      nb_get_retry 3 
  54.      delay_before_retry 3 
  55.      connect_port 6603 
  56.      } 
  57.      } 
  58. } 

! 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]print
  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]print
  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]print
  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]print
  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]print
  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

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30129545/viewspace-1429368/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 突然35岁，检点我的职业生涯
下一篇： cacti+nagios 之cacti的搭建（-）
请登录后发表评论 登录
全部评论
紫翼龙王夜

注册时间：2015-02-05

  • 博文量
    87
  • 访问量
    414052

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593