mysql master/slave结构最大的缺点:当master宕机时不能切换到slave,这样就影响了业务的运行。为了弥补这个缺陷,我采用了keepalived+master/master结构。如图:
关于mysql的配置请参考:http://liuping0906.blog.51cto.com/2516248/879877
安装keepalived
tar zxf keepalived-1.2.8.tar.gz cd keepalived-1.2.8 ./configure --prefix=/opt/app/keepalived-1.2.8 make make install
在192.168.110.128上配置文件keepalived.conf
! Configuration File for keepalived global_defs { router_id MYSQL-HA } vrrp_instance keepalive_mysql { state MASTER interface eth0 virtual_router_id 51 priority 100 advert_int 1 preempt authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.110.8/24 } }
在192.168.110.137上配置文件keepalived.conf
! Configuration File for keepalived global_defs { router_id MYSQL-HA } vrrp_instance keepalive_mysql { state BACKUP interface eth0 virtual_router_id 51 priority 99 advert_int 1 nopreempt authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.110.8/24 } }
说明:
配置上注意priority的值,192.168.110.128是master,优先级要高。
preempt表示抢占服务,当192.168.110.128恢复后,重新把服务抢过来。
keepalived默认网卡eth0和自身服务出现问题时才会切换。
通过编写脚本来监控mysql的状态进而控制keeepalived服务
#!/bin/bash MYSQL="/usr/bin/mysql" MYSQL_HOST=127.0.0.1 MYSQL_USER=admin MYSQL_PASSWORD=admin LOG_FILE="/opt/app/keepalived/check_mysql.log" MYSQL_OK=1 # mysql is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0 check_mysql_helth() { $MYSQL -h$MYSQL_HOST -u$MYSQL_USER -p${MYSQL_PASSWORD} -e "show status" &>/dev/null if [ $? = 0 ] ; then MYSQL_OK=1 else MYSQL_OK=0 fi return $MYSQL_OK } while : do CHECK_TIME=3 while [ $CHECK_TIME -ne 0 ] do let "CHECK_TIME -= 1" check_mysql_helth [ $MYSQL_OK == 1 ] && break if [ $MYSQL_OK -eq 0 ] && [ $CHECK_TIME -eq 0 ]; then pkill keepalived echo `date --date=today +"%Y-%M-%d %H:%m:%S"` - [INFO] - mysql invaild. keepalived stop. >> $LOG_FILE fi done done
说明:
通过监控mysql服务状态来控制keepalived服务,这里mysql停掉则kill掉keepalived。
分别在两台机器上执行下面命令
shell>/opt/app/keepalived/sbin/keepalived -f \ /opt/app/keepalived/etc/keepalived/keepalived.conf -D shell>nohup sh /opt/app/keepalived/bin/check_mysql.sh &
把第二条命令写入/etc/rc.local。
测试:
> ip addr
>killall mysqld
查看192.168.110.137
此时192.168.110.137充当临时服务器。
恢复192.168.110.128上的业务后查看
此时业务被192.168.110.128抢占过来,192.168.110.137充当备份、临时服务器。
遗留的问题:
1、本来在keepalived使用vrrp_script和strack_script来监控这个脚本,但是测试过几遍脚本都不运行。网上很多人都谈到这个问题,我不知道其他人怎么成功的。
2、当写入数据时主服务器宕机,从服务器来不及同步数据。此时造成用户数据写入但不能读取。缓解这个问题可以采用同步或者半同步。
3、主从切换时会造成业务闪断。
4、对于第一个问题可以结合lvs来解决。
5、主键冲突问题
参考文档
http://bbs.ywlm.net/thread-855-1-1.html
转载于:https://blog.51cto.com/liuping0906/1295092