新版keeplived + MySQL双主自动切换

cz-sjm

　　### 新版keeplived + MySQL双主自动切换 ###
　　参考网址：
　　http://biancheng.dnbcw.info/mysql/381020.html
　　###一、网络结构  ###
　　VIP 10.125.141.249
　　mysq1m1 10.125.141.138 主1(相对而言是主库)
　　mysqlm2 192.168.88.131 主2(相对而言是备库)
　　### 二、主要步骤 ###
　　一 mysql 主主同步
　　二 安装keepalived
　　三 keepalived 主备配置文件
　　四 mysql状态检测脚本/root/keepalived_check_mysql.sh
　　五 启动keepalived
　　六 测试
　　### 三、详细步骤 ###
　　#### 3.1、mysql双主搭建####
　　这里省略.....
　　说明：数据库testA和testB分别在mysqlm1和mysqlm2上，testDB是通过主主复制获得，便于后面的测试.
　　#### 3.2、kepplived安装配置 ####
　　3.2.1、如果之前已经安装过，这里可以清理一下环境。
　　[root@bj02-ops-mgotest02 ~]# find / -name "keepalived"
　　/usr/local/keepalived
　　/usr/local/keepalived/sbin/keepalived
　　/usr/local/keepalived/etc/sysconfig/keepalived
　　/usr/local/keepalived/etc/keepalived
　　/usr/local/keepalived/etc/rc.d/init.d/keepalived
　　/usr/sbin/keepalived
　　/gomeo2o/soft/keepalived-1.2.7/bin/keepalived
　　/gomeo2o/soft/keepalived-1.2.7/keepalived
　　/gomeo2o/soft/keepalived-1.2.7/keepalived/etc/keepalived
　　/etc/keepalived
　　/etc/rc.d/init.d/keepalived
　　/etc/sysconfig/keepalived
　　[root@bj02-ops-mgotest02 ~]#
　　rm -rf /usr/local/keepalived
　　rm -rf /usr/sbin/keepalived
　　rm -rf /gomeo2o/soft/keepalived-1.2.7
　　rm -rf /etc/keepalived
　　rm -rf /etc/rc.d/init.d/keepalived
　　rm -rf /etc/sysconfig/keepalived
　　[root@bj02-ops-mgotest02 ~]# rm -rf /usr/local/keepalived
　　[root@bj02-ops-mgotest02 ~]# rm -rf /usr/sbin/keepalived
　　[root@bj02-ops-mgotest02 ~]# rm -rf /usr/sbin/keepalived
　　[root@bj02-ops-mgotest02 ~]# rm -rf /gomeo2o/soft/keepalived-1.2.7
　　[root@bj02-ops-mgotest02 ~]# rm -rf /etc/keepalived
　　[root@bj02-ops-mgotest02 ~]# rm -rf /etc/rc.d/init.d/keepalived
　　[root@bj02-ops-mgotest02 ~]# rm -rf /etc/sysconfig/keepalived
　　3.2.2 下载并安装
　　http://www.keepalived.org/software/keepalived-1.3.2.tar.gz
　　[root@bj02-ops-mgotest02 ~]# cd /gomeo2o/soft/
　　[root@bj02-ops-mgotest02 soft]# wget http://www.keepalived.org/software/keepalived-1.3.2.tar.gz
　　--2016-12-08 15:20:33--  http://www.keepalived.org/software/keepalived-1.3.2.tar.gz
　　Resolving www.keepalived.org... 37.59.63.157, 2001:41d0:8:7a9d::1
　　Connecting to www.keepalived.org|37.59.63.157|:80... connected.
　　HTTP request sent, awaiting response... 200 OK
　　Length: 648434 (633K) [application/x-gzip]
　　Saving to: “keepalived-1.3.2.tar.gz”
　　100%[=========================================================================================>] 648,434     22.8K/s   in 24s
　　2016-12-08 15:20:58 (25.9 KB/s) - “keepalived-1.3.2.tar.gz” saved [648434/648434]
　　[root@bj02-ops-mgotest02 soft]#
　　[root@bj02-ops-mgotest02 soft]# tar -zxf keepalived-1.3.2.tar.gz
　　[root@bj02-ops-mgotest02 soft]# cd keepalived-1.3.2
　　[root@bj02-ops-mgotest03 keepalived-1.3.2]# ./configure --prefix=/usr/local/keeplived
　　[root@bj02-ops-mgotest02 keepalived-1.3.2]# make && make install
　　3.2.3 查看keeplived位置
　　[root@bj02-ops-mgotest03 keepalived-1.3.2]#  find / -name "keepalived"
　　/usr/local/keeplived/sbin/keepalived
　　/usr/local/keeplived/etc/sysconfig/keepalived
　　/usr/local/keeplived/etc/keepalived
　　/gomeo2o/soft/keepalived-1.3.2/bin/keepalived
　　/gomeo2o/soft/keepalived-1.3.2/keepalived
　　/gomeo2o/soft/keepalived-1.3.2/keepalived/etc/sysconfig/keepalived
　　/gomeo2o/soft/keepalived-1.3.2/keepalived/etc/init.d/keepalived
　　/gomeo2o/soft/keepalived-1.3.2/keepalived/etc/keepalived
　　/gomeo2o/soft/keepalived-1.3.2/keepalived/keepalived
　　[root@bj02-ops-mgotest03 keepalived-1.3.2]#
　　3.2.4 复制文件
　　[root@bj02-ops-mgotest02 keepalived-1.3.2]# cp /gomeo2o/soft/keepalived-1.3.2/keepalived/etc/init.d/keepalived /etc/init.d/
　　[root@bj02-ops-mgotest02 keepalived-1.3.2]# cp /gomeo2o/soft/keepalived-1.3.2/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
　　[root@bj02-ops-mgotest02 keepalived-1.3.2]# mkdir -p /etc/keepalived
　　[root@bj02-ops-mgotest02 keepalived-1.3.2]# cp /gomeo2o/soft/keepalived-1.3.2/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
　　[root@bj02-ops-mgotest02 keepalived-1.3.2]#
　　[root@bj02-ops-mgotest02 keepalived-1.3.2]# cp /usr/local/keeplived/sbin/keepalived /usr/sbin/
　　3.2.5 修改配置文件
　　主备配置文件不同处有3处：分别是 state、 nopreempt、 priority 参考资料里有详细说明作用
　　10.125.141.138的配置如下：
　　vim /etc/keepalived/keepalived.conf
　　! Configuration File for keepalived
　　global_defs {
　　notification_email {
　　liweiwei@gomeplus.com
　　}
　　notification_email_from liweiwei@gomeplus.com
　　smtp_connect_timeout 3
　　smtp_server mail.gomeplus.com
　　router_id MySQL-ha141
　　}
　　vrrp_script check_run {
　　script "/root/keepalived_check_mysql.sh"
　　interval 5
　　}
　　vrrp_sync_group 141138 {
　　group {
　　VI_1
　　}
　　}
　　vrrp_instance VI_1 {
　　state BACKUP
　　interface eth0
　　virtual_router_id 249
　　priority 100
　　advert_int 1
　　nopreempt
　　authentication {
　　auth_type pass
　　auth_pass 12345678
　　}
　　track_script {
　　check_run
　　}
　　virtual_ipaddress {
　　10.125.141.249
　　}
　　}
　　10.125.141.139的配置如下：
　　vim /etc/keepalived/keepalived.conf
　　! Configuration File for keepalived
　　global_defs {
　　notification_email {
　　liweiwei@gomeplus.com
　　}
　　notification_email_from liweiwei@gomeplus.com
　　smtp_connect_timeout 3
　　smtp_server mail.gomeplus.com
　　router_id MySQL-ha141
　　}
　　vrrp_script check_run {
　　script "/root/keepalived_check_mysql.sh"
　　interval 5
　　}
　　vrrp_sync_group 141138 {
　　group {
　　VI_1
　　}
　　}
　　vrrp_instance VI_1 {
　　state BACKUP
　　interface eth0
　　virtual_router_id 249
　　priority 99
　　advert_int 1
　　# nopreempt
　　authentication {
　　auth_type pass
　　auth_pass 12345678
　　}
　　track_script {
　　check_run
　　}
　　virtual_ipaddress {
　　10.125.141.249
　　}
　　}
　　### 四、创建检测脚本 ###
　　由于mysql高版本在shell命令行中带密码的时候，会多出一行告警提示，导致检测失败。所以这里先创建一个利用配置文件登陆mysql的参数文件。

　　mysql> create user 'chkuser'@'127.0.0.1'>　　Query OK, 0 rows affected (0.00 sec)
　　mysql> grant all on *.* to 'chkuser'@'127.0.0.1';
　　Query OK, 0 rows affected (0.01 sec)
　　[root@bj02-ops-mgotest02 local]# cat /root/client.cnf
　　[client]
　　host=127.0.0.1
　　user=chkuser
　　password=chkuser1234
　　port=3306
　　[root@bj02-ops-mgotest02 local]# vim /root/keepalived_check_mysql.sh
　　#!/bin/bash
　　MYSQL=/usr/local/mysql/bin/mysql
　　CHECK_TIME=3
　　#mysql  is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0
　　MYSQL_OK=1
　　function check_mysql_helth (){
　　$MYSQL --defaults-file=/root/client.cnf -e "show status;" >/dev/null 2>&1
　　if [ $? = 0 ] ;then
　　MYSQL_OK=1
　　else
　　MYSQL_OK=0
　　fi
　　return $MYSQL_OK
　　}
　　while [ $CHECK_TIME -ne 0 ]
　　do
　　let "CHECK_TIME -= 1"
　　check_mysql_helth
　　if [ $MYSQL_OK = 1 ] ; then
　　CHECK_TIME=0
　　exit 0
　　fi
　　if [ $MYSQL_OK -eq 0 ] &&  [ $CHECK_TIME -eq 0 ]
　　then
　　/etc/init.d/keepalived stop
　　exit 1
　　fi
　　sleep 1
　　done
　　[root@bj02-ops-mgotest02 local]# chmod +x /root/keepalived_check_mysql.sh
　　### 五、启动keeplaived服务 ###
　　[root@bj02-ops-mgotest02 local]# /etc/init.d/keepalived start
　　Starting keepalived:                                       [  OK  ]
　　[root@bj02-ops-mgotest02 local]# ip add
　　1: lo:  mtu 65536 qdisc noqueue state UNKNOWN
　　link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
　　inet 127.0.0.1/8 scope host lo
　　2: eth0:  mtu 1500 qdisc mq state UP qlen 1000
　　link/ether 00:50:56:b9:37:e2 brd ff:ff:ff:ff:ff:ff
　　inet 10.125.141.138/24 brd 10.125.141.255 scope global eth0
　　inet 10.125.141.249/32 scope global eth0
　　[root@bj02-ops-mgotest02 local]#
　　[root@bj02-ops-mgotest02 ~]# tail -f /var/log/messages
　　......
　　......
　　Dec  8 17:09:26 bj02-ops-mgotest02 Keepalived_vrrp[13360]: Sending gratuitous ARP on eth0 for 10.125.141.249
　　Dec  8 17:09:26 bj02-ops-mgotest02 Keepalived_vrrp[13360]: Sending gratuitous ARP on eth0 for 10.125.141.249
　　Dec  8 17:09:26 bj02-ops-mgotest02 Keepalived_healthcheckers[13359]: Netlink reflector reports IP 10.125.141.249 added
　　Dec  8 17:09:31 bj02-ops-mgotest02 Keepalived_vrrp[13360]: Sending gratuitous ARP on eth0 for 10.125.141.249
　　Dec  8 17:09:31 bj02-ops-mgotest02 Keepalived_vrrp[13360]: VRRP_Instance(VI_1) Sending/queueing gratuitous ARPs on eth0 for 10.125.141.249
　　Dec  8 17:09:31 bj02-ops-mgotest02 Keepalived_vrrp[13360]: Sending gratuitous ARP on eth0 for 10.125.141.249
　　Dec  8 17:09:31 bj02-ops-mgotest02 Keepalived_vrrp[13360]: Sending gratuitous ARP on eth0 for 10.125.141.249
　　Dec  8 17:09:31 bj02-ops-mgotest02 Keepalived_vrrp[13360]: Sending gratuitous ARP on eth0 for 10.125.141.249
　　Dec  8 17:09:31 bj02-ops-mgotest02 Keepalived_vrrp[13360]: Sending gratuitous ARP on eth0 for 10.125.141.249
　　Dec  8 17:13:09 bj02-ops-mgotest02 -bash: HISTORY: PPID=8695 PID=8697 SID=8649 UID=0 User=root ip add
　　[root@bj02-ops-mgotest03 ~]# /etc/init.d/keepalived  start
　　Starting keepalived:                                       [  OK  ]
　　[root@bj02-ops-mgotest03 ~]#
　　### 六、测试 ###
　　[root@bj02-ops-mgotest02 local]# mysql
　　mysql> create database testa;
　　Query OK, 1 row affected (0.01 sec)
　　mysql> create database testb;
　　Query OK, 1 row affected (0.00 sec)
　　mysql> show databases;
　　+--------------------+
　　| Database           |
　　+--------------------+
　　| information_schema |
　　| mysql              |
　　| performance_schema |
　　| sys                |
　　| testa              |
　　| testb              |
　　+--------------------+
　　6 rows in set (0.00 sec)
　　mysql>
　　[root@bj02-ops-mgotest03 ~]# mysql
　　mysql> show databases;
　　+--------------------+
　　| Database           |
　　+--------------------+
　　| information_schema |
　　| mysql              |
　　| performance_schema |
　　| sys                |
　　| testa              |
　　| testb              |
　　+--------------------+
　　6 rows in set (0.01 sec)
　　mysql>
　　停止主库，测试。
　　[root@bj02-ops-mgotest02 local]# /etc/init.d/mysqld stop
　　Shutting down MySQL (Percona Server)............. SUCCESS!
　　[root@bj02-ops-mgotest02 local]# ip add
　　1: lo:  mtu 65536 qdisc noqueue state UNKNOWN
　　link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
　　inet 127.0.0.1/8 scope host lo
　　2: eth0:  mtu 1500 qdisc mq state UP qlen 1000
　　link/ether 00:50:56:b9:37:e2 brd ff:ff:ff:ff:ff:ff
　　inet 10.125.141.138/24 brd 10.125.141.255 scope global eth0
　　[root@bj02-ops-mgotest02 ~]# tail -f /var/log/messages
　　Dec  8 17:23:15 bj02-ops-mgotest02 -bash: HISTORY: PPID=12914 PID=12916 SID=12873 UID=0 User=root >/var/log/messages
　　Dec  8 17:23:17 bj02-ops-mgotest02 -bash: HISTORY: PPID=12914 PID=12916 SID=12873 UID=0 User=root tail -f /var/log/messages
　　Dec  8 17:23:20 bj02-ops-mgotest02 -bash: HISTORY: PPID=8695 PID=8697 SID=8649 UID=0 User=root /etc/init.d/mysqld stop
　　Dec  8 17:23:23 bj02-ops-mgotest02 Keepalived[13358]: Stopping
　　Dec  8 17:23:23 bj02-ops-mgotest02 Keepalived_vrrp[13360]: VRRP_Instance(VI_1) sent 0 priority
　　Dec  8 17:23:23 bj02-ops-mgotest02 Keepalived_vrrp[13360]: VRRP_Instance(VI_1) removing protocol VIPs.
　　Dec  8 17:23:23 bj02-ops-mgotest02 Keepalived_healthcheckers[13359]: Stopped
　　Dec  8 17:23:24 bj02-ops-mgotest02 Keepalived_vrrp[13360]: Stopped
　　Dec  8 17:23:24 bj02-ops-mgotest02 Keepalived[13358]: Stopped Keepalived v1.3.2 (12/08,2016)
　　在原有的从库上查看。
　　[root@bj02-ops-mgotest03 ~]# ip add
　　1: lo:  mtu 65536 qdisc noqueue state UNKNOWN
　　link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
　　inet 127.0.0.1/8 scope host lo
　　2: eth0:  mtu 1500 qdisc mq state UP qlen 1000
　　link/ether 00:50:56:b9:03:58 brd ff:ff:ff:ff:ff:ff
　　inet 10.125.141.139/24 brd 10.125.141.255 scope global eth0
　　inet 10.125.141.249/32 scope global eth0
　　[root@bj02-ops-mgotest03 ~]#
　　[root@bj02-ops-mgotest03 ~]# tail -f /var/log/messages
　　Dec  8 17:23:25 bj02-ops-mgotest03 Keepalived_vrrp[21675]: Sending gratuitous ARP on eth0 for 10.125.141.249
　　Dec  8 17:23:25 bj02-ops-mgotest03 Keepalived_vrrp[21675]: Sending gratuitous ARP on eth0 for 10.125.141.249
　　Dec  8 17:23:25 bj02-ops-mgotest03 Keepalived_healthcheckers[21674]: Netlink reflector reports IP 10.125.141.249 added
　　Dec  8 17:23:30 bj02-ops-mgotest03 Keepalived_vrrp[21675]: Sending gratuitous ARP on eth0 for 10.125.141.249
　　Dec  8 17:23:30 bj02-ops-mgotest03 Keepalived_vrrp[21675]: VRRP_Instance(VI_1) Sending/queueing gratuitous ARPs on eth0 for 10.125.141.249
　　Dec  8 17:23:30 bj02-ops-mgotest03 Keepalived_vrrp[21675]: Sending gratuitous ARP on eth0 for 10.125.141.249
　　Dec  8 17:23:30 bj02-ops-mgotest03 Keepalived_vrrp[21675]: Sending gratuitous ARP on eth0 for 10.125.141.249
　　Dec  8 17:23:30 bj02-ops-mgotest03 Keepalived_vrrp[21675]: Sending gratuitous ARP on eth0 for 10.125.141.249
　　Dec  8 17:23:30 bj02-ops-mgotest03 Keepalived_vrrp[21675]: Sending gratuitous ARP on eth0 for 10.125.141.249
　　附加测试。修复原有的主库，停止现有的主库，看看能切换不?
　　[root@bj02-ops-mgotest02 local]# /etc/init.d/mysqld start
　　Starting MySQL (Percona Server).... SUCCESS!
　　[root@bj02-ops-mgotest02 local]# /etc/init.d/keepalived start
　　Starting keepalived:                                       [  OK  ]
　　[root@bj02-ops-mgotest02 local]#
　　[root@bj02-ops-mgotest02 local]# mysql
　　......
　　......
　　mysql> start slave ;
　　Query OK, 0 rows affected (0.20 sec)
　　mysql>
　　停止现有的主库。
　　[root@bj02-ops-mgotest03 ~]# /etc/init.d/mysqld  stop
　　Shutting down MySQL (Percona Server)............... SUCCESS!
　　[root@bj02-ops-mgotest03 ~]#
　　[root@bj02-ops-mgotest03 ~]# ip add
　　1: lo:  mtu 65536 qdisc noqueue state UNKNOWN
　　link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
　　inet 127.0.0.1/8 scope host lo
　　2: eth0:  mtu 1500 qdisc mq state UP qlen 1000
　　link/ether 00:50:56:b9:03:58 brd ff:ff:ff:ff:ff:ff
　　inet 10.125.141.139/24 brd 10.125.141.255 scope global eth0
　　[root@bj02-ops-mgotest03 ~]# tail -f /var/log/messages
　　......
　　......
　　Dec  8 17:23:30 bj02-ops-mgotest03 Keepalived_vrrp[21675]: Sending gratuitous ARP on eth0 for 10.125.141.249
　　Dec  8 17:24:36 bj02-ops-mgotest03 -bash: HISTORY: PPID=15262 PID=15264 SID=15217 UID=0 User=root ip add
　　Dec  8 17:28:00 bj02-ops-mgotest03 -bash: HISTORY: PPID=15262 PID=15264 SID=15217 UID=0 User=root /etc/init.d/mysqld  stop
　　Dec  8 17:28:03 bj02-ops-mgotest03 Keepalived[21673]: Stopping
　　Dec  8 17:28:03 bj02-ops-mgotest03 Keepalived_vrrp[21675]: VRRP_Instance(VI_1) sent 0 priority
　　Dec  8 17:28:03 bj02-ops-mgotest03 Keepalived_vrrp[21675]: VRRP_Instance(VI_1) removing protocol VIPs.
　　Dec  8 17:28:03 bj02-ops-mgotest03 Keepalived_healthcheckers[21674]: Stopped
　　Dec  8 17:28:04 bj02-ops-mgotest03 Keepalived_vrrp[21675]: Stopped
　　Dec  8 17:28:04 bj02-ops-mgotest03 Keepalived[21673]: Stopped Keepalived v1.3.2 (12/08,2016)
　　查看现在的主库。
　　[root@bj02-ops-mgotest02 local]# ip add
　　1: lo:  mtu 65536 qdisc noqueue state UNKNOWN
　　link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
　　inet 127.0.0.1/8 scope host lo
　　2: eth0:  mtu 1500 qdisc mq state UP qlen 1000
　　link/ether 00:50:56:b9:37:e2 brd ff:ff:ff:ff:ff:ff
　　inet 10.125.141.138/24 brd 10.125.141.255 scope global eth0
　　inet 10.125.141.249/32 scope global eth0
　　[root@bj02-ops-mgotest02 local]#
　　[root@bj02-ops-mgotest02 ~]# tail -f /var/log/messages
　　Dec  8 17:26:54 bj02-ops-mgotest02 Keepalived_vrrp[15922]: VRRP_Script(check_run) succeeded
　　Dec  8 17:27:09 bj02-ops-mgotest02 -bash: HISTORY: PPID=8695 PID=8697 SID=8649 UID=0 User=root mysql
　　Dec  8 17:28:03 bj02-ops-mgotest02 Keepalived_vrrp[15922]: VRRP_Instance(VI_1) Transition to MASTER STATE
　　Dec  8 17:28:04 bj02-ops-mgotest02 Keepalived_vrrp[15922]: VRRP_Instance(VI_1) Entering MASTER STATE
　　Dec  8 17:28:04 bj02-ops-mgotest02 Keepalived_vrrp[15922]: VRRP_Instance(VI_1) setting protocol VIPs.
　　Dec  8 17:28:04 bj02-ops-mgotest02 Keepalived_vrrp[15922]: Sending gratuitous ARP on eth0 for 10.125.141.249
　　Dec  8 17:28:04 bj02-ops-mgotest02 Keepalived_vrrp[15922]: VRRP_Instance(VI_1) Sending/queueing gratuitous ARPs on eth0 for 10.125.141.249
　　Dec  8 17:28:04 bj02-ops-mgotest02 Keepalived_vrrp[15922]: Sending gratuitous ARP on eth0 for 10.125.141.249
　　Dec  8 17:28:04 bj02-ops-mgotest02 Keepalived_vrrp[15922]: Sending gratuitous ARP on eth0 for 10.125.141.249
　　Dec  8 17:28:04 bj02-ops-mgotest02 Keepalived_vrrp[15922]: Sending gratuitous ARP on eth0 for 10.125.141.249
　　Dec  8 17:28:04 bj02-ops-mgotest02 Keepalived_vrrp[15922]: Sending gratuitous ARP on eth0 for 10.125.141.249
　　Dec  8 17:28:04 bj02-ops-mgotest02 Keepalived_healthcheckers[15921]: Netlink reflector reports IP 10.125.141.249 added
　　Dec  8 17:28:09 bj02-ops-mgotest02 Keepalived_vrrp[15922]: Sending gratuitous ARP on eth0 for 10.125.141.249
　　Dec  8 17:28:09 bj02-ops-mgotest02 Keepalived_vrrp[15922]: VRRP_Instance(VI_1) Sending/queueing gratuitous ARPs on eth0 for 10.125.141.249
　　再次验证完毕。不过，这个是检测和切换时间的，所以，在高并发期间出现故障切换的话，可能会有数据丢失现象出现。对于中小企业，这种架构是可以放心使用的，个人认为。