MySQL Router单点隐患通过Keepalived实现

目录

一、介绍

有些情况下,可能MySQL Router不便装在每个应用所在的服务器上,那么这时候要解决MySQL Router的单点故障,就需要用到keepalived或者pacemaker了,本文介绍了MySQL Router HA通过keepalived来实现。

二、环境准备

IP地址 角色
172.16.8.53 MySQL Router+MASTER
172.16.8.68 MySQL Router+BACKUP
172.16.8.24 VIP

keepalived版本:2.0.18

三、安装步骤

  1. 下载软件包,解压
mkdir /software
wget https://www.keepalived.org/software/keepalived-2.0.18.tar.gz -P /software
tar -zxvf /software/keepalived-2.0.18.tar.gz ; cd /software/keepalived
  1. 源码安装
yum -y install openssl* libnl‐dev* gcc-c++
./configure --prefix=/usr/local/keepalived
make
make install
  1. 配置keepalived
mkdir /etc/keepalived
cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
cp /data/keepalived-2.0.18/keepalived/etc/init.d/keepalived /etc/init.d/
cp /data/keepalived-2.0.18/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
  1. 修改keepalived配置文件
    MASTER
! Configuration File for keepalived

global_defs {
  notification_email {
    zhenweizheng@upchina.com
  }
  notification_email_from 13912345678@139.com
  smtp_server smtp.139.com
  smtp_connect_timeout 30
  router_id Router1
  vrrp_skip_check_adv_addr
#   vrrp_strict
  vrrp_garp_interval 0
  vrrp_gna_interval 0
}

vrrp_script chk_mysqlrouter {
 script "/usr/bin/killall -0 /usr/bin/mysqlrouter" # check the haproxy process
 interval 2 # every 2 seconds
 weight 2 # add 2 points if OK
 fall 2
}

vrrp_instance VI_1 {
   state MASTER
   interface ens160
   virtual_router_id 51
   priority 102
   advert_int 1
   virtual_ipaddress {
       172.16.8.24
   }
   track_script {
       chk_mysqlrouter
   }
   notify_master "/usr/bin/python /data/keepalived_monitor/keepalived_notify.py master 172.16.8.53 172.168.8.24"
   notify_backup "/usr/bin/python /data/keepalived_monitor/keepalived_notify.py backup 172.16.8.53 172.168.8.24"
}

BACKUP

! Configuration File for keepalived

global_defs {
   notification_email {
     xxx@xxx.com
   }
   notification_email_from 13912345678@139.com
   smtp_server smtp.139.com
   smtp_connect_timeout 30
   router_id Router2
   vrrp_skip_check_adv_addr
#   vrrp_strict
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}

vrrp_script chk_mysqlrouter {
  script "/usr/bin/killall -0 /usr/bin/mysqlrouter" # check the haproxy process
  interval 2 # every 2 seconds
  weight 2 # add 2 points if OK
  fall 2
}

vrrp_instance VI_1 {
    state BACKUP
    interface ens160
    virtual_router_id 51
    priority 101
    advert_int 1
    virtual_ipaddress {
        172.16.8.24
    }
    track_script {
        chk_mysqlrouter
    }
    notify_master "/usr/bin/python /data/keepalived_monitor/keepalived_notify.py master 172.16.8.68 172.168.8.24"
    notify_backup "/usr/bin/python /data/keepalived_monitor/keepalived_notify.py backup 172.16.8.68 172.168.8.24"
}

/usr/bin/killall -0 /usr/bin/mysqlrouter,每2秒钟会执行一次,监测mysqlrouter进程是否存在,一旦超过2次不正常,则会且换vip到BACKUP,并且执行keepalived_notify.py邮件报警脚本发送告警邮件

  1. 启动keepalived
systemctl start keepalived
systemctl enable keepalived

启动参数说明:
使用systemctl start keepalived命令启动服务时,默认会将/etc/sysconfig/keepalived文件中KEEPALIVED_OPTIONS参数作为keepalived服务启动时的参数,并从/etc/keepalived/目录下加载keepalived.conf配置文件,或用-f参数指定配置文件的位置。

  1. 查看VIP绑定情况
    MASTER
[root@node3 keepalived_monitor]# ip a 
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: ens160: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
    link/ether 00:50:56:99:45:ad brd ff:ff:ff:ff:ff:ff
    inet 172.16.8.53/24 brd 172.16.8.255 scope global noprefixroute ens160
       valid_lft forever preferred_lft forever
    inet 172.16.8.24/32 scope global ens160
       valid_lft forever preferred_lft forever
    inet6 fe80::170e:b457:5dbd:d629/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
3: docker0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default 
    link/ether 02:42:c3:9f:ab:0e brd ff:ff:ff:ff:ff:ff
    inet 172.17.0.1/16 scope global docker0
       valid_lft forever preferred_lft forever
4: tunl0@NONE: <NOARP,UP,LOWER_UP> mtu 1440 qdisc noqueue state UNKNOWN group default qlen 1000
    link/ipip 0.0.0.0 brd 0.0.0.0
    inet 10.233.71.0/32 scope global tunl0
       valid_lft forever preferred_lft forever

可以看到VIP:172.16.8.24已经绑定到ens160网卡上

BACKUP

[root@node4 mysqlrouter]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: ens160: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
    link/ether 00:50:56:99:1c:0f brd ff:ff:ff:ff:ff:ff
    inet 172.16.8.68/24 brd 172.16.8.255 scope global noprefixroute ens160
       valid_lft forever preferred_lft forever
    inet6 fe80::25a1:8b8d:a00e:70dd/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
3: docker0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default 
    link/ether 02:42:00:4f:fb:9b brd ff:ff:ff:ff:ff:ff
    inet 172.17.0.1/16 scope global docker0
       valid_lft forever preferred_lft forever
4: tunl0@NONE: <NOARP,UP,LOWER_UP> mtu 1440 qdisc noqueue state UNKNOWN group default qlen 1000
    link/ipip 0.0.0.0 brd 0.0.0.0
    inet 10.233.74.64/32 scope global tunl0
       valid_lft forever preferred_lft forever

BACKUP是没有绑定VIP的,只有当MASTER发生故障,触发了VIP切换,BACKUP才会绑定VIP

四、邮件告警

邮件告警脚本如下,需要在MASTER和BACKUP上都部署

[root@node3 keepalived_monitor]# cat keepalived_notify.py 
#!/usr/bin/python
# -*- coding:utf-8 -*-

import smtplib
from email.mime.text import MIMEText
from email.header import Header
import sys, time, subprocess

# 第三方 SMTP 服务
mail_host="smtp.139.com"  #设置服务器
mail_user="13912345678@139.com"    #用户名
mail_pass="xxx"   #口令


sender = '13912345678@139.com'    # 邮件发送者
receivers = ['xxx@xxx.com', 'xxx@xxx.com']  # 接收邮件,可设置为你的QQ邮箱或者其他邮箱

p = subprocess.Popen('hostname', shell=True, stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
hostname = p.stdout.readline().split('\n')[0]

message_to = ''
for i in receivers:
    message_to += i + ';'

def print_help():
    note = '''python script.py role ip vip
    '''
    print(note)
    exit(1)

time_stamp = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))

if len(sys.argv) != 4:
    print_help()
elif sys.argv[1] == 'master':
    message_content = '%s server: %s(%s) change to Master, VIP: %s' %(time_stamp, sys.argv[2], hostname, sys.argv[3])
    subject = '%s change to Master -- keepalived notify' %(sys.argv[2])
elif sys.argv[1] == 'backup':
    message_content = '%s server: %s(%s) change to Backup, VIP: %s' %(time_stamp, sys.argv[2], hostname, sys.argv[3])
    subject = '%s change to Backup -- keepalived notify' %(sys.argv[2])
else:
    print_help()

message = MIMEText(message_content, 'plain', 'utf-8')
message['From'] = Header(sender, 'utf-8')
message['To'] =  Header(message_to, 'utf-8')

message['Subject'] = Header(subject, 'utf-8')

try:
    smtpObj = smtplib.SMTP()
    smtpObj.connect(mail_host, 25)    # 25 为 SMTP 端口号
    smtpObj.login(mail_user,mail_pass)
    smtpObj.sendmail(sender, receivers, message.as_string())
    print("邮件发送成功")
except smtplib.SMTPException as e:
    print("Error: 无法发送邮件")
    print(e)

五、问题汇总

4.1没有killall命令

解决办法:yum install psmisc

4.2VIP无法ping通

解决办法:关闭防火墙和SELinux,如果要开启防火墙的话,需要开放keepalived的组播端口

上一篇:POJ 2970 The lazy programmer(优先队列+贪心)


下一篇:java中执行系统命令