mysql全备份和增量备份

参照博客：http://lhflinux.blog.51cto.com/1961662/506467

十分感谢博主的共享！

以下是我按照上述博客修改的脚本！

一、完整备份
vi full_mysqldump.sh
#!/bin/sh
# test by xxxxx
#time info
time=` date +%Y%m%d%H `
time1=` date +%Y%m%d\ %H:%M:%S `
#DIR_INFO
BACK_DIR="/backup_db/mysql"
FILE_DIR="/backup_db/mysql"
#MYSQL_BACK
Log=/backup_db/mysqlBack.log
echo "$time1 start to full backup " >> $Log
mysqldump -uroot  --flush-logs --master-data=2 zabbix|gzip >$BACK_DIR/mysqlFull$time.sql.gz
time2=` date +%Y%m%d\ %H:%M:%S `
echo "$time2 full backup successfully" >>$Log
spendTime=$(($(date +%s -d "$time2")-$(date +%s -d "$time1")))
echo "backup spend total time : $spendTime seconds" >> $Log
#get postion
sleep 5s
gunzip < $BACK_DIR/mysqlFull$time.sql.gz | sed -n "22p" | awk -F'=|,|;' '{print $2,$4}' > $FILE_DIR/position
# remove 7 days ago backup
find $BACK_DIR -name "mysql*.sql.gz" -mtime +7 -exec rm {} \;
echo "remove 7 days ago backup !" >>$Log


二、做增量备份，根据上面完整备份所获得的position以及binlog 与服务器当前的position和binlog做增量备份，
备份后把当前的position以及binlog 存到position文件中，留做下一次增量备份的起始点。
 
vi  zl_backup.sh
#!/bin/bash
#
# test by xxxxxx
#
DATE=`date +%Y%m%d`
OLDDATE=`date --date='7 days ago' +%Y%m%d`
DATA_DIR=/var/lib/mysql
FILE_DIR=/backup_db/mysql
BACKUP_DIR=/backup_db/mysqlIncr
BACKUPSQL=$BACKUP_DIR/mysql_zlback_$DATE.sql
Log=/backup_db/mysqlBack.log
startbinlog=`awk '{print $1}' $FILE_DIR/position`
startposition=`awk '{print $2}' $FILE_DIR/position`
echo "start binlog: $startbinlog, start position : $startposition"  >>$Log
cd $FILE_DIR
   mysql -uroot  -e "show master status\G;"|awk '{print $2}' > nowposition
stopbinlog=`sed -n '2p' $FILE_DIR/nowposition`
stopposition=`sed -n '3p' $FILE_DIR/nowposition`
echo "stop binlog: $stopbinlog,stop position: $stopposition" >>$Log
time1=`date +%Y%m%d\ %H:%M:%S`
echo "$time1 start to incremental backup" >>$Log
if [ "$startbinlog" == "$stopbinlog" ]; then
     mysqlbinlog --start-position=$startposition --stop-position=$stopposition $DATA_DIR/$startbinlog >> $BACKUPSQL
else
     startline=`awk "/$startbinlog/{print NR}" $DATA_DIR/mysqld-bin.index`
     stopline=`wc -l $DATA_DIR/mysqld-bin.index |awk '{print $1}'`
     for i in `seq $startline $stopline`
     do
         binlog=`sed -n "$i"p  $DATA_DIR/mysqld-bin.index |sed 's/.\///g'`
         case "$binlog" in
                        "$startbinlog")
                   mysqlbinlog --start-position=$startposition $DATA_DIR/$binlog >> $BACKUPSQL
                   ;;
                        "$stopbinlog")
                   mysqlbinlog --stop-position=$stopposition $DATA_DIR/$binlog >> $BACKUPSQL
                   ;;
                                 *)
                   mysqlbinlog $DATA_DIR/$binlog >> $BACKUPSQL
        esac
    done
fi


if [ -f $BACKUPSQL ];then
    gzip $BACKUPSQL
    echo "$stopbinlog $stopposition" > $FILE_DIR/position
    rm -rf $BACKUP_DIR/mysql_zlback_$OLDDATE.tar.gz
    time2=`date +%Y%m%d\ %H:%M:%S`
    spendTime=$(($(date +%s -d "$time2")-$(date +%s -d "$time1")))
    echo "$time2 incremental backup finished!" >>$Log
    echo "incremental backup spend total time : $spendTime seconds" >>$Log
else 
    echo "$DATE incremental backup fail!!!!" >>$Log


三、做好计划任务
把增量备份脚本 和 完全备份脚本按照想要的时间写在计划任务中，做定时的增量备份。
当然还原的时候需要按照顺序依次还原。