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.logDB_NAME=testMYSQL_USER=nbiopsuserMYSQL_PWD=nbiopsuserBACKUP_PATH=/opt/alu/data/backup/mysqlLOG_FILE=/opt/alu/logs/3rd_party/mysql/backup.logDUMP_FILE=`date +"%Y%m%d"`.sqlTGZ_FILE=`date +"%Y%m%d"`.tgzSHELL_DIR=/opt/alu/shell/sysmgtBAK_DAY=7echo >> ${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 itcheckProcess(){ 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>&1fi# mysql not running ok, exitif [ $? != 0 ]; then echo "Mysql error" exit 1fi## delete old filesfind ${BACKUP_PATH} -mtime +${BAK_DAY} | xargs rm -rf >> /dev/null 2>&1## tgz file today exists,delete itif [ -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>&1ficd ${BACKUP_PATH}mysqldump -u${MYSQL_USER} -p${MYSQL_PWD} --opt ${DB_NAME} > ${DUMP_FILE}tar -czvf ${TGZ_FILE} ${DUMP_FILE} >> ${LOG_FILE} 2>&1echo "[${TGZ_FILE}] Backup success!" >> ${LOG_FILE}rm -rf ${DUMP_FILE} >> /dev/null 2>&1echo "Done"#!/bin/sh#set -x## it's for mysql restore## if no file specify,it will use the backup scriptBACKUP_PATH=/opt/alu/data/backup/mysqlLOG_FILE=/opt/alu/logs/3rd_party/mysql/backup.logSHELL_DIR=/opt/alu/shell/sysmgtDB_NAME=testMYSQL_USER=nbiopsuserMYSQL_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 scriptsres_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 }##### maincheck_statusif [ $# != 0 ];then res_param $1else res_noparam fiecho "Done"