linuxmysql异地备份_Linux下MySQL双主模式下的增量异地备份和定期删除方案

有两个服务器&＃xff0c;192.168.0.1 和192.168.0.2&＃xff0c;采用nginix&＃43;双主模式互为双机热备&＃xff0c;做高可用。

现在客户额外给了一台异地的服务器192.168.0.3&＃xff0c;要求对两台服务器每天自动化的异地备份&＃xff0c;要求不能停机&＃xff0c;不能影响主业务流程。

思来想去&＃xff0c;采用Mysql自身的mysqldump或者mysqlpump都不太可靠&＃xff0c;这两种方式即使设置好参数不锁表&＃xff0c;也容易造成服务器内存占用高&＃xff0c;可能会影响主业务。

两台数据库服务器的数据文件约2T&＃xff0c;要想快又稳&＃xff0c;不影响用户操作&＃xff0c;能找到的好的方案&＃xff0c;只有采用xtrabackup了。

xtrabackup可以实现完整备份和增量备份&＃xff0c;并且不会影响mysql本身的运行&＃xff0c;也不会增加mysql的性能。这方面就不多说了&＃xff0c;有兴趣的可以参考percona xtrabackup官网说明。

为了安全便于恢复&＃xff0c;整体方案如下&＃xff0c;在数据库服务器本机&＃xff0c;每周一次完整备份&＃xff0c;每天进行一次完整备份,每天都把备份拷到异地服务器。

本地服务器&＃xff0c;每天都要滚动删除历史备份&＃xff0c;只保留一周。异地备份服务器&＃xff0c;每天也定期清除历史备份&＃xff0c;保留完整的两周全备份和增量备份文件。

1&＃xff1a;数据库服务器备份脚本,在两台双主服务器都添加该脚本

#!/bin/sh

year&＃61;&＃96;date &＃43;%Y&＃96;

month&＃61;&＃96;date &＃43;%m&＃96;

day&＃61;&＃96;date &＃43;%d&＃96;

week&＃61;&＃96;date &＃43;%w&＃96;

today&＃61;&＃96;date &＃43;%Y%m%d&＃96;

log&＃61;$today.log

CODE&＃61;$?

backup_dir&＃61;/PRODUCT_DB/data/backups

full_backup_dir&＃61;/PRODUCT_DB/data/backups/full

inc_backup_dir&＃61;/PRODUCT_DB/data/backups/inc

inc_full_backup_dir&＃61;$inc_backup_dir/$today

backup_program&＃61;/root/xtrabackup/bin/xtrabackup

config_file&＃61;/etc/my.cnf

dba_user&＃61;root

dba_password&＃61;yourDbPassword

backup_server&＃61;192.168.0.3

backup_server_user&＃61;root

#在主服务器1上是1,2上则是2

backup_server_fullbackupdir&＃61;/UNI/data/backups/1/full

backup_server_incbackupdir&＃61;/UNI/data/backups/1/inc

execute_full_backup&＃61;0

execute_inc_backup&＃61;0

echo "today is:$today"

echo "week day is:$week"

echo "full backup dir is:$backup_dir/full"

echo "incremental backup dir is:$backup_dir/inc/$today"

del_date&＃61;&＃96;date -d &＃39;8 days ago&＃39; &＃43;%Y%m%d&＃96;

echo &＃39;del date is:&＃39; $del_date

endDate&＃61;&＃96;date -d "${del_date}" &＃43;%s&＃96;

echo &＃39;endDate is:&＃39; $endDate

make_full_bak_dir(){

#创建完整备份的目录

if [ ! -d $backup_dir/full ];then

echo "full backup dir:$backup_dir/full is not exist,begin create the directory... "

mkdir -p $backup_dir/full

echo "full backup dir created succeed!"

fi

}

make_inc_bak_dir(){

#创建增量备份的目录

if [ ! -d $backup_dir/inc ];then

echo "incremental backup dir:$backup_dir/inc/$today is not exist,begin create the directory... "

mkdir -p $backup_dir/inc/$today

echo "incremental backup dir created succeed!"

fi

}

full_backup(){

#每当week&＃61;6时进行完整备份

echo "function full_backup"

if [ "$week" -eq "6" ];then

echo "begin full backup"

echo "full backup dir is:$full_backup_dir"

echo "it will take a lot of times,please wait...."

echo "$backup_program --defaults-file&＃61;$config_file --backup --user&＃61;$backup_server_user --password&＃61;$dba_password --host&＃61;127.0.0.1 --target-dir&＃61;$backup_dir/full"

$backup_program --defaults-file&＃61;$config_file --backup --user&＃61;$backup_server_user --password&＃61;$dba_password --host&＃61;127.0.0.1 --target-dir&＃61;$backup_dir/full

[ "$CODE" &＃61;&＃61; "0" ] && echo "full backups succeed"

execute_full_backup&＃61;1

fi

}

full_backup_no_judge(){

#不做任何判断&＃xff0c;直接进行完整备份

echo "function full_backup_no_judge"

echo "begin full backup "

echo "full backup dir is:$full_backup_dir"

echo "it will take a lot of times,please wait...."

echo "$backup_program --defaults-file&＃61;$config_file --backup --user&＃61;$dba_user --password&＃61;$dba_password --host&＃61;127.0.0.1 --target-dir&＃61;$backup_dir/full"

$backup_program --defaults-file&＃61;$config_file --backup --user&＃61;$dba_user --password&＃61;$dba_password --host&＃61;127.0.0.1 --target-dir&＃61;$backup_dir/full

[ "$CODE" &＃61;&＃61; "0" ] && echo "full backups succeed"

execute_full_backup&＃61;1

}

inc_backup(){

#每当week !&＃61; 6 时进行增量备份

echo "function inc_backup"

if [ "$week" -ne "6" ];then

echo "begin incremental backup"

if [ -d $backup_dir/full ];then

echo "full bakup dir:$backup_dir/full is exist..."

if [ ! -f "$backup_dir/full/xtrabackup_info" ];then

echo "full backup not exist,begin full bakcup first"

full_backup_no_judge;

fi

fi

echo "incremental backup dir is:$inc_full_backup_dir"

echo "it will take a lot of times,please wait...."

echo "$backup_program --defaults-file&＃61;$config_file --backup --user&＃61;$dba_user --password&＃61;$dba_password --host&＃61;127.0.0.1 --target-dir&＃61;$inc_backup_dir/$today --incremental-basedir&＃61;$backup_dir/full"

$backup_program --defaults-file&＃61;$config_file --backup --user&＃61;$dba_user --password&＃61;$dba_password --host&＃61;127.0.0.1 --target-dir&＃61;$inc_backup_dir/$today --incremental-basedir&＃61;$backup_dir/full

[ "$CODE" &＃61;&＃61; "0" ] && echo "incremental backups succeed"

execute_inc_backup&＃61;1

fi

}

copy_files(){

#拷贝文件到异地备份服务器

echo "funciton copy_files"

if [ $execute_full_backup &＃61;&＃61; 1 ];then

sleep 5

echo "begin copy full backup files to backup server:$backup_server"

if [ -d $backup_dir/full ];then

ssh $backup_server_user&＃64;$backup_server "mkdir -p $backup_server_fullbackupdir/$today"

scp -r $backup_dir/full/* $backup_server_user&＃64;$backup_server:$backup_server_fullbackupdir/$today

[ "$CODE" &＃61;&＃61; "0" ] && echo "copy full backup files succeed"

fi

fi

if [ $execute_inc_backup &＃61;&＃61; 1 ];then

sleep 5

echo "begin copy incremental backup files to backup server:$backup_server"

if [ -d $backup_dir/inc/$today ];then

scp -r $backup_dir/inc/$today $backup_server_user&＃64;$backup_server:$backup_server_incbackupdir/

[ "$CODE" &＃61;&＃61; "0" ] && echo "copy incremental backup files succeed"

fi

fi

}

del_expire_files(){

#每天删除7天之前的过期备份&＃xff0c;本地服务器始终只保留了最近一周的完整备份和增量备份

echo "function del_expire_files"

#del_date&＃61;&＃96;date -d &＃39;7 days ago&＃39; &＃43;%Y%m%d&＃96;

echo "begin execute del_expire_files"

#下面删除完整备份目录中的过期文件

if [ -d $backup_dir/full ];then

if [ -f "$backup_dir/full/xtrabackup_info" ];then

echo "begin del $backup_dir/full"

filesCnt&＃61;&＃96;find $backup_dir/full -mtime &＃43;7 -type f -name "*"&＃96;

find $backup_dir/full -mtime &＃43;7 -type f -name "*"|xargs rm -rf

dirCnt&＃61;&＃96;find $backup_dir/full -mtime &＃43;7 -type d -name "*"&＃96;

find $backup_dir/full -mtime &＃43;7 -type d -name "*"|xargs rm -rf

fi

fi

#下面删除增量备份目录中的过期文件

if [ -d $backup_dir/inc ];then

echo "begin del $backup_dir/inc"

find $backup_dir/inc/ -mtime &＃43;7 -type f -name "*"|xargs rm -rf

find $backup_dir/inc/ -mtime &＃43;7 -type d -name "*"|xargs rm -rf

path&＃61;/UNI/data/backups/195/inc/

cd $path

files&＃61;$(ls $path)

for filename in $files

do

echo &＃39;find finename:&＃39; $filename

thisDate&＃61;&＃96;date -d "${filename}" &＃43;"%Y%m%d"&＃96;

echo &＃39;thisdate:&＃39;

echo $thisDate

startDate&＃61;&＃96;date -d "${thisDate}" &＃43;%s&＃96;

echo "startDate:"

echo $startDate

stampDiff&＃61;&＃96;expr $endDate - $startDate&＃96;

dayDiff&＃61;&＃96;expr $stampDiff / 86400&＃96;

echo &＃39;dayDiff is:&＃39; $dayDiff

if [ "$dayDiff" -gt 1 ]

then

echo "begin del dir files"

#ls -al $path/$filename

if [ -d "$path/$filename" ]

then

rm -rf $path/$filename

fi

fi

done

fi

}

#下面是依次调用脚本

del_expire_files;

make_full_bak_dir;

make_inc_bak_dir;

full_backup;

inc_backup;

copy_files;

当然具体的细节&＃xff0c;打通linux主服务器与备份服务器之间的SCP权限&＃xff0c;那些就不多说了&＃xff0c;各位可以在网上查资料自行搞定。

2&＃xff1a;在两台数据库服务器都添加crontab

cat /etc/crontab

#主数据库服务器1&＃xff1a;

0 23 * * * root /root/xtrabackup/bin/backup.sh>/root/xtrabackup/bin/backup.log

#主数据库服务器2&＃xff1a;

30 23 * * * root /root/xtrabackup/bin/backup.sh>/root/xtrabackup/bin/backup.log

3&＃xff1a;在备份服务器添加定期删除备份脚本&＃xff0c;以及添加crontab任务

cat delete_expire_files.sh

#!/bin/sh

del_expire_files(){

#del_date&＃61;&＃96;date -d &＃39;16 days ago&＃39; &＃43;%Y%m%d&＃96;

del_date&＃61;&＃96;date -d &＃39;8 days ago&＃39; &＃43;%Y%m%d&＃96;

echo &＃39;del date is:&＃39; $del_date

endDate&＃61;&＃96;date -d "${del_date}" &＃43;%s&＃96;

echo &＃39;endDate is:&＃39; $endDate

echo "begin execute del_expiret_files"

#删除服务器1拷过来的过期备份

if [ -d /UNI/data/backups/1/full ];then

if [ -f "/UNI/data/backups/1/full/xtrabackup_info" ];then

echo "begin del /UNI/data/backups/1/full"

filesCnt&＃61;&＃96;find /UNI/data/backups/1/full/ -mtime &＃43;8 -type f -name "*"&＃96;

find /UNI/data/backups/1/full/ -mtime &＃43;8 -type f -name "*"|xargs rm -rf

echo "$filesCnt total file delete "

dirCnt&＃61;&＃96;find /UNI/data/backups/1/full/ -mtime &＃43;8 -type d -name "*"&＃96;

find /UNI/data/backups/1/full/ -mtime &＃43;8 -type d -name "*"|xargs rm -rf

echo "$dirCnt total directories delete "

fi

echo "begin del /UNI/data/backups/1/full"

filesCnt&＃61;&＃96;find /UNI/data/backups/1/full/ -mtime &＃43;8 -type f -name "*"&＃96;

find /UNI/data/backups/1/full/ -mtime &＃43;8 -type f -name "*"|xargs rm -rf

echo "$filesCnt total file delete "

dirCnt&＃61;&＃96;find /UNI/data/backups/1/full/ -mtime &＃43;8 -type d -name "*"&＃96;

find /UNI/data/backups/1/full/ -mtime &＃43;8 -type d -name "*"|xargs rm -rf

echo "$dirCnt total directories delete "

path&＃61;/UNI/data/backups/1/full/

cd $path

files&＃61;$(ls $path)

for filename in $files

do

echo &＃39;find finename:&＃39; $filename

thisDate&＃61;&＃96;date -d "${filename}" &＃43;"%Y%m%d"&＃96;

echo &＃39;thisdate:&＃39;

echo $thisDate

startDate&＃61;&＃96;date -d "${thisDate}" &＃43;%s&＃96;

echo "startDate:"

echo $startDate

stampDiff&＃61;&＃96;expr $endDate - $startDate&＃96;

dayDiff&＃61;&＃96;expr $stampDiff / 86400&＃96;

echo &＃39;dayDiff is:&＃39; $dayDiff

if [ "$dayDiff" -gt 1 ]

then

echo "begin del dir files"

#ls -al $path/$filename

if [ -d "$path/$filename" ]

then

rm -rf $path/$filename

fi

fi

done

fi

if [ -d /UNI/data/backups/1/inc ];then

echo "begin del /UNI/data/backups/1/inc"

dirCnt&＃61;&＃96;find /UNI/data/backups/1/inc/ -mtime &＃43;8 -type d -name "*"&＃96;

find /UNI/data/backups/1/inc/ -mtime &＃43;8 -type d -name "*"|xargs rm -rf

echo "$dirCnt total directories delete "

fi

#删除服务器2拷过来的过期备份

if [ -d /UNI/data/backups/2/full ];then

if [ -f "/UNI/data/backups/2/full/xtrabackup_info" ];then

echo "begin del /UNI/data/backups/2/full"

filesCnt&＃61;&＃96;find /UNI/data/backups/2/full/ -mtime &＃43;8 -type f -name "*"&＃96;

find /UNI/data/backups/2/full/ -mtime &＃43;8 -type f -name "*"|xargs rm -rf

echo "$filesCnt total file delete "

dirCnt&＃61;&＃96;find /UNI/data/backups/2/full/ -mtime &＃43;8 -type d -name "*"&＃96;

find /UNI/data/backups/2/full/ -mtime &＃43;8 -type d -name "*"|xargs rm -rf

echo "$dirCnt total directories delete "

fi

echo "begin del /UNI/data/backups/2/full"

filesCnt&＃61;&＃96;find /UNI/data/backups/2/full/ -mtime &＃43;8 -type f -name "*"&＃96;

find /UNI/data/backups/2/full/ -mtime &＃43;8 -type f -name "*"|xargs rm -rf

echo "$filesCnt total file delete "

dirCnt&＃61;&＃96;find /UNI/data/backups/2/full/ -mtime &＃43;8 -type d -name "*"&＃96;

find /UNI/data/backups/2/full/ -mtime &＃43;8 -type d -name "*"|xargs rm -rf

echo "$dirCnt total directories delete "

path&＃61;/UNI/data/backups/2/full/

cd $path

files&＃61;$(ls $path)

for filename in $files

do

echo &＃39;find finename:&＃39; $filename

thisDate&＃61;&＃96;date -d "${filename}" &＃43;"%Y%m%d"&＃96;

echo &＃39;thisdate:&＃39;

echo $thisDate

startDate&＃61;&＃96;date -d "${thisDate}" &＃43;%s&＃96;

echo "startDate:"

echo $startDate

stampDiff&＃61;&＃96;expr $endDate - $startDate&＃96;

dayDiff&＃61;&＃96;expr $stampDiff / 86400&＃96;

echo &＃39;dayDiff is:&＃39; $dayDiff

if [ "$dayDiff" -gt 1 ]

then

echo "begin del dir files"

#ls -al $path/$filename

if [ -d "$path/$filename" ]

then

rm -rf $path/$filename

fi

fi

done

fi

if [ -d /UNI/data/backups/2/inc ];then

echo "begin del /UNI/data/backups/2/inc"

dirCnt&＃61;&＃96;find /UNI/data/backups/2/inc/ -mtime &＃43;8 -type d -name "*"&＃96;

find /UNI/data/backups/2/inc/ -mtime &＃43;8 -type d -name "*"|xargs rm -rf

echo "$dirCnt total directories delete "

fi

}

del_expire_files;

cat /etc/crontab  #异地备份服务器的crontab

0 23 * * * root /root/xtrabackup/bin/delete_expire_files.sh>/root/xtrabackup/bin/delete_expire_files.log