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

不念不忘少年蓝@ 2022-07-21 01:25 214阅读 0赞

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