Mysql数据库mys和ora库的备份与恢复脚本

#!/bin/bash
Time=$(date +%Y%m%d%H%M%S)
Back_dir="$HOME/mysqlback/${Time}"
function Detect_u_p {
read -p "输入数据库用户名和密码,用空格分隔:" user passwd
mysql -u${user} -p${passwd} -e "show databases" &>/dev/null
[ $? -eq 0 ] && return 0 || return 1
}
function backup {
Detect_u_p
if [ $? -eq 0 ];then
Back_db=($(mysql -u${user} -p${passwd} -e "show databases" 2>/dev/null | awk '/^mys|ora/ {print $1}'))
[ ! -d ${Back_dir} ] && mkdir -p ${Back_dir}
echo "${Time}开始备份"
for back_db in ${Back_db[]}
do
mysqldump -u${user} -p${passwd} --opt -R ${back_db} > ${Back_dir}/${back_db}.sql 2>/dev/null && echo "${back_db}备份完成"
done
mysqldump -u${user} -p${passwd} --opt -R --all-databases --routines > ${Back_dir}/alldb_back.sql 2>/dev/null && echo "整库备份完成"
else
echo "用户名或密码输入错误"
fi
}
function imp_db {
Del_db=($(mysql -u${user} -p${passwd} -e "show databases" 2>/dev/null | awk '/^mys|ora/ {print $1}'))
for del_db in ${Del_db[]}
do
mysql -u${user} -p${passwd} -e "DROP DATABASE ${del_db}" &>/dev/null
done
Rec_db=($(ls -l $1 | egrep -v "总用量|alldb" | awk '{print $9}' | awk -F"." '{print $1}'))
for rec_db in ${Rec_db[]}
do
mysql -u${user} -p${passwd} -e "CREATE DATABASE ${rec_db}" &>/dev/null && mysql -u${user} -p${passwd} ${rec_db} < $1/${rec_db}.sql &>/dev/null && echo "恢复${rec_db}成功"
done
}
function recover {
Rec_dir=$(find $HOME -type d -name mysqlback)
if [ ! -d ${Rec_dir} ];then
echo "未找到脚本备份的目录"
else
ls -lt ${Rec_dir} | egrep -v "总用量" | awk -v Dir="${Rec_dir}" 'BEGIN {print "备份路径为:"} $9 ~ /[0-9]+/ {print NR"."Dir"/"$9}'
Dir_arr=($(ls -lt ${Rec_dir} | awk -v Dir="${Rec_dir}" '$9 ~ /[0-9]+/ {print Dir"/"$9}'))
read -p "选择目录为${Dir_arr[0]}中的文件来恢复数据库?(y or n):" yn
if [ "$yn" = "y" -o "$yn" = "Y" -o "$yn" = "yes" ];then
Detect_u_p
[ $? -eq 0 ] && imp_db ${Dir_arr[0]} || echo "用户名或密码输入错误"
else
read -p "用其他备份目录来恢复数据库,请输入对应序号:" digit
if [ ${digit} -gt 0 -a ${digit} -le ${#Dir_arr[]} ];then
digit=$((${digit}-1))
Detect_u_p
imp_db ${Dir_arr[${digit}]}
else
echo "输入错误"
fi
fi
fi
}
while [ 1 ]
do
echo -e "1.备份数据库!\n2.恢复数据库!"
read -p "请输入要实现功能的序号:" choice
echo ""
case ${choice} in
1) backup;;
2) recover;;
*) echo "Byebye!"
exit 1
esac
echo ""
read -p "Continue?(y or n):" yn
[ "$yn" = "y" -o "$yn" = "Y" ] && continue || break
done