MySQL增量备份异地备份

#!/bin/sh

#MySQL Info
sqluser=root
sqlpsw=root
#sqluser2=root
#sqlpsw2=root

POSITION=/home/backup/position
if test ! -e $POSITION
then
#echo "The first time daily backup, the file of position deesn't exist or it's content is null"
mysql  -u$sqluser -p$sqlpsw -e "show master status\G;" --default-character-set=gbk | awk '{print $2}' | sed -n '2,3p' > $POSITION
else
DATADIR=/data/mysql
BAKDIR=/home/backup/mysql/daily
TEMPDIR=/home/backup/mysql/temp
DATABASE1=a
DATABASE2=b

startbinlog=$(sed -n '1p' $POSITION)
startposition=$(sed -n '2p' $POSITION)
#echo "startbinlog: "$startbinlog
#echo "startposition: "$startposition

rm -rf $POSITION
mysql  -u$sqluser -p$sqlpsw -e "show master status\G;" --default-character-set=gbk | awk '{print $2}' | sed -n '2,3p' > $POSITION

stopbinlog=$(sed -n '1p' $POSITION)
stopposition=$(sed -n '2p' $POSITION)
#echo "stopbinlog: "$stopbinlog
#echo "stopposition: "$stopposition

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


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


if test  "startbinlog" = "$stopbinlog"
then
#echo "startbinlog = stopbinlog"
mysqlbinlog --start-position=$startposition  --stop-position=$stopposition -d $DATABASE1 $startbinlog >> $TEMPDIR/$DATABASE1$(date +%y%m%d)1.sql
mysqlbinlog --start-position=$startposition  --stop-position=$stopposition -d $DATABASE2 $startbinlog >> $TEMPDIR/$DATABASE2$(date +%y%m%d)1.sql
else
startline=$(awk "/$startbinlog/{print NR}" $DATADIR/$BINLOGNAME.index)
stopline=$(wc -l $DATADIR/$BINLOGNAME.index | awk '{print $1}')
#echo "startline: "$startline
#echo "stopline: "$stopline

for i in $(seq $startline $stopline)
do
binlog=$(sed -n "$i"p $DATADIR/$BINLOGNAME.index | sed 's/.\///g')
#binlog=$(sed -n "$i"p $DATADIR/$BINLOGNAME.index | cut -c3-)
#echo "binlog: "$binlog
case "$binlog" in
	"$startbinlog")
		mysqlbinlog --start-position=$startposition -d $DATABASE1 $binlog >> $TEMPDIR/$DATABASE1$(date +%y%m%d)$i.sql
		mysqlbinlog --start-position=$startposition -d $DATABASE2 $binlog >> $TEMPDIR/$DATABASE2$(date +%y%m%d)$i.sql;;
	"$stopbinlog")
		mysqlbinlog --stop-position=$stopposition -d $DATABASE1 $binlog >> $TEMPDIR/$DATABASE1$(date +%y%m%d)$i.sql
		mysqlbinlog --stop-position=$stopposition -d $DATABASE2 $binlog >> $TEMPDIR/$DATABASE2$(date +%y%m%d)$i.sql;;
	*)
		mysqlbinlog -d $DATABASE1 $binlog >> $TEMPDIR/$DATABASE1$(date +%y%m%d)$i.sql
		mysqlbinlog -d $DATABASE2 $binlog >> $TEMPDIR/$DATABASE2$(date +%y%m%d)$i.sql;;
esac
done
#echo "backup mysql binlog ok"
fi

sleep 2s

cd $TEMPDIR

#delete last daily backup tar files
rm -rf daily$DATABASE1$(date -d "-1 day" +%y%m%d).tar.gz
rm -rf daily$DATABASE2$(date -d "-1 day" +%y%m%d).tar.gz

tarname1=daily$DATABASE1$(date +%y%m%d).tar.gz
tarname2=daily$DATABASE2$(date +%y%m%d).tar.gz
tar czf $tarname1 ./$DATABASE1*
tar czf $tarname2 ./$DATABASE2*
sleep 1s

#ftp Info
ftphost=11.11.11.11
ftpname=dal
ftppsw=psw
REMOTEDIR=dailybackup

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

#echo "end upload"

#cd $TEMPDIR
rm -rf $DATABASE1*
rm -rf $DATABASE2*

#echo "end"

fi

  

说明：

 

show master status：该命令查看当前的binlog及position。

 

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

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

大概思路：

    在备份之前需先执行一次。第一次执行备份时，利用show master status获得startbinlog、startposition，并放到position文件中；之后每次执行，都获取当前的binlog、position作为stopbinlog、stopposition，并放到position文件中，之后flush-logs。利用startbinlog、startposition、stopbinlog、stopposition及binlog即可导出相应的sql，之后压缩、ftp，然后就ok了。