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: [email protected]
### 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="[email protected] [email protected] [email protected] [email protected]"

#     
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

 
転載先:https://www.cnblogs.com/cenliang/p/8627123.html