此文备份脚本是备一个数据库,恢复时列出之前备份的脚本文件名称列表,用户选择恢复哪个备份文件,然后此文件被恢复到数据库。
完成这个脚本后的第二天跟领导说只备份一个数据库的事,领导说所有库都要备份。于是立马修改脚本。修改后是这样子:
备份脚本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 backuped scripts
res_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
}
##### main
check_status
if [ $# != 0 ];then
res_param $1
else
res_noparam
fi
echo "Done"
分享到:
相关推荐
Linux下mysql数据库备份以及恢复shell脚本
基于xtrabackup的MySQL数据库备份及还原Shell脚本
今天这个备份分二个版本一个是linux上直接安装的mysql,另一种是docker上安装的mysql。基本操作都一样只是备份sql语句不同而已。可以选择设置需要备份的库,自动备份压缩,自动删除 7 天前的备份,需要使用 crontab ...
Linux下自动备份Mysql文件shell脚本。
mysql自动备份shell脚本,个人感觉写的挺好的,推荐一下! mysql数据库中存储着网站最核心最宝贵的数据,如果因为不可预测的原因导致数据损坏或丢失,对一个网站的打击是毁灭性的,一次又一次的教训提醒着我们一定要...
今天这个备份分二个版本一个是linux上直接安装的mysql,另一种是docker上安装的mysql。基本操作都一样只是备份sql语句不同而已。可以选择设置需要备份的库,自动备份压缩,自动删除 7 天前的备份,需要使用 crontab ...
Mysql备份,shell脚本运行代码,创建各自不同的目录文件
xtrabackup备份恢复shell脚本,亲测生产使用。 1、全库+累计+差异 2、可恢复到指定时间 3、另外有测试数据
mysql自动备份数据库脚本,使用shell进行编写,自动将备份脚本打包zip包。方便使用。
Linux下定时备份MySQL数据库的Shell脚本.pdf
backup mysql databases and keep newest 5 days backup
mysql数据库定期自动备份的shell脚本。如定期每三天自动备份数据库,并只保存20份备份文件,多余的删除。
主要介绍了shell脚本实现mysql定时备份、删除、恢复功能,本文给大家介绍的非常详细,具有一定的参考借鉴价值,需要的朋友可以参考下
MySQL备份的自动执行脚本,以及MySQL的计划任务,里面的内容都是自己写的,如有用到,记得改一下脚本里的内容,逻辑及格式正确,自己改一下参数就能用。
mysql备份shell脚本与备份文件的还原 ,使用mysqldump每日和每月1号备份shell脚本,要参数说明,还原时的主要问题分析与解决
Mysql_backup.sh 为 mysql 自动备份 脚本,配合 crontab命令 用来管理需要周期性执行任务 Web_backup.sh 为 web文件 自动备份 脚本,配合 crontab命令 用来管理需要周期性执行任务 Mysql_backup.sh 里: * 把 ...
常用shell脚本, Dos攻击防范(自动屏蔽攻击IP).sh 一键部署等等 Linux系统发送告警脚本.sh MySQL数据库备份单循环.sh MySQL数据库备份多循环.sh nginx 访问访问日志按天切割.sh nginx.conf nginx访问日志分析...
mysql自动备份shell脚本,下载放到root下载etc/crontab中加入定时启动命令
基于Xtrabackup的MySQL数据库备份Shell脚本