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