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

1、mysql 主从复制与 lvs+keepalive 族现负载高可用环境主机 A:192,168.5.171主机 B:192,168.5.172W-VIP:192,168.5.173（负责写入）R-VIP:192.168.5.174（负责读取）Client:任意，只要能访问以上三个 IP 即可设计思路1、服务器 A 和 B,通过 mysql 的 slave 进程同步数据。2、通过 keepalived 启用两个虚 IP:W-VIP/R-VIP,一个负责写入，一个负责读取，实现读写分离。3、A 和 B 都存在时，W-VIP 下将请求转发至主机 A,R-VIP 将请求转发给 A 和 B,实现负载均衡

2、。4、当主机 A 异常时，B 接管服务，W-VIP/R-VIP 此时漂到了主机 B 上，此时这两个虚 IP 下都是主机B,实现高可用5、当主机 B 异常时，R-VIP 会将 B 踢出，其他不变具体实现后的效果正常状态ProtLocalAddress:PortSchedulerFlags-RemoteAddress:PortForwardWeightActiveConnInActConnTCP192.168.5.173:3306 wrrpersistent60- 192.168.5.171:3306Local300TCP192.168.5.174:3306 wrrpersistent60- 19

3、2.168.5.172:3306Route300- 192.168.5.171:3306Local100A 故障后，B 的状态ProtLocalAddress:PortSchedulerFlags-RemoteAddress:PortForwardWeightActiveConnInActConnTCP192.168.5.173:3306 wrrpersistent60- 192.168.5.172:3306Local300TCP192.168.5.174:3306 wrrpersistent60- 192.168.5.172:3306Local300一、安装1、mysql 可以根据需要进行安

4、装，此处省略2、lvs+keepalived 的安装In-s/usr/src/kernels/2.6.18-194.el5-x86_64/usr/src/linux安装 lvstar-zxvfipvsadm-1.24.tar.gzcdtar-zxvfipvsadm-1.24makemakeinstall验证ipvsadm-v返回 ipvsadmv1.242005/12/10(compiledwithpoptandIPVSv1.2.1)说明安装成功安装 keepalivedtar-zxvfkeepalived-1.1.20.tar.gzcdkeepalived-1.1.20./configure

5、-prefix=/usr/local/keepalived/makemakeinstallln-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/kee

6、palived/sbin/configure 时注意 UseIPVSFrameworkIPVSsyncdaemonsupport、UseVRRPFramework 要返回 yes,否则无法关联 ipvs 功能二、配置 mysql 的主从Master 的配置vi/etc/f添加如下内容：server-id=1#masterIDbinlog-do-db=ppl#允许同步的库binlog-ignore-db=mysql#忽略同步的库，也就是不能同步的库#配置文件中还需开启 log-bin,例如 log-bin=mysql-binmysql-uroot-p以下内容在 mysql 中执行mysqlgra

7、ntreplicationslaveon*.*tomysqlcreatedatabaseppl;mysqlflushlogs;mysqlshowmasterstatus;sla%dideniedby123456;mysqluseppl;mysqlcreatetabletest(namechar);返回一表格如下，记住 File 的内容，等下 slave 的配置中要用到jn.ysqlishowmastersta-fcus;H411-|File|Positian|Binloig_Io_DB|BinioIgnore_DB|qq1qkImysql-bin.00000TI106|ppi|mysgl|1r

8、owinset(0B00sec)Slave 的配置 vi/etc/f添加如下内容：server-id=2#slaveIDmaster-host=192.168.5.171#指定 master 的地址master-user=slave#同步所用的账号master-password=123456#同步所用的密码master-port=3306#master 上 mysql 的端口replicate-do-db=ppl#要同步的库名replicate-ignore-db=mysql#忽略的库名slave-skip-errors=1062#当同步异常时，那些错误跳过，本例为 1062 错误#log-s

9、lave-updates#同步的同时，也记录自己的 binlog 日志，如果还有台 slave 是通过这台机器进行同步，那需要增加此项，#skip-slave-start#启动时不自动开启 slave 进程#read-only#将库设为只读模式， 只能从 master 同步， 不能直接写入 （避免自增键值冲突） mysql-uroot-p以下内容在 mysql 中执行 mysqlcreatedatabaseppl;mysqlchangemastertomaster_log_file=mysql-bin.000007,master_log=106;mysqlslavestart;mysqlsho

10、wslavestatusG在返回值中查看，如果 slave_IO_Runing 与 slave_SQL_Runing 的值者 B 为 Yes 说明同步成功S1ave_I0_Running：YesS1SQL.Runni勺Yes;ppi通过 lvs+keepalivecR 现负载与热备，并实现读写分离Master 上的配置vi/etc/keepalived/keepalived.conf!ConfigurationFileforkeepalivedglobal_defsrouter_idMySQL-HAvrrp_instanceVI_1stateBACKUPinterfaceeth0virtual

11、_router_id90priority100advert_int1notify_master/usr/local/mysql/bin/remove_slave.shnopreemptauthenticationauth_typePASSauth_virtual_ipaddress192.168.5.173 labeleth0:1192.168.5.174 labeleth0:2virtual_server192.168.5.1733306delay_loop2lb_algowrrlb_kindDRpersistence_timeout60protocolTCPreal_server192.1

12、68.5.1713306weight3notify_down/usr/local/mysql/bin/mysql.shTCP_CHECKconnect_timeout10nb_get_retry3delay_before_retry3connect_port3306virtual_server192.168.5.1743306delay_loop2lb_algowrrlb_kindDRpersistence_timeout60protocolTCPreal_server192.168.5.1713306weight1notify_down/usr/local/mysql/bin/mysql.s

13、hTCP_CHECKconnecttimeout10nb_get_retry3delay_before_retry3connect_port3306real_server192.168.5.1723306weight3TCP_CHECKconnect_timeout10nb_get_retry3delay_before_retry3connect_port3306vi/usr/local/mysql/bin/remove_slave.sh#!/bin/bashuser=rootpassword=123456log=/root/mysqllog/remove_slave.logechodate$

14、log/usr/bin/mysql-u$user-p$password-esetglobalread_only=OFF;resetmaster;stopslave;changemastertomaster_host=localhost;$log/bin/sed-is#read-only#read-only#/etc/fchomd755/usr/local/mysql/bin/remove_slave.shvi/usr/local/mysql/bin/mysql.sh#!/bin/bash/etc/init.d/keepalivedstopSlave 上的配置vi/etc/keepalived/

15、keepalived.conf!ConfigurationFileforkeepalivedglobal_defsrouter_idMySQL-HAvrrp_instanceVI_1stateBACKUPinterfaceeth0virtual_router_id90priority99advert_int1notify_master/usr/local/mysql/bin/remove_slave.shauthenticationauth_typePASSauth_virtual_ipaddress6e9labeleth0:16e9labeleth0:2virtual_server192.1

16、68.5.1733306delay_loop2lb_algowrrlb_kindDRpersistence_timeout60protocolTCPreal_server192.168.5.1723306weight3notify_down/usr/local/mysql/bin/mysql.shTCP_CHECKconnect_timeout10nb_get_retry3delay_before_retry3connect_port3306)virtual_server192.168.5.1743306delay_loop2lb_algowrrlb_kindDRpersistence_tim

17、eout60protocolTCPreal_server192.168.5.1723306weight3notify_down/usr/local/mysql/bin/mysql.shTCP_CHECKconnect_timeout10nb_get_retry3delay_before_retry3connect_port3306)real_server192.168.5.1723306weight3TCP_CHECKconnect_timeout10nb_get_retry3delay_before_retry3connect_port3306)vi/usr/local/mysql/bin/

18、remove_slave.sh#!/bin/bashuser=rootpassword=123456log=/root/mysqllog/remove_slave.logechodate$log/usr/bin/mysql-u$user-p$password-esetglobalread_only=OFF;resetslave;changemastertomaster_host=localhost;$log/bin/sed-is#read-only#read-only#/etc/fchomd755/usr/local/mysql/bin/remove_slave.shvi/usr/local/

19、mysql/bin/mysql.sh#!/bin/bash/etc/init.d/keepalivedstopvi/usr/local/keepalived/bin/lvs-rs.shmaster;stop#!/bin/bashWEB_VIP=192.168.5.174./etc/rc.d/init.d/functionscase$1instart)ifconfiglo:0$WEB_VIPnetmask255.255.255.255broadcast$WEB_VIP/sbin/routeadd-host$WEB_VIPdevlo:0echo1/proc/sys/net/ipv4/conf/lo

20、/arp_ignoreecho2/proc/sys/net/ipv4/conf/lo/arp_announceecho1/proc/sys/net/ipv4/conf/all/arp_ignoreecho2/proc/sys/net/ipv4/conf/all/arp_announcesysctl-p/dev/null2&1echoRealServerStartOK;stop)ifconfiglo:0downroutedel$WEB_VIP/dev/null2&1echo0/proc/sys/net/ipv4/conf/lo/arp_ignoreecho0/proc/sys/net/ipv4/