脚本说明全量和增量备份的物理和文件夹目录。可以添加时间戳和日志文件,指定MySQL配置和用户密码。上一次备份目录可选择全备份或增量备份。
热衷于分享各种干货知识,大家有想看或者想学的可以评论区留言,秉承着“开源知识来源于互联网,回归于互联网”的理念,分享一些日常工作中能用到或者频率比较的内容,希望大家能够喜欢,并提出宝贵地意见,我们一起提升,守住自己的饭碗。
一、一键备份
[root@myoracle ~]# chmod +x Xtrabackup.sh
[root@myoracle ~]# sh -x Xtrabackup.sh
脚本内容:
说明:此脚本可以进行全量和增量的物理备份
cat Xtrabackup.sh
#!/bin/bash
# 定义变量
BACKUP_BASE_DIR="/data/backup"
DATE_STR=$(date +%F)
TIME_STR=$(date +%H%M%S)# 添加时间戳
DAY_OF_WEEK=$(date +%u)
FULL_BACKUP_DIR="${BACKUP_BASE_DIR}/db_full_${DATE_STR}"
INCREMENTAL_BACKUP_DIR="${BACKUP_BASE_DIR}/db_incr_${DATE_STR}_${TIME_STR}"
LOG_FILE="${BACKUP_BASE_DIR}/db_backup_${DATE_STR}_${TIME_STR}.log"
MYSQL_CONF="/etc/my.cnf"
MYSQL_USER="root"
MYSQL_PASSWORD="123456"
MYSQL_SOCKET="/tmp/mysql.sock"
# 上一次备份目录(可以是全备份或上一次增量备份)
LAST_BACKUP_DIR="${BACKUP_BASE_DIR}/db_last"
# 判断是否是周一,如果是,则进行全量备份;否则进行增量备份
if["$DAY_OF_WEEK"-eq 1];then
BACKUP_DIR=$FULL_BACKUP_DIR
BACKUP_TYPE="FULL"
INCREMENTAL_OPTION=""
else
if[-L "$LAST_BACKUP_DIR"]&&[-d "$(readlink -f $LAST_BACKUP_DIR)"]&&[-f "$(readlink -f $LAST_BACKUP_DIR)/xtrabackup_checkpoints"];then
BACKUP_DIR=$INCREMENTAL_BACKUP_DIR
BACKUP_TYPE="INCREMENTAL"
INCREMENTAL_OPTION="--incremental-basedir=$(readlink -f $LAST_BACKUP_DIR)"
else
BACKUP_DIR=$FULL_BACKUP_DIR
BACKUP_TYPE="FULL"
INCREMENTAL_OPTION=""
fi
fi
# 创建新的备份目录
mkdir -p $BACKUP_DIR
# 执行 xtrabackup 备份命令
xtrabackup --defaults-file=$MYSQL_CONF \
--backup \
--target-dir=$BACKUP_DIR \
$INCREMENTAL_OPTION \
--user=$MYSQL_USER \
--password=$MYSQL_PASSWORD \
--socket=$MYSQL_SOCKET \
> $LOG_FILE 2>&1
# 检查备份是否成功
if[ $?-eq 0];then
echo "${BACKUP_TYPE} backup completed successfully at $(date)"| tee -a $LOG_FILE
else
echo "${BACKUP_TYPE} backup failed at $(date)"| tee -a $LOG_FILE
exit1
fi
# 更新 LAST_BACKUP_DIR 链接指向最新备份目录
ln -sfn $BACKUP_DIR $LAST_BACKUP_DIR
# 清理3天以前的备份
find $BACKUP_BASE_DIR -type d -name "db_full_*"-mtime +3-exec rm -rf {} \;
find $BACKUP_BASE_DIR -type d -name "db_incr_*"-mtime +3-exec rm -rf {} \;
find $BACKUP_BASE_DIR -type f -name "db_backup_*.log"-mtime +3-exec rm -f {} \;
echo "Cleanup of backups older than 3 days completed at $(date)"| tee -a $LOG_FILE
添加定时任务:
crontab -e
# 每周一凌晨12点进行全量备份
0 0 * * 1 /root/Xtrabackup.sh
# 每周二至周日凌晨1点进行增量备份
0 1 * * 2-7 /root/Xtrabackup.sh
模拟新增数据进行增量备份
root@localhost:(none)>create database test;
root@localhost:(none)>use test;
root@localhost:test>create table test(id int);
root@localhost:test>insert into test values(1);
root@localhost:test>select * from test;
root@localhost:test>flush logs;
#继续继续备份
[root@myoracle ~]# sh -x Xtrabackup.sh
二、一键恢复
[root@myoracle ~]# pkill mysql
[root@myoracle ~]# ps -ef|grep mysql
[root@myoracle ~]# mv /data/mysql/ /data/mysql.bak
[root@myoracle ~]# chmod +x XtraRestoreall.sh
[root@myoracle ~]# sh -x XtraRestoreall.sh
脚本内容:
说明:此脚本可以进行全量和增量的物理备份恢复
cat XtraRestoreall.sh
#!/bin/bash
# 定义变量
BACKUP_BASE_DIR="/data/backup"
FULL_BACKUP_DIR="${BACKUP_BASE_DIR}/db_full_2024-09-22"# 第一次全量备份的目录
INCREMENTAL_BACKUP_DIR="${BACKUP_BASE_DIR}/db_last"# 增量备份目录
MYSQL_DATA_DIR="/data/mysql"
LOG_FILE="${BACKUP_BASE_DIR}/db_restore_$(date +%F).log"
MYSQL_CONF="/etc/my.cnf"
MYSQL_USER="root"
MYSQL_PASSWORD="123456"
# 日志记录函数
log(){
echo "$1"| tee -a $LOG_FILE
}
# 检查是否存在必要文件的辅助函数
check_required_files(){
local backup_dir=$1
if[!-f "${backup_dir}/xtrabackup_checkpoints"]||[!-f "${backup_dir}/backup-my.cnf"];then
log "Error: Backup directory ${backup_dir} is missing necessary files."
return1
fi
return0
}
# 准备全量备份
prepare_full_backup(){
log "Preparing full backup..."
xtrabackup --prepare --apply-log-only --target-dir=$FULL_BACKUP_DIR >> $LOG_FILE 2>&1
if[ $?-ne 0];then
log "Failed to prepare full backup."
exit1
fi
}
# 应用增量备份
apply_incremental_backups(){
if[-d "$INCREMENTAL_BACKUP_DIR"];then
log "Applying incremental backups..."
for incr in $(ls -d ${INCREMENTAL_BACKUP_DIR}/* | sort); do
check_required_files $incr
if [ $? -eq 0 ]; then
log "Applying incremental backup: $incr"
xtrabackup --prepare --apply-log-only --target-dir=$FULL_BACKUP_DIR --incremental-dir=$incr >> $LOG_FILE 2>&1
if [ $? -ne 0 ]; then
log "Failed to apply incremental backup: $incr"
exit 1
fi
else
log "Skipping incremental backup: $incr due to missing files."
fi
done
else
log "No incremental backup directory found, skipping incremental backup application."
fi
}
# 执行最后的准备步骤
finalize_backup_preparation() {
log "Finalizing the backup preparation..."
xtrabackup --defaults-file=$MYSQL_CONF --user=$MYSQL_USER --password=$MYSQL_PASSWORD --prepare --target-dir=$FULL_BACKUP_DIR >> $LOG_FILE 2>&1
if [ $? -ne 0 ]; then
log "Failed to finalize the backup preparation."
exit 1
fi
}
# 恢复备份到数据目录
restore_backup() {
# 停止MySQL服务
service mysql stop
if [ $? -ne 0 ]; then
log "Failed to stop MySQL service."
exit 1
fi
# 清空当前数据目录
rm -rf $MYSQL_DATA_DIR/*
if [ $? -ne 0 ]; then
log "Failed to clear MySQL data directory."
service mysql start
exit 1
fi
# 恢复备份到数据目录
log "Restoring backup to data directory..."
xtrabackup --defaults-file=$MYSQL_CONF --user=$MYSQL_USER --password=$MYSQL_PASSWORD --copy-back --target-dir=$FULL_BACKUP_DIR >> $LOG_FILE 2>&1
if [ $? -ne 0 ]; then
log "Failed to restore backup to data directory."
service mysql start
exit 1
fi
# 修复文件权限
chown -R mysql:mysql $MYSQL_DATA_DIR
if [ $? -ne 0 ]; then
log "Failed to set proper file permissions."
service mysql start
exit 1
fi
# 启动MySQL服务
service mysql start
if [ $? -eq 0 ]; then
log "Database restored successfully and MySQL service started."
else
log "Failed to start MySQL service after restoration."
exit 1
fi
}
# 主函数
main() {
# 判断是否存在第一次全量备份
check_required_files $FULL_BACKUP_DIR
if [ $? -ne 0 ]; then
log 'Error: No valid first full backup directory found.'
exit 1
fi
# 准备全量备份
prepare_full_backup
# 应用增量备份
apply_incremental_backups
# 执行最后的准备步骤
finalize_backup_preparation
# 恢复备份
restore_backup
}
# 执行主函数main
文中的概念来源于互联网,如有侵权,请联系我删除。
欢迎关注公众号:小周的数据库进阶之路,一起交流数据库、中间件和云计算等技术。如果觉得读完本文有收获,可以转发给其他朋友,大家一起学习进步!感兴趣的朋友可以加我微信,拉您进群与业界的大佬们一起交流学习。
原文来源:https://mp.weixin.qq.com/s/F3c_rEibKFUszNpueIO04w
来源:本文内容搜集或转自各大网络平台,并已注明来源、出处,如果转载侵犯您的版权或非授权发布,请联系小编,我们会及时审核处理。
声明:江苏教育黄页对文中观点保持中立,对所包含内容的准确性、可靠性或者完整性不提供任何明示或暗示的保证,不对文章观点负责,仅作分享之用,文章版权及插图属于原作者。
Copyright©2013-2024 JSedu114 All Rights Reserved. 江苏教育信息综合发布查询平台保留所有权利
苏公网安备32010402000125 苏ICP备14051488号-3技术支持:南京博盛蓝睿网络科技有限公司
南京思必达教育科技有限公司版权所有 百度统计