mysql备份脚本 shell_mysql自动备份与恢复SHELL脚本(1)

备份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"

分享到：

2011-09-22 17:01

浏览 1576

分类:数据库

评论

2 楼

hardwin

2011-09-24

另外，脚本里面的mysql_supervise.sh start 只是个启动和停止Mysql服务的脚本，它的目的是监控mysql是否异常退出的。因为我们是用它启动服务，启动时会在一个目录下生成一个以当前Pid命名的文件；用它停止服务时，会删除该pid命名文件。而如果服务不是通过此脚本停止的，文件就不会删，因此判断服务是异常终止，于是去启动服务。

1 楼

hardwin

2011-09-24

由于本人基础有限，写脚本有点仓促，于是脚本里面冗余很多，有的验证完全可以用正则来做，但因为不会，就采用很傻瓜的方式，挨个判断用户所输的列表编号是否正确。