MySQL：监控慢日志、错误日志、备份数据库的脚本-蒲公英云

MySQL：监控慢日志、错误日志、备份数据库的脚本

1、监控慢日志、错误日志、备份数据库的脚本

mon_mysql_log.sh(点view plain 查看正确的脚本内容，ERROR、Warning有变形)

#!/bin/bash
user=root
passwd=''
host=''
port=3306
mysql_bin='/data/mysql/bin'
bak_dir='/data/mysqlbak'
my_cnf='/data/mysq/etc/my.cnf'
slowlog_file='/data/mysql/log/slow.log'
errlog_file='/var/log/mysqld.log'
#lepus的db_servers_mysql表的ID键值
lepus_server_id=274
#创建监控日志文件
InfoFile=/tmp/mon_mysql.log
[ ! -f ${InfoFile} ] && touch ${InfoFile}
cat ${InfoFile} >> /tmp/mon_mysql_history.log
cat /dev/null > ${InfoFile}
echo " " | tee -a $InfoFile
echo "----------------"`date +"%Y-%m-%d %H:%M:%S"`"--------------------" | tee -a $InfoFile
echo "------------------------"BEGIN"--------------------------" |tee -a $InfoFile
now=`date +"%Y%m%d"`
del_time=`date -d '7 day ago' +"%Y%m%d"`
#创建备份目录
check_dir () {
[ ! -d ${bak_dir}/db_bak/ ] && mkdir -p ${bak_dir}/db_bak/
[ ! -d ${bak_dir}/errlog_bak/ ] && mkdir -p ${bak_dir}/errlog_bak/
[ ! -d ${bak_dir}/slowlog_bak/ ] && mkdir -p ${bak_dir}/slowlog_bak/
}
#备份慢日志
bak_slowlog () {
cat ${slowlog_file} >> ${bak_dir}/slowlog_bak/${now}_${port}.slow
cat /dev/null > ${slowlog_file}
echo "[ `date +"%Y-%m-%d %H:%M:%S"` ] slowlog backup is ok!"| tee -a $InfoFile
}
#基于lepus慢日志入库
slowquery () {
/usr/local/bin/pt-query-digest --user=lepus --password=xxx --port=3306 --review h=192.168.x.x,D=lepus,t=mysql_slow_query_review --history h=192.168.x.x,D=lepus,t=mysql_slow_query_review_history --no-report --limit=100% --filter="\$event->{add_column} = length(\$event->{arg}) and\$event->{serverid}=$lepus_server_id " ${bak_dir}/slowlog_bak/${now}_${port}.slow >/tmp/lepus_slowquery.log
echo "[ `date +"%Y-%m-%d %H:%M:%S"` ] lepus slowquery is ok!"| tee -a $InfoFile
}
#备份error日志
mon_errlog () {
[ ! -f ${bak_dir}/errlog_bak/bak_error_${port}.log ] && cat ${errlog_file} > ${bak_dir}/errlog_bak/bak_error_${port}.log && > ${errlog_file}
err_row=`cat ${errlog_file} | egrep "\[ERROR\]|\[Warning\]" | grep -v 'Aborted connection' | grep -v 'skip-name-resolve mode' | wc -l` 
if [ ${err_row} -gt 0 ];then 
cat ${errlog_file} >> ${bak_dir}/errlog_bak/bak_error_${port}.log
echo "[ `date +"%Y-%m-%d %H:%M:%S"` ] [ERROR] MySQL errorlog is not empty!" | tee -a $InfoFile
> ${errlog_file}
else
echo "[ `date +"%Y-%m-%d %H:%M:%S"` ] MySQL errorlog is OK!" | tee -a $InfoFile
fi
}
#备份数据库
bak_db() {
/usr/bin/innobackupex-1.5.1 --defaults-file=${my_cnf} --user=${user} --password=${passwd} --host=${host} --port=${port} --slave-info --no-timestamp  ${bak_dir}/db_bak/${now}_${port} 2>${bak_dir}/db_bak/${now}_${port}.log
}
#检查备份是否OK
check_db_bak () {
status=`sed -n '$p' ${bak_dir}/db_bak/${now}_${port}.log|grep "completed OK"|wc -l`
if [ "$status" -ne 1 ]
then
                echo "[ `date +"%Y-%m-%d %H:%M:%S"` ] [ERROR] MySQL db backup is error!" | tee -a $InfoFile
else
                echo "[ `date +"%Y-%m-%d %H:%M:%S"` ] MySQL db backup is OK!"  | tee -a $InfoFile
fi
}
#删除过期备份
del_bak () {
rm -rf /data/mysqlbak/db_bak/${del_time}_${port}
rm -rf /data/mysqlbak/db_bak/${del_time}_${port}.log
echo "[ `date +"%Y-%m-%d %H:%M:%S"` ] MySQL del backup is OK! " | tee -a $InfoFile
echo "----------------"`date +"%Y-%m-%d %H:%M:%S"`"--------------------" | tee -a $InfoFile
echo "-------------------------"END"---------------------------" |tee -a $InfoFile
}
check_dir
bak_slowlog
slowquery
mon_errlog
bak_db
check_db_bak
del_bak

2、检查错误信息及报警

check_mysql_log.sh

#!/bin/bash
ip=`/sbin/ifconfig | grep "192.168.1.255" | awk -F "[: ]+" '{print $4}'`
lognum=`cat /tmp/mon_mysql.log | grep "\[ERROR\]" |wc -l`
call(){
for phone in 186xxxx
do
        curl "短信接口"
done
}
check_status () {
[ ${lognum} -gt 0 ] && call
}
bak_tmp_log () {
cat /tmp/mon_mysql.log >> /tmp/mon_mysql_history.log
cat /dev/null > /tmp/mon_mysql.log
}
check_status
bak_tmp_log