MySQL 之 MHA + ProxySQL + keepalived 实现读写分离，高可用（三）

设置Keepalived VIP切换邮件告警

修改keepalived.conf配置：

[root@server01 keepalived]# cat keepalived.conf 
! Configuration File for keepalived

global_defs {
     notification_email {
     [e-mail]
   }
   notification_email_from [e-mail]
   smtp_server 127.0.0.1
   smtp_connect_timeout 30
   router_id MySQL-HA
}
vrrp_script vs_mysql_100 {
    script "python /etc/keepalived/checkProxySQL.py -h localhost -P 6033"
    interval 5
}
vrrp_instance VI_1 {
    state BACKUP
    nopreempt
    interface eth0
    virtual_router_id 51
    priority 150
    advert_int 1
    nopreempt

    authentication {
    auth_type PASS
    auth_pass 1111
    }
    track_script {
       vs_mysql_100
    }
    virtual_ipaddress {
        10.200.22.178
    }
    notify_master "/etc/keepalived/notify.sh master"
    notify_backup "/etc/keepalived/notify.sh backup"
    notify_fault "/etc/keepalived/notify.sh fault"
}

通知 notify.sh：

#!/bin/bash

contact='root@localhost'

notify() {
    local mailsubject="$(hostname) to be $1, vip floating"
    local mailbody="$(date +'%F %T'): vrrp transition, $(hostname) changed to be $1"
    python /etc/keepalived/sendMail.py "$mailsubject" "$mailbody"
    echo $mailbody >> /etc/keepalived/notify.log
}

case $1 in
    master)
        notify master
        ;;
    backup)
        notify backup
        ;;
    fault)
        notify fault
        ;;
    *)
    echo "Usage: $(basename $0) {master|backup|fault}"
    exit 1
    ;;
esac

发送邮件 sendMail.py：

#!/usr/bin/python
# -*- coding: utf-8 -*-
# author  xuaiqi
# date    20190418

import os
import time
import shutil
import MySQLdb
import smtplib
#import requests
import datetime

from email.MIMEText import MIMEText
from email.MIMEImage import MIMEImage
from email.MIMEMultipart import MIMEMultipart
from email.MIMEBase import MIMEBase
from email import Encoders
from datetime import datetime

import sys
default_encoding = 'utf-8'
if sys.getdefaultencoding() != default_encoding:
    reload(sys)
    sys.setdefaultencoding(default_encoding)

# email info
mailserver = "smtp.vcredit.com" 
EMAIL_USERNAME = '[e-mail]'
EMAIL_PASSWORD = 'dbmonitor1234!!'

title=sys.argv[1]
contents=sys.argv[2]

#发送邮件
def send_mail(title, to_list):
    me = "[e-mail]"

    def _create_msg():
        msg = MIMEMultipart('related')
        msg['Subject'] =  title
        msg['From'] = me
        msg['To'] = ';'.join(to_list)
        msg.preamble = 'This is a multi-part message in MIME format.'            
        msg_text = MIMEText(contents, 'html','utf-8')
        msg_alternative = MIMEMultipart('alternative')
        msg_alternative.attach(msg_text)
        msg.attach(msg_alternative)
        #print msg
        return msg
    try:
        server = smtplib.SMTP()
        server.connect(mailserver)
        server.login(EMAIL_USERNAME, EMAIL_PASSWORD)
        server.sendmail(me, to_list, _create_msg().as_string())
        server.close()
        print 'send mail Ok!'
    except Exception, e:
        print e



print"--------------------------------------", datetime.now().strftime('%Y-%m-%d %H:%M:%S'),"Start sending mail --------------------"
if __name__ == '__main__':
    user=['[e-mail]','[e-mail]','[e-mail]']
    print "发送的用户",user
    print "内容",contents
    send_mail(title, user)
print"---------------------------------", datetime.now().strftime('%Y-%m-%d %H:%M:%S'),"Sucessfull sending mail --------------------"

 

设置MHA切换邮件通知

报告脚本 send_report.py：

#!/usr/bin/env python
#-*- encoding:utf-8 -*-
#-------------------------------------------------------------------------------
# Name:        send_report.py
# Author:      zhoujy
#----------------------------------------------
import os
import sys
import time
import datetime
import smtplib
import subprocess
import fileinput
import getopt
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.Utils import COMMASPACE, formatdate

reload(sys)
sys.setdefaultencoding('utf8')

def send_mail(to, subject, text, from_mail, server="localhost"):
    message = MIMEMultipart()
    message['From'] = from_mail
    message['To'] = COMMASPACE.join(to)
    message['Date'] = formatdate(localtime=True)
    message['Subject'] = subject
    message.attach(MIMEText(text,_charset='utf-8'))
    smtp = smtplib.SMTP(server)
    smtp.login(from_mail, 'dbmonitor1234!!')
    smtp.sendmail(from_mail, to, message.as_string())
    smtp.close()

if __name__ == "__main__":
    opts,args = getopt.getopt(sys.argv[1:],"h",["orig_master_host=","new_master_host=","new_slave_hosts=","conf=","subject=","body=","app_vip=","new_master_ssh_port=","ssh_user="])
#    print opts,args
    for lines in opts:
        key,values = lines
        if key == '--orig_master_host':
            orig_master_host = values
        if key == '--new_master_host':
            new_master_host = values
        if key == '--new_slave_hosts':
            new_slave_hosts = values
        if key == '--subject':
            subject = values
        if key == '--body':
            body = values
#    text = sys.stdin.read()
    mail_list = ['[e-mail]','[e-mail]','[e-mail]']
    send_mail(mail_list, subject.encode("utf8"), body, "[e-mail]", server="smtp.vcredit.com")

Master基础配置：

[server default]
user=root
password=iforgot
ssh_user=root
repl_user=repl
repl_password=repl
ping_interval=1
#master_binlog_dir= /var/lib/mysql,/var/log/mysql
secondary_check_script=masterha_secondary_check -s 10.200.22.136 -s 10.200.22.138 -s 10.200.22.223
master_ip_failover_script="/etc/mha/scripts/master_ip_failover"
master_ip_online_change_script="/etc/mha/scripts/master_ip_online_change"
report_script="/etc/mha/scripts/send_report.py"

 

测试验证

keepalived vip切换

 

 

 

 

mha切换