Mysql keepalived 缺陷_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

三、安装步骤

3.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

3.2源码安装

yum -y install openssl* libnl‐dev* gcc-c++

./configure --prefix=/usr/local/keepalived

make

make install

3.3配置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/

3.4修改keepalived配置文件

3.4.1以下在Keepalived MASTER上修改

! 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 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"

}

3.4.2以下在Keepalived 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邮件报警脚本发送告警邮件

发送邮箱请自行设置

3.5启动keepalived

systemctl start keepalived

systemctl enable keepalived

启动参数说明：

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

3.6查看VIP绑定情况

以下是MASTER

[root@node3 keepalived_monitor]# ip a

1: lo: 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: 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: 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: 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: 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: 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: 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: 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)

五、问题汇总

5.1没有killall命令

解决办法：yum install psmisc

5.2VIP无法ping通

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