centos 自动备份mysql数据库

创建自动备份脚本

创建文件 mysql_auth_back.sh

#!/bin/bash                                                                                                     
# source /etc/profile

# 以下配置信息需要根据自己情况进行编辑
DB_HOST="127.0.0.1"                                	# IP地址默认localhost
DB_USER="root"                                	# 备份用户
DB_PASSWORD="af92233423423423"               	# 备份用户的密码
DB_PORT="52189"                                  	# 端口
DB_Charset="utf8"                                	# 编码
# 要备份的数据库名称，多个用空格分开隔开 如("dba" "dbb" "dbc")
DB_BACKUP_ARRLIST=("ddwgame")
DB_BACKUP_PATH=/home/libo/mysql_back/database  # 备份数据存放位置，末尾请不要带"/"
Expire_Backup_Del="ON"                              # 是否开启过期备份删除 ON为开启 OFF为关闭
Expire_Days=365                                     # 过期时间天数 默认为三天，此项只有在Expire_Backup_Del开启时有效
skip_extended_insert="--skip-extended-insert"       # 作用就是生成多条insert语句
complete_insert="--complete-insert"					# 使用完整的insert语句，能提高插入效率，可能会受到max_allowed_packet参数的影响而导致插入失败
ignore_table="--ignore-table"

# 本行开始以下不需要修改
Backup_Time=`date +%Y%m%d%H%M`                      # 定义备份详细时间
Backup_Ymd=`date +%Y-%m-%d`                         # 定义备份目录中的年月日时间
Backup_3ago=`date -d '3 days ago' +%Y-%m-%d`        # 3天之前的日期
Backup_DIR=$DB_BACKUP_PATH/$Backup_Ymd              # 备份文件夹全路径
Welcome_MSG="Welcome to Use MySQL8 Backup Tools!"   # 欢迎语

# 判断MySQL是否启动 MySQL没有启动则备份退出
# 连接到mysql数据库 无法连接则备份退出
mysql  -u$DB_USER -p$DB_PASSWORD --host=$DB_HOST --port=$DB_PORT <<end
use mysql;
select host,user from user where user='root' and host='localhost';
exit
end

flag=`echo $?`
if [ $flag != "0" ]; then
        echo "ERROR:Can't Connect MySQL Server! Backup Stop!"
        exit
else
        echo "MySQL Connect OK! Please Wait......"
        # 判断有没有定义备份的数据库，如果定义则开始备份，否则退出备份
        if [ "$DB_BACKUP_ARRLIST" != "" ];then
                #dbnames=$(cut -d ',' -f1-5 $backup_database)
                #echo "arr is (${DB_BACKUP_ARRLIST[@]})"
                for dbname in ${DB_BACKUP_ARRLIST[@]}
                do
                        echo "database [ $dbname ] Backup Start..."
                       # `mkdir -p $Backup_DIR`
                        `mysqldump --host=$DB_HOST  --port=$DB_PORT -u$DB_USER -p$DB_PASSWORD $skip_extended_insert $complete_insert $dbname $ignore_table=ddwgame.sys_job_log $ignore_table=ddwgame.gen_table  $ignore_table=ddwgame.gen_table_column  --default-character-set=$DB_Charset | gzip > $Backup_DIR-$dbname-$Backup_Time.sql.gz`
                        flag=`echo $?`
                        if [ $flag == "0" ];then
                                echo "database $dbname Success Backup to $Backup_DIR-$dbname-$Backup_Time.sql.gz"
                        else
                                echo "database $dbname Sackup Fail!"
                        fi

                done
        else
                echo "ERROR:No Database to Backup! Backup Stop"
                exit
        fi
        # 如果开启了删除过期备份，则进行删除操作
        if [ "$Expire_Backup_Del" == "ON" -a "$DB_BACKUP_PATH" != "" ];then
                 #`find $DB_BACKUP_PATH/ -type d -o -type f -ctime +$Expire_Days -exec rm -rf {} \;`
                 `find $DB_BACKUP_PATH/ -type d -mtime +$Expire_Days | xargs rm -rf`
                 echo "Expired Backup Data Delete Complete!"
        fi
        echo "All Database Backup Success! Thank you!"
        exit
fi

 sql 语句中 $ignore_table 设置例外，比如一些日志表就不备份了

脚本授权

chmod +x mysql_auto_backup.sh

配置定时任务

创建定时任务
vim /etc/crontab
# 每天凌晨3点执行一次
00 03 * * *   /usr/bin/sh /startup/mysql_auto_backup.sh >> /startup/logs/mysql_auto_backup_`date +\%Y\%m\%d`.log 2>&1 &

# 重新装载生效
systemctl restart crond && crontab /etc/crontab && systemctl status crond

# 查看定时器运行日志
tailf -n 10 /var/log/cron