MySQL + Keepalived

Keep alive+mysql automatic switch

Project environment:

 VIP 192.168.14.110 mysql1 192.168.14.120 mysql2 192.168.14.130

Implementation steps

I Keepalived active/standby configuration file

192.168.14.120 Master configuration

 [ root@localhost  ~]# vim /etc/keepalived/keepalived.conf ============================================================== !  Configuration File for keepalived global_defs { router_id mysqli } vrrp_script check_run { script "/root/keepalived_check_mysql.sh" interval 5 } vrrp_instance VI_1 { state MASTER interface ens33 virtual_router_id 88 priority 100 advert_int 1 authentication { auth_type PASS auth_pass 1111 } track_script { check_run } virtual_ipaddress { 192.168.14.100 } }

192.168.14.130 Master configuration

 [ root@localhost  ~]# vim /etc/keepalived/keepalived.conf ============================================================== !  Configuration File for keepalived global_defs { router_id mysql2 } vrrp_script check_run { script "/root/keepalived_check_mysql.sh" interval 5 } vrrp_instance VI_1 { state BACKUP interface ens33 virtual_router_id 88 priority 50 advert_int 1 authentication { auth_type PASS auth_pass 1111 } track_script { check_run } virtual_ipaddress { 192.168.14.100 } }

1. Note the space

2. Log to check whether the script is executed

 [ root@localhost  ~]# tailf /var/log/messages Jun 19 15:20:19 xen1 Keepalived_vrrp[6341]: Using LinkWatch kernel netlink reflector... Jun 19 15:20:19 xen1 Keepalived_vrrp[6341]: VRRP sockpool: [ifindex(2),proto(112),fd(11,12)] Jun 19 15:20:19 xen1 Keepalived_vrrp[6341]: VRRP_Script(check_run) succeeded

2、 MySQL status check script/root/keepalived_check_mysql.sh (two MySQL scripts with the same origin)

Version 1: simple and practical

 #!/ bin/bash /usr/bin/mysql -uroot -p123456 -e "show status" &>/dev/null if [ $? -ne 0 ] ; then service keepalived stop fi

Version 2: Check many times

 [ root@localhost  ~]# vim /root/keepalived_check_mysql.sh ======================================================== #!/ bin/bash MYSQL=/usr/local/mysql/bin/mysql MYSQL_HOST=localhost MYSQL_USER=root MYSQL_PASSWORD=123456 CHECK_TIME=3 #mysql is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0 MYSQL_OK=1 check_mysql_health (){ $MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p${MYSQL_PASSWORD} -e "show status" &>/dev/null if [ $? -eq 0 ] ; then MYSQL_OK=1 else MYSQL_OK=0 fi return $MYSQL_OK } while [$CHECK_TIME -ne 0 ] do check_mysql_health if [ $MYSQL_OK -eq 1 ] ;  then exit 0 fi if [ $MYSQL_OK -eq 0 ] && [ $CHECK_TIME -eq 1 ] ;  then /etc/init.d/keepalived stop exit 1 fi let CHECK_TIME-- sleep 1 done

Start both active and standby

 [ root@localhost  ~]# chmod 744 /root/keepalived_check_mysql.sh [ root@localhost  ~]# /etc/init.d/keepalived start [ root@localhost  ~]# chkconfig --add keepalived [ root@localhost  ~]# chkconfig keepalived on