mysql 服务器巡检脚本

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  "##############################################################################
"
}

# 表空间使用率检查函数
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