MySQL备份恢复——MySQL 增量备份脚本

xtrabackup 定时增量备份脚本

#!/bin/bash

###########################################################
#
# Written by : cai182081
# Weekly : Full backup time (week)
#
##########################################################

User=""
Passwd=""
Port=3306
BackupDir=/data/backup
Mycnf=/etc/my.cnf
Weekly=1

Xtrabackup=/usr/bin/innobackupex
Today=`date +%Y-%m-%d`
Yesterday=`date -d"yesterday" +%Y-%m-%d`
Full_Backup_File="$BackupDir"/"$Today"
Inc_Backup_File="$BackupDir"/"$Today"
Base_Backup_File="$BackupDir"/"$Yesterday"
LogFile="$BackupDir"/"$Today".log

echo "---------------------------------------------------------" >> $LogFile
echo " $(date +"%y-%m-%d %H:%M:%S") Start " >> $LogFile
echo "---------------------------------------------------------" >> $LogFile


if [ ! -d "$BackupDir"/"$Yesterday" ]; then
        #   Database Initialize Backup
        $Xtrabackup --defaults-file=$Mycnf -u$User -p$Passwd -P$Port --slave-info --no-timestamp $Full_Backup_File >> /dev/null 2>&1
        cd $BackupDir;
        tar -zcf "$Today"_full.tar.gz $Today >> /dev/null 2>&1
else
        if [ -f "$BackupDir"/"$Today"_full.tar.gz ] || [ -f "$BackupDir"/"$Today"_inc.tar.gz ];then
                echo "["$Today"_full.tar.gz] || ["$Today"_inc.tar.gz] The Backup File is exists,Can't Backup!" >> $LogFile
        else
                #   Regular Full Backup
                if [ $(date +%u) == $Weekly ];then
                        $Xtrabackup --defaults-file=$Mycnf -u$User -p$Passwd -P$Port --slave-info --no-timestamp $Full_Backup_File >> /dev/null 2>&1
                        cd $BackupDir;
                        if [ -d "$Yesterday" ];then
                                rm -rf $(date +%Y-%m-%d --date='1 days ago')
                                echo "[rm -rf "$Yesterday"] Delete Success!" >> $LogFile
                        else
                                echo "[rm -rf "$Yesterday")] is not exist" >> $LogFile
                        fi
                                 tar -zcf "$Today"_full.tar.gz $Today >> /dev/null 2>&1
                else
                #   Incremental Backup
                        $Xtrabackup --defaults-file=$Mycnf -u$User -p$Passwd -P$Port --slave-info --no-timestamp --incremental \
                            --incremental-basedir=$Base_Backup_File $Inc_Backup_File >> /dev/null 2>&1
                        cd $BackupDir;
                        if [ -d $(date +%Y-%m-%d --date='1 days ago') ];then
                                rm -rf $(date +%Y-%m-%d --date='1 days ago')
                                echo "[rm -rf "$Yesterday"] Delete Success!" >> $LogFile
                        else
                                echo "[rm -rf "$Yesterday"] is not exist" >> $LogFile
                        fi
                                tar -zcf "$Today"_inc.tar.gz $Today >> /dev/null 2>&1
                fi
        fi
fi

echo "---------------------------------------------------------" >> $LogFile
echo " $(date +"%y-%m-%d %H:%M:%S") End " >> $LogFile
echo "---------------------------------------------------------" >> $LogFile

#Backup retention time
find $BackupDir -mtime +15 -name "*.*" -exec rm -rf {} \;

mysqldump 定时全备份脚本

#!/bin/bash

###########################################################
#
# Written by : cai182081
#
##########################################################

User=""
Passwd=""
Host=""
Port=3306
BackupDir=/data/backup
Today=`date +%Y-%m-%d`
MysqlDump=/usr/local/mysql/bin/mysqldump
LogFile="$BackupDir"/"$Host"_"$Today".log

echo "---------------------------------------------------------" >> $LogFile
echo " $(date +"%Y-%m-%d %H:%M:%S") Start " >> $LogFile
echo "---------------------------------------------------------" >> $LogFile

if [ -f "$BackupDir"/"$Host"_"$Today".tar.gz]; then
 rm -rf "$BackupDir"/"$Host"_"$Today".tar.gz
fi

Databases=`mysql -e "show databases;" -u$User -p$Passwd -h$Host -P$Port | grep -Ev "Database|information_schema|performance_schema|test|sys"`

$MysqlDump -h"$Host" -u$User -p$Passwd -P$Port --single-transaction --master-data=2 --flush-logs --triggers -E -R -B "$Databases" |gzip> "$BackupDir"/"$Host"_"$Today".tar.gz

echo "---------------------------------------------------------" >> $LogFile
echo " $(date +"%Y-%m-%d %H:%M:%S") End " >> $LogFile
echo "---------------------------------------------------------" >> $LogFile

#Backup retention time
find $BackupDir -mtime +7 -name ""$Host"*" -exec rm -rf {} \;