MySQL Slow log monitor script instance anatomy

MySQL Slow Log on the company line, has not been well monitored. While I was idle last week, I wrote the monitoring script, and today I specially sent out the code to share with 51 Bo friends.

For the annotations and the overall idea of the script, I'll put it in the script and explain it to you.

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465 66676869707172737475767778798081828384858687888990919293

#!/bin/bash## 本脚本用来在指定频率内监控 MySQL 慢日志的变化，并在发生增长时及时报警# Written by sunsky# Mail : [email protected]# Date : 2014-11-17 10:00:00#MON_FILE="$2"# 指定所要监控的脚本路径SEC=60          # 指定所要监控的频率，即间隔多久去查看一次MON_POINT_FILE=/tmp/mon_mysql_slow.point  # 指定MySQL慢日志的监控点存放的路径DIFF_MON_FILE=/tmp/mon_mysql_slow.log     # 指定在监控频率内增加的MySQL慢日志信息存放路径[email protected]            # 指定发送给哪个管理员functionUSAGE {    echo-e "\033[31m脚本名称: \033[37m"    echo "    $0"    echo-e "\033[31m语法结构: \033[37m"    echo"    $0 {start|stop|restart} MySQL慢日志文件路径"    echo-e "\033[31m使用范例: \033[37m"    echo "    $0 start /usr/local/mysql/log/mysql_slow.log"    echo"    $0 stop"    echo"    $0 restart /usr/local/mysql/log/mysql_slow.log"    echo-e "\033[31m注意事项: \033[37m"    echo"    1. 除了stop操作,start和restart操作时,\$2 参数不能为空"    echo"    2. \$2 参数指定的文件必须存在"    exit2}functionstart {    echo "MySQL慢日志监控进程已经启动,监控文件为 $MON_FILE ,监控频率为 ${SEC}s一次."    while:    do        [ -f $MON_POINT_FILE ] || echo0 > $MON_POINT_FILE        NEW_POINT=$(awk ‘END{print NR}‘$MON_FILE)        OLD_POINT=$(<$MON_POINT_FILE)        [[ -z $OLD_POINT ]]&&OLD_POINT=0        SUM_POINT0=$(($NEW_POINT-$OLD_POINT))        SUM_POINT=${SUM_POINT0#-}        tail-$SUM_POINT $MON_FILE > $DIFF_MON_FILE        if[[ -s $DIFF_MON_FILE ]];then            sed-i ‘1i 本次新增慢日志 ‘$SUM_POINT‘ 条‘$DIFF_MON_FILE            mail -s "[警告] 服务器 $(hostname) 产生 MySQL 慢日志 $SUM_POINT 条"$ADMIN_MAIL < $DIFF_MON_FILE            > $DIFF_MON_FILE            echo$NEW_POINT > $MON_POINT_FILE        fi        sleep${SEC}s    done}functionstop {        if[[ -n `ps-ef|awk ‘$0~"mon_mysql_slow_log.sh"{print $2}‘` ]];            then                forPID in`ps-ef|awk‘$0~"mon_mysql_slow_log.sh"{print $2}‘`;                       do                           [[ $PID != $$ ]] && kill-9 $PID >& /dev/null                       done            else                echo‘目前暂无MySQL慢日志监控进程‘                exit0        fi        echo‘MySQL慢日志监控进程已经停止运行‘}functionrestart {        stop        start &}if[[ $1 == stop ]]    then        :    else        [[ $2 < 3 ]] && USAGE        [[ ! -f $2 ]] && USAGEficase$1 in    start)        start &        ;;    stop)        stop        ;;    restart)        MON_FILE=$2        restart        ;;    *)        USAGE        ;;esac

Ok!

The above is the entire contents of the script. The entire script is comprised of four main functions. For the purpose of each function, here's what I do:

1234	USAGE   # 该函数负责提示用户如何正确使用该脚本start   # 该函数负责启动脚本stop    # 该函数负责停止监控脚本restart # 该函数负责重启监控脚本

The following script is used:

The whole idea of the script is to start a dead loop through the while:;d o statement;done, and then control the cycle interval of the dead loop through sleep in the dead loop. In the specified cycle interval, the log is changed by taking the length of the MySQL slow log as a record point, and then when the next loop arrives, by comparison to the previous record point. If there is a change, the growth value is calculated by calculating the difference of the record point. This portion of the log that grows is obtained by increasing the value, and then sent to the specified administrator mailbox via mail.

Here are the messages sent out:

Ok! The other one can understand, here is no nonsense.

This article concludes, hope to be able to help 51 Bo friends!

MySQL Slow log monitor script instance anatomy