MySQL数据库备份

全量备份

#!/bin/bash
MY_USER="root"
MY_PASS="123123"
MY_HOST="localhost"
MY_DB="test"
BF_DIR="/data/mysql/bak"
BF_TIME=$(date +%Y%m%d-%H%M)
NAME="${MY_DB}-${BF_TIME}"
BACKUP_FILE="${BF_DIR}/${NAME}.sql.gz"
LOG_FILE="$BF_DIR/bak.log"

# 确保备份目录存在（新增，避免目录不存在导致备份失败）
[ ! -d "$BF_DIR" ] && mkdir -p "$BF_DIR"

mysqldump -u"$MY_USER" -p"$MY_PASS" -h"$MY_HOST" --set-gtid-purged=off --triggers --routines --events "$MY_DB" | gzip > "$BACKUP_FILE"

if [ -s "$BACKUP_FILE" ]; then
    echo "$BF_TIME backup success：$NAME.sql.gz" >> "$LOG_FILE"
else
    echo "$BF_TIME backup failed" >> "$LOG_FILE"
    rm -f "$BACKUP_FILE"  # 清理无效文件
    exit 1
fi

find "$BF_DIR" -name "${MY_DB}*.gz" -mtime +15 -type f -exec rm -f {} \; 

 

增量备份

#!/bin/bash
# MySQL数据库增量备份脚本
# 设置登录变量
MY_USER="root"
MY_PASS="123123"
MY_HOST="localhost"
MY_CONN="-u$MY_USER -p$MY_PASS -h$MY_HOST"
# 定义备份路径、工具、二进制日志前缀、二进制日志存放路径
BF_TIME="$(date +%Y%m%d)"
BF_DIR="/mysql_bak/zengbei/$BF_TIME"
CMD="/usr/bin/mysqladmin"
QZ="mysql-bin"
LOG_DIR="/var/lib/mysql"
# 拷贝二进制日志
[ -d $BF_DIR ] || mkdir -p $BF_DIR
$CMD $MY_CONN flush-logs
/bin/cp -p $(ls $LOG_DIR/$QZ.* |awk -v RS="" '{print $(NF-2)}') $BF_DIR

 

xtrabackup全量备份

#!/bin/bash  
  
# 定义备份目录、用户名、密码、日期和日志文件名  
backup_dir=/cmsdate/databackup1  
user=root  
pwd=1q2w3e4r#@!  
dt=$(date +%Y%m%d)  
log_file="${backup_dir}/backup_${dt}.log"  
  
cd $backup_dir/
# 执行xtrabackup备份  
xtrabackup --defaults-file=/cmsdate/mysql/my.cnf --user=$user --password=$pwd --backup --target-dir="${backup_dir}/backup_${dt}" --socket=/tmp/mysql.sock > "${log_file}" 2>&1  
  
# 检查xtrabackup的退出状态  
if [ $? -eq 0 ]; then  
    echo "$(date): Backup completed successfully." >> $log_file  
else  
    echo "$(date): Error occurred during backup process." >> $log_file
    exit 1  
fi  
  
# 定义打包后的文件名和目标目录  
tar_file="${backup_dir}/backup_${dt}.tar.gz"  
target_dir=/cmsdate/databackup2  
  
# 打包备份目录  
tar -czvf "${tar_file}" "${backup_dir}/backup_${dt}" >> "${log_file}" 2>&1  
  
# 检查打包是否成功  
if [ $? -eq 0 ]; then  
    echo "$(date): Backup has been successfully tarred." >> "${log_file}"  
  
    # 移动打包文件到目标目录  
    mv "${tar_file}" "${target_dir}/" >> "${log_file}" 2>&1  
  
    # 检查移动是否成功  
    if [ $? -eq 0 ]; then  
        echo "$(date): Backup has been moved to ${target_dir}." >> "${log_file}"  
    else  
        echo "$(date): Error occurred while moving backup." >> "${log_file}"  
    fi  
else  
    echo "$(date): Error occurred while tarring the backup." >> "${log_file}"  
fi  
  
# 删除超过7天的旧备份目录和文件  
find "${backup_dir}/" -type d -name "backup_*" -mtime +7 -exec rm -rf {} \;  
find "${backup_dir}/" -type f -name "backup_*" -mtime +7 -exec rm -rf {} \;