mysql備份腳本：
腳本實(shí)現(xiàn)：按照數(shù)據(jù)庫(kù)名稱(chēng)，全量備份mysql數(shù)據(jù)庫(kù)并定期刪除
#!/bin/bash
#全備方式，一般在從機(jī)上執(zhí)行，適用于小中型mysql數(shù)據(jù)庫(kù)
#刪除15天以前備份
#作者：lcm_linux
#時(shí)間：2019.08.06
source ~/.bash_profile #加載用戶環(huán)境變量
set -o nounset #引用未初始化變量時(shí)退出
set -o errexit #執(zhí)行shell命令遇到錯(cuò)誤時(shí)退出
#備份用戶---需要在mysql中提前創(chuàng)建并授權(quán)
#GRANT SELECT,RELOAD,LOCK TABLES,REPLICATION CLIENT,SHOW VIEW,TRIGGER,EVENT ON *.* TO 'backup'@'%' IDENTIFIED BY 'baifendian';
user="backup"
#備份用戶密碼
password="123456"
#mysql連接端口
port="3306"
mysql_path="/usr/local/mysql"
date=$(date +%Y-%m-%d_%H-%M-%S)
del_date=$(date +%Y-%m-%d)
#備份路徑---需要修改
backup_path="/opt/mysql_backup/new-bak/data"
backup_log_path="/opt/mysql_backup/new-bak/log"
day=0
backup_log="/opt/mysql_backup/new-bak/log/mysqldump_backup_${date}.log"
# 除了127.0.0.1以外的ip截取 /sbin/ifconfig -a|grep inet|grep -v 127.0.0.1|grep -v inet6|awk '{print $2}'|tr -d "addr:"
#刪除以前備份
find ${backup_path} -type f -mtime +$day -name "*.tar.gz" -exec rm -rf {} \; > /dev/null 2>1
for f in `ls $backup_path`;
do
if [[ $f = ${del_date}_* ]]; then
rm -rf $backup_path/$f
fi
done
#建立備份目錄
if [ ! -e $backup_path/${date} ];then
mkdir -p {$backup_path/${date},$backup_log_path}
fi
#echo "開(kāi)始備份所有數(shù)據(jù)庫(kù)"
echo "備份開(kāi)始,結(jié)果查看 $backup_log"
echo "==========All databases backups begin==========" >>$backup_log
#備份并壓縮
for dbname in $(mysql -P$port -u$user -p$password -A -e "show databases\G"|grep Database|grep -v schema|grep -v test|awk '{print $2}')
do
sleep 1
mysqldump -P$port -u$user -p$password $dbname > $backup_path/${date}/$dbname.sql
if [[ $? == 0 ]];then
cd $backup_path/${date}
size=$(du $backup_path/${date}/$dbname.sql -sh | awk '{print $1}')
echo "備份時(shí)間:${date} 備份方式:mysqldump 備份數(shù)據(jù)庫(kù):$dbname($size) 備份狀態(tài):成功！" >>$backup_log
else
cd $backup_path/${date}
echo "備份時(shí)間:${date} 備份方式:mysqldump 備份數(shù)據(jù)庫(kù):${dbname} 備份狀態(tài):失敗,請(qǐng)查看日志." >>$backup_log
fi
done
cd $backup_path
tar zcpvf mysql_all_databases_$date.tar.gz $date
rm -rf $backup_path/$date
du mysql_all_databases_$date.tar.gz -sh | awk '{print "文件:" $2 ",大小:" $1}'
echo "==========All databases backups over==========" >>$backup_log
配置定時(shí)任務(wù)：
crontab -e
內(nèi)容如下：
0 1 * * * /bin/bash /opt/script/mysql_shell/mysqlbackup.sh
附上恢復(fù)腳本A：
目標(biāo)實(shí)現(xiàn)：對(duì)于刪除數(shù)據(jù)庫(kù)，根據(jù)上述備份腳本產(chǎn)生的備份文件內(nèi)容，進(jìn)行恢復(fù)數(shù)據(jù)庫(kù)操作，包括創(chuàng)建數(shù)據(jù)庫(kù)名稱(chēng)
注意：恢復(fù)腳本需要手動(dòng)操作
#! /bin/bash
source /etc/profile #加載系統(tǒng)環(huán)境變量
source ~/.bash_profile #加載用戶環(huán)境變量
set -o nounset #引用未初始化變量時(shí)退出
set -o errexit #執(zhí)行shell命令遇到錯(cuò)誤時(shí)退出
date_tar=$(date +%Y-%m-%d_%H-%M-%S)
date=$(date +%Y-%m-%d)
local_path=${PWD}
user="root"
#備份用戶密碼
password="123456"
#mysql連接端口
port="3306"
DB_HOST=`/sbin/ifconfig -a|grep inet|grep -v 127.0.0.1|grep -v inet6|awk '{print $2}'|tr -d "addr:"`
mysql_path="/usr/local/mysql"
read -ep "請(qǐng)輸入數(shù)據(jù)庫(kù)備份文件的路徑: " choose_path
if [ ! -d ${choose_path} ];then
echo "請(qǐng)輸入正確路徑，已退出"
exit
else
echo "以下為檢測(cè)到的數(shù)據(jù)庫(kù)文件名稱(chēng)："
cd ${choose_path}
for file in $(ls ${choose_path})
do
[ -f ${file} ] echo ${file%.*} echo ${file%.*} >> ${local_path}/list.txt #先判斷是否是目錄，然后再輸出
done
fi
cat_list=`cat ${local_path}/list.txt`
function recovery(){
for dbname in ${cat_list};do
sleep 1
create_db="create database IF NOT EXISTS \`${dbname}\`"
mysql -h$DB_HOST -P$port -u$user -p$password -e "${create_db}"
mysql -h$DB_HOST -P$port -u$user -p$password -A $dbname ${choose_path}/$dbname.sql
if [[ $? == 0 ]];then
echo "恢復(fù)時(shí)間:${date} 恢復(fù)方式:mysqldump 恢復(fù)數(shù)據(jù)庫(kù):$dbname 恢復(fù)狀態(tài):成功！" >> $local_path/recovery_${date}.log
else
echo "恢復(fù)時(shí)間:${date} 恢復(fù)方式:mysqldump 恢復(fù)數(shù)據(jù)庫(kù):${dbname} 恢復(fù)狀態(tài):失敗,請(qǐng)查看日志." >> $local_path/recovery_${date}.log
fi
done
}
read -ep "全部恢復(fù)輸入(A/a)，單個(gè)恢復(fù)輸入(B/b):" choose
if [ $choose = 'A' -o $choose = 'a' ];then
recovery
elif [ $choose = 'B' -o $choose = 'b' ];then
read -ep "正在單個(gè)恢復(fù)數(shù)據(jù)庫(kù)，請(qǐng)輸入想要恢復(fù)的數(shù)據(jù)庫(kù)名稱(chēng):" mydatabase
echo "正在恢復(fù)$mydatabase 請(qǐng)稍后....."
create_db="create database IF NOT EXISTS \`${mydatabase}\`"
mysql -h$DB_HOST -P$port -u$user -p$password -e "${create_db}"
mysql -h$DB_HOST -P$port -u$user -p$password -A $mydatabase $choose_path/$mydatabase.sql
echo "$mydatabase 已成功恢復(fù)，恭喜！"
exit
else
echo "輸入不符合規(guī)則，請(qǐng)重新執(zhí)行..."
exit
fi
附上恢復(fù)腳本B：
腳本實(shí)現(xiàn)：在未刪除數(shù)據(jù)庫(kù)的情況下，對(duì)mysql數(shù)據(jù)庫(kù)進(jìn)行數(shù)據(jù)恢復(fù)，可以選擇全部恢復(fù)或者逐個(gè)恢復(fù)。
注意：恢復(fù)腳本需要手動(dòng)操作
#! /bin/bash
source /etc/profile #加載系統(tǒng)環(huán)境變量
source ~/.bash_profile #加載用戶環(huán)境變量
set -o nounset #引用未初始化變量時(shí)退出
set -o errexit #執(zhí)行shell命令遇到錯(cuò)誤時(shí)退出
user="root"
#備份用戶密碼
password="123456"
#mysql連接端口
port="3306"
DB_HOST=`/sbin/ifconfig -a|grep inet|grep -v 127.0.0.1|grep -v inet6|awk '{print $2}'|tr -d "addr:"`
mysql_path="/usr/local/mysql"
date=$(date +%Y-%m-%d_%H-%M-%S)
#備份路徑---需要修改
#recovery_path="/opt/mysql_backup/new-bak"
#recovery_log="${recovery_path}/mysqldump_recovery_${date}.log"
mysql_list=`mysql -h$DB_HOST -P$port -u$user -p$password -A -e "show databases\G"|grep Database|grep -v schema|grep -v test|awk '{print $2}'`
echo "數(shù)據(jù)庫(kù)名稱(chēng)列表如下:"
echo "${mysql_list}"
read -p "全部恢復(fù)輸入(A/a)，單個(gè)恢復(fù)輸入(B/b):" choose
#echo $choose
# if [ $choose = 'A' -o $choose = 'a' -o $choose != 'B' -o $choose != 'b' ];then
if [ $choose = 'A' -o $choose = 'a' ];then
read -p "正在準(zhǔn)備恢復(fù)全部數(shù)據(jù)庫(kù)，請(qǐng)輸入恢復(fù)數(shù)據(jù)文件所在路徑:" allpath
if [ ! -d $allpath ];then
echo "此路徑不存在，請(qǐng)重新執(zhí)行..."
exit
fi
for dbname in ${mysql_list};do
sleep 1
create_db="create database IF NOT EXISTS ${dbname}"
mysql -h$DB_HOST -P$port -u$user -p$password -e "${create_db}"
mysql -h$DB_HOST -P$port -u$user -p$password -A $dbname $allpath/$dbname.sql
if [[ $? == 0 ]];then
cd $allpath
size=$(du $allpath/$dbname.sql -sh | awk '{print $1}')
echo "恢復(fù)時(shí)間:${date} 恢復(fù)方式:mysql 恢復(fù)數(shù)據(jù)庫(kù):$dbname($size) 恢復(fù)狀態(tài):成功！" >> $allpath/recovery_${date}.log
else
cd $allpath
echo "恢復(fù)時(shí)間:${date} 恢復(fù)方式:mysqldump 恢復(fù)數(shù)據(jù)庫(kù):${dbname} 恢復(fù)狀態(tài):失敗,請(qǐng)查看日志." >> $allpath/recovery_${date}.log
fi
done
elif [ $choose = 'B' -o $choose = 'b' ];then
read -p -e "正在單個(gè)恢復(fù)數(shù)據(jù)庫(kù)，請(qǐng)輸入想要恢復(fù)的數(shù)據(jù)庫(kù)名稱(chēng):" mydatabase
read -p -e "請(qǐng)輸入數(shù)據(jù)庫(kù)恢復(fù)的路徑:" mypath
if [ ! -d $mypath ];then
echo "此路徑不存在，請(qǐng)重新執(zhí)行..."
exit
elif [ ! -f $mypath/$mydatabase.sql ];then
echo "此路徑下沒(méi)有目標(biāo)數(shù)據(jù)庫(kù)文件,請(qǐng)重新執(zhí)行..."
exit
elif [ $mydatabase != $mysql_list ];then
echo "$mydatabase 不在此數(shù)據(jù)庫(kù)中，請(qǐng)確認(rèn)數(shù)據(jù)庫(kù)名稱(chēng)后再試，再見(jiàn)！"
else
echo "正在恢復(fù)$mydatabase 請(qǐng)稍后....."
create_db="create database IF NOT EXISTS ${dbname}"
mysql -h$DB_HOST -P$port -u$user -p$password -e "${create_db}"
mysql -h$DB_HOST -P$port -u$user -p$password -A $mydatabase $mypath/$mydatabase.sql
echo "$mydatabase 已成功恢復(fù)，恭喜！"
exit
fi
# fi
# fi
# fi
else
# if [ $choose != 'B' -o $choose != 'b' -o $choose != 'A' -o $choose != 'a' ];then
echo "輸入不符合規(guī)則，請(qǐng)重新執(zhí)行..."
exit
fi
總結(jié)
以上所述是小編給大家介紹的 shell腳本實(shí)現(xiàn)mysql定時(shí)備份、刪除、恢復(fù) ，希望對(duì)大家有所幫助，如果大家有任何疑問(wèn)請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持！
如果你覺(jué)得本文對(duì)你有幫助，歡迎轉(zhuǎn)載，煩請(qǐng)注明出處，謝謝！
標(biāo)簽：武威 紹興 蚌埠 麗江 迪慶 西寧 安康 日喀則
巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《shell腳本實(shí)現(xiàn)mysql定時(shí)備份、刪除、恢復(fù)功能》，本文關(guān)鍵詞 shell,腳本,實(shí)現(xiàn),mysql,定時(shí),；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。
