mysql自动备份与恢复SHELL脚本(一)

mysql自动备份与恢复SHELL脚本(1)
此文备份脚本是备一个数据库，恢复时列出之前备份的脚本文件名称列表，用户选择恢复哪个备份文件，然后此文件被恢复到数据库。

完成这个脚本后的第二天跟领导说只备份一个数据库的事，领导说所有库都要备份。于是立马修改脚本。修改后是这样子：

备份脚本mysql_backup.sh运行时带参数指明要备份的数据库，备份完成后文件名是dbname_20110924.tgz这种。

然后恢复时，如果没带参数，将根据备份目录下文件名，可以得到所有的dbname，显示db name列表，让用户选择是恢复哪个数据库；最后，根据所选数据库名，显示出以该dbname打头的备份文件列表，用户再选择将哪个备份文件恢复到所选数据库。
如果带了2个参数，第一个参数是数据库名第二个是sql脚本文件名，直接恢复就OK了。
具体脚本，请看mysql自动备份与恢复SHELL脚本(2)

备份文件存放路径：
/opt/alu/data/backup/mysql

备份与恢复产生的日志文件：
/opt/alu/logs/3rd_party/mysql/backup.log

备份SHELL脚本：mysql_backup.sh

 #!/bin/sh  # set -x  ## this script is for auto mysql backup ## the backup files will be keeped for 10 days ## backup dir: /opt/alu/data/backup/mysql/ ## log file: /opt/alu/logs/3rd_party/mysql/backup.log  DB_NAME=test MYSQL_USER=nbiopsuser MYSQL_PWD=nbiopsuser BACKUP_PATH=/opt/alu/data/backup/mysql LOG_FILE=/opt/alu/logs/3rd_party/mysql/backup.log DUMP_FILE=`date +"%Y%m%d"`.sql TGZ_FILE=`date +"%Y%m%d"`.tgz SHELL_DIR=/opt/alu/shell/sysmgt BAK_DAY=7  echo >> ${LOG_FILE} echo "-------------Backup-------------" >> ${LOG_FILE} echo `date +"%Y-%m-%d %H:%M:%S"` >> ${LOG_FILE} echo "-------------Backup-------------" >> ${LOG_FILE} echo >> ${LOG_FILE}  ## check mysql pid, and kill it checkProcess(){   PIDS=`ps -ef|grep mysqld|grep -v grep|grep 3306|awk '{print $2}'`   if [ -n ${PIDS} ]; then     for pid in ${PIDS}     do       kill -9 ${pid}     done   fi }  ## check mysql service, make sure it's alive `mysqladmin -u${MYSQL_USER} -p${MYSQL_PWD} ping >>/dev/null 2>&1` if [ $? != 0 ]; then   checkProcess   echo "mysql is not alive,will be start now!" >> ${LOG_FILE}   ${SHELL_DIR}/mysql_supervise.sh start >> /dev/null 2>&1 fi  # mysql not running ok, exit if [ $? != 0 ]; then   echo "Mysql error"   exit 1 fi  ## delete old files find ${BACKUP_PATH} -mtime +${BAK_DAY} | xargs rm -rf >> /dev/null 2>&1  ## tgz file today exists,delete it if [ -f ${BACKUP_PATH}/${TGZ_FILE} ]; then   echo "[${BACKUP_PATH}/${TGZ_FILE}] Backup file is exists,will be backup as .bak" >> ${LOG_FILE}   cp ${BACKUP_PATH}/${TGZ_FILE} ${BACKUP_PATH}/${TGZ_FILE}".bak" >> ${LOG_FILE}   rm -f ${BACKUP_PATH}/${TGZ_FILE} >> /dev/null 2>&1 fi  cd ${BACKUP_PATH} mysqldump -u${MYSQL_USER} -p${MYSQL_PWD} --opt ${DB_NAME} > ${DUMP_FILE} tar -czvf ${TGZ_FILE} ${DUMP_FILE} >> ${LOG_FILE} 2>&1 echo "[${TGZ_FILE}] Backup success!" >> ${LOG_FILE} rm -rf ${DUMP_FILE} >> /dev/null 2>&1 echo "Done"  

欢迎大家阅读《mysql自动备份与恢复SHELL脚本(一)》,跪求各位点评，by 搞代码

恢复脚本 mysql_restore.sh

 #!/bin/sh  #set -x  ## it's for mysql restore ## if no file specify,it will use the backup script   BACKUP_PATH=/opt/alu/data/backup/mysql LOG_FILE=/opt/alu/logs/3rd_party/mysql/backup.log SHELL_DIR=/opt/alu/shell/sysmgt DB_NAME=test MYSQL_USER=nbiopsuser MYSQL_PWD=nbiopsuser   ## make sure if mysql's status is OK  check_status(){   `mysqladmin -u${MYSQL_USER} -p${MYSQL_PWD} ping >>/dev/null 2>&1`   if [ $? != 0 ]; then     PIDS=`ps -ef|grep mysqld|grep -v grep|grep 3306|awk '{print $2}'`       if [ -n ${PIDS} ]; then       for pid in ${PIDS}       do         kill -9 ${pid}       done     fi     echo "Mysql is not alive,will be start now!" >> ${LOG_FILE}       ${SHELL_DIR}/mysql_supervise.sh start >> /dev/null 2>&1   fi }  # mysql not running ok, exit   if [ $? != 0 ]; then     echo "Mysql error"     exit 1   fi    p_echo(){   echo >> ${LOG_FILE}   echo "------------Restore------------" >> ${LOG_FILE}   echo `date +"%Y-%m-%d %H:%M:%S"` >> ${LOG_FILE}   echo "------------Restore------------" >> ${LOG_FILE}   echo >> ${LOG_FILE} }   ## when no parameter specify,list all backuped scripts res_noparam(){   let I=0   FILES=`ls -t ${BACKUP_PATH}/*.tgz`   for FILE in ${FILES}   do     VARFILE[$I]=$(basename $FILE)     let I++   done     arr_len=${#VARFILE[*]}   if [ ${arr_len} != 0 ];then     echo     echo "Backuped scripts list below:"     let index1=0     while [ ${index1} -lt ${arr_len} ]     do       echo       echo "[$(expr ${index1} + 1)] "${VARFILE[${index1}]}       let index1++     done     echo     read -p "Please input the number before file name. Otherwise,exit.Input:"     select=$REPLY     if [ -z ${select} ];then       echo       echo "Exit now"       exit 1     fi     let index2=1     let flag=1     while [ ${index2} -le ${arr_len} ]     do       if [ "${select}" = "${index2}" ];then         let flag=0         break       else          let index2++         continue       fi           done     # User's input error,exit     if [ ${flag} = 1 ];then       echo       echo "Exit now"       exit 1           fi     # User's input correct,backup use the select script     p_echo     cd ${BACKUP_PATH} >> /dev/null     tar -zxvf ${VARFILE[$(expr ${select} - 1)]} >> /dev/null     FILE_PRE=`ls ${VARFILE[$(expr ${select} - 1)]}|cut -d "." -f1`            FILE_RESTORE=${FILE_PRE}".sql"     mysql -u${MYSQL_USER} -p${MYSQL_PWD} -e "CREATE DATABASE IF NOT EXISTS ${DB_NAME};" >> ${LOG_FILE} 2>&1     mysql -u${MYSQL_USER} -p${MYSQL_PWD} ${DB_NAME} < ${FILE_RESTORE} >> ${LOG_FILE} 2>&1     if [ $? = 0 ];then       echo "[${FILE_RESTORE}] Restore success!"       echo "[${FILE_RESTORE}] Restore success!" >> ${LOG_FILE}     else       echo "[${FILE_RESTORE}] Restore fail!"       echo "[${FILE_RESTORE}] Restore fail!" >> ${LOG_FILE}     fi     rm -f ${FILE_RESTORE} >> /dev/null     return $?   else     echo "No backuped scripts under ${BACKUP_PATH},exit now."     exit 1       fi }   res_param(){   if [ -f $1 ]; then     read -p "Use $1 to restore now?[yes or no]:"     if [[ "$REPLY" = "y" || "$REPLY" = "Y" || "$REPLY" = "yes" || "$REPLY" = "YES" ]];then       p_echo       mysql -u${MYSQL_USER} -p${MYSQL_PWD} -e "CREATE DATABASE IF NOT EXISTS ${DB_NAME};" >> ${LOG_FILE} 2>&1       mysql -u${MYSQL_USER} -p${MYSQL_PWD} ${DB_NAME} < $1 >> ${LOG_FILE} 2>&1       if [ $? = 0 ];then         echo "[$1] Restore success!"         echo "[$1] Restore success!" >> ${LOG_FILE}       else         echo "[$1] Restore fail!"         echo "[$1] Restore fail!" >> ${LOG_FILE}       fi       return $?     else       echo "Not restore,exit now"       exit 1           fi   else     echo     echo "File error!"      echo     echo 'List files under '"'${BACKUP_PATH}'"' now'     res_noparam   fi   }   ##### main check_status if [ $# != 0 ];then   res_param $1 else  res_noparam  fi echo "Done"   

喜欢 (0)赏

[搞代码]

分享 (0)