xtrack mysql_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

#解压

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