#!/bin/bash#全備方式，一般在從機(jī)上執(zhí)行，適用于小中型mysql數(shù)據(jù)庫#刪除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=0backup_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  fidone#建立備份目錄if [ ! -e $backup_path/${date} ];then mkdir -p {$backup_path/${date},$backup_log_path}fi  #echo "開始備份所有數(shù)據(jù)庫" echo "備份開始,結(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ù)庫:$dbname($size) 備份狀態(tài):成功！" >>$backup_log else  cd $backup_path/${date}  echo "備份時(shí)間:${date} 備份方式:mysqldump 備份數(shù)據(jù)庫:${dbname} 備份狀態(tài):失敗,請查看日志." >>$backup_log fi  done  cd $backup_path tar zcpvf mysql_all_databases_$date.tar.gz $date rm -rf $backup_path/$datedu mysql_all_databases_$date.tar.gz -sh | awk '{print "文件:" $2 ",大小:" $1}'echo "==========All databases backups over==========" >>$backup_log

#! /bin/bashsource /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 "請輸入數(shù)據(jù)庫備份文件的路徑: " choose_pathif [ ! -d ${choose_path} ];then	echo "請輸入正確路徑，已退出"	exitelseecho "以下為檢測到的數(shù)據(jù)庫文件名稱："cd ${choose_path}for file in $(ls ${choose_path})do  [ -f ${file} ] && echo ${file%.*} && echo ${file%.*} >> ${local_path}/list.txt #先判斷是否是目錄，然后再輸出doneficat_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ù)庫:$dbname 恢復(fù)狀態(tài):成功！" >> $local_path/recovery_${date}.log        else            echo "恢復(fù)時(shí)間:${date} 恢復(fù)方式:mysqldump 恢復(fù)數(shù)據(jù)庫:${dbname} 恢復(fù)狀態(tài):失敗,請查看日志." >> $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ù)庫，請輸入想要恢復(fù)的數(shù)據(jù)庫名稱:" mydatabase       echo "正在恢復(fù)$mydatabase 請稍后....."	   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ī)則，請重新執(zhí)行..."	   exit	  fi	

#! /bin/bashsource /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ù)庫名稱列表如下:"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ù)庫，請輸入恢復(fù)數(shù)據(jù)文件所在路徑:" allpath		if [ ! -d $allpath ];then			echo "此路徑不存在，請重新執(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ù)庫:$dbname($size) 恢復(fù)狀態(tài):成功！" >> $allpath/recovery_${date}.log 		else  			cd $allpath  			echo "恢復(fù)時(shí)間:${date} 恢復(fù)方式:mysqldump 恢復(fù)數(shù)據(jù)庫:${dbname} 恢復(fù)狀態(tài):失敗,請查看日志." >> $allpath/recovery_${date}.log		fi	done		 elif [ $choose = 'B' -o $choose = 'b' ];then	   read -p -e "正在單個(gè)恢復(fù)數(shù)據(jù)庫，請輸入想要恢復(fù)的數(shù)據(jù)庫名稱:" mydatabase	   read -p -e "請輸入數(shù)據(jù)庫恢復(fù)的路徑:" mypath		if [ ! -d $mypath ];then    		echo "此路徑不存在，請重新執(zhí)行..."    		 	exit		elif [ ! -f $mypath/$mydatabase.sql ];then			echo "此路徑下沒有目標(biāo)數(shù)據(jù)庫文件,請重新執(zhí)行..."			exit		   elif [ $mydatabase != $mysql_list ];then                echo "$mydatabase 不在此數(shù)據(jù)庫中，請確認(rèn)數(shù)據(jù)庫名稱后再試，再見！"			 else                echo "正在恢復(fù)$mydatabase 請稍后....."								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ī)則，請重新執(zhí)行..."	   exit	  fi