XtraBackup数据库还原脚本

August 15, 2019Comments

$ cat restore.sh
#!/bin/bash
# Author:Byrd
# Version:0.1
# Site:note.t4x.org
# Contact:root#t4x.org
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
export PATH
backup_dir="/backup/<span class='keyword-be keyword-inner'><a href="https://note.t4x.org/database/" title="database,mysql" target="_blank">MySQL</a></span>"
restore_dir="$2"
mysql_cnf="/etc/my.cnf"
mysql_user="root"
mysql_password="admin"
mysql_sock="/usr/local/mysql/tmp/mysql.sock"
backup_week_year=$(date +%U)
backup_day_week=$(date +%u)
backup_application="/usr/local/bin/innobackupex"
return_value=0
restore_base_dir=`ls -l {restore_dir} | awk '{print $9}' | grep -v "^$" | head -1`
restore_base_date=`ls -l ${restore_dir} | awk '{print $9}' | grep -v "^$" | awk -F '_' '{print $1}' | head -1`
restore_sn_number=`ls -l ${restore_dir} | awk '{print $9}' | grep -v "^$" | awk -F '_' '{print $2}' | head -1`
full_apply_log(){
if [ ${restore_sn_number} -eq 0 ]; then
${backup_application} --defaults-file={mysql_cnf} --user ${mysql_user} --password ${mysql_password} --socket=${mysql_sock} --apply-log --redo-only ${restore_dir}/${restore_base_dir} >/dev/null 2>&1
if [ `echo $?` -ne ${return_value} ];then
echo "Recovery Error" > /tmp/MySQL_Innodb_backupex_recovery.txt
fi
else
echo ""
echo -e "\033[31m = = = = 命令执行失败. = = = = \033[0m"
echo ""
exit 2
fi
}
full_copy_back(){
if [ {restore_sn_number} -eq 0 ]; then
${backup_application} --defaults-file={mysql_cnf} --user ${mysql_user} --password ${mysql_password} --socket=${mysql_sock} --copy-back ${restore_dir}/${restore_base_dir} >/dev/null 2>&1
if [ `echo $?` -ne ${return_value} ];then
echo "Recovery Error" > /tmp/MySQL_Innodb_backupex_recovery.txt
fi
else
echo ""
echo -e "\033[31m = = = = 命令执行失败. = = = = \033[0m"
echo ""
exit 2
fi
}
incremental_apply_logreccovery(){
if [ {restore_sn_number} -eq 0 ]; then
${backup_application} --defaults-file={mysql_cnf} --user ${mysql_user} --password ${mysql_password} --socket=${mysql_sock} --apply-log --redo-only ${restore_dir}/${restore_base_dir} >/dev/null 2>&1
if [ `echo $?` -ne ${return_value} ];then
echo "Recovery Error" > /tmp/MySQL_Innodb_backupex_recovery.txt
fi
count=`ls {restore_dir} | wc -l`
for i in `seq $((${count} - 1))`
do
${backup_application} --defaults-file=${mysql_cnf} --user ${mysql_user} --password ${mysql_password} --socket=${mysql_sock} --apply-log --redo-only ${restore_dir}/${restore_base_dir} --incremental-dir=${restore_dir}/$(date -d "-$i day ago ${restore_base_date}" +%F)_$i >/dev/null 2>&1
if [ `echo $?` -ne ${return_value} ];then
echo "Recovery Error" > /tmp/MySQL_Innodb_backupex_recovery.txt
fi
done
else
echo ""
echo -e "\033[31m = = = = 命令执行失败. = = = = \033[0m"
echo ""
exit 2
fi
}

$ cat restore.sh

#!/bin/bash

# Author:Byrd

# Version:0.1

# Site:note.t4x.org

# Contact:root#t4x.org

PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin

export PATH

backup_dir="/backup/mysql"

restore_dir="$2"

mysql_cnf="/etc/my.cnf"

mysql_user="root"

mysql_password="admin"

mysql_sock="/usr/local/mysql/tmp/mysql.sock"

backup_week_year=$(date +%U)

backup_day_week=$(date +%u)

backup_application="/usr/local/bin/innobackupex"

return_value=0

restore_base_dir=`ls -l {restore_dir} | awk '{print $9}' | grep -v "^$" | head -1`

restore_base_date=`ls -l ${restore_dir} | awk '{print $9}' | grep -v "^$" | awk -F '_' '{print $1}' | head -1`

restore_sn_number=`ls -l ${restore_dir} | awk '{print $9}' | grep -v "^$" | awk -F '_' '{print $2}' | head -1`

full_apply_log(){

if [ ${restore_sn_number} -eq 0 ]; then

${backup_application} --defaults-file={mysql_cnf} --user ${mysql_user} --password ${mysql_password} --socket=${mysql_sock} --apply-log --redo-only ${restore_dir}/${restore_base_dir} >/dev/null 2>&1

if [ `echo $?` -ne ${return_value} ];then

echo "Recovery Error" > /tmp/MySQL_Innodb_backupex_recovery.txt

else

echo ""

echo -e "\033[31m = = = = 命令执行失败. = = = = \033[0m"

echo ""

exit 2

}

full_copy_back(){

if [ {restore_sn_number} -eq 0 ]; then

${backup_application} --defaults-file={mysql_cnf} --user ${mysql_user} --password ${mysql_password} --socket=${mysql_sock} --copy-back ${restore_dir}/${restore_base_dir} >/dev/null 2>&1

if [ `echo $?` -ne ${return_value} ];then

echo "Recovery Error" > /tmp/MySQL_Innodb_backupex_recovery.txt

else

echo ""

echo -e "\033[31m = = = = 命令执行失败. = = = = \033[0m"

echo ""

exit 2

}

incremental_apply_logreccovery(){

if [ {restore_sn_number} -eq 0 ]; then

${backup_application} --defaults-file={mysql_cnf} --user ${mysql_user} --password ${mysql_password} --socket=${mysql_sock} --apply-log --redo-only ${restore_dir}/${restore_base_dir} >/dev/null 2>&1

if [ `echo $?` -ne ${return_value} ];then

echo "Recovery Error" > /tmp/MySQL_Innodb_backupex_recovery.txt

count=`ls {restore_dir} | wc -l`

for i in `seq $((${count} - 1))`

${backup_application} --defaults-file=${mysql_cnf} --user ${mysql_user} --password ${mysql_password} --socket=${mysql_sock} --apply-log --redo-only ${restore_dir}/${restore_base_dir} --incremental-dir=${restore_dir}/$(date -d "-$i day ago ${restore_base_date}" +%F)_$i >/dev/null 2>&1

if [ `echo $?` -ne ${return_value} ];then

echo "Recovery Error" > /tmp/MySQL_Innodb_backupex_recovery.txt

done

else

echo ""

echo -e "\033[31m = = = = 命令执行失败. = = = = \033[0m"

echo ""

exit 2

}

incremental_copy_back_reccovery(){
if [ {restore_sn_number} -eq 0 ]; then
${backup_application} --defaults-file={mysql_cnf} --user ${mysql_user} --password ${mysql_password} --socket=${mysql_sock} --copy-back ${restore_dir}/${restore_base_dir} >/dev/null 2>&1
if [ `echo $?` -ne ${return_value} ];then
echo "Recovery Error" > /tmp/MySQL_Innodb_backupex_recovery.txt
fi
fi
}

incremental_copy_back_reccovery(){

if [ {restore_sn_number} -eq 0 ]; then

${backup_application} --defaults-file={mysql_cnf} --user ${mysql_user} --password ${mysql_password} --socket=${mysql_sock} --copy-back ${restore_dir}/${restore_base_dir} >/dev/null 2>&1

if [ `echo $?` -ne ${return_value} ];then

echo "Recovery Error" > /tmp/MySQL_Innodb_backupex_recovery.txt

}

文章源自 note.t4x.orgByrd's Blog-https://note.t4x.org/code/xtrabackup-mysql-recovery/

case $1 in
FullApplyLog)
if [ $# != 2 ] ; then
echo "USAGE: $0 FullApplyLog RestoreDir"
echo " e.g.: $0 FullApplyLog /backup/mysql/18"
exit 1;
fi
full_apply_log
echo -e "\033[32m= = = = 应用备份日志成功,请关闭MySQL后执行FullCopyBack操作 = = = =\033[0m"
echo -e "\033[32m= = = = 关闭MySQL后,请将数据库目录改名,请新建一个数据库目录 = = = =\033[0m"
;;
FullCopyBack)
if [ $# != 2 ] ; then
echo "USAGE: $0 FullCopyBack RestoreDir"
echo " e.g.: $0 FullCopyBack /backup/mysql/18"
exit 1;
fi
full_copy_back
echo -e "\033[32m= = = = 数据还原成功,请修改MySQL数据库目录权限 = = = =\033[0m"
;;
IncrementalApplyLogReccovery)
if [ $# != 2 ] ; then
echo "USAGE: $0 IncrementalApplyLogReccovery RestoreDir"
echo " e.g.: $0 IncrementalApplyLogReccovery /backup/mysql/18"
exit 1;
fi
incremental_apply_logreccovery
echo -e "\033[32m= = = = 应用备份日志成功,请关闭MySQL后执行IncrementalCopyBackReccovery操作 = = = =\033[0m"
echo -e "\033[32m= = = = 关闭MySQL后,请将数据库目录改名,请新建一个数据库目录 = = = =\033[0m"
;;
IncrementalCopyBackReccovery)
if [ $# != 2 ] ; then
echo "USAGE: $0 IncrementalCopyBackReccovery RestoreDir"
echo " e.g.: $0 IncrementalCopyBackReccovery /backup/mysql/18"
exit 1;
fi
incremental_copy_back_reccovery
echo -e "\033[32m= = = = 数据还原成功,请修改MySQL数据库目录权限 = = = =\033[0m"
;;
*)
echo "Usage:$0(FullApplyLog|FullCopyBack|IncrementalApplyLogReccovery|IncrementalCopyBackReccovery)"
;;
esac

case $1 in

FullApplyLog)

if [ $# != 2 ] ; then

echo "USAGE: $0 FullApplyLog RestoreDir"

echo " e.g.: $0 FullApplyLog /backup/mysql/18"

exit 1;

full_apply_log

echo -e "\033[32m= = = = 应用备份日志成功,请关闭MySQL后执行FullCopyBack操作 = = = =\033[0m"

echo -e "\033[32m= = = = 关闭MySQL后,请将数据库目录改名,请新建一个数据库目录 = = = =\033[0m"

;;

FullCopyBack)

if [ $# != 2 ] ; then

echo "USAGE: $0 FullCopyBack RestoreDir"

echo " e.g.: $0 FullCopyBack /backup/mysql/18"

exit 1;

full_copy_back

echo -e "\033[32m= = = = 数据还原成功,请修改MySQL数据库目录权限 = = = =\033[0m"

;;

IncrementalApplyLogReccovery)

if [ $# != 2 ] ; then

echo "USAGE: $0 IncrementalApplyLogReccovery RestoreDir"

echo " e.g.: $0 IncrementalApplyLogReccovery /backup/mysql/18"

exit 1;

incremental_apply_logreccovery

echo -e "\033[32m= = = = 应用备份日志成功,请关闭MySQL后执行IncrementalCopyBackReccovery操作 = = = =\033[0m"

echo -e "\033[32m= = = = 关闭MySQL后,请将数据库目录改名,请新建一个数据库目录 = = = =\033[0m"

;;

IncrementalCopyBackReccovery)

if [ $# != 2 ] ; then

echo "USAGE: $0 IncrementalCopyBackReccovery RestoreDir"

echo " e.g.: $0 IncrementalCopyBackReccovery /backup/mysql/18"

exit 1;

incremental_copy_back_reccovery

echo -e "\033[32m= = = = 数据还原成功,请修改MySQL数据库目录权限 = = = =\033[0m"

;;

echo "Usage:$0(FullApplyLog|FullCopyBack|IncrementalApplyLogReccovery|IncrementalCopyBackReccovery)"

;;

esac

文章源自 note.t4x.orgByrd's Blog-https://note.t4x.org/code/xtrabackup-mysql-recovery/ 文章源自 note.t4x.orgByrd's Blog-https://note.t4x.org/code/xtrabackup-mysql-recovery/

申明：除非注明Byrd's Blog内容均为原创,未经许可禁止转载!详情请阅读版权申明!

On this day in past years

August

2014速查笔记(Linux Shell编程<上>)

Hot search

On this day in past years

Comment