使用shell脚本xtrabackup自动恢复MySQL数据库

【背景说明】

按照安全的一些要求，需要定期对数据库进行恢复演练操作

【环境说明】

MySQL 5.7 的xtrabackup全库xbstream的加密备份（如果不是流备份跟加密，去掉相关参数）

【脚本说明】

v_backupdir="/mysqlbackup/recovery/yiyuan" 备份文件的目录路径
v_dir="/mysqlbackup/recovery/yiyuan"       恢复数据库的数据文件路径
v_recovery_dir="${v_dir}/mysql"            恢复数据库的数据文件路径的子路径为mysql
v_cnf="/etc/my.13309.cnf"                  数据库的配置文件，可以自己修改名称跟端口信息，参数内容可以根据源库的配置修改相关信息，这里只添加常见参数
v_xbfile=`find ${v_backupdir} -name *fullbackup_cloud.xbstream`   匹配备份文件的名称格式，可以根据备份情况定义格式匹配
v_encrypt_key="XXXXXrDFVx6UAsRb88uLVbAVWbK+Yzfs"                  加密备份时候的密文
v_parallel="8"                             数据库恢复时候的并行度，提升恢复速度，根据主机配置调整参数

startup_recover_mysql                      函数部分可以根据实际情况方式修改自己想要启动MySQL

 

vim  auto_recovery_xb.sh 
######################################################################
# This script is mysql xtrabackup recovery
# Author CZT
######################################################################
#!/bin/sh
. ~/.bash_profile
v_backupdir="/mysqlbackup/recovery/yiyuan"
v_dir="/mysqlbackup/recovery/yiyuan"
v_recovery_dir="${v_dir}/mysql"
v_cnf="/etc/my.13309.cnf"
v_date=`date +%Y-%m-%d`
v_xbfile=`find ${v_backupdir} -name *fullbackup_cloud.xbstream`
v_encrypt_key="XXXXXrDFVx6UAsRb88uLVbAVWbK+Yzfs"
v_parallel="8"
v_date_info=`date '+%Y-%m-%d %H:%M:%S' `

echo "`date '+%Y-%m-%d %H:%M:%S' ` [INFO]: Auto recovery mysql"
if [ ! -f "$v_xbfile" ]; then
  echo "`date '+%Y-%m-%d %H:%M:%S' ` [INFO]: Not found Recovery xbfile. Please check the xbstream files"
  exit 1
else
  echo "`date '+%Y-%m-%d %H:%M:%S' ` [INFO]: Recovery xbfile: $v_xbfile"
  echo "`date '+%Y-%m-%d %H:%M:%S' ` [INFO]: Encrypt key: $v_encrypt_key"
  echo "`date '+%Y-%m-%d %H:%M:%S' ` [INFO]: Recovery directory: $v_recovery_dir"
  echo "`date '+%Y-%m-%d %H:%M:%S' ` [INFO]: Mysql config: $v_cnf"
fi

echo "`date '+%Y-%m-%d %H:%M:%S' ` [INFO]: Add mysql config: /etc/my.13309.cnf"
cat > /etc/my.13309.cnf << EOF
[mysql]

[client]
port               = 13309
socket             = /tmp/mysql_13309.sock

[mysqld]
user               = mysql
port               = 13309
socket             = /tmp/mysql_13309.sock
datadir            = ${v_recovery_dir}
tmpdir             = ${v_recovery_dir}
pid-file           = ${v_recovery_dir}/mysql_13309.pid
log-error          = ${v_dir}/logs/mysqld.log
server-id          = 13309
character_set_server      = utf8
lower_case_table_names    = 1
sql_mode                  = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
log_timestamps            = SYSTEM

max_binlog_size           = 500M
binlog_format             = row
log_bin                   = ${v_dir}/binlog/mysql-bin
relay-log                 = ${v_dir}/relaylog/mysql-relay
relay_log_info_repository = TABLE
relay-log-recovery        = 0
log_slave_updates         = ON
gtid_mode                 = ON
enforce-gtid-consistency  = ON
skip-slave-start
master_info_repository    = TABLE


EOF
echo "`date '+%Y-%m-%d %H:%M:%S' ` [INFO]: Add mysql config completed OK! Please check /etc/my.13309.cnf"

function innobackupex_fullrecovery(){
  echo "`date '+%Y-%m-%d %H:%M:%S' ` [INFO]: Clear directory: $v_dir"
# rm -rf ${v_dir}
  echo "`date '+%Y-%m-%d %H:%M:%S' ` [INFO]: Create directory: $v_recovery_dir"  
  if [ ! -d ${v_recovery_dir} ];then mkdir -p ${v_recovery_dir} ;fi
  mkdir -p ${v_dir}/logs
  mkdir -p ${v_dir}/binlog
  mkdir -p ${v_dir}/relaylog
  mkdir -p ${v_dir}/masterlog

  ###copy xbstream file
  echo "`date '+%Y-%m-%d %H:%M:%S' ` [INFO]: Copy xbstream files"
  xbstream --parallel=${v_parallel} --decrypt=AES256 --encrypt-key=${v_encrypt_key}  --encrypt-threads=4 -x -C ${v_recovery_dir} < ${v_xbfile}
  if [ $? -ne 0 ]
  then
    echo "`date '+%Y-%m-%d %H:%M:%S' ` [INFO]: Copy xbstream files failed! Please check directory $v_recovery_dir files"
    exit 1
  else
    echo "`date '+%Y-%m-%d %H:%M:%S' ` [INFO]: Copy xbstream files completed OK! Please check directory $v_recovery_dir files"
  fi
  
  ###decompress xb file
  echo "`date '+%Y-%m-%d %H:%M:%S' ` [INFO]: Decompress xb files"
  xtrabackup --parallel=${v_parallel} --decompress --remove-original --target-dir=${v_recovery_dir}  2> decompress.log
  v_decompress=`cat decompress.log |awk 'END {print}'|grep 'completed OK!'|wc -l`
  if [ "${v_decompress}" == "1" ];then
    echo "`date '+%Y-%m-%d %H:%M:%S' ` [INFO]: Decompress xb files completed OK! Please check decompress.log"
  else
    echo "`date '+%Y-%m-%d %H:%M:%S' ` [INFO]: Decompress xb files failed! Please check decompress.log"
    exit 1
  fi

  ###prepare mysql
  echo "`date '+%Y-%m-%d %H:%M:%S' ` [INFO]: prepare mysql"
  xtrabackup --defaults-file=${v_cnf} --parallel=${v_parallel} --prepare  --target-dir=${v_recovery_dir}  2> prepare.log
  v_prepare=`cat prepare.log |awk 'END {print}'|grep 'completed OK!'|wc -l`
  if [ "${v_prepare}" == "1" ];then
    echo "`date '+%Y-%m-%d %H:%M:%S' ` [INFO]: prepare mysql completed OK! Please check prepare.log"
  else
    echo "`date '+%Y-%m-%d %H:%M:%S' ` [INFO]: prepare mysql failed! Please check prepare.log"
    exit 1
  fi
}

function startup_recover_mysql(){
  ###chown mysql
  echo "`date '+%Y-%m-%d %H:%M:%S' ` [INFO]: Chown mysql directory on $v_dir"
  touch ${v_dir}/logs/mysqld.log
  chown -R mysql:mysql ${v_dir}

  ###start mysql
  echo "`date '+%Y-%m-%d %H:%M:%S' ` [INFO]: Start mysql"
  mysqld_safe --defaults-file=${v_cnf} --user=mysql &
  if [ $? -ne 0 ]
  then
    echo "`date '+%Y-%m-%d %H:%M:%S' ` [INFO]: Start mysql failed! Please check ${v_dir}/logs/mysqld.log"
    exit 1
  else
    echo "`date '+%Y-%m-%d %H:%M:%S' ` [INFO]: Start mysql completed OK! Please check ${v_dir}/logs/mysqld.log"
  fi
}

innobackupex_fullrecovery;
startup_recover_mysql;

　　

【执行脚本】

直接后台运行执行脚本

nohup sh auto_recovery_xb.sh &

查看恢复日志信息

ls -trl
total 6504
-rwxr-xr-x 1 mysql mysql    5012 Dec 12 17:25 auto_recovery_xb.sh 自动恢复的脚本文件
-rw-r--r-- 1 mysql mysql 6635956 Dec 12 17:26 decompress.log      解压缩日志文件
-rw-r--r-- 1 mysql mysql    6409 Dec 12 17:26 prepare.log         prepare阶段日志文件
-rw------- 1 mysql mysql    1580 Dec 12 17:26 nohup.out           恢复日志的步骤信息
cat nohup.out
2023-12-12 17:25:18 [INFO]: Auto recovery mysql
2023-12-12 17:25:18 [INFO]: Recovery xbfile: /mysqlbackup/recovery/yiyuan/20XXXXX_23-00-01_fullbackup_XXXXX.xbstream
2023-12-12 17:25:18 [INFO]: Encrypt key: XXXXXrDFVx6UAsRb88uLVbAVWbK+Yzfs
2023-12-12 17:25:18 [INFO]: Recovery directory: /mysqlbackup/recovery/yiyuan/mysql
2023-12-12 17:25:18 [INFO]: Mysql config: /etc/my.13309.cnf
2023-12-12 17:25:18 [INFO]: Add mysql config: /etc/my.13309.cnf
2023-12-12 17:25:18 [INFO]: Add mysql config completed OK! Please check /etc/my.13309.cnf
2023-12-12 17:25:18 [INFO]: Clear directory: /mysqlbackup/recovery/yiyuan
2023-12-12 17:25:18 [INFO]: Create directory: /mysqlbackup/recovery/yiyuan/mysql
2023-12-12 17:25:18 [INFO]: Copy xbstream files
2023-12-12 17:25:40 [INFO]: Copy xbstream files completed OK! Please check directory /mysqlbackup/recovery/yiyuan/mysql files
2023-12-12 17:25:40 [INFO]: Decompress xb files
2023-12-12 17:26:28 [INFO]: Decompress xb files completed OK! Please check decompress.log
2023-12-12 17:26:28 [INFO]: prepare mysql
2023-12-12 17:26:48 [INFO]: prepare mysql completed OK! Please check prepare.log
2023-12-12 17:26:48 [INFO]: Chown mysql directory on /mysqlbackup/recovery/yiyuan
2023-12-12 17:26:48 [INFO]: Start mysql
2023-12-12 17:26:48 [INFO]: Start mysql completed OK! Please check /mysqlbackup/recovery/yiyuan/logs/mysqld.log
2023-12-12T09:26:49.442971Z mysqld_safe Logging to '/mysqlbackup/recovery/yiyuan/logs/mysqld.log'.
2023-12-12T09:26:49.475630Z mysqld_safe Starting mysqld daemon with databases from /mysqlbackup/recovery/yiyuan/mysql