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 [email protected]//发送邮件的邮箱
#set smtp=smtp.qq.com
set smtp.exmail.qq.com
set [email protected]//用户
set smtp-auth-password=nmwjxtkdyoqifehbxx//校验码需要根据发送邮件邮箱填写 网页QQ邮箱->设置->账户(开启IMAP/SMTP服务)点击生成授权码
set smtp-auth=login

mail 命令发送邮件

echo "test3" | mail -s "test" [email protected]
php 发送邮件

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


理由PHPMailer发送邮件


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




【基础代码】

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


<?php
require_once("PHPMailer-master/src/PHPMailer.php");
require_once("PHPMailer-master/src/SMTP.php");
class%20QQMailer
{
public%20static%20$HOST%20=%20'smtp.qq.com';%20//%20QQ%20邮箱的服务器地址
public%20static%20$PORT%20=%20465;%20//%20smtp%20服务器的远程服务器端口号
public%20static%20$SMTP%20=%20'ssl';%20//%20使用%20ssl%20加密方式登录
public%20static%20$CHARSET%20=%20'UTF-8';%20//%20设置发送的邮件的编码
private%20static%20$USERNAME%20=%20'[email protected]';%20//%20授权登录的账号
private%20static%20$PASSWORD%20=%20'nmwjxtkdyoqixxx';%20//%20授权登录的密码
private%20static%20$NICKNAME%20=%20'hello%20world';%20//%20发件人的昵称
/**
%20*%20QQMailer%20constructor.
%20*%[email protected]%20bool%20$debug%20[调试模式]
%20*/
public%20function%20__construct($debug%20=%20false)
{
%20$this->mailer%20=%20new%20PHPMailer/PHPMailer/PHPMailer();
%20$this->mailer->SMTPDebug%20=%20$debug%20?%201%20:%200;
%20$this->mailer->isSMTP();%20//%20使用%20SMTP%20方式发送邮件
}
/**
%20*%[email protected]%20PHPMailer
%20*/
public%20function%20getMailer()
{
%20return%20$this->mailer;
}
private%20function%20loadConfig()
{
%20/*%20Server%20Settings*/
%20$this->mailer->SMTPAuth%20=%20true;%20//%20开启%20SMTP%20认证
%20$this->mailer->Host%20=%20self::$HOST;%20//%20SMTP%20服务器地址
%20$this->mailer->Port%20=%20self::$PORT;%20//%20远程服务器端口号
%20$this->mailer->SMTPSecure%20=%20self::$SMTP;%20//%20登录认证方式
%20/*%20Account%20Settings%20*/
%20$this->mailer->Username%20=%20self::$USERNAME;%20//%20SMTP%20登录账号
%20$this->mailer->Password%20=%20self::$PASSWORD;%20//%20SMTP%20登录密码
%20$this->mailer->From%20=%20self::$USERNAME;%20//%20发件人邮箱地址
%20$this->mailer->FromName%20=%20self::$NICKNAME;%20//%20发件人昵称(任意内容)
%20/*%20Content%20Setting*/
%20$this->mailer->isHTML(true);%20//%20邮件正文是否为%20HTML
%20$this->mailer->CharSet%20=%20self::$CHARSET;%20//%20发送的邮件的编码
}
/**
%20*%20Add%20attachment
%20*%[email protected]%20$path%20[附件路径]
%20*/
public%20function%20addFile($path)
{
%20$this->mailer->addAttachment($path);
}
/**
%20*%20Send%20Email
%20*%[email protected]%20$email%20[收件人]
%20*%[email protected]%20$title%20[主题]
%20*%[email protected]%20$content%20[正文]
%20*%[email protected]%20bool%20[发送状态]
%20*/
public%20function%20send($email,%20$title,%20$content)
{
%20$this->loadConfig();
%20$this->mailer->addAddress($email);%20//%20收件人邮箱
%20$this->mailer->Subject%20=%20$title;%20//%20邮件主题
%20$this->mailer->Body%20=%20$content;%20//%20邮件信息
%20return%20(bool)$this->mailer->send();%20//%20发送邮件
}
}
<?php
require_once%20'QQMailer.php';
//%20实例化%20QQMailer
$mailer%20=%20new%20QQMailer(true);
//%20添加附件
$mailer->addFile('20130VL.jpg');
// 邮件标题
$title = '愿得一人心,白首不相离。';
// 邮件内容
$content = <<< EOF


皑如山上雪,皎若云间月。
闻君有两意,故来相决绝。
今日斗酒会,明旦沟水头。
躞蹀御沟上,沟水东西流。
凄凄复凄凄,嫁娶不须啼。
愿得一人心,白首不相离。
竹竿何袅袅,鱼尾何簁簁!
男儿重意气,何用钱刀为!


EOF;
// 发送QQ邮件
$mailer->send('[email protected]', $title, $content);



分享给朋友:
您可能感兴趣的文章:
随机阅读: