MySQL数据库使用xtrabackup进行数据备份和恢复

最新推荐文章于 2024-08-06 09:30:24 发布

XBLYL1992

最新推荐文章于 2024-08-06 09:30:24 发布

阅读量405

点赞数 1

分类专栏： centos7 shell脚本 mysql 文章标签： linux 服务器 centos 运维 mysql Powered by 金山文档

本文链接：https://blog.csdn.net/xblyl1992/article/details/129058843

版权

centos7 同时被 3 个专栏收录

15 篇文章 0 订阅

订阅专栏

shell脚本

12 篇文章 0 订阅

订阅专栏

mysql

4 篇文章 0 订阅

订阅专栏

#!/bin/bash

#Author Mr.Wu

#使用xtrabackup工具备份和恢复MySQL数据

#2023年2月14日09:36:44

#######################################################

read -p "请输入账户名：" users

read -p "请输入账户密码：" psw

read -p "请输入您要备份的数据库名称：" name

backup_dir1="/opt/mysql_xtrabackup/"

backup_dir2="/opt/mysql_xtrabackup/add/"

backup_log="$(pwd)/mysql_xbackup.log"

data_dir="/data/mysql/data"

dev_dir="/dev/null"

rpm_xtra="percona-xtrabackup-24-2.4.14-1.el7.x86_64.rpm"

all_sql="/opt/mysql_backup/all.sql"

sockt="/usr/local/mysql/mysql.sock"

colour=(31 34)

result=("失败！！！" "成功！！！")

messages=("下载xtrabackup源码包" "安装xtrabackup" "检查并安装xtrabackup" "全库完全备份" "指定库完全备份" "判断增量备份目录是否存在" "备份准备" "将增量备份应用到完全备份" "数据恢复")

#######################################################

delimiter(){#定义分界符

echo -e "\033[32;1m＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊\033[0m"

}

echoinfo(){#定义输入信息

echo -e "\033[35;1m#$1\033[0m"

}

echoresult(){#定义输出信息

echo -e "\033[$1;1m$2$3\033[0m"

}

if_test(){#定义if判断语句

if [ $? -eq 0 ];then

echoresult ${colour[1]} $1 ${result[1]}

else

echoresult ${colour[0]} $1 ${result[0]}

exit

}

checkall_sql(){#检查是否存在全库备份文件

if [ ! -f $all_sql ];then

echo "$all_sql不存在，无需操作" &>> $backup_log

else

mv -f $all_sql /opt/all.sql.bak`date "+%Y%m%d%H%M%S"` &> $dev_dir

sleep 2

}

check_dir1(){#判断完全备份目录是否存在

if [ ! -d $backup_dir1 ];then

mkdir $backup_dir1

elif [ -d $backup_dir1 ];then

echo "$backup_dir1目录已存在，请判断是否为空！！！" &>> $backup_log

if [ ! -z "$backup_dir1" ];then

tar -zcvf /opt/mysqlfullxtr_`date "+%Y%m%d%H%M%S"`.tar.gz $backup_dir1 --remove-files &> $dev_dir

elif [ -z "$backup_dir1" ];then

echo "$backup_dir1目录为空,可以使用" &>> $backup_log

sleep 2

}

get_xtra(){#下载xtrabackup源码包

delimiter | tee -a $backup_log

echoinfo ${messages[0]} | tee -a $backup_log

wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.14/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.14-1.el7.x86_64.rpm &> $dev_dir

sleep 2

if_test ${messages[0]} | tee -a $backup_log

}

instal(){#安装xtrabackup

delimiter | tee -a $backup_log

echoinfo ${messages[1]} | tee -a $backup_log

yum install -y $rpm_xtra &>> $backup_log

sleep 2

if_test ${messages[1]} | tee -a $backup_log

}

check_install(){#检查并安装xtrabacku

delimiter | tee -a $backup_log

echoinfo ${messages[2]} | tee -a $backup_log

if [ ! -f $rpm_xtra ];then

get_xtra | tee -a $backup_log

else

instal | tee -a $backup_log

sleep 2

if_test ${messages[2]} | tee -a $backup_log

}

allfullback(){#全库完全备份

delimiter | tee -a $backup_log

echoinfo ${messages[3]} | tee -a $backup_log

innobackupex --dafaults-file=/etc/my.cnf --socket=$sockt --user=$users --password=$psw --port=3306 --no-timestamp $backup_dir1full-`date "+%Y%m%d%H%M%S"` &> $dev_dir

sleep 2

if_test ${messages[3]} | tee -a $backup_log

}

xtrfulback(){#指定库完全备份

delimiter | tee -a $backup_log

echoinfo ${messages[4]} | tee -a $backup_log

innobackupex --dafaults-file=/etc/my.cnf --socket=$sockt --user=$users --password=$psw --database=$name --port=3306 --no-timestamp $backup_dir1$name-`date "+%Y%m%d%H%M%S"` &> $dev_dir

ls -l $backup_dir1 &>> $backup_log

sleep 2

if_test ${messages[4]} | tee -a $backup_log

}

check_dir2(){#判断增量备份目录是否存在

if [ ! -d $backup_dir2 ];then

mkdir $backup_dir2

elif [ -d $backup_dir2 ];then

if [ ! -z "$backup_dir2" ];then

tar -zcvf /opt/mysqlxtradd_`date "+%Y%m%d%H%M%S"`.tar.gz $backup_dir2 --remove-files &> $dev_dir

elif [ -z "$backup_dir2" ];then

echo "$backup_dir2目录为空,可以使用" &>> $backup_log

sleep 2

}

full_file="$(ls -lt $backup_dir1 | grep -v "总用量" | awk '{print $9}' | grep -E "^full" | sed -n "1p")"

zl_backup(){

#指定库增量备份

delimiter | tee -a $backup_log

echoinfo ${messages[5]} | tee -a $backup_log

#第一次增量

mysql -u $users -p$psw -e "insert into $name.test1(id,name) values(100,"Tom"),(110,"Jerry"),(111,"Marry");" &> $dev_dir

mysql -u $users -p$psw -e "update $name.test1 set id=888 where name="Marry";" &> $dev_dir

sockt="$(find / -name "mysql.sock")"

innobackupex --dafaults-file=/etc/my.cnf --socket=$sockt --user=$users --password=$psw --databases=$name --port=3306 --no-timestamp --incremental $backup_dir2$name-`date "+%Y%m%d%H%M%S"` --incremental-basedir=$backup_dir1$full_file/ &> $dev_dir

#第二次增量

mysql -u $users -p$psw -e "insert into $name.test1(id,name) values(114,"Tpf"),(115,"Mll"),(116,"Lss");" &> $dev_dir

test_zl="$(ls -lt $backup_dir2 | grep -v "总用量" | awk '{print $9}' | sed -n "1p")"

sleep 2

if_test ${messages[5]} | tee -a $backup_log

}

full_pre(){#准备一个完全备份

delimiter | tee -a $backup_log

echoinfo ${messages[6]} | tee -a $backup_log

innobackupex --apply-log --redo-only $backup_dir1$full_file &> $dev_dir

sleep 2

if_test ${messages[6]} | tee -a $backup_log