windows mysql 双活 mysql异地双活

转载

mob6454cc782a8c 2023-11-12 17:33:36

文章标签 windows mysql 双活 linux mysql 异地备份服务器数据库服务器增量备份 文章分类 MySQL 数据库

有两个服务器，192.168.0.1 和192.168.0.2，采用nginix+双主模式互为双机热备，做高可用。

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

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

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

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

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

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

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

#!/bin/sh
year=`date +%Y`
month=`date +%m`
day=`date +%d`
week=`date +%w`
today=`date +%Y%m%d`
log=$today.log
CODE=$?
backup_dir=/PRODUCT_DB/data/backups
full_backup_dir=/PRODUCT_DB/data/backups/full
inc_backup_dir=/PRODUCT_DB/data/backups/inc
inc_full_backup_dir=$inc_backup_dir/$today
backup_program=/root/xtrabackup/bin/xtrabackup
config_file=/etc/my.cnf
dba_user=root
dba_password=yourDbPassword
backup_server=192.168.0.3
backup_server_user=root
#在主服务器1上是1,2上则是2
backup_server_fullbackupdir=/UNI/data/backups/1/full
backup_server_incbackupdir=/UNI/data/backups/1/inc
execute_full_backup=0
execute_inc_backup=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=`date -d '8 days ago' +%Y%m%d`
echo 'del date is:' $del_date
endDate=`date -d "${del_date}" +%s`
echo 'endDate is:' $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=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=$config_file --backup --user=$backup_server_user --password=$dba_password --host=127.0.0.1 --target-dir=$backup_dir/full"
$backup_program --defaults-file=$config_file --backup --user=$backup_server_user --password=$dba_password --host=127.0.0.1 --target-dir=$backup_dir/full
[ "$CODE" == "0" ] && echo "full backups succeed"
execute_full_backup=1
fi
}
full_backup_no_judge(){
#不做任何判断，直接进行完整备份
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=$config_file --backup --user=$dba_user --password=$dba_password --host=127.0.0.1 --target-dir=$backup_dir/full"
$backup_program --defaults-file=$config_file --backup --user=$dba_user --password=$dba_password --host=127.0.0.1 --target-dir=$backup_dir/full
[ "$CODE" == "0" ] && echo "full backups succeed"
execute_full_backup=1
}
inc_backup(){
#每当week != 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=$config_file --backup --user=$dba_user --password=$dba_password --host=127.0.0.1 --target-dir=$inc_backup_dir/$today --incremental-basedir=$backup_dir/full"
$backup_program --defaults-file=$config_file --backup --user=$dba_user --password=$dba_password --host=127.0.0.1 --target-dir=$inc_backup_dir/$today --incremental-basedir=$backup_dir/full
[ "$CODE" == "0" ] && echo "incremental backups succeed"
execute_inc_backup=1
fi
}
copy_files(){
#拷贝文件到异地备份服务器
echo "funciton copy_files"
if [ $execute_full_backup == 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@$backup_server "mkdir -p $backup_server_fullbackupdir/$today"
scp -r $backup_dir/full/* $backup_server_user@$backup_server:$backup_server_fullbackupdir/$today
[ "$CODE" == "0" ] && echo "copy full backup files succeed"
fi
fi
if [ $execute_inc_backup == 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@$backup_server:$backup_server_incbackupdir/
[ "$CODE" == "0" ] && echo "copy incremental backup files succeed"
fi
fi
}
del_expire_files(){
#每天删除7天之前的过期备份，本地服务器始终只保留了最近一周的完整备份和增量备份
echo "function del_expire_files"
#del_date=`date -d '7 days ago' +%Y%m%d`
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=`find $backup_dir/full -mtime +7 -type f -name "*"`
find $backup_dir/full -mtime +7 -type f -name "*"|xargs rm -rf
dirCnt=`find $backup_dir/full -mtime +7 -type d -name "*"`
find $backup_dir/full -mtime +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 +7 -type f -name "*"|xargs rm -rf
find $backup_dir/inc/ -mtime +7 -type d -name "*"|xargs rm -rf
path=/UNI/data/backups/195/inc/
cd $path
files=$(ls $path)
for filename in $files
do
echo 'find finename:' $filename
thisDate=`date -d "${filename}" +"%Y%m%d"`
echo 'thisdate:'
echo $thisDate
startDate=`date -d "${thisDate}" +%s`
echo "startDate:"
echo $startDate
stampDiff=`expr $endDate - $startDate`
dayDiff=`expr $stampDiff / 86400`
echo 'dayDiff is:' $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;
当然具体的细节，打通linux主服务器与备份服务器之间的SCP权限，那些就不多说了，各位可以在网上查资料自行搞定。
2：在两台数据库服务器都添加crontab
cat /etc/crontab
#主数据库服务器1：
0 23 * * * root /root/xtrabackup/bin/backup.sh>/root/xtrabackup/bin/backup.log
#主数据库服务器2：
30 23 * * * root /root/xtrabackup/bin/backup.sh>/root/xtrabackup/bin/backup.log
3：在备份服务器添加定期删除备份脚本，以及添加crontab任务
cat delete_expire_files.sh
#!/bin/sh
del_expire_files(){
#del_date=`date -d '16 days ago' +%Y%m%d`
del_date=`date -d '8 days ago' +%Y%m%d`
echo 'del date is:' $del_date
endDate=`date -d "${del_date}" +%s`
echo 'endDate is:' $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=`find /UNI/data/backups/1/full/ -mtime +8 -type f -name "*"`
find /UNI/data/backups/1/full/ -mtime +8 -type f -name "*"|xargs rm -rf
echo "$filesCnt total file delete "
dirCnt=`find /UNI/data/backups/1/full/ -mtime +8 -type d -name "*"`
find /UNI/data/backups/1/full/ -mtime +8 -type d -name "*"|xargs rm -rf
echo "$dirCnt total directories delete "
fi
echo "begin del /UNI/data/backups/1/full"
filesCnt=`find /UNI/data/backups/1/full/ -mtime +8 -type f -name "*"`
find /UNI/data/backups/1/full/ -mtime +8 -type f -name "*"|xargs rm -rf
echo "$filesCnt total file delete "
dirCnt=`find /UNI/data/backups/1/full/ -mtime +8 -type d -name "*"`
find /UNI/data/backups/1/full/ -mtime +8 -type d -name "*"|xargs rm -rf
echo "$dirCnt total directories delete "
path=/UNI/data/backups/1/full/
cd $path
files=$(ls $path)
for filename in $files
do
echo 'find finename:' $filename
thisDate=`date -d "${filename}" +"%Y%m%d"`
echo 'thisdate:'
echo $thisDate
startDate=`date -d "${thisDate}" +%s`
echo "startDate:"
echo $startDate
stampDiff=`expr $endDate - $startDate`
dayDiff=`expr $stampDiff / 86400`
echo 'dayDiff is:' $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=`find /UNI/data/backups/1/inc/ -mtime +8 -type d -name "*"`
find /UNI/data/backups/1/inc/ -mtime +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=`find /UNI/data/backups/2/full/ -mtime +8 -type f -name "*"`
find /UNI/data/backups/2/full/ -mtime +8 -type f -name "*"|xargs rm -rf
echo "$filesCnt total file delete "
dirCnt=`find /UNI/data/backups/2/full/ -mtime +8 -type d -name "*"`
find /UNI/data/backups/2/full/ -mtime +8 -type d -name "*"|xargs rm -rf
echo "$dirCnt total directories delete "
fi
echo "begin del /UNI/data/backups/2/full"
filesCnt=`find /UNI/data/backups/2/full/ -mtime +8 -type f -name "*"`
find /UNI/data/backups/2/full/ -mtime +8 -type f -name "*"|xargs rm -rf
echo "$filesCnt total file delete "
dirCnt=`find /UNI/data/backups/2/full/ -mtime +8 -type d -name "*"`
find /UNI/data/backups/2/full/ -mtime +8 -type d -name "*"|xargs rm -rf
echo "$dirCnt total directories delete "
path=/UNI/data/backups/2/full/
cd $path
files=$(ls $path)
for filename in $files
do
echo 'find finename:' $filename
thisDate=`date -d "${filename}" +"%Y%m%d"`
echo 'thisdate:'
echo $thisDate
startDate=`date -d "${thisDate}" +%s`
echo "startDate:"
echo $startDate
stampDiff=`expr $endDate - $startDate`
dayDiff=`expr $stampDiff / 86400`
echo 'dayDiff is:' $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=`find /UNI/data/backups/2/inc/ -mtime +8 -type d -name "*"`
find /UNI/data/backups/2/inc/ -mtime +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

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。