项目需要对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
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);