2018-03-22
crontab -l
# 每天早上1点 服务器巡检 0 1 * * * /bin/bash /data/backup/scripts/check_server.sh > /data/backup/log/my3306/check_server.log 2>&1
/data/backup/scripts/check_server.sh
#! /bin/bash ### AUTHOR: cenliang ### EMAIL: xuanniaoxi@sina.com ### DATE: 2018/03/22 ### REV: 3.0 # 数据库服务器 性能巡检报告 source /etc/profile source /root/.bash_profile ################################# # # # 全局配置 # # # ################################# # 巡检日期 CheckDate=`date +"%Y-%m-%d"` # 巡检主机 CheckServer=`hostname` # 巡检IP CheckIP=`ifconfig eth0 |grep "inet " |awk '{print $2}'` # 巡检报告 CheckReport=/data/backup/log/my3306/check_server.log # 实例名 InstanceName=my3306 # 数据目录 DataDir=/data/mysql/my3306 # 备份同步路径 TargetRsyncDir=/data/backup/mysql/my3306 # 收件人 addr="cenliang@allinpay.com liulj3@allinpay.com chenzp3@allinpay.com wuyong@allinpay.com" # 邮件标题 subj="${CheckServer}(${CheckIP})巡检报告" # 邮件正文 body=$CheckReport # 正文标题 title="${CheckServer}(${CheckIP})巡检报告" ################################# # # # 巡检内容 # # # ################################# # 格式化标题函数 function f_format(){ echo "##############################################################################" echo "# " echo "# "$1" " echo "# " echo -e "##############################################################################\n" } # 表空间使用率检查函数 function f_check_tbs(){ echo "SELECT a.schema_name db_name, CONCAT(IFNULL(ROUND((SUM(b.data_length) + SUM(b.index_length)) / 1024 / 1024, 0), 0), 'M') total, CONCAT(IFNULL(ROUND(((SUM(b.data_length) + SUM(b.index_length)) - SUM(b.data_free)) / 1024 / 1024, 0), 0), 'M') used, CONCAT(IFNULL(ROUND(SUM(data_free) / 1024 / 1024, 0), 0), 'M') free, CONCAT(IFNULL(ROUND((((SUM(b.data_length) + SUM(b.index_length)) - SUM(b.data_free)) / ((SUM(b.data_length) + SUM(b.index_length))) * 100), 0), 0), '%') ratio, COUNT(table_name) TABLES FROM information_schema.schemata a LEFT JOIN information_schema. TABLES b ON a.schema_name = b.table_schema WHERE a.schema_name NOT IN ('information_schema', 'mysql', 'performance_schema') GROUP BY a.schema_name ORDER BY 1;" } f_format $title f_format "01、CPU空闲率、系统负载" top - b -n1 -d1|head -n5 f_format "02、可用内存" free -g f_format "03、磁盘空间使用率" df -Th f_format "04、磁盘IO性能" iostat -d -m 2 5 f_format "05、数据库实例状态" ps -ef | grep mysql|grep -v "grep" f_format "06、数据库监听状态" netstat -atun| grep 3306 f_format "07、数据库大小" echo "数据目录: $DataDir" du -ch --max-depth=1 $DataDir |grep total|cut -f 1 f_format "08、数据库表空间使用率" f_check_tbs|mysql --login-path=my3306 f_format "09、错误日志" grep `date +"%Y-%m-%d"` $DataDir/error.log f_format "10、备份状态" echo "备份路径: $TargetRsyncDir" ls -hl $TargetRsyncDir |grep `date +"%Y%m%d"` f_format "THE END ! " #applied_email mail -s $subj $addr < $body