xtrabackup实现mysql数据库全备脚本

最新推荐文章于 2024-07-11 21:41:59 发布

追风筝的小青年

最新推荐文章于 2024-07-11 21:41:59 发布

阅读量118

点赞数 1

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/qq_48736646/article/details/139988875

版权

#!/bin/bash
#myql全量备份脚本
# 用法：bash ./full_bak.sh

#set -x
set -u

mysql_conf_file=/etc/my.cnf
backup_dir=/mysql_back  #备份路径
ulimit -n 65535

#host="192.168.60.x"  # 主机ip
#port="3306"   #mysql端口
user="root"    #mysql用户
passwd='1xxxxxx'   #root密码

#每天在备份目录对应数据库下面生成一个文件夹
run_full_bak(){
    # database=$1   #需要备份的数据库
    today_dir=${backup_dir}/$(date +%Y%m%d)  #得到今天需要全备份的数据库目录   格式为today_dir=/mysql_back/20231214
    if [ ! -d $today_dir ];
    then
        mkdir -p $today_dir  #不存在则新建
    else
        echo "$today_dir exists, it will be cleaned before full-backup"
        rm -rf ${today_dir}/*  #存在则删除
        echo "$today_dir clean success"
    fi
    /app/xtrabackup/bin/innobackupex  --defaults-file="$mysql_conf_file" -S /var/lib/mysql/mysql.sock \
        --user=$user --password=$passwd \
        "$today_dir"  #使用percona-xtrabackup将$database mysql performance_schema进行备份 放到today_dir目录下

    if [ $? -eq 0 ]; then
        data_size_kb=$(du -sk /data | awk '{print $1}')
        today_dir_kb=$(du -sk ${today_dir} | awk '{print $1}')
        echo " /data file size: ${data_size_kb} KB" >> /tmp/yunbanji-backup-$(date +%Y%m%d).log
        echo "today_dir ${today_dir} file size ${today_dir_kb} KB"  >> /tmp/yunbanji-backup-$(date +%Y%m%d).log
        #echo "$(date '+%c') Backup successful!" >> "/tmp/yunbanji-backup-$(date +%Y%m%d).log"
    else
        echo "$(date '+%c') Backup failed, check logs!" >> "/tmp/yunbanji-backup-$(date +%Y%m%d).log"
        # 可以添加额外的错误处理逻辑
    fi
    # echo ">_< successfully full-backup to $today_dir" >> /tmp/yunbanji-backup-$(date +%Y%m%d).log  #输出全备成功提示
}


today_dir=${backup_dir}/$(date +%Y%m%d)   # 设置为全局变量
# 执行函数
run_full_bak
echo "$(date '+%c') yunbanji-$(date +%Y%m%d) backup successful!" >> /tmp/yunbanji-backup-$(date +%Y%m%d).log  #输出备份成功提示
#done

# 如果存在目录则压缩
if [ -d ${today_dir} ]; then
    zip -r ${backup_dir}/zip_mysql/yunbanji-$(date +%Y%m%d).zip ${today_dir}  # 格式为 zip -r /mysql_back/zip_mysql/yunbanji-20231214.zip    /mysql_back/20231214
   # tar zcvf ${backup_dir}/yunbanji-$(date +%Y%m%d).tar.gz ${today_dir}  # 将today_dir目录压缩到backup_dir目录下   tar文件以时间命名
    echo "$(date '+%c') yunbanji-$(date +%Y%m%d) backup successful! start arichive  " >> /tmp/yunbanji-backup-$(date +%Y%m%d).log
else
    echo "$(date '+%c') yunbanji-$(date +%Y%m%d) is none, please check !!! " >> /tmp/yunbanji-backup-$(date +%Y%m%d).log
fi

zip_file="${backup_dir}/zip_mysql/yunbanji-$(date +%Y%m%d).zip"
# 如果存在压缩文件则传输，传输完后则删除
if [ -f ${zip_file} ]; then
    rsync -az ${zip_file} root@192.168.60.xx0:/mysql/mysqlzip/  #通过rsync传输。传输到另外一台服务器中。需要提前做免密
    zip_size_kb=$(du -sk ${zip_file} | awk '{print $1}')
    echo "$(date '+%c') yunbanji-$(date +%Y%m%d) send successful ! Zip file size: ${zip_size_kb} KB" >> /tmp/yunbanji-backup-$(date +%Y%m%d).log
    #echo "$(date '+%c') yunbanji-$(date +%Y%m%d) send successful !" >> /tmp/yunbanji-backup-$(date +%Y%m%d).log
    rm -rf ${today_dir} ${zip_file}  # 执行完成后  删除这个tar包  也删除/xtrabackup备份的文件
    echo "$(date '+%c') local db_backup_file clean success!" >> /tmp/yunbanji-backup-$(date +%Y%m%d).log  #输出成功
else
    echo "$(date '+%c') yunbanji-$(date +%Y%m%d) send fail, please check !!!" >> /tmp/yunbanji-backup-$(date +%Y%m%d).log
fi