MySQL(MariaDB)定时自动热备份+增量+远程备份脚本

219次阅读

共计 3790 个字符，预计需要花费 10 分钟才能阅读完成。

优点：热备份，不影响业务，增量备份，远程备份。
目的: 自动打包备份到远程 10.0.0.111 备份服务器

好用帮点赞! 谢谢小伙伴的支持。

前提：
1. 安装 xtrabackup
yum install http://www.percona.com/downloads/percona-release/RedHat/0.1-3/percona-release-0.1-3.noarch.rpm
yum install percona-xtrabackup-22

2. 创建目录
mkdir -p /data/backup
mkdir -p /data/backuptar/

3. 添加 MySQL 公钥到备份服务器（10.0.0.111），能免密码远程登录。
4. 创建备份用户
GRANT RELOAD,LOCK TABLES,REPLICATION CLIENT ON *.* TO ‘bkuser’@’localhost’ IDENTIFIED BY ‘passwd’;
FLUSH PRIVILEGES;

————————
脚本：
#!/bin/bash
backdir=/data/backup
backupbin=/usr/bin
begintime=`date +”%Y-%m-%d %H:%M:%S”`
format_time=`date +”%Y-%m-%d_%H_%M_%S”`
time_cost=$backdir/xtrabackup_time.txt
week=`date +%w`
user_name=bkuser
password=”passwd”
file_cnf=/etc/my.cnf.d/wsrep.cnf
backtar=/data/backuptar/
remote_host=”10.0.0.111″
remote_dir=/data/mysqlbak_remote_23
remote_user=”wanzi”
out_log=$backdir/xtrabackup_log_$format_time

if [-d “$backdir/rec5”];then
echo “ 开始打包 5 次的备份 ”
cd $backdir
tar -zcf lastweek.gz ./*
mv lastweek.gz $backtar/$format_time.lastweek.gz
echo “ 完成 5 次备份打包 ”
cd $backtar
rm -rf `ls |grep -v “$format_time.lastweek.gz”`
echo “ 删除本地上次备份完成 ”

echo “ 开始删除远程备份 …..”
ssh $remote_user@$remote_host “cd $remote_dir;rm -rf ./*”
echo “ 删除远程多余备份完成 ”
echo “ 开始拷贝本地备份到远程服务器 …”
scp $backtar/$format_time.lastweek.gz $remote_host:/$remote_dir
echo “ 远程备份完成 ”
rm -rf $backdir
mkdir $backdir
fi

#fullbackup
if [! -d “$backdir/fullbackup”];then
echo “ 在 $begintime 开始全量备份 ” >>$time_cost
$backupbin/innobackupex –defaults-file=$file_cnf –user=$user_name –password=$password –no-timestamp –slave-info $backdir/fullbackup 1>$out_log 2>&1
echo “ 完成全备 ”
elif [! -d “$backdir/rec0”];then
echo “#####start 0 incremental backup at $BEGINTIME to directory rec0” >>$time_cost
$backupbin/innobackupex –defaults-file=$file_cnf –no-timestamp –user=$user_name –password=$password –slave-info –incremental –incremental-basedir=$bac
kdir/fullbackup $backdir/rec0 1> $out_log 2>&1
echo “ 完成第 0 次增量备份 ”

elif [! -d “$backdir/rec1”];then
echo “#####start 1 incremental backup at $BEGINTIME to directory rec1” >>$time_cost
$backupbin/innobackupex –defaults-file=$file_cnf –no-timestamp –user=$user_name –password=$password –slave-info –incremental –incremental-basedir=$bac
kdir/rec0 $backdir/rec1 1> $out_log 2>&1
echo “ 完成第 1 次增量备份 ”

elif [! -d “$backdir/rec2”];then
echo “#####start 2 incremental backup at $BEGINTIME to directory rec2” >>$time_cost
$backupbin/innobackupex –defaults-file=$file_cnf –no-timestamp –user=$user_name –password=$password –slave-info –incremental –incremental-basedir=$bac
kdir/rec1 $backdir/rec2 1> $out_log 2>&1
echo “ 完成第 2 次增量备份 ”

elif [! -d “$backdir/rec3”];then
echo “#####start 3 incremental backup at $BEGINTIME to directory rec3” >>$time_cost
$backupbin/innobackupex –defaults-file=$file_cnf –no-timestamp –user=$user_name –password=$password –slave-info –incremental –incremental-basedir=$bac
kdir/rec2 $backdir/rec3 1> $out_log 2>&1
echo “ 完成第 3 次增量备份 ”

elif [! -d “$backdir/rec4”];then
echo “#####start 4 incremental backup at $BEGINTIME to directory rec4” >>$time_cost
$backupbin/innobackupex –defaults-file=$file_cnf –no-timestamp –user=$user_name –password=$password –slave-info –incremental –incremental-basedir=$bac
kdir/rec3 $backdir/rec4 1> $out_log 2>&1
echo “ 完成第 4 次增量备份 ”

elif [! -d “$backdir/rec5”];then
echo “#####start 5 incremental backup at $BEGINTIME to directory rec5” >>$time_cost
$backupbin/innobackupex –defaults-file=$file_cnf –no-timestamp –user=$user_name –password=$password –slave-info –incremental –incremental-basedir=$bac
kdir/rec4 $backdir/rec5 1> $out_log 2>&1
echo “ 完成第 5 次增量备份 ”
fi
ENDTIME=`date +”%Y-%m-%d %H:%M:%S”`
begin_data=`date -d “$BEGINTIME” +%s`
end_data=`date -d “$ENDTIME” +%s`
spendtime=`expr $end_data – $begin_data`
echo “it takes $spendtime sec for packing the data directory” >>$time_cost