Keepalived+Lvs+Mysql主主复制_MySQL
| 一简介 Keepalived+lvs+mysql主主复制是比较常用的一种Mysql高可用方案，其中lvs 提供读负载均衡，Keepalived通过虚拟vip漂移实现故障自动转移，而Mysql虽然配置成主主复制，但是一般都是采用单点写入来保证数据一致性。 整个方案实现非常简单，仅需两台服务器即可，安装配置也相对简单，但需要自己写脚本实现keepalived服务的开启和关闭，适用于只有两台数据库服务器并且未实现读写分离的场景。系统整体结构框图如下
二环境搭建 2.1 环境配置如下
2.2 mysql master-master复制配置 主主复制配置本质上就是配置两台服务器互相的主从，实现比较简单，大致步骤如下 2.2.1 在Master上创建一个复制账户 mysql>grantreplication slave on *.* to rep71@10.0.11.244identified by‘123456’; mysql>flushprivileges;
2.2.2 修改两台mysql配置文件my.cnf如下 ##Replication General Config (both master and slave)server-id= 101log-bin= mysql-log-binauto-increment-increment= 2auto-increment-offset= 1binlog_format= rowrelay_log= mysql-relay-bin
2.2.3 在Master服务器上执行show master status，记录下File和Position mysql>show master status/G;***************************1. row ***************************File:mysql-log-bin.000005Position:327933Binlog_Do_DB:Binlog_Ignore_DB:test,mysql,information_schema,performance_schema 2.2.4 在Slave服务上执行 mysql> slave stop;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> change master to-> master_host='10.0.11.243',-> master_user='rep71',-> master_password='123456',-> master_log_file=' mysql-log-bin.000005',-> master_log_pos=327933;Query OK, 0 rows affected (0.01 sec)mysql> start slave; 2.2.5重复以上四个步骤，在Slave服务器上创建账户rep72，show master status，在Master上start slave，这样mysql的主主同步就配置好了，分别在两台服务器上运行show slave status，如果结果都显示Slave_IO_Running: Yes，Slave_SQL_Running: Yes，则证明同步正常。
2.3 Keepalived的安装 2.3.1 在 master、backup 服务器都进行安装： wget http://www.linuxvirtualserver.org/software/kernel-2.6/ipvsadm-1.24.tar.gzsudotar zxvf ipvsadm-1.24.tar.gzcdipvsadm-1.24sudoln -s /usr/src/kernels/2.6.32-358.el6.x86_64/ /usr/src/linuxmakesudomake install
2.4 Keepalived 的安装 2.4.1在 master、backup 服务器都进行安装： wget http://www.keepalived.org/software/keepalived-1.1.19.tar.gztarzxvf keepalived-1.1.19.tar.gzcdkeepalived-1.1.19yuminstall -y openssl openssl-develsudoyum install popt-devel –y./configure--sysconf=/etc/ --with-kernel-dir=/usr/src/kernels/2.6.18-308.el5-x86_64/makesudomake installln -s /usr/local/sbin/keepalived /sbin/ 2.4.2 Master上的Keepalived 的配置 global_defs{router_id mysqlmha1}vrrp_scriptcheck_run {script"/etc/keepalived/check_mysqll.sh"interval 1}vrrp_instanceVI_1 {state MASTERinterface em1virtual_router_id 51priority 100advert_int 1authentication {auth_type PASSauth_pass 1111}track_script {check_run}virtual_ipaddress {10.0.11.190 dev em1 label em1:110.0.11.191 dev em1 label em1:2}}virtual_server10.0.11.190 3306 {delay_loop6lb_algorrlb_kindDRpersistence_timeout20protocolTCPsorry_server10.0.11.244 3306real_server10.0.11.243 3306 {weight3TCP_CHECK{connect_timeout3nb_get_retry3delay_before_retry3connect_port3306}}}virtual_server10.0.11.191 3306 {delay_loop6lb_algorrlb_kindDR#persistence_timeout20protocolTCPreal_server10.0.11.243 3306 {weight3TCP_CHECK{connect_timeout3nb_get_retry3delay_before_retry3connect_port3306}}real_server10.0.11.244 3306 {weight3TCP_CHECK{connect_timeout3nb_get_retry3delay_before_retry3connect_port3306}}}2.4.3 Backup 的 keepalived 的配置 global_defs {router_id mysqlmha1}vrrp_script check_run {script "/etc/keepalived/check_mysqll.sh"interval 1}vrrp_instance VI_1 {state BACKUPinterface em1virtual_router_id 51priority 90advert_int 1authentication {auth_type PASSauth_pass 1111}track_script {check_run}virtual_ipaddress {10.0.11.190 dev em1 label em1:110.0.11.191 dev em1 label em1:2}}virtual_server 10.0.11.190 3306 {delay_loop 6lb_algo rrlb_kind DRpersistence_timeout 20protocol TCPsorry_server 10.0.11.244 3306real_server 10.0.11.243 3306 {weight 3TCP_CHECK {connect_timeout 3nb_get_retry 3delay_before_retry 3connect_port 3306}}}virtual_server 10.0.11.191 3306 {delay_loop 6lb_algo rrlb_kind DR#persistence_timeout 20protocol TCPreal_server 10.0.11.243 3306 {weight 3TCP_CHECK {connect_timeout 3nb_get_retry 3delay_before_retry 3connect_port 3306}}real_server 10.0.11.244 3306 {weight 3TCP_CHECK {connect_timeout 3nb_get_retry 3delay_before_retry 3connect_port 3306}}}2.5 Master 和 backup 的 realserver 的配置 对于 realserver 的配置 master 和 backup 是一致的，脚本内容如下： #!/bin/bash#description: Config realserver lo and apply noarpSNS_VIP=10.1.11.190SNS_VIP2=10.0.11.191/etc/rc.d/init.d/functionscase"$1" instart)ifconfiglo:0 $SNS_VIP netmask 255.255.255.255 broadcast $SNS_VIPifconfiglo:1 $SNS_VIP2 netmask 255.255.255.255 broadcast $SNS_VIP2/sbin/routeadd -host $SNS_VIP dev lo:0/sbin/routeadd -host $SNS_VIP2 dev lo:1echo"1" >/proc/sys/net/ipv4/conf/lo/arp_ignoreecho"2" >/proc/sys/net/ipv4/conf/lo/arp_announceecho"1" >/proc/sys/net/ipv4/conf/all/arp_ignoreecho"2" >/proc/sys/net/ipv4/conf/all/arp_announceecho"1" >/proc/sys/net/ipv4/conf/em1/arp_ignoreecho"2" >/proc/sys/net/ipv4/conf/em1/arp_announcesysctl-p >/dev/null 2>&1echo"RealServer Start OK";;stop)ifconfiglo:0 downifconfiglo:1 downroutedel $SNS_VIP >/dev/null 2>&1routedel $SNS_VIP2 >/dev/null 2>&1echo"0" >/proc/sys/net/ipv4/conf/lo/arp_ignoreecho"0" >/proc/sys/net/ipv4/conf/lo/arp_announceecho"0" >/proc/sys/net/ipv4/conf/all/arp_ignoreecho"0" >/proc/sys/net/ipv4/conf/all/arp_announceecho"0" >/proc/sys/net/ipv4/conf/em1/arp_ignoreecho"0" >/proc/sys/net/ipv4/conf/em1/arp_announceecho"RealServer Stoped";;*)echo"Usage: ___FCKpd___7 {start|stop}"exit1esacexit02.6 编辑脚本控制Keepalived的开启和停止 大体意思是只要检测到mysql服务停止keepalived服务也停止 ，因为keepalived是通过组播方式告诉本网段自己还活着当mysql服务停止后keepalived还依然运行 这时就需要停止keepalived让另一个主机获得虚拟IP，可以在后台运行这个脚本 也可以在keepalived配置文件加入这个脚本，master和slave节点都需要，只是数据库用户名和密码有区别 [root@244 ~]#more /etc/keepalived/check_mysql.sh#20140722#!/bin/bashMYSQL=/usr/bin/mysqlMYSQL_HOST=10.0.11.243MYSQL_USER=rootMYSQL_PASSWORD=mysqlCHECK_TIME=3#mysql isworking MYSQL_OK is 1 , mysql down MYSQL_OK is 0MYSQL_OK=1function check_mysql_helth (){$MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "showstatus;" >/dev/null 2>&1if [ $? = 0 ] ;thenMYSQL_OK=1elseMYSQL_OK=0fireturn $MYSQL_OK}while [ $CHECK_TIME -ne 0 ]dolet "CHECK_TIME -= 1"check_mysql_helthif [ $MYSQL_OK = 1 ] ; thenCHECK_TIME=0exit 0fiif [ $MYSQL_OK -eq 0 ] && [ $CHECK_TIME -eq 0 ]thenpkill keepalivedexit 1fisleep 1done该脚本需要有执行权限，通过以下命令加上执行权限 2.7 测试Keepalived自动故障转移 Master和slave上执行sudo servicekeepalived start，在master上执行ip a
Slave上执行 ip a
从上可以看到masteMySQL上虚拟IP绑定成功，backup MySQL上keepalived服务正常运行，但无虚拟IP(这属于正常) 客户端能通过vip连接上mysql
停止master上的mysql服务，可发现Keepalived服务也会停止
这时候slave运行ip a发现绑定了虚拟ip，并且从库mysql运行正常，而客户端依旧能通过vip连上数据库
重启master的mysql和Keepalived以后，并在slave库运行start slave，master运行ip a发现恢复之前的服务
2.8 测试lvs的负载均衡效果 启动 master 和 backup 的 mysql 以后，再在 master 和 backup 执行如下命令启 动 keepalived 和 realserver 脚本： /etc/rc.d/init.d/realserver.sh start /etc/rc.d/init.d/keepalived start 运行master和backup上的sudo ipvsadm -ln可看到如下信息
在客户端通过read vip连接到mysql并执行几次select,可考到inaction发生了变化，证明实现了负载均衡
|
