shell脚本---实现mysql定时备份、删除、恢复

最新推荐文章于 2022-02-21 22:17:40 发布

lcm_linux

最新推荐文章于 2022-02-21 22:17:40 发布

阅读量797

点赞数

分类专栏： shell 文章标签： linux mysql备份 shell脚本 mysql恢复

本文链接：https://blog.csdn.net/lcm_linux/article/details/102910365

版权

shell 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

mysql备份脚本：
脚本实现：按照数据库名称，全量备份mysql数据库并定期删除

#!/bin/bash
#全备方式，一般在从机上执行，适用于小中型mysql数据库
#删除15天以前备份
#作者：lcm_linux
#时间：2019.08.06
 
source ~/.bash_profile  #加载用户环境变量
set -o nounset       #引用未初始化变量时退出
set -o errexit      #执行shell命令遇到错误时退出
#备份用户---需要在mysql中提前创建并授权
#GRANT SELECT,RELOAD,LOCK TABLES,REPLICATION CLIENT,SHOW VIEW,TRIGGER,EVENT ON *.* TO 'backup'@'%' IDENTIFIED BY '123456';
#GRANT SELECT,RELOAD,LOCK TABLES,REPLICATION CLIENT,SHOW VIEW,TRIGGER,EVENT ON *.* TO 'backup'@'localhost' IDENTIFIED BY '123456';
user="backup"
#备份用户密码
password="123456"
#mysql连接端口
port="3306"
#mysql path---视情况修改
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="${backup_path}/log"
day=0
backup_log="${backup_log_path}/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 "开始备份所有数据库" 
echo "备份开始,结果查看 $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 "备份时间:${date} 备份方式:mysqldump 备份数据库:$dbname($size) 备份状态:成功！" >>$backup_log
  else
    cd $backup_path/${date}
    echo "备份时间:${date} 备份方式:mysqldump 备份数据库:${dbname} 备份状态:失败,请查看日志." >>$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

配置定时任务：
crontab -e
内容如下：

0 1 * * * /bin/bash /opt/script/mysql_shell/mysqlbackup.sh

附上恢复脚本A：
目标实现：对于删除数据库，根据上述备份脚本产生的备份文件内容，进行恢复数据库操作，包括创建数据库名称
注意：恢复脚本需要手动操作

#! /bin/bash
source /etc/profile    #加载系统环境变量
source ~/.bash_profile  #加载用户环境变量
set -o nounset       #引用未初始化变量时退出
set -o errexit      #执行shell命令遇到错误时退出
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 "请输入数据库备份文件的路径: " choose_path
if [ ! -d ${choose_path} ];then
	echo "请输入正确路径，已退出"
	exit
else
echo "以下为检测到的数据库文件名称："
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 "恢复时间:${date} 恢复方式:mysqldump 恢复数据库:$dbname 恢复状态:成功！" >> $local_path/recovery_${date}.log
                else
                        echo "恢复时间:${date} 恢复方式:mysqldump 恢复数据库:${dbname} 恢复状态:失败,请查看日志." >> $local_path/recovery_${date}.log

                fi
        done    


}

read -ep "全部恢复输入(A/a)，单个恢复输入(B/b):" choose
        if [ $choose = 'A' -o $choose = 'a' ];then
	recovery
	 elif [ $choose = 'B' -o $choose = 'b' ];then
	     read -ep "正在单个恢复数据库，请输入想要恢复的数据库名称:" mydatabase
             echo "正在恢复$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 已成功恢复，恭喜！"
       	     exit			
			  
	else 
		echo "输入不符合规则，请重新执行..."
	     exit
	    fi

附上恢复脚本B：
脚本实现：在未删除数据库的情况下，对mysql数据库进行数据恢复，可以选择全部恢复或者逐个恢复。
注意：恢复脚本需要手动操作

#! /bin/bash
source /etc/profile    #加载系统环境变量
source ~/.bash_profile  #加载用户环境变量
set -o nounset       #引用未初始化变量时退出
set -o errexit      #执行shell命令遇到错误时退出
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 "数据库名称列表如下:"
echo "${mysql_list}"

read -p "全部恢复输入(A/a)，单个恢复输入(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 "正在准备恢复全部数据库，请输入恢复数据文件所在路径:" allpath
		if [ ! -d $allpath ];then
			echo "此路径不存在，请重新执行..."
			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 "恢复时间:${date} 恢复方式:mysql 恢复数据库:$dbname($size) 恢复状态:成功！" >> $allpath/recovery_${date}.log
  		else
    			cd $allpath
    			echo "恢复时间:${date} 恢复方式:mysqldump 恢复数据库:${dbname} 恢复状态:失败,请查看日志." >> $allpath/recovery_${date}.log

		fi
	done	
	 elif [ $choose = 'B' -o $choose = 'b' ];then
	     read -p -e "正在单个恢复数据库，请输入想要恢复的数据库名称:" mydatabase
	     read -p -e "请输入数据库恢复的路径:" mypath
		if [ ! -d $mypath ];then
        		echo "此路径不存在，请重新执行..."
       		 	exit
		elif [ ! -f $mypath/$mydatabase.sql ];then
			echo "此路径下没有目标数据库文件,请重新执行..."
			exit
		     elif [ $mydatabase != $mysql_list ];then
                                echo "$mydatabase 不在此数据库中，请确认数据库名称后再试，再见！"
			  else
                                echo "正在恢复$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 已成功恢复，恭喜！"
       			 	exit			
			  fi
#		     fi
#		fi
#	     fi
	else 
#	    if [ $choose != 'B' -o $choose != 'b' -o $choose != 'A' -o $choose != 'a' ];then 
		echo "输入不符合规则，请重新执行..."
	     exit
	    fi