MySQL主从复制与lvs+keepalived单点写入读负载均衡高可用实验

一、环境Master（主机A）：192.168.1.1Slave（主机B）：192.168.1.2W-VIP（写入）：192.168.1.3R-VIP（读取）：192.168.1.4Client（测试）：192.168.1.100操作系统版本：CentOSrelease6.4MySQL数据库版本：5.6.14keepalived版本：1.2.7LVS版本：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安装yuminstallkeepalived2.LVS安装yuminstallipvsadm五、配置1.配置MySQL的主从复制（略）2.配置keepalivedMaster上的配置vi/etc/keepalived/keepalived.conf[plain]viewplaincopy1.!ConfigurationFileforkeepalived2.3.global_defs{4.router_idMySQL-ha5.}6.7.vrrp_instanceVI_1{8.stateBACKUP9.interfaceeth110.virtual_router_id9011.priority10012.advert_int113.notify_master/usr/local/mysql/bin/remove_slave.sh14.nopreempt15.authentication{16.auth_typePASS17.auth_pass111118.}19.virtual_ipaddress{20.192.168.1.3labeleth1:121.192.168.1.4labeleth1:222.}23.}24.25.virtual_server192.168.1.36603{26.delay_loop227.lb_algowrr28.lb_kindDR29.persistence_timeout6030.protocolTCP31.real_server192.168.1.16603{32.weight333.notify_down/usr/local/mysql/bin/mysql.sh34.TCP_CHECK{35.connect_timeout1036.nb_get_retry337.delay_before_retry338.connect_port660339.}40.}41.}42.43.virtual_server192.168.1.46603{44.delay_loop245.lb_algowrr46.lb_kindDR47.persistence_timeout6048.protocolTCP49.real_server192.168.1.16603{50.weight151.notify_down/usr/local/mysql/bin/mysql.sh52.TCP_CHECK{53.connect_timeout1054.nb_get_retry355.delay_before_retry356.connect_port660357.}58.}59.real_server192.168.1.26603{60.weight361.TCP_CHECK{62.connect_timeout1063.nb_get_retry364.delay_before_retry365.connect_port660366.}67.}68.}keepalived配置成服务并开机启动[plain]viewplaincopy1.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--addkeepalived5.chkconfig--level345keepalivedonvi/usr/local/mysql/bin/remove_slave.sh[plain]viewplaincopy1.#!/bin/bash2.user=u13.password=123454.log=/usr/local/mysql/log/remove_slave.log5.echo`date`$log6./usr/local/mysql/bin/mysql-u$user-p$password-esetglobalread_only=OFF;resetmaster;stopslave;changemastertomaster_host='localhost';$log7./bin/sed-i's#read-only#\#read-only#'/etc/my.cnfvi/usr/local/mysql/bin/mysql.sh[plain]viewplaincopy1.#!/bin/bash2./etc/init.d/keepalivedstopSlave上的配置vi/etc/keepalived/keepalived.conf[plain]viewplaincopy1.!ConfigurationFileforkeepalived2.3.global_defs{4.router_idMySQL-ha5.}6.7.vrrp_instanceVI_1{8.stateBACKUP9.interfaceeth110.virtual_router_id9011.priority9912.advert_int113.notify_master/usr/local/mysql/bin/remove_slave.sh14.authentication{15.auth_typePASS16.auth_pass111117.}18.virtual_ipaddress{19.192.168.1.3labeleth1:120.192.168.1.4labeleth1:221.}22.}23.24.virtual_server192.168.1.36603{25.delay_loop226.lb_algowrr27.lb_kindDR28.persistence_timeout6029.protocolTCP30.real_server192.168.1.26603{31.weight332.notify_down/usr/local/mysql/bin/mysql.sh33.TCP_CHECK{34.connect_timeout1035.nb_get_retry336.delay_before_retry337.connect_port660338.}39.}40.}41.42.virtual_server192.168.1.46603{43.delay_loop244.lb_algowrr45.lb_kindDR46.persistence_timeout6047.protocolTCP48.real_server192.168.1.26603{49.weight350.notify_down/usr/local/mysql/bin/mysql.sh51.TCP_CHECK{52.connect_timeout1053.nb_get_retry354.delay_before_retry355.connect_port660356.}57.}58.}keepalived配置成服务并开机启动[plain]viewplaincopy1.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--addkeepalived5.chkconfig--level345keepalivedonvi/usr/local/mysql/bin/remove_slave.sh[plain]viewplaincopy1.#!/bin/bash2.user=u13.password=123454.log=/usr/local/mysql/log/remove_slave.log5.echo`date`$log6./usr/local/mysql/bin/mysql-u$user-p$password-esetglobalread_only=OFF;resetmaster;stopslave;changemastertomaster_host='localhost';$log7./bin/sed-i's#read-only#\#read-only#'/etc/my.cnfvi/usr/local/mysql/bin/mysql.sh[plain]viewplaincopy1.#!/bin/bash2./etc/init.d/keepalivedstop3.配置LVSMaster与Slave上的配置相同：vi/usr/local/bin/lvs_real.sh[plain]viewplaincopy1.#!/bin/bash2.#description:Configrealserverloandapplynoarp3.4.SNS_VIP=192.168.1.35.SNS_VIP2=192.168.1.46.source/etc/rc.d/init.d/functions7.case$1in8.9.start)10.ifconfiglo:0$SNS_VIPnetmask255.255.255.255broadcast$SNS_VIP11.ifconfiglo:1$SNS_VIP2netmask255.255.255.255broadcast$SNS_VIP212./sbin/routeadd-host$SNS_VIPdevlo:013./sbin/routeadd-host$SNS_VIP2devlo:114.echo1/proc/sys/net/ipv4/conf/lo/arp_ignore15.echo2/proc/sys/net/ipv4/conf/lo/arp_announce16.echo1/proc/sys/net/ipv4/conf/all/arp_ignore17.echo2/proc/sys/net/ipv4/conf/all/arp_announce18.sysctl-p/dev/null2&119.echoRealServerStartOK20.;;21.22.stop)23.ifconfiglo:0down24.ifconfiglo:1down25.routedel$SNS_VIP/dev/null2&126.routedel$SNS_VIP2/dev/null2&127.echo0/proc/sys/net/ipv4/conf/lo/arp_ignore28