mysql自动备份与恢复shell脚本(2)

backup_mysql.sh

#!/bin/sh

# set -x

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

declare db_names
LOG_FILE=/opt/alu/logs/3rd_party/mysql/backup.log
TIME=`date +"%Y%m%d%H%M%S"`
DUMP_FILE=".sql"
TGZ_FILE=".tgz"
SHELL_DIR=/opt/alu/shell/sysmgt

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
  `mysqladmin ping > /dev/null 2>&1`
  if [[ $? != 0 ]];then
    echo "Mysql server error"
    exit 1
  fi
}

## find all database name
find_db_names(){
  DB_NAMES_ALL=$(mysql -e "show databases;")
  index=0
  for DB in ${DB_NAMES_ALL}
  do
    if [[ "$DB" != "Database" && "$DB" != "information_schema" && "$DB" != "performance_schema" ]];then
      db_names[$index]=$DB
      let index++
    fi
  done
}
 
## tgz file today exists,delete 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

  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 "[$1] Backup success!"
  echo "[${db_tgz_file}] Backup success!" >> ${LOG_FILE}   
  rm -rf $db_dump_file > /dev/null 2>&1 
}


## backup all db
backup_all(){
  # find all db name
  find_db_names
  echo
  echo -e "Please enter the directory for save the backup file:"  
  read dir
  if [[ ! -d $dir ]];then
    log_failure_msg "directory error!"
    exit 1
  fi

  echo
  cd $dir; mkdir $TIME; cd $TIME
  echo "Backup............."
  for arr in ${db_names[@]}
  do
    func_mysqldump $arr    
  done 
  echo "Finish"
}

## backup one db user specified
backup_one(){
  # find all db name
  find_db_names
  echo
  echo -e "Please enter the db name you want to backup(${db_names[@]}):"
  read dbname
  flag=1
  for arr in ${db_names[@]}
  do
    if [[ $dbname = $arr ]];then
      flag=0
      break
    else
      flag=1
      continue
    fi
  done
  if [[ $flag = 1 ]];then
    log_failure_msg "db not exist!"
    exit 1
  fi
  echo
  echo -e "Please enter the directory for save the backup file:"
  read dir
  if [[ ! -d $dir ]];then
    log_failure_msg "directory error!"
    exit 1
  fi
  echo
 
  # others,all right
  cd $dir; mkdir $TIME; cd $TIME
  echo "Backup.................."
  func_mysqldump $dbname
  echo "Finish"
}

## for one parameter
has_param(){
  dir=$1
  if [[ ! -d $dir ]];then
    log_failure_msg "directory error!"
    exit 1
  fi

  # find all db name
  find_db_names
  cd $dir; mkdir $TIME; cd $TIME
  for arr in ${db_names[@]}
  do
    func_mysqldump $arr
  done
  exit $?
}

menu(){
  echo
  PS3="Please select: "
  select t in "backup all" "backup one db" "exit"
  do
    case $t in
     'backup all')
      backup_all
      exit $?
      ;;
      'backup one db')
      backup_one
      exit $?
      ;;
      *)
      exit 1
      ;;
    esac
  done
}


######### main

main(){
  p_echo
  checkStatus
  if [[ $# != 0 ]];then
    has_param $@
  else
    menu
  fi
}

main $@


restore_mysql.sh


#!/bin/sh

#set -x

## it's for mysql restore

declare array
declare array_sql
declare time_select
LOG_FILE=/opt/alu/logs/3rd_party/mysql/backup.log
SHELL_DIR=/opt/alu/shell/sysmgt


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

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

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

## validate user's enter
## $1,enter string; $2,a number
enter_validate(){
  number_match $1
  if [[ $? != 0 ]];then
    return 1
  fi 
  if [[ $1 -lt 1 || $1 -gt $2 ]];then
    return 1
  fi
  return 0
}

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


## list all file folder name,such as 20110928171259,20110927171259
time_folder_names(){
  i=0
  files=`ls -t $@`

  for file in ${files}
  do
    array[$i]=$file
    ((i++))
  done
}

## show folder list,and get the selected
func_folder_select(){
  i=0
  folders=`ls -t $@`
  for folder in $folders
  do
    array[$i]=$folder
    ((i++))
  done

# time_folder_names $@

  len=${#array[@]}

  # No backup file
  if [[ $len = 0 ]];then
    echo "No backuped scripts under $@,exit."
    exit 1
  fi    

  # have files
  echo
  echo "Please select the time"
  for((index=0;index<$len;index++))
  do
    echo
    echo "[`expr $index + 1`] "${array[$index]}
  done

  echo
  echo "Please input the number before folder name.Otherwise,exit.Input:"
  read select

  # validate
  enter_validate $select $len   
  if [[ $? = 1 ]];then
    echo "Input error.Exit now"
    exit 1
  fi

  # selected folder name
  time_select=${array[`expr $select - 1`]}
}


func_param(){
  read -p "[$1] Sure to restore now?[yes or no]:"
  if [[ "$REPLY" = "y" || "$REPLY" = "Y" || "$REPLY" = "yes" || "$REPLY" = "YES" ]]
  then
    p_echo
    func_folder_select $@
    restore_all $@/$time_select
  else
    echo "Not restore,exit now"
    exit 1     
  fi
}

## public restore function,$1 is dbname,$2 is sql file
restore(){
  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 
}

restore_all(){
  sql_dir=$@
  files=`ls $sql_dir`
  if [[ -z $files ]];then
    echo "No backup file.exit." >> ${LOG_FILE}
    exit 1
  fi
  cd $sql_dir >> /dev/null

  for file in $files
  do
    tar -zxvf $file >> /dev/null
    dbname=${file%.*}
    sql=$dbname".sql"
    restore $dbname $sql
  done

  # delete sql files
  ls *.sql|xargs rm -rf     
}

restore_one(){
  files=`ls $@/$time_select`
  if [[ -z $files ]];then
    echo "No backup file.exit." >> ${LOG_FILE}
    exit 1
  fi
  echo
  echo "Database list below:"
  echo
  i=0
  for file in $files
  do
    array_sql[$i]=$file
    echo "[`expr $i + 1`] "${file%.*}
    ((i++))
  done 
  len=${#array_sql[@]}
  echo "Please select:"
  read select
  enter_validate $select $len
  if [[ $? = 1 ]];then
    echo "Input error.Exit now"
    exit 1
  fi
 
  # restore
  cd $@/$time_select
  tgz_file=${array_sql[`expr $select - 1`]}
  tar -zxvf $tgz_file >> /dev/null
  db=${tgz_file%.*}
  sql=$db".sql"
  restore $db $sql  
  ls *.sql|xargs rm -rf
}


menu(){
  echo
  echo "Please enter the backup script file directory:"
  read dir
  if [[ ! -d $dir ]];then
    log_failure_msg "directory error!"
    exit 1
  fi
  echo
  func_folder_select $dir 
  echo
  echo "Please select the restore type:"
  PS3="Please select: "
  select t in "Restore all" "Restore one db" "exit"
  do
    case $t in
      'Restore all')
      restore_all $dir/$time_select
      exit $?
      ;;
      'Restore one db')
      restore_one $dir
      exit $?
      ;;
      *)
      exit 1
      ;;
    esac
  done
}


####### main
main(){
  p_echo
  check_status
  if [ $# = 0 ];then
    menu
  else
    func_param $@
  fi
}
main $@


mysql_supervise.sh


#!/bin/sh

PID_MYSQL=`ps -ef|grep mysqld|grep -v grep|grep 3306|awk '{print $2}'`

PID_LOC=/opt/alu/logs/sysmgt/
mode=$1    # start or stop

basename=`basename "$0"`

if [ $# = 0 ]; then
    echo "Usage:$basename {start|stop}"
    exit 1
fi
[ $# -ge 1 ] && shift

case "$mode" in
  'start')
    if [ -n "${PID_MYSQL}" ]; then
        echo "MySQL is running (${PID_MYSQL})"
        exit 1
    fi
   
    # delete old pid file if exists
    if
      ls -lrt $PID_LOC|grep mysql >/dev/null 2>&1
    then
      rm -rf ${PID_LOC}mysql.*
    fi

    /etc/init.d/mysql start
    PID_MYSQL=`ps -ef|grep mysqld|grep -v grep|grep 3306|awk '{print $2}'`
    touch "${PID_LOC}mysql."${PID_MYSQL}
    echo ${PID_MYSQL}
    ;;

  'stop')
    if [ -z "${PID_MYSQL}" ]; then
        echo "MySQL is not running"
        exit 1
    fi
    /etc/init.d/mysql stop
    if
      ls "${PID_LOC}mysql."${PID_MYSQL} >/dev/null 2>&1
    then
      rm -f "${PID_LOC}mysql."${PID_MYSQL}
    fi
    ;;

    *)  
    echo "Usage:$basename {start|stop}"
    exit 1
    ;;
esac