mail工具发送mysql 慢查邮件

最新推荐文章于 2024-09-25 11:26:24 发布

陈娇娜!

最新推荐文章于 2024-09-25 11:26:24 发布

阅读量494

点赞数

分类专栏：运维

本文链接：https://blog.csdn.net/jiaona_chen123/article/details/105981933

版权

运维专栏收录该内容

13 篇文章 0 订阅

订阅专栏

mail工具发送mysql 慢查邮件

开启慢日志查询

[root@controller scripts]# grep "slow" /etc/my.cnf
slow_query_log=ON

设置慢查询时间为0.1s

mysql> show variables  like '%long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.100000 |
+-----------------+----------+
1 row in set (0.01 sec)

编辑脚本

[root@controller scripts]# cat mail.sh 
#!/bin/bash
slowfile="/data/mysql/data/controller-slow.log"
tmpslow="/data/mysql/data/tmp.log"
# create tmp-slow
if [ ! -e ${tmpslow}  ];then
        touch ${tmpslow}
else
        >${tmpslow}
fi

# slow-file copy to tmp-slow
if [ -s ${slowfile} ];then
        cat ${slowfile}  > ${tmpslow} 

fi
# send mail
        user="root"
        pass=" "
        host="127.0.0.1"
info() {
        time_first=`mysql -u$user -p$pass -h$host -e "select now();" |awk 'END{print}'`
        time_second=`mysql -u$user -p$pass -h$host -e "select ADDTIME(sysdate(),'0:10:0');" |awk 'END{print}'`
        StartTime=`grep -E "Time" /data/mysql/data/controller-slow.log | awk '{print $3}'`
        DBname=`mysql -u$user -p$pass -h$host  -e "select database();"|awk 'END{print}' `
        SQLtext=`awk 'END{print}' /data/mysql/data/controller-slow.log`
        repeat=`awk '/Rows_examined/{print $NF}' /data/mysql/data/controller-slow.log`
        QueryTimes=` awk '/Query_time/{print $3}' /data/mysql/data/controller-slow.log`
        HostAdress=`awk -F "[: ]+" '/User@Host/{print $3,$4,$5}' /data/mysql/data/controller-slow.log`
        LockTime=`awk '/Lock_time/{print $5}' /data/mysql/data/controller-slow.log`
        echo "time_range: " $time_first "-" $time_second
        echo -e "\n"
        echo "StartTime: " $StartTime
        echo "DBname: "$DBname
        echo "SQLtext: "$SQLtext
        echo "repeat: "$repeat
        echo "QueryTimes: "$QueryTimes
        echo "HostAdress: "$HostAdress
        echo "LockTime: "$LockTime
}
# 调用函数且发送邮件
if [ -s ${tmpslow} ];then
        main(){
        info >/data/mysql/data/info.log
        echo "mysql slow log already sending!" | mail -s "mysql slow log" 1987631089@qq.com < /data/mysql/data/info.log
}
main
fi
if [ -s ${slowfile} ];then
        >${slowfile}
fi

安装mailx软件

# yum install mailx -y

配置mailx

[root@controller scripts]# tail /etc/mail.rc 

set from=1987631089@qq.com

set smtp=smtp.qq.com                 

set smtp-auth-user=1987631089@qq.com

set smtp-auth-password=wdvoujxnowowbgid

set smtp-auth=login