Mysql写数据库HA实施环境说明

行政采购平台写数据库HA 实施环境说明一、架构介绍1、 mysql 双机介绍1) 主机hosts 列表10.37.144.151 mysql01 elcndc2xzdb01t10.37.144.152 mysql02 elcndc2xzdb02t10.37.144.177 vip集群中10.37.144.151是主节点，10.37.144.152是从节点；2) 目前在数据库中创建了一个监控帐号：keystone ，原则上数据库的root 帐号只允许本地访问，如果需要远程登录则需要创建一个普通帐号登录，本例出于测试考虑，创建普通帐号，建议后续生产系统创建本地帐号，进行远程登录；3) mysql 帐号：root ，密码：1qazxsw@4) Mysql 版本：5.6.24 MySQL Community Server (GPL)；5) 安装软件包：rgmanager 、cman 、ricci 、clvmd 、gfs2；6) 关闭NetworkManager 开机自启动，rgmanager 、cman 、ricci 、clvmd 、gfs2设置为开机自启动；2、 mysql 架构介绍集中存储共享Mysql dataKeepalived 心跳VIP 用户访问Ha IP ：10.37.144.177集团内网节点二10.37.144.152节点二10.37.144.1523、 共享磁盘本例中使用gfs2文件系统实现双机共享存储[root@elcndc2xzdb02t ~]# df -hFilesystem Size Used Avail Use% Mounted on/dev/mapper/mysqldbvg-lv_mysqldata 260G 370M 260G 1% /mysqldata[root@elcndc2xzdb02t ~]# mount/dev/mapper/mysqldbvg-lv_mysqldata on /mysqldata type gfs2 (rw,relatime,hostdata=jid=1)4、 keepalived 工作机制实现虚拟IP切换，通过keepalived监听对方服务并进行相应的脚本切换，实现双机HA，具体配置详见附录；5、主机双节点中，主节点启动mysql后，从节点将无法启动mysql，原因在于两台主机上同时安装了mysql，但是使用的是同一个数据库文件，如果同时启动会给数据库造成不一致的现象，因此启动mysql的服务交由keepalived进程管理二、切换机制该集群采用的是单向HA切换，如果节点服务切换后，故障消除后需要手工切换。

1、主节点虚拟IP丢失模拟场景：手工删除虚拟ip，切换交由keepalived接管，虚拟地址和mysql服务将切换到从节点；# ip a d 10.37.144.177/32 eth72、主节点keepalived异常终止模拟场景：手工将keepalived服务停止，切换将由crontab接管，1分钟将虚拟地址和mysql服务切换到从节点；# service keepalived stop3、主节点OS重启手工将主节点重启，虚拟地址和mysql服务切换至从节点；# reboot4、虚拟地址和mysql服务由从节点切换到主节点，只要将主节点的keepalived服务进行重启即可；# service keepalived restart三、脚本脚本存放目录：/script （两台主机存放路径一致）[root@elcndc2xzdb01t script]# lschk_alive.sh mysqlv.sh1、keepalived调用脚本mysqlv.sh，主要实现当mysql异常down掉后主机切换。

1）节点一（10.37.144.151）调用脚本#!/bin/bashDATE=$(date +%F)echo $MYSQLIDVIP=`ip a | grep -v 'grep' | grep eth7:0| awk '{print $2}'|sed 's/\/32//g'`LOGFILE=/var/log/mysqlha/chkmysql_$DATE.logMYSQLID=(`ps -C mysqld --no-header | wc -l`)MYVIP=10.37.144.177VIP=`ip a | grep -v 'grep' | grep eth7:0| awk '{print $2}'|sed 's/\/32//g'`ALIVE=`ps aux | grep keepalived | grep -v "grep" | wc -l`if [ $ACTIVE = 0 ];thenservice mysql stopexit 0fiif [ "$VIP" != "$MYVIP" ] && [ $MYSQLID -eq "1" ];thenservice mysql stopservice keepalived stopexit 0else if [ "$VIP" = "$MYVIP" ] && [ $MYSQLID -eq "0" ];thendate >>$LOGFILEservice mysql startexit 0fifi2）节点二（10.37.144.152）调用脚本#!/bin/bashDATE=$(date +%F)LOGFILE=/var/log/mysqlha/chkmysql_$DATE.logMYSQLID=(`ps -C mysqld --no-header | wc -l`)MYVIP=10.37.144.177VIP=`ip a | grep -v 'grep' | grep eth7:0| awk '{print $2}'|sed 's/\/32//g'`MYSQL_HOST=localhostMYSQL_USER=keystoneMYSQL_PASS=1qazxsw@if [ "$VIP" != "$MYVIP" ] && [ $MYSQLID -eq "1" ];thenservice mysql stopexit 0else if [ "$VIP" = "$MYVIP" ] && [ $MYSQLID -eq "0" ];thendate >>$LOGFILEservice mysql startexit 0fifi2、系统调用脚本chk_alive.sh，主要是实现keepalived进程丢失后主机切换。

实现每分钟检查keepalived，配置系统自动检测crontab，配置在节点一（10.37.144.151）# crontab –l* * * * * /bin/bash -c /script/chk_alive.sh > /var/log/kpalive.log 2>&1脚本内容：#!/bin/bashALIVE=`ps aux | grep keepalived | grep -v "grep" | wc -l`MYSQLID=`ps aux | grep mysql | grep -v "grep" | wc -l`echo `date +%F-%T`if [ "$MYSQLID" = "0" ];thenecho "the mysql status is stoped"exit 1fiif [ "$ALIVE" = "0" ];thenecho "if keepalived is stoped,now it will be stop the mysql database!"/sbin/service mysql stopexit 0fi四、添加监控目的是当HA的主节点切换后，以邮件的形式提醒相关的运维人员和负责人知悉，以便及时处理故障，邮件接收可自定义设置；监控脚本：# more /usr/local/nagios/libexec/chk_mysql.sh#!/bin/bash#mysql proc monitorMYSQL=/usr/bin/mysqlMYSQL_HOST=localhostMYSQL_USER=rootCHECK_COUNT=1MYSQL_PASS=1qazxsw@mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASS -e "show status;" >/dev/null 2>&1i=$?if [ $? = 0 ]; thenecho "the mysql is alive,ok....."exit $STATE_OKelseecho "the ensap proc is down,error!!!!!"exit $STATE_CRITICALfi五、附录1、keepalive配置：#! Configuration File for keepalivedglobal_defs {notification_email {root@localhost}notification_email_from lizhanchan@smtp_server 127.0.0.1smtp_connect_timeout 30router_id mysql_clus}vrrp_script check_run {script "/script/mysqlv.sh"interval 5}vrrp_instance VI_10 {state MASTERinterface eth7virtual_router_id 60priority 100advert_int 1nopreempttrack_script {check_run}authentication {auth_type PASSauth_pass password}virtual_ipaddress {10.37.144.177 dev eth7 label eth7:0}}。