MySQL数据库备份

全量备份

#!/bin/bash
# MySQL数据库完全备份脚本
# 设置登录变量
MY_USER="root"
MY_PASS="123123"
MY_HOST="localhost"
MY_CONN="-u$MY_USER -p$MY_PASS -h$MY_HOST"
# 设置备份的数据库(或表)
MY_DB="client"
# 定义备份路径、工具、时间、文件名
BF_DIR="/mysql_bak/wanbei"
BF_CMD="/usr/bin/mysqldump"
BF_TIME=$(date +%Y%m%d-%H%M)
NAME="$MY_DB-$BF_TIME"
# 备份为.sql脚本，然后打包压缩(打包后删除原文件)
[ -d $BF_DIR ] || mkdir -p $BF_DIR
cd $BF_DIR
$BF_CMD $MY_CONN --databases $MY_DB > $NAME.sql
/bin/tar zcf $NAME.tar.gz $NAME.sql --remove &>/dev/null

增量备份

#!/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 {} \;