xtrackback还原数据库脚本

#! /bin/bash

mysqlprod=139

newmysqlprod=16140

#mkdir -p /dbs/$mysqlprod/

mkdir -p /mysql/mysql_sysn_${mysqlprod}_01/conf/ /mysql/mysql_sysn_${mysqlprod}_01/binlog/ && chmod -R 777 /mysql/mysql_sysn_${mysqlprod}_01/binlog/

sed "s/${mysqlprod}/${mysqlprod}_01/g"  /mysql/mysql_sysn_${mysqlprod}/mysql/conf/my.cnf > /mysql/mysql_sysn_${mysqlprod}_01/conf/my.cnf

sed "s/${mysqlprod}/${mysqlprod}_01/g"  /mysql/mysql_sysn_${mysqlprod}/mysql/conf/mysqld.cnf > /mysql/mysql_sysn_${mysqlprod}_01/conf/mysqld.cnf

build_mysql(){

  docker run --restart=always --name mysql_${mysqlprod}_01 -p ${newmysqlprod}:3306 -v  /mysql/mysql_sysn_${mysqlprod}_01/conf:/etc/mysql/conf.d  -v /mysql/mysql_sysn_${mysqlprod}_01/conf/mysqld.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf -v /dbs/${mysqlprod}/full/mysql/:/mysql/mysql_sysn_${mysqlprod}_01/mysql/data -v /mysql/mysql_sysn_${mysqlprod}_01/binlog/:/binlog/ -e MYSQL_ROOT_PASSWORD=xxxx -d mysql:5.7

  #打印容器日志

  dockerId=`docker ps -a -q|grep mysql_${mysqlprod}_01`

  echo "数据库容器id为：$dockerId ....."

}

xbtream="/usr/bin/xbstream"

innobackupex="/usr/bin/innobackupex"

#备份目录

dbbackdir="/dbs/${mysqlprod}"

#全量目录

fulldir="/dbs/${mysqlprod}/full/mysql"

dbconfig="/mysql/mysql_sysn_${mysqlprod}/mysql/conf/mysqld.cnf"

myconfig="/mysql/mysql_sysn_${mysqlprod}/mysql/conf/my.cnf"

depress(){

  #拷贝备份文件到还原目录

  echo "拷贝备份文件到备份目录中..."`date -R`...

  cp /dbs/backup/mysql/$mysqlprod /dbs -rp

  echo "完成拷贝备份文件"`date -R`...

  echo "/dbs/back/mysql/$mysqlprod"

  #获取备份文件并解压

  echo "开始解压备份文件...."

  for file in `find  $dbbackdir  -type f -regex  ".*\(.xbstream\)"`; do

    ##获取增量备份文件全路径

    filedir=`dirname $file`

    ##解压全增量备份文件

    #echo $filedir

    #pwd filedir

    cd $filedir

    dir=`pwd`

    mkdir mysql

    filename=`ls *.xbstream`

    $xbtream -x -C $dir/mysql<$file

    #解压

    innobackupex --decompress --parallel=6 --compress-threads=6 $dir/mysql

    #删除压缩文件

    find  $dir/mysql  -type f -regex  ".*\(.qp\)" -exec rm -rf {} \;   

  done

  #恢复全量

  $innobackupex --defaults-file=$dbconfig --use-memory=256M --apply-log $fulldir --redo-only

  #恢复增量

  for file in `find $dbbackdir  -type f -regex  ".*\(incremental.xbstream\)"|xargs ls -ltr|tr -s " "|cut -d" " -f 9`; do

  #  echo "$file"

    #增量目录

    incrementaldir=`dirname $file`

    echo $incrementaldir

    $innobackupex --defaults-file=$dbconfig --use-memory=256M --apply-log $fulldir --incremental-dir=$incrementaldir  --redo-only

    $innobackupex --defaults-file=$dbconfig --use-memory=256M --apply-log $fulldir

  done

  chown -R -f mysql:mysql $fulldir

  #需要修改docker mysql配置文件指向新的mysql数据目录，然后重启mysql

  #sed -i '3,3c\datadir = $fulldir'

  #docker restart mysql_${mysqlprod}_01

  sed -i '3,3c\datadir = $fulldir' $dbconfig

  #docker restart mysql_${mysqlprod}_01

}

#sed -i '3,3c\datadir = $fulldir' $dbconfig

#docker restart mysql_${mysqlprod}_01

#binlog恢复

#查看最后同步的binlog文件和位点

#tart_binlog=`cat $fulldir/xtrabackup_binlog_pos_innodb |awk '{print $1}'`

#sart_position=`cat $fulldir/xtrabackup_binlog_pos_innodb |awk '{print $2}'`

#原库binlog位置

binlogdir=/mysql/mysql_sysn_${mysqlprod}/mysql/binlog

binlogcount=`ls $binlogdir|grep mysql-bin.00*|wc -l`

mysql="/usr/bin/mysql"

mysql_host="127.0.0.1"

mysql_user="root"

mysql_pwd="LzslovZJHEN6x123RU"

mysql_port=${newmysqlprod}

mysqlbinlog=/usr/bin/mysqlbinlog

reback_binlog(){

  echo "开始恢复binlog,请耐心等待。。。"

  start_binlog=`cat $fulldir/xtrabackup_binlog_info |awk '{print $1}'`

  ls $fulldir/xtrabackup_binlog_info

  start_position=`cat $fulldir/xtrabackup_binlog_info |awk '{print $2}'`

  #直接获取原库日志来恢复binlog数据

  for log in `ls $binlogdir |grep "mysql-bin.00*"|grep -A 10 "$start_binlog"`;do

    echo $log

    cd $binlogdir

    pwd

    if [ $log = $start_binlog ]; then

        #echo "mysql -u$mysql_user -p$mysql_pwd -h$mysql_host -P$mysql_port"

        mysqlbinlog $log --start-position=$start_position|mysql -u$mysql_user -p$mysql_pwd -h$mysql_host -P$mysql_port

    else

        #echo 2

        #mysqlbinlog $log |mysql -uroot -pLzslovZJHEN6x123RU -h127.0.0.1 -P16140

        mysqlbinlog $log|mysql -u$mysql_user -p$mysql_pwd -h$mysql_host -P$mysql_port

    fi

  done

}

case $1 in

  depress)

    starttime=`date -R`

    echo "解压还原..."

    depress

    build_mysql

    endtime=`date -R`

    echo "解压完成，耗时start->end: $starttime -> $endtime"

  ;;

  binlog)

    starttime=`date -R`

    echo "恢复binlog"

    reback_binlog

    endtime=`date -R`

    echo "追加binlog完成，耗时start->end: $starttime -> $endtime"

  ;;

  *)

    echo "depress|binlog"

  ;;

esac