MySQL binlog 自动备份脚本
1 利用shell进行备份
#!/bin/sh
#mysql binlog backup script
/usr/local/mysql/bin/mysqladmin -u* -p* -h127.0.0.1 -P3306 flush-logs
DATADIR=/data/mysqldata/3306/binlog
BAKDIR=/data/mysqldata/backup/binlogbk
LOG=/data/mysqldata/backup/mysqlbinlog_bk.logecho "NOW is : Backup type=mysql binlog backup" >> ${LOG}
DATE_D=`date +'%Y%m%d'`if [ ! -d "$BAKDIR}" ]; then
mkdir -p "$BAKDIR"
echo -e "===$BAKDIR does not exsits,created them!" >> ${LOG}
fi
cd $DATADIR
FILELIST=`cat /data/mysqldata/3306/binlog/mysql-bin.index`
COUNTER=0
for file in $FILELIST
do
COUNTER=`expr $COUNTER + 1`
done
NextNum=0
for file in $FILELIST
do
base=`basename $file`
NextNum=`expr $NextNum + 1`
if [ $NextNum -eq $COUNTER ]
then
echo -e "skip lastest" >> ${LOG}
else
dest=$BAKDIR/$base
if(test -e $dest)
then
echo -e "skip exist $base" >> ${LOG}
else
echo -e "copying $base" >> ${LOG}
cp -rp $base $BAKDIR/.
fi
fi
done
if [ $? = 0 ]
then
echo -e "backup mysql binlog ok on $DATE_D" >> ${LOG}
else
echo -e "backup mysql binlog failover on $DATE_D!!!" >> ${LOG}
fi
[mysql@mysqlhq scripts]$ ./mysql_binlog_backup.sh
Warning: Using a password on the command line interface can be insecure.
[mysql@mysqlhq scripts]$ cd /home/data/mysqldata/backup/binlogbk/
[mysql@mysqlhq binlogbk]$ ll
total 1496016
-rw-rw—- 1 mysql mysql 65279 Sep 19 10:12 mysql-bin.000001
-rw-rw—- 1 mysql mysql 1053952 Sep 19 10:12 mysql-bin.000002
-rw-rw—- 1 mysql mysql 139989697 Oct 8 09:12 mysql-bin.000003
-rw-rw—- 1 mysql mysql 524288167 Oct 18 09:45 mysql-bin.000004
-rw-rw—- 1 mysql mysql 524288148 Oct 31 08:16 mysql-bin.000005
-rw-rw—- 1 mysql mysql 83358311 Nov 2 09:08 mysql-bin.000006
-rw-rw—- 1 mysql mysql 200437 Nov 2 09:17 mysql-bin.000007
-rw-rw—- 1 mysql mysql 258655163 Nov 8 17:13 mysql-bin.000008
##由于脚本里面配置的是明文密码，Warning: Using a password on the command line interface can be insecur
## 可以加上 2>/dev/null
