#!/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
fi
}
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
fi
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
fi
fi
sleep 2
}
get_xtra(){#下载xtrabackup源码包
delimiter | tee -a $backup_log
echoinfo ${messages[0]} | tee -a $backup_log
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
fi
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
fi
fi
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")"
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_dir2$test_zl &> $dev_dir
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
}
zl_appl(){#将增量备份应用到完全备份
delimiter | tee -a $backup_log
echoinfo ${messages[7]} | tee -a $backup_log
num1="$[$(ls $backup_dir2 | wc -l) -1]"
num2=1
while [ $num2 -lt $num1 ]
do
if [ $num2 -gt $num1 ];then
zlname="$(find $backup_dir2 -maxdepth 1 -ctime -7 -type d | ls -lrt | grep -v "总用量" | awk '{print $9}' | sed -n "${num2}p")"
fi
num2=$((num2+1))
done
innobackupex --apply-log --redo-only $backup_dir1$full_file --incremental-dir=$backup_dir2$zlname &> $dev_dir
zengl_dir="$(find $backup_dir2 -maxdepth 1 -ctime -7 -type d | ls -lt | grep -v "总用量" | awk '{print $9}' | sed -n "1p")"
innobackupex --apply-log $backup_dir1$full_file --incremental-dir=$backup_dir2$zengl_dir
sleep 2
if_test ${messages[7]} | tee -a $backup_log
mysql -u $users -p$psw -e "select * from $name.test1;" &>> $backup_log
}
huifu(){#数据恢复
delimiter | tee -a $backup_log
echoinfo ${messages[8]} | tee -a $backup_log
systemctl stop mysqld
mv -f $data_dir/ /tmp/
sleep 2
mkdir $data_dir
innobackupex --copy-back $backup_dir1$full_file &> $dev_dir
sleep 2
if_test ${messages[8]} | tee -a $backup_log
chown -R mysql:mysql $data_dir
mysql -u $users -p$psw -e "select * from $name.test1;" &>> $backup_log
}
#######################################################
checkall_sql
wait
check_dir1
wait
check_dir2
wait
get_xtra
wait
instal
wait
check_install
wait
allfullback
wait
xtrfulback
wait
zl_backup
wait
full_pre
wait
zl_appl
wait
huifu
wait
#######################################################