mysqldump全量&增量备份脚本,生产可用

　　

路径和密码等相关变量根据实际情况修改,经过验证,数据量不大可用

全量脚本

#!/bin/env bash

full_dir=/opt
incremental_dir=/opt
log_file=/opt/full_backup.log

commence=$(date +%s)
mysqldump --single-transaction --flush-logs --quick --all-databases --master-data=2 --events --routines --triggers \
        | gzip -9  --quiet --force - > $full_dir/$(date +%F__%H-%M-%S).sql.gz

# Another way to backup
#sleep 2
#
#pipe=$(mktemp)
#rm -f $pipe
#mkfifo $pipe
#gzip -9 --quiet --force < $pipe > $full_dir/$(date +%F__%H-%M-%S).sql.gz &
#mysqldump --single-transaction --flush-logs --quick --all-databases --master-data=2 --events --routines --triggers >> $pipe
#rm -f $pipe

closure=$(date +%s)
rm -rf $incremental_dir/binlog.*
abidance=$((closure-commence))

echo -e '\n' >> $log_file
echo "Commence: `date --date=@$commence +%F__%T`, Closure: `date --date=@$closure +%F__%T`" >> $log_file

function convert_seconds() {
    local T=$1
    local D=$((T/60/60/24))
    local H=$((T/60/60%24))
    local M=$((T/60%60))
    local S=$((T%60))

    (($D > 0)) && printf ' %d days' $D
    (($H > 0)) && printf ' %d hours' $H
    (($M > 0)) && printf ' %d minutes' $M
    ((($D > 0 || $H > 0 || $M > 0) && $S != 0)) && printf ' and '
    (($S > 0 )) && printf '%d seconds\n' $S
}

echo full backup abidance `convert_seconds $abidance` >> $log_file

 

增量脚本

#!/bin/bash

incremental_dir=/opt
data_dir=/var/lib/mysql
log_file=/opt/incremental_backup.log
binlog_index=/var/lib/mysql/binlog.index

criterion=`date -d 'last sunday' +%s`  # record last full backup time

mysql -uroot --password='' -e 'flush logs' >& /dev/null  # flush logs

quantity=`wc --lines $binlog_index | awk '{print $1}'`  # number of binlog files

quantum=0

for file in `cat $binlog_index`;do
    base=`basename $file`
    quantum=$((++quantum))
    destination_file=$incremental_dir/$base
    source_file=${data_dir}/$base


    timestamp=`stat --format=%Y $source_file`  # timestamp of the binlog

    if [[ $timestamp -lt $criterion ]];then
        continue
    fi

    if [[ $quantum -eq $quantity ]];then
        echo skip newly flush logs created $source_file >> $log_file
    else

        if [[ $quantum -eq 1 ]];then
            echo -e '\n' >> $log_file
            echo `date +"%F %T %u %A %B"` binlog backup starting >> $log_file
        fi

        if ! [[ $source_file -nt $destination_file ]];then
            echo $source_file exist! >> $log_file
        else
            cp -a $source_file $incremental_dir
            echo $source_file copyed >> $log_file
        fi
    fi
done

echo `date +"%F %T %u %A %B"` binlog backup finished >> $log_file

 

定时任务

0 2 * * 0 /bin/bash -x /opt/full_backup.sh > /dev/null 2>&1
0 2 * * 1-6 /bin/bash -x /opt/incremental_backup.sh > /dev/null 2>&1