巡检脚本（daily_check.sh）
#!/bin/bash
server_list=`cat /home/mysql/server_list`
check_date=`date "+%Y-%m-%d %H:%M:%S"`
current_date=`date +%F`
echo "" > /home/mysql/mysqlcheck/insert_daily_check.sql
echo "<p>GEMDALE Mysql daily check" > /home/mysql/mysqlcheck/daily_check_${current_date}.html
echo "<p>password is about to expire or has expired:" > /home/mysql/mysqlcheck/daily_check_tmp.html
for server in $server_list
do
server_port=`grep $server /home/mysql/server_list_withport |awk '{print $NF}'`
hname=`ssh $server -p $server_port hostname`
ip=`ssh $server -p $server_port ip a|grep 'inet '|grep -v '127.0.0.1'|awk '{print $2}'|awk -F '/' 'NR==1 {print $1}'`
mysql_port=`ssh $server -p $server_port netstat -nultp|grep mysqld|awk '{print $4}'|awk -F ':' '{print $NF}'|grep -v 33060|grep -v 4567`
mysql_ping=`/usr/bin/mysqladmin ping -umonitor -ppassword_xxx -h $ip -P $mysql_port`
mysql_status=`/usr/bin/mysql -umonitor -ppassword_xxx -h $ip -P $mysql_port -e 'status'`
mysql_version=`echo "${mysql_status}"|grep "Server version"|awk '{print $3}'|awk -F '-' '{print $1}'`
mysql_db_size=`/usr/bin/mysql -umonitor -ppassword_xxx -h $ip -P $mysql_port -e 'SELECT sum(round((data_length+index_length)/1024/1024,2)) FROM information_schema.TABLES' -s|tail -1`
mysql_expiry=`/usr/bin/mysql -umonitor -ppassword_xxx -h $ip -P $mysql_port -H -e "select user,host,password_last_changed,IFNULL(password_lifetime,@@default_password_lifetime) as password_lifetime,date_add(password_last_changed,interval IFNULL(password_lifetime,@@default_password_lifetime) DAY) as expiry_date from mysql.user where date_add(password_last_changed,interval IFNULL(password_lifetime,@@default_password_lifetime) DAY) < date_add(SYSDATE(),interval 10 day) and date_add(password_last_changed,interval IFNULL(password_lifetime,@@default_password_lifetime) DAY)!=password_last_changed and user not in ('mysql.session','mysql.sys','mysql.infoschema');"`
echo "insert into daily_check values ('${check_date}','${hname}','${ip}','${mysql_port}','${mysql_ping}','${mysql_status}','${mysql_version}','${mysql_db_size}');">> /home/mysql/mysqlcheck/insert_daily_check.sql
echo "commit;">>/home/mysql/mysqlcheck/insert_daily_check.sql
if [ -n "${mysql_expiry}" ];then
echo "<p>${hname}-${ip}:" >> /home/mysql/mysqlcheck/daily_check_tmp.html
echo "${mysql_expiry}" >> /home/mysql/mysqlcheck/daily_check_tmp.html
fi
done
# for windows mysql
server_list_w=`cat /home/mysql/server_list_w |awk '{print $2}'`
for server in $server_list_w
do
hname=${server}
ip=`grep ${server} /home/mysql/server_list_w|awk '{print $1}'`
mysql_port=`grep ${server} /home/mysql/server_list_w|awk '{print $3}'`
mysql_ping=`/usr/bin/mysqladmin ping -umonitor -ppassword_xxx -h $ip -P $mysql_port`
mysql_status=`/usr/bin/mysql -umonitor -ppassword_xxx -h $ip -P $mysql_port -e 'status'`
mysql_version=`echo "${mysql_status}"|grep "Server version"|awk '{print $3}'|awk -F '-' '{print $1}'`
mysql_db_size=`/usr/bin/mysql -umonitor -ppassword_xxx -h $ip -P $mysql_port -e 'SELECT sum(round((data_length+index_length)/1024/1024,2)) FROM information_schema.TABLES' -s|tail -1`
mysql_expiry=`/usr/bin/mysql -umonitor -ppassword_xxx -h $ip -P $mysql_port -H -e "select user,host,password_last_changed,IFNULL(password_lifetime,@@default_password_lifetime) as password_lifetime,date_add(password_last_changed,interval IFNULL(password_lifetime,@@default_password_lifetime) DAY) as expiry_date from mysql.user where date_add(password_last_changed,interval IFNULL(password_lifetime,@@default_password_lifetime) DAY) < date_add(SYSDATE(),interval 10 day) and date_add(password_last_changed,interval IFNULL(password_lifetime,@@default_password_lifetime) DAY)!=password_last_changed and user not in ('mysql.session','mysql.sys','mysql.infoschema');"`
echo "insert into daily_check values ('${check_date}','${hname}','${ip}','${mysql_port}','${mysql_ping}','${mysql_status}','${mysql_version}','${mysql_db_size}');">> /home/mysql/mysqlcheck/insert_daily_check.sql
echo "commit;">>/home/mysql/mysqlcheck/insert_daily_check.sql
if [ -n "${mysql_expiry}" ];then
echo "<p>${hname}-${ip}:" >> /home/mysql/mysqlcheck/daily_check_tmp.html
echo "${mysql_expiry}" >> /home/mysql/mysqlcheck/daily_check_tmp.html
fi
done
# insert the daily check result
mysql -udaily_check -ppassword_xxx mysql_daily_check < /home/mysql/mysqlcheck/insert_daily_check.sql
mysql -udaily_check -ppassword_xxx mysql_daily_check -H -e "select check_date,hostname,ip,mysql_port,mysql_ping,mysql_version,round(mysql_db_size/1024,2) as 'db_size(GB)' from daily_check where check_date='${check_date}';" >> /home/mysql/mysqlcheck/daily_check_${current_date}.html
echo "<p>" >> /home/mysql/mysqlcheck/daily_check_${current_date}.html
cat /home/mysql/mysqlcheck/daily_check_tmp.html >> /home/mysql/mysqlcheck/daily_check_${current_date}.html
echo "<p>" >> /home/mysql/mysqlcheck/daily_check_${current_date}.html
#python /home/mysql/sendemailmysql.py
python3 /home/mysql/sendemailmysql.py
邮件发送（sendemailmysql.py）
import smtplib
from email.header import Header
from email.mime.text import MIMEText
import datetime
ISOTIMEFORMAT = '%Y-%m-%d'
today = datetime.datetime.now().strftime(ISOTIMEFORMAT)
filename = 'daily_check_' + today + '.html'
filepath = '/home/mysql/mysqlcheck/' + filename
sender = 'cw_check@163.com'
receiver = 'xxx@163.com,chenyu10@163.com,jiangxiaoming1@163.com'
smtpserver = 'mail.163.com'
username = 'cw_check@163.com'
password = 'password_xxx'
mail_title = 'Gemdale Monitor Report for Mysql daily check-' + today
file = open(filepath,'rb')
mail_body = file.read()
message = MIMEText(mail_body, 'html', 'utf-8')
message['From'] = sender
message['To'] = receiver
message['Subject'] = Header(mail_title, 'utf-8')
#try:
smtp = smtplib.SMTP()
smtp.connect(smtpserver)
smtp.starttls()
smtp.login(username, password)
smtp.sendmail(sender, receiver.split(','), message.as_string())
smtp.quit()
#except smtplib.SMTPException:
# print("Fail to send email.")
中间表daily_check_tmp.html
daily_check_tmp.html
<p>password is about to expire or has expired:
<p>gem-ats-p-db03-10.36.13.217:
<TABLE BORDER=1><TR><TH>user</TH><TH>host</TH><TH>password_last_changed</TH><TH>password_lifetime</TH><TH>expiry_date</TH></TR><TR><TD>zabbix</TD><TD>127.0.0.1</TD><TD>2022-12-30 17:39:52</TD><TD>90</TD><TD>2023-03-30 17:39:52</TD></TR></TABLE>
巡检效果图：
