How to detect MySQL master and slave status in real time and do email alerts?

Real-time detection of MySQL master-slave status and email alerts

Requirements Analysis:

When we finish the master-slave, master and slave success, slave_io_running and slave_sql_running two process state is yes, but in the decision from synchronization also do not exclude, there will be some unexpected situation, so we need to write a script real-time detection slave_ Io_running and slave_sql_running status, once the MySQL master and slave status has occurred, send an alarm by mail, and then do maintenance.

Scripting ideas:

Regular check MySQL master-slave state, White is through the script to let the system to self-detect the master-slave state, if the master-slave state is normal, then do not alarm out, but once the state is abnormal, by sending mail to the operation and maintenance personnel, operations and personnel received the alarm email, and then do the related treatment

1, e-mail alarm, I am directly using a Python script to send mail messages, you can also Yum pack a SendMail package, do the information sent, the script is as follows.

[[Email protected] ~]# cd /python/send_mail.py#!/usr/bin/python#coding: utf-8import  smtplibimport sysfrom email.mime.text import MIMEText_user =  "847536**@ Qq.com "    # #这里填写你的邮件号_pwd   = " ilbsly****** "   # #这里填写你邮件的密码 &NBSP, I use the QQ mailbox (other mailboxes can also), QQ mailbox password is to apply to the QQ mailbox, and then it will generate a password #_to   =  "[email protected] "    # #这里是发送给谁, this is generally commented out, in the execution of the script to specify who can be sent Def send_mail (To,subject,contain):     msg = mimetext (contain)     msg["Subject"] = subject     msg["from"]    = _user    msg["to"]       = to    try:         s = smtplib. Smtp_ssl ("smtp.qq.com",  465)         s.login (_user, _pwd)         s.sendmail (_user, to, msg.as_string ())          s.quit ()         with open ('/ Tmp/zabbix.log ',  ' W ')  as f:             f.write ("%s\n%s\n%s\n"% (to,subject,contain)) #        print  "Success!"     except smtplib. smtpexception,e:        print  "Falied,%s"%eif __name__  ==  "__main__":     send_mail (Sys.argv[1], sys.argv[2], sys.argv[3])

2. Test script Writing

Next, write a detection master-slave script, according to our thinking, the first is to get the state of slave_io_running and slave_sql_running, and then use the IF statement to determine if the status of no on the Python script to send mail, serious, If the MySQL service from the server down, we also send the message alarm, the entire process execution process, write the time task inside, here we need to use functions to achieve, this convenient, next I detailed analysis.

[[Email protected] ~]# cd /python/checkslave.sh #!/bin/bash   send_ mail  () {            # #定义一个函数, used to do the integration to detect MySQL state content. Ip= ' ifconfig eth0 | sed -n 2p | awk  ' {print $2} '  | cut  -d: -f2 '    # #获取本地的ip, my local NIC is eth0, use sed, awk, cut command to get IP address hostname= ' hostname '        # #获取本地的主机名status = (' mysql -uroot -pmichael123456  -e  ' Show slave status\g "|grep " Running " |awk  ' {print $2} ')    ## Using the MySQL command to get the status of Slave_io_running and slave_sql_running, the result is (yes, yes, slave), because there are three elements, so we can use tuples to implement netstat - ntpl | grep 3306  # #查看3306是否存在, used to detect the state of MySQL   if [ $? -eq  0 ];then    # #写一个判断语句如果netstat命令执行成功话, Output $? is 0, does not exist $? output greater than 0      if [  "${statUs[0]} " == " Yes " ] | |  [  "${status[1]}"  ==  "Yes"  ] ;then       ## Gets the number of the 0th element of the tuple and the first element, using the If judgment, with a set.             echo  "Master-slave replication Success"        else    # #做个判断              python /python/send_mail.py  "[email protected]"   "$hostname: $ip-Master-slave replication Failed"   "$hostname: $ip- slave_io_running  or  slave_sql_running already for no please as soon as possible: do maintenance treatment"               # #当输出的元组的结果不为Yes, then execute, mail send alarm $hostname , $ IP for Get parameters                          sed  -i  ' s/^*\/10/#&/g '  /etc/crontab                # #为了防止, it has been in the mail alarm, we need to use SED comment off, scheduled task, this function is only allowed to send fault after,              #脚本只执行一次, of course, if you want to send two emails, you can also add mail commands two times.      fielse     python /python/send_mail.py  "[ Email protected] " " $hostname: $ip-slave host MySQL service has been turned off " " $hostname: $ip-The host has stopped running please do maintenance as soon as possible "      # #netstat输出 $? The result is not 0, sending an alarm. fi}$1   # #添加函数的参数, let yourself decide whether to execute the function crontab  ()  {  cat /etc/crontab |  grep  "bash $0 send_mail "    # #查找是否有计划任务   if [ $?  -eq 0 ]; then    # #做判断, there is the output, there is no write      echo   "The port test has been written to the time task"    else     echo  "*/10 * * * *  root   bash $0 send_mail  " >> /etc/crontab # #定义每十分钟, execute once   fi   } crontab 

3. Implementation results

[[email protected] ~]# bash/python/checkslave.sh #执行第一次, can't see the effect

# #通过函数来执行, you can see that it's already working,

[[email protected] ~]# bash/python/checkslave.sh send_mail warning:using a password on the command line interface can b  E insecure.tcp 0 0::: 3306:::* LISTEN 27684/mysqld master-slave copy succeeded */10 * * * * Root bash/python/checkslave.sh send_mail The port test has been written to the time task

4. Analog fault

I stopped the slave and waited a few minutes for the mail to be received, and the results were as follows:

5. Summary

After writing a script, I found that I didn't have to check the status of slave at intervals. A lot of convenience, of course, there are other methods, also can be the master from the replication status monitoring.

How to detect MySQL master and slave status in real time and do email alerts?