实现mysqlldump+binlog日志全量备份和增量备份脚本

实现mysqlldump+binlog日志全量备份和增量备份脚本

#!/bin/bash
###########################################################################################
#  @programe  : backup_mysql.sh
#  @version   : 0.0.3                                                       
#  @function@ : backup of the mysql database with full and incremental
#  @campany   : YOUGOU
#  @dep.      : Operation and maintenance Team                                        
#  @writer    : Huang Ling Fei                                              
#  @date      : 2012-03-12                                                
############################################################################################


PATH=/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
export PATH


Datetime=$(date +%Y%m%d_%H%M)
Week=$(date +%u)
Log="/data/sh/logs/backupmyql.log"
Mysqldopt="-R -E -e --single-transaction --master-data=1 --flush-logs --default-character-set=utf8"
Mysqldnet="--max_allowed_packet=167772160 --net_buffer_length=163840"
Mysqldu="mysqlbak"
Mysqldp="passwd"
Mysqldfullbak="yitian_b2c_db_full.${Datetime}"
Mysqldpath="/data/backup_db"
Mysqldatapath="/data/mysql/data"
if [ ! -d /data/sh/logs ];then
   mkdir -p /data/sh/logs
fi
if [ ! -d /data/backup_db ];then
   mkdir -p /data/backup_db
fi
if [ ! -f /data/backup_db/startpostion ];then
   touch /data/backup_db/startpostion
fi
fullbackup(){
echo Start full backup the databases++++++++$Datetime
mysql -u$Mysqldu -p$Mysqldp -e "slave stop"
mysql -u$Mysqldu -p$Mysqldp -e "set global max_allowed_packet=167772160"
mysql -u$Mysqldu -p$Mysqldp -e "set global net_buffer_length=163840"
mysqldump -u$Mysqldu -p$Mysqldp $Mysqldopt $Mysqldnet --database yitian_b2c_db >${Mysqldpath}/${Mysqldfullbak}
mysql -u$Mysqldu -p$Mysqldp -e "slave start"
################get postion###############
head -n22 ${Mysqldpath}/${Mysqldfullbak}|tail -n1|awk -F"=|'|,|;" '{print$3,$6}' >${Mysqldpath}/startpostion
}
cat ${Mysqldpath}/startpostion
echo ++++++++++++++++++++++++++++++++++++++++++++++++++++++++
incrementalbackup(){
echo Start incremental backup the database++++++++$Datetime


###############get start postion##########
echo =========================================================
Startbinlog=$(awk '{print $1}' ${Mysqldpath}/startpostion)
echo "Startbinlog $Startbinlog"
Startpostion=$(awk '{print $2}' ${Mysqldpath}/startpostion)
echo "Startpostion $Startpostion"


###############get end postion##########
mysql -u$Mysqldu -p$Mysqldp -e "show master status\G"|awk '{print $2}' >${Mysqldpath}/endpostion
echo -------------------------------------------------------- 
Endbinlog=$(sed -n '2p' ${Mysqldpath}/endpostion) 
echo "Endbinlog $Endbinlog"
Endpostion=$(sed -n '3p' ${Mysqldpath}/endpostion)
echo "Endpostion $Endpostion"
echo =========================================================




if [ "$Startbinlog" == "$Endbinlog" ]; then
    echo "start-position=$Startpostion stop-position=$Endpostion"
    mysqlbinlog --start-position=$Startpostion --stop-position=$Endpostion ${Mysqldatapath}/${Startbinlog} >>${Mysqldpath}/incremental_$Datetime.sql
   else
    Startline=$(awk "/${Startbinlog}/{print NR}" ${Mysqldatapath}/mysql-bin.index)
    Endline=$(wc -l ${Mysqldatapath}/mysql-bin.index |awk '{print $1}')
    echo "Startline $Startline - Endline $Endline"
    for i in `seq $Startline $Endline`
    do
    Binlog=$(sed -n "$i"p ${Mysqldatapath}/mysql-bin.index |sed 's/.\///g')
    case "$Binlog" in
        "$Startbinlog")
                   mysqlbinlog --start-position=$Startpostion ${Mysqldatapath}/${Binlog} >>${Mysqldpath}/incremental_$Datetime.sql
                   ;;
        "$Endbinlog")
                   mysqlbinlog --stop-position=$Endpostion ${Mysqldatapath}/${Binlog} >>${Mysqldpath}/incremental_$Datetime.sql
                   ;;
                    *)
                  mysqlbinlog ${Mysqldatapath}/${Binlog} >>${Mysqldpath}/incremental_$Datetime.sql
    esac
    done
fi
echo "Start set the startpostion is to Endbinlog $Endbinlog - Endpostion $Endpostion"
echo "$Endbinlog $Endpostion" >${Mysqldpath}/startpostion
}
if [ $Week -ne 1 ];then
    echo "today is $Week and begin incremental backup the database"
    incrementalbackup
else
    echo "today is $Week and begin full backup of the database"
    fullbackup
fi