MySQL binlog 自动备份脚本

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.log

echo "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