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