mysql监控 php&&shell 发送电子邮件

项目需要对MySQL的状态及性能进行监控。监控的指标包含是否存活、慢查询数、连接数、内存占用等指标。

shell脚本代码如下所示:(没有涉及发送邮件代码，可根据具体需求添加邮件发送代码)

watch.sh

#!/bin/bash
host=127.0.0.1
port=3306
user=root
password=792a1d086a2b
path=/usr/bin/
curFile=./status.txt
cacheFile=./last.cache
sockParam=`ps aux | grep -P "mysqld" | grep -oP " --socket.*\.sock"`
echo "MySQL sock info: "${sockParam}
MYSQL="${path}mysql -h${host} --port=${port} ${sockParam} -u${user} -p${password}"
MYSQL_ADMIN="${path}/mysqladmin -h${host} --port=${port} ${sockParam} -u${user} -p${password} "
#MySQL服务运行状态
curStatus=`${MYSQL} -e"show global status"`
#echo "mysql status: "${curStatus}
if [ -z "${curStatus}" ]
then
	portExists=0
else
    echo "${curStatus}" > ${curFile}
    portExists=1
fi
echo "MySQL服务运行状态: "${portExists}
#链接数
curConn=`${MYSQL_ADMIN} processlist -v | wc -l`
echo "MySQL当前连接数: "${curConn}
#线程数
curThread=`grep 'Threads_connected' ${curFile} | awk '{print $2}'`
echo "MySQL当前线程数: "${curThread}
#慢查询
curSlowQuery=`grep 'Slow_queries' ${curFile} | awk -F ' ' '{print $2}'`
lastSlowQuery=`grep 'Slow_queries' ${cacheFile} | awk -F ' ' '{print $2}'`
echo "Slow_queries ${curSlowQuery}" > ${cacheFile}
((diffSlowQuery=${curSlowQuery}-${lastSlowQuery}))
echo "MySQL慢查询数之差: "${diffSlowQuery}
#打开表数
curOpenTable=`grep 'Open_tables' ${curFile} | awk -F ' ' '{print $2}'`
echo "MySQL打开表数: "${curOpenTable}
#每秒执行select数
curSelect=`grep 'Com_select' ${curFile} | awk -F ' ' '{print $2}'`
echo "MySQL每秒执行select数: "${curSelect}
#每秒执行delete数
curDelete=`grep 'Com_delete' ${curFile} | grep -v 'multi' | awk -F ' ' '{print $2}'`
echo "MySQL每秒执行delete数: "${curDelete}
#每秒执行insert数
curDelete=`grep 'Com_delete' ${curFile} | grep -v 'multi' | awk -F ' ' '{print $2}'`
echo "MySQL每秒执行delete数: "${curDelete}
#每秒执行update数
curUpdate=`grep 'Com_update' ${curFile} | grep -v 'multi' | awk -F ' ' '{print $2}'`
echo "MySQL每秒执行update数: "${curUpdate}
#每秒执行replace数
curReplace=`grep 'Com_replace' ${curFile} | grep -v 'select' | awk -F ' ' '{print $2}'`
echo "MySQL每秒执行replace数: "${curReplace}
#每秒执行的innodb_rows_deleted
curInnodbRowsDelete=`grep 'Innodb_rows_deleted' ${curFile} | awk -F ' ' '{print $2}'`
echo "MySQL每秒执行的innodb_rows_deleted数: "${curInnodbRowsDelete}
#每秒执行的innodb_rows_inserted
curInnodbRowsInserted=`grep 'Innodb_rows_inserted' ${curFile} | awk -F ' ' '{print $2}'`
echo "MySQL每秒执行的innodb_rows_inserted数: "${curInnodbRowsInserted}
#每秒执行的innodb_rows_read
curInnodbRowsRead=`grep 'Innodb_rows_read' ${curFile} | awk -F ' ' '{print $2}'`
echo "MySQL每秒执行的innodb_rows_read数: "${curInnodbRowsRead}
#每秒执行的innodb_rows_updated数
curInnodbRowsUpdated=`grep 'Innodb_rows_updated' ${curFile} | awk -F ' ' '{print $2}'`
echo "MySQL每秒执行的innodb_rows_updated数: "${curInnodbRowsUpdated}
#每秒钟执行的innodb_rows_total
curInnodbRowsTotal=`expr ${curInnodbRowsDelete} + ${curInnodbRowsInserted} + ${curInnodbRowsRead} + ${curInnodbRowsUpdated}`
echo "MySQL每秒钟执行的innodb_rows_total数: "${curInnodbRowsTotal}
#每秒接收字节数 KByte/s
curBytesReceived=`grep 'Bytes_received' ${curFile} | awk -F ' ' '{print $2}'`
echo "MySQL每秒接收字节数 KByte/s: "${curBytesReceived}
#每秒发送字节数
curBytesSend=`grep 'Bytes_sent' ${curFile} | awk -F ' ' '{print $2}'`
echo "MySQL每秒发送字节数: "${curBytesSend}
#可立即获得锁的次数
curTableLock=`grep 'Table_locks_immediate' ${curFile} | awk -F ' ' '{print $2}'`
echo "MySQL可立即获得锁的次数: "${curTableLock}
#一行锁定需等待时间
curInnodbRowLockWaits=`grep 'Innodb_row_lock_waits' ${curFile} | awk -F ' ' '{print $2}'`
echo "MySQL一行锁定需等待时间: "${curInnodbRowLockWaits}
#当前脏页数
curInnodbBufferPoolPagesDirty=`grep 'Innodb_buffer_pool_pages_dirty' ${curFile} | awk -F ' ' '{print $2}'`
echo "MySQL当前脏页数: "${curInnodbBufferPoolPagesDirty}
#要求清空的缓冲池页数
curInnodbBufferPoolPageFlushed=`grep 'Innodb_buffer_pool_pages_flushed' ${curFile} | awk -F ' ' '{print $2}'`
echo "MySQL要求清空的缓冲池页数: "${curInnodbBufferPoolPageFlushed}
#innodb写入日志字节数 KByte
curInnodbOsLogWritten=`grep 'Innodb_os_log_written' ${curFile} | awk -F ' ' '{print $2}'`
echo "innodb写入日志字节数 KByte: "${curInnodbOsLogWritten}
#占用内存大小 MBytes
pid=`ps aux | grep 'mysqld' | grep -Ev 'safe|grep' | awk '{print $2}' `
mem=`cat /proc/${pid}/status | grep 'VmRSS' | awk '{print $2}'`
mysqlMem=`echo "scale=2;${mem} / 1024" | bc`
echo "MySQL占用内存大小 MBytes: "${mysqlMem}
#handler socket每秒处理数
TableLock=`grep 'Hs_table_lock' ${curFile} | awk '{print $2}'`
preHsTableLock=`grep 'Hs_table_lock' ${preFile} | awk '{print $2}'`
if [ -n "${curHsTableLock}" ]
then
    hsQPS=`echo "scale=0;(${curHsTableLock} - ${preHsTableLock}) / ${intervalTime}" | bc`
else
    hsQPS=0
fi
echo "MySQL handler socket每秒处理数: "${hsQPS}
#主从同步和状态
#主从信息
#是否为从服务器
slave_running=`grep 'Slave_running' ${curFile} | awk '{print $2}'`
if [ "${slave_running}A" = "ONA" ]
then
    slaveRunning=1
    slaveStatus=`${MYSQL} -e'show slave status\G'`
    echo "${slaveStatus}" > ${slaveFile}
    
    slaveIoRunning=`grep 'Slave_IO_Running' ${slaveFile} | awk -F ':' '{print $2}'`
    slaveSqlRunning=`grep 'Slave_SQL_Running' ${slaveFile} | awk -F ':' '{print $2}'`

    if [ "${slaveIoRunning}A" == "NoA" -o "${slaveSqlRunning}A" == "NoA" ]
    then
        slaveRunning=3
    fi
    
    secondsBehindMaster=`grep 'Seconds_Behind_Master' ${slaveFile} | awk -F ':' '{print $2}'`
    if [ "${secondsBehindMaster}A" = "NULLA" ]
    then
        secondsBehindMaster=8888  # 表示主从不同步
    fi

    #是从库时 获取主库ip
    master=`grep 'Master_Host' ${slaveFile} | awk -F ':' '{print $2}'`
    masterPort=`grep 'Master_Port' ${slaveFile} | awk -F ':' '{print $2}'`
else
    master=""
    masterPort=""
    slaveRunning=0
    secondsBehindMaster=10000  # 不用检测
fi

shell 发送告警邮件

利用linux自带的mail命令

修改配置文件 vim /etc/mail.rc

在文件最后添加如下参数

set from=120700@qq.com//发送邮件的邮箱
#set smtp=smtp.qq.com
set smtp.exmail.qq.com
set smtp-auth-user=120700@qq.com//用户
set smtp-auth-password=nmwjxtkdyoqifehbxx//校验码需要根据发送邮件邮箱填写 网页QQ邮箱->设置->账户（开启IMAP/SMTP服务）点击生成授权码
set smtp-auth=login

mail 命令发送邮件 

echo "test3" | mail -s "test" 1207016200@qq.com

php 发送邮件

PHPMailer 是一个封装好的 PHP 邮件发送类，支持发送 HTML 内容的电子邮件，以及可以添加附件发送，并不像 PHP 本身 mail() 函数需要服务器环境支持，您只需要设置邮件服务器以相关信息就能实现邮件发送功能。

理由PHPMailer发送邮件

PHPMailer 项目地址：https://github.com/PHPMailer/PHPMailer

【基础代码】

// 引入PHPMailer的核心文件
require_once("PHPMailer-master/src/PHPMailer.php");
require_once("PHPMailer-master/src/SMTP.php");

// 实例化PHPMailer核心类
$mail = new PHPMailer\PHPMailer\PHPMailer();
// 是否启用smtp的debug进行调试 开发环境建议开启 生产环境注释掉即可 默认关闭debug调试模式
$mail->SMTPDebug = 1;
// 使用smtp鉴权方式发送邮件
$mail->isSMTP();
// smtp需要鉴权 这个必须是true
$mail->SMTPAuth = true;
// 链接qq域名邮箱的服务器地址
$mail->Host = 'smtp.qq.com';
// 设置使用ssl加密方式登录鉴权
$mail->SMTPSecure = 'ssl';
// 设置ssl连接smtp服务器的远程服务器端口号
$mail->Port = 465;
// 设置发送的邮件的编码
$mail->CharSet = 'UTF-8';
// 设置发件人昵称 显示在收件人邮件的发件人邮箱地址前的发件人姓名
$mail->FromName = '发件人昵称';
// smtp登录的账号 QQ邮箱即可
$mail->Username = '120700@qq.com';
// smtp登录的密码 使用生成的授权码
$mail->Password = 'nmwjxtkdyoqixxxx';
// 设置发件人邮箱地址 同登录账号
$mail->From = '120700@qq.com';
// 邮件正文是否为html编码 注意此处是一个方法
$mail->isHTML(true);
// 设置收件人邮箱地址
$mail->addAddress('120700@qq.com');
// 添加多个收件人 则多次调用方法即可
$mail->addAddress('123456@163.com');
// 添加该邮件的主题
$mail->Subject = '邮件主题';
// 添加邮件正文
$mail->Body = '<h1>Hello World</h1>';
// 为该邮件添加附件
$mail->addAttachment('./example.pdf');
// 发送邮件 返回状态
$status = $mail->send();

【封装方法】

<?php
require_once("PHPMailer-master/src/PHPMailer.php");
require_once("PHPMailer-master/src/SMTP.php");

class QQMailer
{
    public static $HOST = 'smtp.qq.com'; // QQ 邮箱的服务器地址
    public static $PORT = 465; // smtp 服务器的远程服务器端口号
    public static $SMTP = 'ssl'; // 使用 ssl 加密方式登录
    public static $CHARSET = 'UTF-8'; // 设置发送的邮件的编码

    private static $USERNAME = '120700@qq.com'; // 授权登录的账号
    private static $PASSWORD = 'nmwjxtkdyoqixxx'; // 授权登录的密码
    private static $NICKNAME = 'hello world'; // 发件人的昵称

    /**
     * QQMailer constructor.
     * @param bool $debug [调试模式]
     */
    public function __construct($debug = false)
    {
        $this->mailer = new PHPMailer\PHPMailer\PHPMailer();
        $this->mailer->SMTPDebug = $debug ? 1 : 0;
        $this->mailer->isSMTP(); // 使用 SMTP 方式发送邮件
    }

    /**
     * @return PHPMailer
     */
    public function getMailer()
    {
        return $this->mailer;
    }

    private function loadConfig()
    {
        /* Server Settings  */
        $this->mailer->SMTPAuth = true; // 开启 SMTP 认证
        $this->mailer->Host = self::$HOST; // SMTP 服务器地址
        $this->mailer->Port = self::$PORT; // 远程服务器端口号
        $this->mailer->SMTPSecure = self::$SMTP; // 登录认证方式
        /* Account Settings */
        $this->mailer->Username = self::$USERNAME; // SMTP 登录账号
        $this->mailer->Password = self::$PASSWORD; // SMTP 登录密码
        $this->mailer->From = self::$USERNAME; // 发件人邮箱地址
        $this->mailer->FromName = self::$NICKNAME; // 发件人昵称（任意内容）
        /* Content Setting  */
        $this->mailer->isHTML(true); // 邮件正文是否为 HTML
        $this->mailer->CharSet = self::$CHARSET; // 发送的邮件的编码
    }

    /**
     * Add attachment
     * @param $path [附件路径]
     */
    public function addFile($path)
    {
        $this->mailer->addAttachment($path);
    }


    /**
     * Send Email
     * @param $email [收件人]
     * @param $title [主题]
     * @param $content [正文]
     * @return bool [发送状态]
     */
    public function send($email, $title, $content)
    {
        $this->loadConfig();
        $this->mailer->addAddress($email); // 收件人邮箱
        $this->mailer->Subject = $title; // 邮件主题
        $this->mailer->Body = $content; // 邮件信息
        return (bool)$this->mailer->send(); // 发送邮件
    }
}

<?php
require_once 'QQMailer.php';

// 实例化 QQMailer
$mailer = new QQMailer(true);
// 添加附件
$mailer->addFile('20130VL.jpg');
// 邮件标题
$title = '愿得一人心，白首不相离。';
// 邮件内容
$content = <<< EOF
<p align="center">
皑如山上雪，皎若云间月。<br>
闻君有两意，故来相决绝。<br>
今日斗酒会，明旦沟水头。<br>
躞蹀御沟上，沟水东西流。<br>
凄凄复凄凄，嫁娶不须啼。<br>
愿得一人心，白首不相离。<br>
竹竿何袅袅，鱼尾何簁簁！<br>
男儿重意气，何用钱刀为！</p>
EOF;
// 发送QQ邮件
$mailer->send('1207016200@qq.com', $title, $content);