MySQL增量备份本地异地双备份

 

#!/bin/sh

#MySQL Info
sqluser=root
sqlpsw=slave@101B

/usr/bin/mysqladmin -u$sqluser -p$sqlpsw flush-logs --default-character-set=GBK

DATADIR=/data/mysql
BAKDIR=/home/backup/mysql/daily
TEMPDIR=/home/backup/mysql/temp

BINLOGNAME='mysql-bin'
cd $DATADIR
FILELIST=$(cat $BINLOGNAME.index | cut -c3-)

## COUNTER number
COUNTER=0
for file in $FILELIST
do
COUNTER=$(expr $COUNTER + 1)
done

NextNum=0
for file in $FILELIST
do
NextNum=$(expr $NextNum + 1)
if test "$NextNum" = "$COUNTER"
then
echo "skip lastest"
else
dest=$BAKDIR/$file
if test -e $dest
then
echo "skip exist $dest"
else
cp $file $TEMPDIR
fi
fi
done
echo "backup mysql binlog ok"

sleep 2s

cd $TEMPDIR
tarname=binlog$(date +%y%m%d).tar.gz
tar czf $tarname ./mysql-bin*

sleep 1s

#ftp Info
ftphost=118.123.22.162
ftpname=datasql
ftppsw=backup99@KF

cd $TEMPDIR

ftp -i -n <<!
open $ftphost
user $ftpname $ftppsw
type binary
put $tarname
close
bye
!
sleep 10s

echo "end upload"

cd $TEMPDIR
mv $BINLOGNAME* $BAKDIR
rm -r $tarname

echo "end"

 

 

 

说明：

mysqladmin -u$sqluser -p$sqlpsw flush-logs：mysql自动把内存中的日志放到日志文件binlog的最后一个文件里,并生成一个空的新日志文件,之后只需要备份前面的几个即可,最后一个因为是刚生成的，并且是空的，所以不需备份。

关于binlog可参考http://cxhyrf-sina-com.iteye.com/blog/1680055

 

大概思路：

    把datadir和bakdir目录中的binlog进行对比，如bakdir中没有，且该binlog不是最后一个，则把该binlog复制到tempdir中；这样tempdir中的binlog都是新增的binlog。之后把tempdir中的binlog进行打包压缩，ftp到另一个服务器上。