欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

mysql全量备份，增量备份并同步上传ftp服务器，linux本地及window异地数据还原

程序员文章站 2022-04-09 12:48:50
...

mysql增量备份
1，需求
要实现将linux服务器的数据库同步备份至window服务器数据库，做法是先将linux服务器生成的全量备份脚本在异机window服务器执行，执行完成后，将异机window服务器作为全量备份，然后定时的将linux生成的增量备份上传至window的ftp服务器，window定期的执行脚本来还原增量备份，使得window与linux数据同步
2，脚本
共三个执行脚本，
全量备份脚本:
进行数据库全量备份，将sql压缩文件生成至linux文件夹并同步上传至ftp服务器,清空linux增量备份文件，重置并刷新mysql二进制日志，生成一个新的二进制日志用于记录用户对mysql的更新操作，如果多次执行全量备份，则重复之前的执行逻辑，脚本仅保留近五天的全备份文件，目前要做的仅手动执行一次全备份，并在异地服务器还原增量备份
增量备份脚本:
执行全量备份并重置日志后，定期执行增量备份脚本，脚本先刷新并生成新的二进制日志用于记录用户对mysql的更新操作,将旧的增量二进制文件备份至linux相应的文件夹并同步上传至ftp服务器，脚本定期删除一周前的linux备份文件及其文件夹，删除mysql的二进制日志
增量备份记录的增量，是对数据库日志进行两次刷新操作的区间增量
批处理脚本：
将全备份与增量备份上传至ftp服务器后，在window服务器先还原全备份数据库文件，然后设置window定时任务，脚本会定期执行执行当天的增量备份文件
如果执行增量备份发生错误，如何获取错误日志，并判断是否删除旧的增量备份文件的方法未查明！！

1，全量备份脚本

#databak_ftp.sh
#数据库全量备份并同步上传至服务器
#!/bin/bash
export LANG=en_US.UTF-8
BakDir=/home/mysql/backup
LogFile=/home/mysql/backup/bak.log
#ftp上传地址
ftp_upload_dir=fullbackup
#系统当前时间
Date=`date +%Y%m%d`
Begin=`date +"%Y年%m月%d日 %H:%M:%S"`
cd $BakDir/$ftp_upload_dir
DumpFile=$Date.sql
GZDumpFile=$Date.sql.tgz
echo $Begin Full backup start >> $LogFile

mysqldump -u用户名 -p密码 --all-databases --flush-logs --delete-master-logs --single-transaction > $DumpFile

tar -czvf $GZDumpFile $DumpFile
rm $DumpFile

count=$(ls -l *.tgz |wc -l)

if [ $count -ge 5 ]
then
	file=$(ls -l *.tgz |awk '{print $9}'|awk 'NR==1')
	rm -f $file
fi

#只保留近四周的数据全备份
cd $BakDir/daily
#清空daily文件夹中增量日志文件
rm -rf *
Last=`date +"%Y年%m月%d日 %H:%M:%S"`
echo $Last Full backup end >> $LogFile


#ftp配置相关参数
#ftp地址
ip=
#ftp端口号
port=
#ftp用户名
uname=
#ftp密码
upass


#ftp上传操作
echo Start copy to Ftp server .... >> $logFile
#连接ftp
ftp -v -n $ip $port <<- EOF
user $uname $upass
binary
hash
mkdir $ftp_upload_dir
cd MysqlBackup
mkdir fullbackup
cd fullbackup
cd $ftp_upload_dir
mkdir $Date
cd $Date
#切换到本机log目录
lcd $BakDir/$ftp_upload_dir
prompt off
mput $Date.sql.tgz
close
bye
EOF
echo "commit to ftp successfully" >> $logFile

2，增量备份脚本

#mysqlbackup_ftp.sh

#增量备份并同步上传ftp服务器
#linux备份目录,增量,ftp上传使用
backupDir=/home/mysql/backup/daily
#增量备份时复制mysql-bin.00000*的目标目录，提前手动创建这个目录
mysqlDir=/data/mysql
#记录操作日志信息，增量,ftp上传使用
logFile=/home/mysql/backup/bak.log
#mysql的index文件路径，放在数据目录下的
BinFile=/data/mysql/mysql-bin.index
#daily文件夹目录
dailydir=$(ls -l $backupDir |awk '/^d/ {print $NF}')

#ftp配置相关参数
#系统当前时间
Date=`date +%Y%m%d`
#ftp地址
ip=
#ftp端口号
port=
#ftp用户名
uname=
#ftp密码
upass=
#ftp上传地址
ftp_upload_dir=MysqlBackup

#mysqladmin -uroot -pboxuan393374 flush-logs
#使用flush-logs命令重新生成MySQL的相关日志文件
/usr/local/mysql/bin/mysqladmin -uroot -pboxuan393374 flush-logs
#这个是用于产生新的mysql-bin.00000*文件
# wc -l 统计行数
# awk 简单来说awk就是把文件逐行的读入，以空格为默认分隔符将每行切片，切开的部分再进行各种分析处理。
Counter=`wc -l $BinFile |awk '{print $1}'`
NextNum=0
ExistNum=0
#这个for循环用于比对$Counter,$NextNum这两个值来确定文件是不是存在或最新的
for file in `cat $BinFile`
do
	base=`basename $file`
	echo $base
	#basename用于截取mysql-bin.00000*文件名，去掉./mysql-bin.000005前面的./
	NextNum=`expr $NextNum + 1`
	ExistNum=0
	if test $NextNum -eq $Counter 
	then
		#如果是新生成的日志文件则跳过
		echo $base skip! >> $logFile
	else
		for i in $dailydir
		do
			#test -e用于检测目标文件是否存在，存在就写exist!到$logFile去
			dest=$backupDir/$i/$base
			if(test -e $dest)
		then
			echo $base exist! >> $logFile
			ExistNum=$(($i+1))
		fi
	done

	if test $ExistNum -gt 0 ; then
		echo $base exist! >> $logFile
	else
		if test ! -d $backupDir/$Date ;
		then
			mkdir $backupDir/$Date
			#不存在则将生成的增量日志文件复制到相应文件夹
			cp $mysqlDir/$base $backupDir/$Date
			echo $base copying >> $logFile
		else
			cp $mysqlDir/$base $backupDir/$Date
			echo $base copying >> $logFile
		fi
	fi
fi
done
echo `date +"%Y年%m月%d日 %H:%M:%S"` $Next Bakup succ! >> $logFile

#删除一周前备份
#遍历标识
#traverseNum=0
#获取一周前的日期
last_week_Date=`date -d last-week +%Y%m%d`

#遍历所有日期文件夹
for i in $dailydir
do
	#traverseNum=`expr $traverseNum + 1`
	echo $i
	echo $last_week_Date
	if test $i -le $last_week_Date 
	then
		echo start delete $backupDir/$i  >> $logFile
		echo start delete $backupDir/$i
		#遍历删除超期文件,同时删除mysql生成的超期binlog日志
		for j in $(ls $backupDir/$i) ; do
			#判断备份文件夹是否存在超期文件,存在则删除
			if(test -e $backupDir/$i/$j)
			then
				#判断MySQL文件夹是否存在超期文件,存在则删除
				if(test -e /data/mysql/$j)
				then
					rm -f $backupDir/$i/$j
					echo delete $backupDir/$i/$j >> $logFile
					echo delete $backupDir/$i/$j
					rm -f /data/mysql/$j
					echo delete mysql binlog /data/mysql/$j >> $logFile
					echo delete mysql binlog /data/mysql/$j
				else
				     $backupDir/$i/$j not Exist>> $logFile
				     $backupDir/$i/$j not Exist
				fi
			else
				echo /data/mysql/$j not Exist >> $logFile
				echo /data/mysql/$j not Exist
			fi
			#判断是否存在超期文件夹,存在则删除
			if test -d $backupDir/$Date ;
			then
				rm -rf $backupDir/$i
				echo delete $backupDir/$i >> $logFile
				echo delete $backupDir/$i
			else
				echo $backupDir/$i not Exist>> $logFile
				echo $backupDir/$i not Exist
			fi
		done
	fi
done


#ftp上传操作
echo Start copy to Ftp server .... >> $logFile
#连接ftp
ftp -v -n $ip $port <<- EOF
user $uname $upass
binary
hash
#判断服务器上的目录是否存在
if test ! -d $ftp_upload_dir;then
	mkdir $ftp_upload_dir
fi
cd $ftp_upload_dir
mkdir daily
cd daily
if test ! -d $Date;then
	mkdir $Date
fi
cd $Date
#切换到本机log目录
lcd $backupDir/$Date
prompt off
mput *
close
bye
EOF
echo "commit to ftp successfully" >> $logFile

3，批处理还原脚本

#dbincre_backup_recover.bat
::2020-03-05 v1.1
@echo off

::恢复mysql数据，并删除指定时间的数据
title 正在mysql数据,请勿关闭
::ftp备份目录
::set filePath=F:\rfid\MysqlBackup\
set filePath=F:\rfid\MysqlBackup\
::备份文件名前部分
set backup=scm_backup_
::日志文件名前部分
set log=mysql_recover_log_
set errorlog=mysql_error_log_
set uname=
set upass=
::设置删除几天前文件
set saveDays=-7
::mysql错误日志生成路径
::set log_error=

::当前日期
set dateStr=%date:~0,4%%date:~5,2%%date:~8,2%
::当前时间
set sfmDateStr=%time:~0,2%:%time:~3,2%:%time:~6,2%
::生成操作日志路径
set logFile=%filePath%%log%%dateStr%.log
::生成错误日志路径
set errorlogFile=%filePath%%errorlog%%dateStr%.log

for %%i in (%filePath%%dateStr%\*.*) do (
		::增量备份还原
		if exist %%i (
			echo %%i
			echo %dateStr% %sfmDateStr%  recovering backup start %%i  >> %logFile%
			mysqlbinlog %%i | mysql -u%uname% -p%upass% sell 
			echo %dateStr% %sfmDateStr%  recovering backup end %%i  >> %logFile%
		)
	)
)


::删除旧文件
echo %dateStr% %sfmDateStr% delete old files start >> %logFile%
::forfiles [/p Path ] [/m SearchMask ] [/s ] [/c Command ] [/d [{+ | - }] [{MM / DD / YYYY | DD }]]
:: Forfiles /p %filePath% /s /d %saveDays% /m *log /c "cmd /c del /q /f @path"
forfiles /p %filePath% /m "*" /d %saveDays%  /c "cmd /c if @isdir==TRUE (rmdir /q /s @path) else (del /f @path)"

echo %dateStr% %sfmDateStr% delete old files end >> %logFile%

rem :End
pause

3，备份流程详情

1，配置开启mysql日志功能
编辑mysql配置文件 my.cnf
添加如下配置

log-bin=/var/lib/mysql/mysql-bin
server-id=1

重启mysql服务，查看bin.log是否成功开启

show variables like '%log_bin%';

2，为相应shell脚本赋予执行权限

chmod a+x /home/mysql/binlogbak.sh（脚本存放完整路径）

3，设置脚本定时任务
Linux定时任务
进入linux 定时任务编辑界面命令：crontab -e
为shell脚本设置定时任务

每分钟执行一次备份脚本：
* * * * * sh /usr/your/path/mysqlBackup.sh
每五分钟执行 ：
*/5 * * * * sh /usr/your/path/mysqlBackup.sh
每小时执行：
0 * * * * sh /usr/your/path/mysqlBackup.sh
每天执行：
0 0 * * * sh /usr/your/path/mysqlBackup.sh
每周执行：
0 0 * * 0 sh /usr/your/path/mysqlBackup.sh
每月执行：
0 0 1 * * sh /usr/your/path/mysqlBackup.sh
每年执行:
0 0 1 1 * sh /usr/your/path/mysqlBackup.sh

Window定时任务
1,输入任务名称
mysql全量备份，增量备份并同步上传ftp服务器，linux本地及window异地数据还原
2,设置触发器
mysql全量备份，增量备份并同步上传ftp服务器，linux本地及window异地数据还原
3,选择定期执行的脚本
mysql全量备份，增量备份并同步上传ftp服务器，linux本地及window异地数据还原
文件备份路径
1,Linux
mysql全量备份，增量备份并同步上传ftp服务器，linux本地及window异地数据还原
2,Window
mysql全量备份，增量备份并同步上传ftp服务器，linux本地及window异地数据还原
4,数据恢复
1，全备份恢复
（1）还原全备份所有数据库

mysql -uroot -pboxuan393374 < /home/mysql/backup/20200301.sql

（2）还原全备份中某一个数据库，如果数据库已经被删除，需要先创建数据库，再进行还原

mysql -uroot -pboxuan393374 "sell" < /home/mysql/backup/20200301.sql

2，增量备份手动恢复
如果恢复出现错误
注释掉mysql配置文件my.cnf中的配置项

#default-character-set = utf8mb4

(1)一般恢复

# mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名
mysqlbinlog /home/mysql/backup/daily/mysql-bin.000104 |
 mysql -uroot -pboxuan393374 sell

(2)pos点还原
如果数据库有误删操作，通过pos还原至相应位置

mysqlbinlog --stop-position=828 --database=sell 
/data/mysql/mysql-bin.000106 |
 mysql -uroot -pboxuan393374 -v sell;
相关标签： MySQL数据库 linux

上一篇： Angular-UI Bootstrap组件实现警报功能

下一篇： 基于docker的mysql数据库主从备份，详细步骤，亲测有效

推荐阅读