自己选个路径。如:mkdir -p /opt/mysql/
cd /opt/mysql/
vim mysql_backup.sh
#!/bin/bash
# Database info
DB_USER="root" # 数据库备份用户
DB_PASS="root" # 备份用户密码
DB_HOST="10.10.6.27" # 数据库 IP
BACKUP_DIR="/opt/mysql" # 本地备份路径
DATE=$(date -d '-1 day' +%Y-%m-%d) # 获取当前时间
JUST_7_DAY_AGO=$(date -d '-7 day' +%Y-%m-%d) # 取 7 天前的时间,格式为:2024-5-1,用于删除备份文件取文件时间,该参数可自行调整
REMOTE_IP=${node-02} # 可以改成具体IP,或者/etc/hosts 配置一下
#### 根据实际情况进行数据库罗列,此处可修改 ###
DB_LIST=("gz_metadata" "gz_manager" "gz_exy" "gz_jkj" "gz_mzy" "gz_users") # 需要备份的数据库
# 创建备份目录,带日期
if [ ! -d "$BACKUP_DIR/$DATE/" ]; then
mkdir -p $BACKUP_DIR/$DATE
fi
if [ ! -d "$BACKUP_DIR/logs/" ]; then
mkdir -p $BACKUP_DIR/logs/
fi
echo "开始本地备份中... $(date) " >> $BACKUP_DIR/logs/$DATE.log
if [ ! -d "$BACKUP_DIR/$DATE" ]; then
echo "error $date 存放备份文件的路径不存在,终止执行 。。。" >> $BACKUP_DIR/logs/$DATE.log
exit 1
fi
# 逐个数据库进行备份
for _db in "${DB_LIST[@]}"; do
/usr/local/mysql-8.0.26/bin/mysqldump -u$DB_USER -p$DB_PASS --databases $_db > $BACKUP_DIR/$DATE/${_db}_${DATE}.sql
if [ ! -e $BACKUP_DIR/$DATE/${_db}_${DATE}.sql ]; then
echo "error $(date) 数据${_db}数据库导出失败,$BACKUP_DIR/$DATE/${_db}_${DATE}.sql文件不存在。程序退出" >> $BACKUP_DIR/logs/$DATE.log
exit 1
fi
echo "$(date) ${_db} db bakcup success! $(date) " >> $BACKUP_DIR/logs/$DATE.log
done
echo "本地备份完成! $(date)" >> $BACKUP_DIR/logs/$DATE.log
echo "对备份文件夹开始进行压缩... $(date) " >> $BACKUP_DIR/logs/$DATE.log
cd $BACKUP_DIR && tar -zcvf mysql_backup_$DATE.sql.tar.gz ./$DATE >> $BACKUP_DIR/logs/$DATE.log && rm -fr $BACKUP_DIR/$DATE/
if [ ! -e mysql_backup_$DATE.sql.tar.gz ]; then
echo "文件压缩失败,退出程序 ... $(date) " >> $BACKUP_DIR/logs/$DATE.log
exit 1
fi
echo "备份文件夹压缩完成! $(date)" >> $BACKUP_DIR/logs/$DATE.log
echo "开始删除 7 天前的数据库备份文件... $(date) " >> $BACKUP_DIR/logs/$DATE.log
rm -rf $BACKUP_DIR/mysql_backup_${JUST_7_DAY_AGO}.sql.tar.gz
echo "7 天前的数据库备份文件删除完毕! $(date) " >> $BACKUP_DIR/logs/$DATE.log
# 远程备份服务器需要先开通ssh 免密
echo "开始远程备份中... $(date) " >> $BACKUP_DIR/logs/$DATE.log
scp -r $BACKUP_DIR/mysql_backup_${DATE}.sql.tar.gz node-02:$(pwd)
echo "远程备份完毕! $(date) " >>/$BACKUP_DIR/logs/$DATE.log
创建定时任务: crontab -e
0 2 * * * /opt/mysql/mysql_backup.sh