mydumper備份數據庫腳本
#!/bin/bash#ocpyang@126.com ########腳本說明 #1.默認讀取當前目錄下database.txt文件,備份部分數據庫 #2.如果database.txt為空,則執行全備或備份指定的個別數據庫 #set parameter mydumper=/usr/local/bin/mydumper #根據自己的實際情況設置 dir_backup=/backup #根據自己的實際情況設置 mysql_host=localhost #根據自己的實際情況設置 mysql_port=33306 #根據自己的實際情況設置 mysql_user=root #根據自己的實際情況設置 mysql_pass=123456 #根據自己的實際情況設置 now_date=`date +%Y%m%d%H%M` if [ ! -d $dir_backup ]; then echo -e "\e[1;31m 保存備份的主目錄:$dir_backup不存在,將自動新建. \e[0m" mkdir -p ${dir_backup} fi export black='\033[0m' export boldblack='\033[1;0m' export red='\033[31m' export boldred='\033[1;31m' export green='\033[32m' export boldgreen='\033[1;32m' export yellow='\033[33m' export boldyellow='\033[1;33m' export blue='\033[34m' export boldblue='\033[1;34m' export magenta='\033[35m' export boldmagenta='\033[1;35m' export cyan='\033[36m' export boldcyan='\033[1;36m' export white='\033[37m' export boldwhite='\033[1;37m' cecho () ## -- Function to easliy print colored text -- ## # Color-echo. # 參數 $1 = message # 參數 $2 = color { local default_msg="No message passed." message=${1:-$default_msg} # 如果$1沒有輸入則為默認值default_msg. color=${2:-black} # 如果$1沒有輸入則為默認值black. case $color in black) printf "$black" ;; boldblack) printf "$boldblack" ;; red) printf "$red" ;; boldred) printf "$boldred" ;; green) printf "$green" ;; boldgreen) printf "$boldgreen" ;; yellow) printf "$yellow" ;; boldyellow) printf "$boldyellow" ;; blue) printf "$blue" ;; boldblue) printf "$boldblue" ;; magenta) printf "$magenta" ;; boldmagenta) printf "$boldmagenta" ;; cyan) printf "$cyan" ;; boldcyan) printf "$boldcyan" ;; white) printf "$white" ;; boldwhite) printf "$boldwhite" ;; esac printf "%s\n" "$message" tput sgr0 # tput sgr0即恢復默認值 printf "$black" return } cechon () # Color-echo. # 參數1 $1 = message # 參數2 $2 = color { local default_msg="No message passed." # Doesn't really need to be a local variable. message=${1:-$default_msg} # 如果$1沒有輸入則為默認值default_msg. color=${2:-black} # 如果$1沒有輸入則為默認值black. case $color in black) printf "$black" ;; boldblack) printf "$boldblack" ;; red) printf "$red" ;; boldred) printf "$boldred" ;; green) printf "$green" ;; boldgreen) printf "$boldgreen" ;; yellow) printf "$yellow" ;; boldyellow) printf "$boldyellow" ;; blue) printf "$blue" ;; boldblue) printf "$boldblue" ;; magenta) printf "$magenta" ;; boldmagenta) printf "$boldmagenta" ;; cyan) printf "$cyan" ;; boldcyan) printf "$boldcyan" ;; white) printf "$white" ;; boldwhite) printf "$boldwhite" ;; esac printf "%s" "$message" tput sgr0 # tput sgr0即恢復默認值 printf "$black" return } judegedate_01="judegedate01.`date +%y%m%d%h%m%s`.txt" schema_judege01="select schema_name from information_schema.schemata ;" mysql -h${mysql_host} -P${mysql_port} -u${mysql_user} -p${mysql_pass} -e"${schema_judege01}" >${judegedate_01} echo -e "\e[1;31m The databases name in current instance is: \e[0m" awk 'NR==2,NR==0 { print $1}' ${judegedate_01} echo " " #Get the current script path and create a file named database.txt #in order to save the backup databases name. filepath=$(cd "$(dirname "$0")"; pwd) if [ ! -s "${filepath}/database.txt" ];then echo "將在當前目錄下新建databases.txt." touch ${filepath}/database.txt echo "#Each line is stored a valid database name">${filepath}/database.txt chmod 700 ${filepath}/database.txt fi #Remove the comment line awk 'NR==2,NR==0 { print $1}' ${filepath}/database.txt> ${filepath}/tmpdatabases.txt #To determine whether a file is empty if [ -s ${filepath}/tmpdatabases.txt ];then #開始時間 started_time=`date +%s` echo "備份開始時間:${started_time}" db_num00=`awk 'NR==1,NR==0 { print NR}' ${filepath}/tmpdatabases.txt |tail -n1` echo "此次將備份${db_num00}個數據庫:" echo or_dbnum=0 for i in `awk 'NR==1,NR==0 { print $1}' ${filepath}/tmpdatabases.txt`; do ((or_dbnum+=1)) mysql_databases=$i db_dpname=$dir_backup/${i}.${now_date} echo -e "\e[1;32m mydumper開始備份第${or_dbnum}個數據庫$i..... \e[0m" sleep 2 echo -e "\e[1;32m mydumper玩命備份中.....稍等片刻.... \e[0m" ${mydumper} \ --database=${mysql_databases} \ --host=${mysql_host} \ --port=${mysql_port} \ --user=${mysql_user} \ --password=${mysql_pass} \ --outputdir=${db_dpname} \ --no-schemas \ --rows=50000 \ --build-empty-files \ --threads=4 \ --compress-protocol \ --kill-long-queries if [ "$?" -eq 0 ];then echo -e "\e[1;32m mydumper成功將數據庫$i備份到:${db_dpname}. \e[0m" echo else echo -e "\e[1;31m 備份異常結束. \e[0m" fi done else ipname='' read -p "Please input you want to backup database name[a|A:ALL]:" ipname #開始循環 #開始時間 started_time=`date +%s` echo "備份開始時間:${started_time}" if [ "$ipname" = "a" -o "$ipname" = "A" ];then db_num=`awk 'NR==2,NR==0 { print NR-1}' ${judegedate_01} |tail -n1` echo "此次將備份${db_num}個數據庫:" echo mysql_databases=$ipname or_dbnum=0 for i in `awk 'NR==2,NR==0 { print $1}' ${judegedate_01}`; do ((or_dbnum+=1)) mysql_databases=$i db_dpname=$dir_backup/${i}.${now_date} echo -e "\e[1;32m mydumper開始備份第${or_dbnum}個數據庫$i..... \e[0m" sleep 2 echo -e "\e[1;32m mydumper玩命備份中.....稍等片刻.... \e[0m" ${mydumper} \ --database=${mysql_databases} \ --host=${mysql_host} \ --port=${mysql_port} \ --user=${mysql_user} \ --password=${mysql_pass} \ --outputdir=${db_dpname} \ --no-schemas \ --rows=50000 \ --build-empty-files \ --threads=4 \ --compress-protocol \ --kill-long-queries if [ "$?" -eq 0 ];then echo -e "\e[1;32m mydumper成功將數據庫$i備份到:${db_dpname}. \e[0m" echo else echo -e "\e[1;31m 備份異常結束. \e[0m" fi done else echo "此次備份的數據庫名為:$ipname" echo #開始時間 STARTED_TIME=`date +%s` mysql_databases=$ipname db_dpname=$dir_backup/${mysql_databases}.${now_date} judegedate_02="judegedate02.`date +%y%m%d%h%m%s`.txt" schema_judege02="select schema_name from information_schema.schemata where schema_name='${ipname}';" mysql -h${mysql_host} -P${mysql_port} -u${mysql_user} -p${mysql_pass} -e"${schema_judege02}" >${judegedate_02} if [ ! -s "${judegedate_02}" ];then echo " " echo -e "\e[1;31m ******************************************************************* \e[0m" echo -e "\e[1;31m !o(︶︿︶)o! The schema_name ${ipname} not exits,pleae check . ~~~~(>_<)~~~~ \e[0m" echo -e "\e[1;31m ********************************************************************** \e[0m" echo " " rm -rf ${judegedate_01} rm -rf ${judegedate_02} exit 0 else echo -e "\e[1;32m mydumper開始備份請稍等..... \e[0m" sleep 2 echo -e "\e[1;32m mydumper玩命備份中.....稍等片刻.... \e[0m" ${mydumper} \ --database=${mysql_databases} \ --host=${mysql_host} \ --port=${mysql_port} \ --user=${mysql_user} \ --password=${mysql_pass} \ --outputdir=${db_dpname} \ --no-schemas \ --rows=50000 \ --build-empty-files \ --threads=4 \ --compress-protocol \ --kill-long-queries if [ "$?" -eq 0 ];then echo -e "\e[1;32m mydumper成功將數據庫備份到:${db_dpname}. \e[0m" else echo -e "\e[1;31m 備份異常結束. \e[0m" fi fi # 循環結束 fi fi rm -rf ${judegedate_01} rm -rf ${judegedate_02} rm -rf ${filepath}/tmpdatabases.txt echo "完成于: `date +%F' '%T' '%w`" ###################################################################################################################### 執行結果如下:##################### The databases name in current instance is: information_schema mysql performance_schema test wind mpiao mpadmin mplog Please input you want to backup database name[a|A:ALL]:A 備份開始時間:1431574874 此次將備份8個數據庫: mydumper開始備份第1個數據庫information_schema..... mydumper玩命備份中.....稍等片刻.... mydumper成功將數據庫information_schema備份到:/backup/information_schema.201505141141. mydumper開始備份第2個數據庫mysql..... mydumper玩命備份中.....稍等片刻.... mydumper成功將數據庫mysql備份到:/backup/mysql.201505141141. mydumper開始備份第3個數據庫performance_schema..... mydumper玩命備份中.....稍等片刻.... mydumper成功將數據庫performance_schema備份到:/backup/performance_schema.201505141141. mydumper開始備份第4個數據庫test..... mydumper玩命備份中.....稍等片刻.... mydumper成功將數據庫test備份到:/backup/test.201505141141. mydumper開始備份第5個數據庫wind..... mydumper玩命備份中.....稍等片刻.... mydumper成功將數據庫wind備份到:/backup/wind.201505141141. mydumper開始備份第6個數據庫mpiao..... mydumper玩命備份中.....稍等片刻.... mydumper成功將數據庫mpiao備份到:/backup/mpiao.201505141141. mydumper開始備份第7個數據庫mpadmin..... mydumper玩命備份中.....稍等片刻.... mydumper成功將數據庫mpadmin備份到:/backup/mpadmin.201505141141. mydumper開始備份第8個數據庫mplog..... mydumper玩命備份中.....稍等片刻.... mydumper成功將數據庫mplog備份到:/backup/mplog.201505141141. 完成于: 2015-05-14 11:42:05 4 </pre>
本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。
轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。
本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！