mysql全备份和增量备份

最新推荐文章于 2023-09-13 17:10:22 发布

liucui5201013

最新推荐文章于 2023-09-13 17:10:22 发布

阅读量3.9k

点赞数

本文详细介绍了如何使用Shell脚本实现MySQL数据库的完整备份和增量备份，并通过计划任务实现定时备份。包括备份流程、脚本执行过程、备份文件管理、以及备份后位置信息的更新。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

参照博客：http://lhflinux.blog.51cto.com/1961662/506467

十分感谢博主的共享！

以下是我按照上述博客修改的脚本！

一、完整备份
vi full_mysqldump.sh
#!/bin/sh
# test by xxxxx
#time info
time=` date +%Y%m%d%H `
time1=` date +%Y%m%d\ %H:%M:%S `
#DIR_INFO
BACK_DIR="/backup_db/mysql"
FILE_DIR="/backup_db/mysql"
#MYSQL_BACK
Log=/backup_db/mysqlBack.log
echo "$time1 start to full backup " >> $Log
mysqldump -uroot --flush-logs --master-data=2 zabbix|gzip >$BACK_DIR/mysqlFull$time.sql.gz
time2=` date +%Y%m%d\ %H:%M:%S `
echo "$time2 full backup successfully" >>$Log
spendTime=$(($(date +%s -d "$time2")-$(date +%s -d "$time1")))
echo "backup spend total time : $spendTime seconds" >> $Log
#get postion
sleep 5s
gunzip < $BACK_DIR/mysqlFull$time.sql.gz | sed -n "22p" | awk -F'=|,|;' '{print $2,$4}' > $FILE_DIR/position
# remove 7 days ago backup
find $BACK_DIR -name "mysql*.sql.gz" -mtime +7 -exec rm {} \;
echo "remove 7 days ago backup !" >>$Log

二、做增量备份，根据上面完整备份所获得的position以及binlog 与服务器当前的position和binlog做增量备份，
备份后把当前的position以及binlog 存到position文件中，留做下一次增量备份的起始点。

vi zl_backup.sh
#!/bin/bash
#
# test by xxxxxx
#
DATE=`date +%Y%m%d`
OLDDATE=`date --date='7 days ago' +%Y%m%d`
DATA_DIR=/var/lib/mysql
FILE_DIR=/backup_db/mysql
BACKUP_DIR=/backup_db/mysqlIncr
BACKUPSQL=$BACKUP_DIR/mysql_zlback_$DATE.sql
Log=/backup_db/mysqlBack.log
startbinlog=`awk '{print $1}' $FILE_DIR/position`
startposition=`awk '{print $2}' $FILE_DIR/position`
echo "start binlog: $startbinlog, start position : $startposition" >>$Log
cd $FILE_DIR
mysql -uroot -e "show master status\G;"|awk '{print $2}' > nowposition
stopbinlog=`sed -n '2p' $FILE_DIR/nowposition`
stopposition=`sed -n '3p' $FILE_DIR/nowposition`
echo "stop binlog: $stopbinlog,stop position: $stopposition" >>$Log
time1=`date +%Y%m%d\ %H:%M:%S`
echo "$time1 start to incremental backup" >>$Log
if [ "$startbinlog" == "$stopbinlog" ]; then
mysqlbinlog --start-position=$startposition --stop-position=$stopposition $DATA_DIR/$startbinlog >> $BACKUPSQL
else
startline=`awk "/$startbinlog/{print NR}" $DATA_DIR/mysqld-bin.index`
stopline=`wc -l $DATA_DIR/mysqld-bin.index |awk '{print $1}'`
for i in `seq $startline $stopline`
do
binlog=`sed -n "$i"p $DATA_DIR/mysqld-bin.index |sed 's/.\///g'`
case "$binlog" in
"$startbinlog")
mysqlbinlog --start-position=$startposition $DATA_DIR/$binlog >> $BACKUPSQL
;;
"$stopbinlog")
mysqlbinlog --stop-position=$stopposition $DATA_DIR/$binlog >> $BACKUPSQL
;;
*)
mysqlbinlog $DATA_DIR/$binlog >> $BACKUPSQL
esac
done
fi

if [ -f $BACKUPSQL ];then
gzip $BACKUPSQL
echo "$stopbinlog $stopposition" > $FILE_DIR/position
rm -rf $BACKUP_DIR/mysql_zlback_$OLDDATE.tar.gz
time2=`date +%Y%m%d\ %H:%M:%S`
spendTime=$(($(date +%s -d "$time2")-$(date +%s -d "$time1")))
echo "$time2 incremental backup finished!" >>$Log
echo "incremental backup spend total time : $spendTime seconds" >>$Log
else
echo "$DATE incremental backup fail!!!!" >>$Log

三、做好计划任务
把增量备份脚本和完全备份脚本按照想要的时间写在计划任务中，做定时的增量备份。
当然还原的时候需要按照顺序依次还原。