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

三、做好計劃任務
把增量備份指令碼 和 完全備份指令碼按照想要的時間寫在計劃任務中，做定時的增量備份。
當然還原的時候需要按照順序依次還原。