MySQL异地增量备份

MySQL异地增量备份: 1.#!/bin/sh 2. 3.#MySQL Info 4.sqluser=root 5.sqlpsw=root 6. 7./usr/bin/mysqladmin -u$sqluser -p$sqlpsw flush-logs --default-character-set=GBK 8. 9.DATADIR=/data/mysql 10.BAKDIR=/home/backup/mysql/daily 11.TEMPDIR=/home/b

MySQL异地增量备份:

1.#!/bin/sh
2.
3.#MySQL Info
4.sqluser=root
5.sqlpsw=root
6.
7./usr/bin/mysqladmin -u$sqluser -p$sqlpsw flush-logs --default-character-set=GBK 8.
9.DATADIR=/data/mysql
10.BAKDIR=/home/backup/mysql/daily
11.TEMPDIR=/home/backup/mysql/temp
12.
13.BINLOGNAME='mysql-bin' 14.cd $DATADIR
15.FILELIST=$(cat $BINLOGNAME.index | cut -c3-) 16.
17.## COUNTER number
18.COUNTER=0
19.for file in $FILELIST 20.do 21.COUNTER=$(expr $COUNTER + 1)
22.done
23.
24.NextNum=0
25.for file in $FILELIST 26.do 27.NextNum=$(expr $NextNum + 1)
28.if test "$NextNum" = "$COUNTER" 29.then
30.echo "skip lastest" 31.else 32.dest=$BAKDIR/$file
33.if test -e $dest 34.then
35.echo "skip exist $dest" 36.else 37.cp $file $TEMPDIR
38.fi
39.fi
40.done
41.echo "backup mysql binlog ok" 42.
43.sleep 2s
44.
45.cd $TEMPDIR
46.tarname=binlog$(date +%y%m%d).tar.gz
47.tar czf $tarname ./mysql-bin*
48.
49.sleep 1s 50.
51.#ftp Info
52.ftphost=111.111.11.11
53.ftpname=xxx
54.ftppsw=psw
55.
56.cd $TEMPDIR
57.
58.ftp -i -n 59.open $ftphost
60.user $ftpname $ftppsw
61.type binary
62.put $tarname
63.close
64.bye
65.!
66.sleep 10s
67.
68.echo "end upload" 69.
70.cd $TEMPDIR
71.mv $BINLOGNAME* $BAKDIR
72.rm -r $tarname
73.
74.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到另一个服务器上。