使用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
