MySQL异地增量备份:
- #!/bin/sh
- #MySQL Info
- sqluser=root
- sqlpsw=root
- /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=111.111.11.11
- ftpname=xxx
- ftppsw=psw
- 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://www.linuxidc.com/Linux/2012-09/70815.htm
大概思路:
把datadir和bakdir目录中的binlog进行对比,如bakdir中没有,且该binlog不是最后一个,则把该binlog复制到tempdir中;这样tempdir中的binlog都是新增的binlog。之后把tempdir中的binlog进行打包压缩,ftp到另一个服务器上。