mysql基于myduper做增量备份

mysql基于myduper做增量备份

由于二进制的特性，并不能为单独库做增量，单独库只能全量，或者给整个实例做增量备份
需要mysql的二进制日志记录格式为statement或mixed(默认)，row格式时为数据结果，也能做但人读sql看不到，如果不需要编辑sql，此格式也可以

#!/usr/bin/env bash
set -e

user=root
passwd=123456
ctime=`date +'%Y%m%d_%H%M'`
cday=`date +%u`
info_file=bak_info
log_file=bak.log
dbs="hj"
threads=4
pbz2_cmd="pbzip2 -m800 -5 -b8000 -l"
mysql_datadir=/data/mysql
bak_dir=/opt/bk

total_backup(){
  total_dir=total_data_$ctime
  mydumper -u $user -p $passwd --less-locking -L $log_file -t $threads -o ./$total_dir && \
  echo "latest_dir=$total_dir" > ./$info_file

  pos=`awk '/Position/{print $3}' $total_dir/metadata`
  binlog=`awk '/File/{print $3}' $total_dir/metadata`
  echo -e "end_pos=$pos\nend_binlog=$binlog" >> ./$info_file
  tar cf - $total_dir |$pbz2_cmd > ${total_dir}.tbz2
}
db_backup(){
  for db in $dbs ;do
    mydumper -u $user -p $passwd --less-locking -L $log_file -t $threads -o ./${db}_$total_dir -B $db && \
    echo "latest_dir=${db}_$total_dir" > ./${db}_$info_file

    pos=`awk '/Position/{print $3}' ${db}_${total_dir}/metadata`
    binlog=`awk '/File/{print $3}' ${db}_${total_dir}/metadata`
    echo -e "end_pos=$pos\nend_binlog=$binlog" >> ./${db}_$info_file
    tar cf - ${db}_$total_dir |$pbz2_cmd > ${db}_${total_dir}.tbz2
  done
}

inc_backup(){
    . ./$info_file
    start_pos=$end_pos
    start_binlog=$end_binlog
    end_pos=`mysql -u$user -p$passwd -e 'show master status' 2> /dev/null |awk  'NR==2{print $2}'`
    end_binlog=`mysql -u$user -p$passwd -e 'show master status' 2> /dev/null |awk  'NR==2{print $1}'`
    increment_file=increment_$ctime

    if [ $end_binlog \> $start_binlog ] ;then
      mysqlbinlog --start-position $start_pos ${mysql_datadir}/$start_binlog > ./$increment_file
      mysqlbinlog --stop-position $end_pos ${mysql_datadir}/$end_binlog >> ./$increment_file
    elif [ $end_binlog \= $start_binlog ] ;then
      mysqlbinlog --start-position $start_pos --stop-position $end_pos ${mysql_datadir}/$start_binlog > ./$increment_file
    fi
    rm -rf ./$latest_dir
    echo -e "latest_dir=${increment_file}\nend_pos=$end_pos\nend_binlog=$end_binlog" > ./$info_file
    tar cf - $increment_file |$pbz2_cmd > ${increment_file}.tbz2
}
# 恢复： myloader -u root -p 123456 -t $threads --append-if-not-exist -o --innodb-optimize-keys -B hj -d 备份目录

cd $bak_dir
case $cday in
  7)
      total_backup
#      db_backup
      ;;
  *)  inc_backup ;;
esac