mysql异地增量备份工具_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的最后一个文件里,并生成一个空的新日志文件,之后只需要备份前面的几个即可,最后一个因为是刚生成的，并且是空的，所以不需备份。

大概思路：

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

分享到：

2012-09-18 18:37

浏览 860

分类:数据库

评论