mysql replication 运维脚本收集

#!/usr/bin/env bash


## Matthew Montgomery ##

## [email][e-mail][/email] ##


repeat_alert_interval=15 # minutes

lock_file=/tmp/slave_alert.lck
active=yes

## Check if alert is already sent ##


function check_alert_lock () {
    if [ -f $lock_file ] ; then
        current_file=`find $lock_file -cmin -$repeat_alert_interval`
        if [ -n "$current_file" ] ; then
            # echo "Current lock file found"

            return 1
        else
            # echo "Expired lock file found"

            return 2
        fi
    else
    return 0
    fi
}

## Find the location of the mysql.sock file ##


function check_for_socket () {
        if [ -z $socket ] ; then
                if [ -S /var/lib/mysql/mysql.sock ] ; then
                        socket=/var/lib/mysql/mysql.sock
                elif [ -S /tmp/mysql.sock ] ; then
                        socket=/tmp/mysql.sock
                else
                        ps_socket=`netstat -ln | egrep "mysql(d)?\.sock" | awk '{ print $9 }'`
                        if [ "$ps_socket" ] ; then
                        socket=$ps_socket
                        fi
                fi
        fi
        if [ -S "$socket" ] ; then
                echo UP > /dev/null
        else
                echo "No valid socket file "$socket" found!"
                echo "mysqld is not running or it is installed in a custom location"
                echo "Please set the $socket variable at the top of this script."
                exit 1
        fi
}

check_for_socket

Slave_IO_Running=`mysql -Bse "show slave status\G" | grep Slave_IO_Running | awk '{ print $2 }'`
Slave_SQL_Running=`mysql -Bse "show slave status\G" | grep Slave_SQL_Running | awk '{ print $2 }'`
Last_error=`mysql -Bse "show slave status\G" | grep Last_error | awk -F \: '{ print $2 }'`


if [ -z $Slave_IO_Running -o -z $Slave_SQL_Running ] ; then
        echo "Replication is not configured or you do not have the required access to MySQL"
        exit
fi

if [ $Slave_IO_Running == 'Yes' ] && [ $Slave_SQL_Running == 'Yes' ] ; then
    if [ -f $lock_file ] ; then
        rm $lock_file
        echo "Replication slave is running"
        echo "Removed Alert Lock"
    fi
    exit 0
elif [ $Slave_SQL_Running == 'No' ] ; then
    if [ $active == 'yes' ] ; then
        check_alert_lock
        if [ $? = 1 ] ; then
            ## Current Lock ##

            echo "up" > /dev/null
        else
            ## Stale/No Lock ##

            touch $lock_file
            echo "SQL thread not running on server `hostname -s`!"
            echo "Last Error:" $Last_error
        fi
    fi
    exit 1
elif [ $Slave_IO_Running == 'No' ] ; then
        if [ $active == 'yes' ] ; then
                check_alert_lock
                if [ $? = 1 ] ; then
                        ## Current Lock ##

            echo "up" > /dev/null
                else
                        ## Stale/No Lock ##

                        touch $lock_file
                        echo "LOG IO thread not running on server `hostname -s`!"
                        echo "Last Error:" $Last_error
                fi
    fi
    exit 1
else
        if [ $active == 'yes' ] ; then
                check_alert_lock
                if [ $? = 1 ] ; then
                        ## Current Lock ##

            echo "up" > /dev/null
                else
                        ## Stale/No Lock ##

                        touch $lock_file
            echo "Unexpected Error!"
            echo "Check Your permissions!"
                fi
        fi
    exit 2
fi


#!/bin/bash

# 译者:龙力勤

# 最新更新:2009-08-16

# 出自懒人运维:

# Eamil:[e-mail]

# 用途:mysql监控

# 描述:shell脚本监控mysql服务，并在服务停止时自动开启

# 更多关于此脚本的讨论，请参考:/2009/08/821.html

# 用法:./scriptname.sh

 
# mysql root/admin username

MUSER="root"
# mysql admin/root password

MPASS="SET-ROOT-PASSWORD"
# mysql server hostname

MHOST="localhost"
#Shell script to start MySQL server i.e. path to MySQL daemon start/stop script.

# Debain uses following script, need to setup this according to your UNIX/Linux/BSD OS.

MSTART="/etc/init.d/mysql start"
# Email ID to send notification

EMAILID="[e-mail]"
# path to mail program

MAILCMD="$(which mail)"
# path mysqladmin

MADMIN="$(which mysqladmin)"
 
#### DO NOT CHANGE anything BELOW ####

MAILMESSAGE="/tmp/mysql.fail.$$"
 
# see if MySQL server is alive or not

# 2&1 could be better but i would like to keep it simple and easy to

# understand stuff :)

$MADMIN -h $MHOST -u $MUSER -p${MPASS} ping 2>/dev/null 1>/dev/null
if [ $? -ne 0 ]; then
 echo "" >$MAILMESSAGE
 echo "Error: MySQL Server is not running/responding ping request">>$MAILMESSAGE
 echo "Hostname: $(hostname)" >>$MAILMESSAGE
 echo "Date & Time: $(date)" >>$MAILMESSAGE
 # try to start mysql

 $MSTART>/dev/null
 # see if it is started or not

 o=$(ps cax | grep -c ' mysqld$')
 if [ $o -eq 1 ]; then
  sMess="MySQL Server MySQL server successfully restarted"
 else
  sMess="MySQL server FAILED to restart"
 fi
 # Email status too

 echo "Current Status: $sMess" >>$MAILMESSAGE
 echo "" >>$MAILMESSAGE
 echo "*** This email generated by $(basename $0) shell script ***" >>$MAILMESSAGE
 echo "*** Please don't reply this email, this is just notification email ***" >>$MAILMESSAGE
 # send email

 $MAILCMD -s "MySQL server" $EMAILID < $MAILMESSAGE
else # MySQL is running :) and do nothing

 :
fi
# remove file

rm -f $MAILMESSAGE

#!/bin/sh

#

# created by yejr, 2007/06/03

#

# 本脚本用于监控MySQL 复制是否运行，并且根据具体的错误代码自动判断是否忽略

#


now=`date +"%Y%m%d%H%M%S"`

StatFile="./slave_status.$now"

echo "show slave status\G" | mysql -uroot -pmypasswd > $StatFile

#取得 io_thread, sql_thread, last_errno 的状态

IoStat=`cat $StatFile | grep Slave_IO_Running | awk '{print $2}'`
SqlStat=`cat $StatFile | grep Slave_SQL_Running | awk '{ print $2}'`
Errno=`cat $StatFile | grep Last_Errno | awk '{print $2}'`
Behind=`cat $StatFile | grep Seconds_Behind_Master | awk '{print $2}'`

#IoStat=`cat $StatFile | head -n 12 | tail -n 1 | awk '{print $2}'`

#SqlStat=`cat $StatFile | head -n 13 | tail -n 1 | awk '{print $2}'`

#Errno=`cat $StatFile | head -n 20 | tail -n 1 | awk '{print $2}'`



if [ $IoStat = 'No' ] || [ $SqlStat = 'No' ] ; then

echo "[chkslave]"
date
#如果错误代码为 0，则可能是因为网络等原因导致复制中断，直接重新启动复制即可

if [ "$Errno" -eq 0 ] ; then
  echo "start slave io_thread; start slave sql_thread;" | mysql -uroot -pmypasswd
  echo "start slave io_thread; start slave sql_thread;"
#如果是一些不是很要紧的错误代码，也可以直接略过

elif [ "$Errno" -eq 1007 ] || [ "$Errno" -eq 1053 ] || [ "$Errno" -eq 1062 ] || [ "$Errno" -eq 1213 ] \
|| [ "$Errno" -eq 1158 ] || [ "$Errno" -eq 1159 ] || [ "$Errno" -eq 1008 ] ; then
  echo "stop slave; set global sql_slave_skip_counter=1; slave start;" | mysql -uroot -pmypasswd
  echo "stop slave; set global sql_slave_skip_counter=1; slave start;"
else
  echo `date` "slave is down!!!"
fi

# 远远落后于 master

if [ $Behind -gt 200 ] ; then
   echo `date` "slave is behind master $Behind seconds!!!"
fi

#删除临时状态文件

rm -f $StatFile

echo "[/chkslave]"

fi