mysql 备份与恢复shell脚本

 mysql_backup.sh

 

#!/bin/sh

# set -x

## this script is for auto mysql backup
## log file: /opt/alu/logs/3rd_party/mysql/backup.log

BACKUP_PATH=/opt/alu/data/backup/mysql
LOG_FILE=/opt/alu/logs/3rd_party/mysql/backup.log
DATE=`date +"%Y%m%d"`
DUMP_FILE=$DATE".sql"
TGZ_FILE=$DATE".tgz"
SHELL_DIR=/opt/alu/shell/sysmgt
BAK_DAY=7

usage(){
  echo "Usage:`basename $0`"
  echo "OR    `basename $0` DB1 [DB2 DB3...]"
}

log_success_msg(){
  echo " SUCCESS! $@"
}

log_failure_msg(){
  echo " ERROR! $@"
}

p_echo(){
  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
checkStatus(){
  `mysqladmin 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 >> ${LOG_FILE} 2>&1
  fi
}

## find all database name
declare VARDB
find_db_names(){
  DB_NAMES_ALL=$(mysql -e "show databases;")
  index=0
  for DB in ${DB_NAMES_ALL}
  do
    if [[ "$DB" != "Database" ]];then
      VARDB[$index]=$DB
      let index++
    fi
  done
}
 
## delete old files
# find ${BACKUP_PATH} -mtime +${BAK_DAY} | xargs rm -rf > /dev/null 2>&1

## tgz file today exists,backup it
delete_old_file(){
  if [ -f $1 ]; then
    echo "[$1] Backup file is exists,will be delete" >> ${LOG_FILE}
    rm -f $1 > /dev/null 2>&1
  fi
}

## mysqldump function
func_mysqldump(){
  db_dump_file=$1"_"$DUMP_FILE
  db_tgz_file=$1"_"$TGZ_FILE

  delete_old_file $db_tgz_file

  mysqldump -q --add-drop-table --single-transaction --lock-tables=false $1 > $db_dump_file
  tar -czvf $db_tgz_file $db_dump_file > /dev/null
  echo "[${db_tgz_file}] Backup success!" >> ${LOG_FILE}   
  rm -rf $db_dump_file > /dev/null 2>&1 
}

######### main
usage
p_echo
# check mysql server status
checkStatus

if [[ $? != 0 ]];then
  echo "Mysql server error"
  exit 1
fi

cd ${BACKUP_PATH}

if [[ $# = 0 ]];then
  find_db_names
  for arr in ${VARDB[@]}
  do
    func_mysqldump $arr
  done   
else
  for var_db_name in $@
  do
    func_mysqldump ${var_db_name}
  done 
fi

if [[ $? = 0 ]];then
  echo "Done"
else
  echo "Error"
fi

 

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

## help
help(){
  echo "Usage"
  echo "1. can give a database name and a script file:"
  echo "   Usage: `basename $0` db_name script_file"
  echo
  echo "2. No parameters give:"
  echo "   Will list database and backuped file can used to restore"
}

## number match
number_match(){
  if [[ $# = 0 ]];then
    echo "Need a parameter"
    return 1   
  else
    if [[ ! $1 =~ ^[0-9]+$ ]]
    then
      return 1
    fi
    return 0
  fi
}

## 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
}

## global variable
declare array

## list all database
db_names(){
  i=0
  files=$(ls -t ${BACKUP_PATH}/*.tgz)
  for file in ${files}
  do
    # nbi_db_20110920.tgz
    file_name=$(basename $file)
    # nbi_db
    db_name=${file_name%_*}
    array1[$i]=$db_name
    ((i++))
  done
  length=${#array1[@]}
  for ((i=0;i<$length;i++));do
    for ((j=$(expr $length - 1);j>$i;j--));do
      if [[ "${array1[$i]}" = "${array1[$j]}" ]];then
        unset array1[$i]
      fi
    done
  done

  index=0
  for arr in ${array1[@]}
  do
    array[$index]=$arr
    ((index++))   
  done
}

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}
}

## global variable
declare db_select

## show database list,and get selected
func_db_select(){
  # get all database name,call function db_names()
  db_names
  db_len=${#array[@]}

  # No backup file
  if [[ $db_len = 0 ]];then
    echo "No backuped scripts under ${BACKUP_PATH},exit now."
    exit 1
  fi    

    # have files
    echo
    echo "Database below:"
    for((index=0;index<$db_len;index++))
    do
      echo
      echo "[`expr $index + 1`] "${array[$index]}
    done

    echo
    read -p "Please input the number before database name.Otherwise,exit.Input:"
    select_db_name=$REPLY
    if [[ -z ${select_db_name} ]];then
      echo
      echo "Exit now"
      exit 1
    fi

    # test if input is number
    number_match $select_db_name

    if [[ $? = 1 ]];then
      echo "Input error.Exit now"
      exit 1
    fi
    if [[ $select_db_name -lt 1 || $select_db_name -gt $db_len ]];then
      echo "Input error.Exit now"
      exit 1
    fi

    # selected database name
    db_select=${array[`expr $select_db_name - 1`]}
}

## when no parameter specify,list all backuped scripts
func_noparam(){
  # call function
    func_db_select

    I=0
    FILES=$(ls -t ${BACKUP_PATH}/${db_select}*.tgz)
    for FILE in ${FILES}
    do
      VARFILE[$I]=$(basename $FILE)
      ((I++))
    done 
    arr_len=${#VARFILE[@]}

    echo
    echo "Backuped scripts list below:"
    for((index=0;index<$arr_len;index++))
    do
      echo
      echo "[`expr $index + 1`] "${VARFILE[$index]}
    done

    echo
    read -p "Please input the number before file name. Otherwise,exit.Input:"
    select_file_name=$REPLY
    if [[ -z ${select_file_name} ]];then
      echo
      echo "Exit now"
      exit 1
    fi

    number_match $select_file_name
    if [[ $? = 1 ]];then
      echo "Input error.Exit now"
      exit 1
    fi
    if [[ $select_file_name -lt 1 || $select_file_name -gt $arr_len ]];then
      echo "Input error.Exit now"
      exit 1
    fi   

    # selected script file
    select_file=${VARFILE[`expr $select_file_name - 1`]}

    echo
    read -p "Sure to restore \"$db_select\" use \"$select_file\"? (yes or no):"
    if [[ $REPLY = "n" || $REPLY = "N" || $REPLY = "no" || $REPLY = "NO" ]]
    then
      echo "Not restore, exit now"
      exit 1
    fi
 
    # User's input correct,backup use the select script
    p_echo

    cd ${BACKUP_PATH} >> /dev/null

    tar -zxvf ${select_file} >> /dev/null
    FILE_PRE=`ls ${select_file}|cut -d "." -f1`      
    FILE_RESTORE=${FILE_PRE}".sql"

    mysql -e "CREATE DATABASE IF NOT EXISTS ${db_select};" >> ${LOG_FILE} 2>&1

    mysql ${db_select} < ${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

}

func_param(){
  read -p "Use $2 to restore to $1 now?[yes or no]:"
  if [[ "$REPLY" = "y" || "$REPLY" = "Y" || "$REPLY" = "yes" || "$REPLY" = "YES" ]];then
    p_echo
    mysql -e "CREATE DATABASE IF NOT EXISTS $1;" >> ${LOG_FILE} 2>&1
    mysql $1 < $2 >> ${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

  else
    echo "Not restore,exit now"
    exit 1     
  fi
}

####### main
help
check_status

if [[ $# = 2 ]];then
  func_param $1 $2
else
  func_noparam
fi

if [[ $? = 0 ]];then
  echo "Done"
else
  echo "Error"
fi