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

数据库  /  管理员 发布于 6年前   262

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 '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 "开始备份所有数据库" 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/$datedu 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/bashsource /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_pathif [ ! -d ${choose_path} ];thenecho "请输入正确路径，已退出"exitelseecho "以下为检测到的数据库文件名称："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 ]];thenecho "恢复时间:${date} 恢复方式:mysqldump 恢复数据库:$dbname 恢复状态:成功！" >> $local_path/recovery_${date}.log        elseecho "恢复时间:${date} 恢复方式:mysqldump 恢复数据库:${dbname} 恢复状态:失败,请查看日志." >> $local_path/recovery_${date}.log        fi    done  }read -ep "全部恢复输入(A/a)，单个恢复输入(B/b):" choose    if [ $choose = 'A' -o $choose = 'a' ];thenrecovery 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/bashsource /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' ];thenread -p "正在准备恢复全部数据库，请输入恢复数据文件所在路径:" allpathif [ ! -d $allpath ];thenecho "此路径不存在，请重新执行..."exitfifor dbname in ${mysql_list};dosleep 1create_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 ]];thencd $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}.logfidone elif [ $choose = 'B' -o $choose = 'b' ];then   read -p -e "正在单个恢复数据库，请输入想要恢复的数据库名称:" mydatabase   read -p -e "请输入数据库恢复的路径:" mypathif [ ! -d $mypath ];then    echo "此路径不存在，请重新执行..."     exitelif [ ! -f $mypath/$mydatabase.sql ];thenecho "此路径下没有目标数据库文件,请重新执行..."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#   fielse #  if [ $choose != 'B' -o $choose != 'b' -o $choose != 'A' -o $choose != 'a' ];then echo "输入不符合规则，请重新执行..."   exit  fi

总结

以上所述是小编给大家介绍的 shell脚本实现mysql定时备份、删除、恢复 ，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对站的支持！
如果你觉得本文对你有帮助，欢迎转载，烦请注明出处，谢谢！