MySQL定时逻辑备份并发送备份邮件

  很久之前的小学鸡脚本了，针对某些生产库专门做的定时逻辑备份（用了很多年，懒，没有优化升级，等网友看完给个完善建议），备份时间每天23:30，备份过程写入emailfile，备份完发邮件到指定邮箱，备份本地保留7天，阵列盘保留30天。

  备份脚本：

#!/bin/bash
# Script:MysqlBackup.sh
# Author:ChengYu
# Date:2014-06-08
export MYSQL_HOME=/usr/local/mysql
export PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH
mysql_user="root"
mysql_password="***************"
mysql_host="127.0.0.1"
mysql_port="3306"
backup_db_arr=("db_cy" "db_yx" "db_yb" "db_yh" "db_wxyx")
dbname="mysql"
# expire_days=6
BakDate=`date '+%Y-%m-%d'`
# backup_time=`date +%Y%m%d%H%M`
backup_ymd=`date +%Y%m%d`
baklog=/home/mysqlbak/log/MysqlBackup.log
emailfile=/home/mysqlbak/log/mysqlemail.txt
sendemail=88******@qq.com
backup_dir=/home/mysqlbak/bak/
welcome_msg="Dear ChengYu, Welcome to dbplus_backup!"

# 检查MySQL服务开启否
mysql_ps=`ps -ef |grep mysql |wc -l`
mysql_listen=`netstat -an |grep LISTEN |grep $mysql_port |wc -l`
echo $welcome_msg>$baklog
if [ [$mysql_ps == 0] -o [$mysql_listen == 0] ]; then
echo "ERROR OF DBPLUS1!!! MySQL IS Not Running!!! Backup Stop!!!">>$baklog
exit
else
echo $welcome_msg
fi

# 尝试连接MySQL，不成功则退出
mysql -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password <<end
use mysql;
select user,host from user where user='root' and host='localhost';
exit
end
flag=`echo $?`
if [ $flag != 0 ];then
echo "ERROR!!! you cann't connet to the mysql server!!！backup stop!!!">>$baklog
exit
else
echo "MySQL connet ok! please wait.....">>$baklog

# 查询各个库的数据量，监测异常增量
echo "-----------------DB_CAPACITY OF DBPLUS-----------------" >$emailfile
`mysql -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $dbname -e "SELECT table_schema ,sum((DATA_LENGTH+INDEX_LENGTH)/1024/1024) as capacity from information_schema.tables group by table_schema">>$emailfile`

# 判断有没有定义需备份的数据库
if [ "$backup_db_arr" != "" ];then
                for dbname in ${backup_db_arr[@]}
do
echo "$dbname backup start .....">>$baklog

# 开始备份
`mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password --single-transaction $dbname > $backup_dir$dbname$backup_ymd.sql`
flag=`echo $?`
                        if [ $flag == "0" ];then
                                echo "$dbname backup successfully to $backup_dir$dbname$backup_ymd.sql">>$baklog
                        else
                                echo "$dbname backup fail!">>$baklog
                        fi

                done
        else
                echo "ERROR: No database backup completed! backup stop">>$baklog
                exit
        fi
        
# 压缩备份文件
    cd $backup_dir
    tar --remove-files -zcvf MySQLBak$backup_ymd.tar.gz *$backup_ymd.*
    
# 备份管理，定时删除
    find $backup_dir/MySQLBak*.tar.gz -atime +7 -exec rm -f {} \;
    cp MySQLBak$backup_ymd.tar.gz /mnt/upload/MySQL-Backup/
    find /mnt/upload/MySQL-Backup/MySQLBak*.tar.gz -atime +30 -exec rm -f {} \;
    echo "All databases backup completed successfully! Have a nice day!">>$baklog
    
# 发送邮件
    echo "----------------------------Database backup list --------------------------" >>$emailfile
    ll -h /home/mysqlbak/bak >>$emailfile
    echo "----------------------------Database backup Detail Information --------------------------" >>$emailfile
    cat /home/mysqlbak/log/MysqlBackup.log >>$emailfile
    mail -s "$BakDate(DBPLUS) Database Usage/Backup of MySQL"< $emailfile -r chengyu@dbplus.***.com $sendemail
        exit
fi

  设置定时任务：

  [root@dbplus ~]# crontab -e

  20 23 * * 0-6 /home/mysqlbak/script/MysqlBackup.sh

  收到邮件（马赛克需要，本篇数据只得造假了）：

2020-07-02(DBPLUS) Database Usage/Backup of MySQL
发件人：chengyu <chengyu@dbplus.***.com>    	
时   间：2020年7月2日（星期四）下午11 : 50 
收件人：
ChengYu <88******@qq.com>

-----------------DB_CAPACITY OF DBPLUS-----------------
table_schema capacity
db_cy 902.15467835
db_yx 1020.16530991
db_yb 6012.81500626
db_yh 271.62500000
db_wxyx 29870.76554967
----------------------------Database backup list --------------------------
total 55771672
total 54G
-rw-r--r-- 1 root root 7136821263 Jun 25 23:44 MySQLBak20200625.tar.gz
-rw-r--r-- 1 root root 7136821263 Jun 26 23:44 MySQLBak20200626.tar.gz
-rw-r--r-- 1 root root 7136821263 Jun 27 23:44 MySQLBak20200627.tar.gz
-rw-r--r-- 1 root root 7136821263 Jun 28 23:44 MySQLBak20200628.tar.gz
-rw-r--r-- 1 root root 7136821263 Jun 29 23:44 MySQLBak20200629.tar.gz
-rw-r--r-- 1 root root 7136821263 Jun 30 23:44 MySQLBak20200630.tar.gz
-rw-r--r-- 1 root root 7136821263 Jul  1 23:44 MySQLBak20200701.tar.gz
-rw-r--r-- 1 root root 7136821263 Jul  2 23:44 MySQLBak20200702.tar.gz
----------------------------Database backup Detail Information --------------------------
Dear ChengYu, Welcome to dbplus_backup!
MySQL connet ok! please wait.....
db_cy backup start .....
db_cy backup successfully to /home/mysqlbak/bak/db_cy20200702.sql
db_yx backup start .....
db_yx backup successfully to /home/mysqlbak/bak/db_yx20200702.sql
db_yb backup start .....
db_yb backup successfully to /home/mysqlbak/bak/db_yb20200702.sql
db_yh backup start .....
db_yh successfully to /home/mysqlbak/bak/db_yh20200702.sql
db_wxyx backup start .....
db_wxyx backup successfully to /home/mysqlbak/bak/db_wxyx20200702.sql
All databases backup completed successfully! Have a nice day!

2020年07月08日

  下午茶等待中……

  早餐吃个皮蛋瘦肉粥吃出肠胃不适，相克命不假了，吃一次伤一次，杠上了。

  好巧，今天部门安排了下午茶，只能期待下午茶拯救一下里外受伤的灵魂。

  来了来了，哇哦

  精分现场，一边：马甲线稳住，自律人设稳住；一边：混健身房的都吃得淡定，你纠结个鬼，给我吃！