mysql自动备份与恢复shell脚本(2)

[size=large]backup_mysql.sh[/size]
[size=medium]
#!/bin/sh

# set -x

## this script is for auto mysql backup
## log file: /opt/alu/logs/3rd_party/mysql/backup.log

declare db_names
LOG_FILE=/opt/alu/logs/3rd_party/mysql/backup.log
TIME=`date +"%Y%m%d%H%M%S"`
DUMP_FILE=".sql"
TGZ_FILE=".tgz"
SHELL_DIR=/opt/alu/shell/sysmgt

log_success_msg(){
echo " SUCCESS! $@"
}

log_failure_msg(){
echo " ERROR! $@"
}


p_echo(){
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} ][ $? != 0 ][ $? != 0 ][ "$DB" != "Database" && "$DB" != "information_schema" && "$DB" != "performance_schema" ];then
db_names[$index]=$DB
let index++
fi
done
}

## tgz file today exists,delete it
delete_old_file(){
if [ -f $1 ]; then
echo "[$1] Backup file is exists,will be delete" >> ${LOG_FILE}
rm -f $1 > /dev/null 2>&1
fi
}

## mysqldump function
func_mysqldump(){
db_dump_file=$1$DUMP_FILE
db_tgz_file=$1$TGZ_FILE

mysqldump -q --add-drop-table --single-transaction --lock-tables=false $1 > $db_dump_file
tar -czvf $db_tgz_file $db_dump_file > /dev/null
echo "[$1] Backup success!"
echo "[${db_tgz_file}][ ! -d $dir ];then
log_failure_msg "directory error!"
exit 1
fi

echo
cd $dir; mkdir $TIME; cd $TIME
echo "Backup............."
for arr in ${db_names[@]}
do
func_mysqldump $arr
done
echo "Finish"
}

## backup one db user specified
backup_one(){
# find all db name
find_db_names
echo
echo -e "Please enter the db name you want to backup(${db_names[@]}):"
read dbname
flag=1
for arr in ${db_names[@][ $dbname = $arr ][ $flag = 1 ][ ! -d $dir ][ ! -d $dir ];then
log_failure_msg "directory error!"
exit 1
fi

# find all db name
find_db_names
cd $dir; mkdir $TIME; cd $TIME
for arr in ${db_names[@][ $# != 0 ];then
has_param $@
else
menu
fi
}

main $@
[/size]

[size=large]restore_mysql.sh[/size]
[size=medium][ $# = 0 ];then
echo "Need a parameter"
return 1
else
if [[ ! $1 =~ ^[0-9][ $? != 0 ]];then
return 1
fi
if [[ $1 -lt 1 || $1 -gt $2 ]];then
return 1
fi
return 0
}

## 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
}


## list all file folder name,such as 20110928171259,20110927171259
time_folder_names(){
i=0
files=`ls -t $@`

for file in ${files}
do
array[$i]=$file
((i++))
done
}

## show folder list,and get the selected
func_folder_select(){
i=0
folders=`ls -t $@`
for folder in $folders
do
array[$i]=$folder
((i++))
done

# time_folder_names $@

len=${#array[@]}

# No backup file
if [[ $len = 0 ]];then
echo "No backuped scripts under $@,exit."
exit 1
fi

# have files
echo
echo "Please select the time"
for((index=0;index<$len;index++))
do
echo
echo "[`expr $index + 1`] "${array[$index]}
done

echo
echo "Please input the number before folder name.Otherwise,exit.Input:"
read select

# validate
enter_validate $select $len
if [[ $? = 1 ]];then
echo "Input error.Exit now"
exit 1
fi

# selected folder name
time_select=${array[`expr $select - 1`]}
}


func_param(){
read -p "[$1] Sure to restore now?[yes or no]:"
if [[ "$REPLY" = "y" || "$REPLY" = "Y" || "$REPLY" = "yes" || "$REPLY" = "YES" ]]
then
p_echo
func_folder_select $@
restore_all $@/$time_select
else
echo "Not restore,exit now"
exit 1
fi
}

## public restore function,$1 is dbname,$2 is sql file
restore(){
mysql -e "CREATE DATABASE IF NOT EXISTS $1;" >> ${LOG_FILE} 2>&1
mysql $1 < $2 >> ${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
}

restore_all(){
sql_dir=$@
files=`ls $sql_dir`
if [[ -z $files ]];then
echo "No backup file.exit." >> ${LOG_FILE}
exit 1
fi
cd $sql_dir >> /dev/null

for file in $files
do
tar -zxvf $file >> /dev/null
dbname=${file%.*}
sql=$dbname".sql"
restore $dbname $sql
done

# delete sql files
ls *.sql|xargs rm -rf
}

restore_one(){
files=`ls $@/$time_select`
if [[ -z $files ]];then
echo "No backup file.exit." >> ${LOG_FILE}
exit 1
fi
echo
echo "Database list below:"
echo
i=0
for file in $files
do
array_sql[$i]=$file
echo "[`expr $i + 1`] "${file%.*}
((i++))
done
len=${#array_sql[@]}
echo "Please select:"
read select
enter_validate $select $len
if [[ $? = 1 ]];then
echo "Input error.Exit now"
exit 1
fi

# restore
cd $@/$time_select
tgz_file=${array_sql[`expr $select - 1`]}
tar -zxvf $tgz_file >> /dev/null
db=${tgz_file%.*}
sql=$db".sql"
restore $db $sql
ls *.sql|xargs rm -rf
}


menu(){
echo
echo "Please enter the backup script file directory:"
read dir
if [[ ! -d $dir ]];then
log_failure_msg "directory error!"
exit 1
fi
echo
func_folder_select $dir
echo
echo "Please select the restore type:"
PS3="Please select: "
select t in "Restore all" "Restore one db" "exit"
do
case $t in
'Restore all')
restore_all $dir/$time_select
exit $?
;;
'Restore one db')
restore_one $dir
exit $?
;;
*)
exit 1
;;
esac
done
}


####### main
main(){
p_echo
check_status
if [ $# = 0 ];then
menu
else
func_param $@
fi
}
main $@
[/size]

[size=large]mysql_supervise.sh[/size]
[size=medium]

#!/bin/sh

PID_MYSQL=`ps -ef|grep mysqld|grep -v grep|grep 3306|awk '{print $2}'`

PID_LOC=/opt/alu/logs/sysmgt/
mode=$1 # start or stop

basename=`basename "$0"`

if [ $# = 0 ]; then
echo "Usage:$basename {start|stop}"
exit 1
fi
[ $# -ge 1 ] && shift

case "$mode" in
'start')
if [ -n "${PID_MYSQL}" ]; then
echo "MySQL is running (${PID_MYSQL})"
exit 1
fi

# delete old pid file if exists
if
ls -lrt $PID_LOC|grep mysql >/dev/null 2>&1
then
rm -rf ${PID_LOC}mysql.*
fi

/etc/init.d/mysql start
PID_MYSQL=`ps -ef|grep mysqld|grep -v grep|grep 3306|awk '{print $2}'`
touch "${PID_LOC}mysql."${PID_MYSQL}
echo ${PID_MYSQL}
;;

'stop')
if [ -z "${PID_MYSQL}" ]; then
echo "MySQL is not running"
exit 1
fi
/etc/init.d/mysql stop
if
ls "${PID_LOC}mysql."${PID_MYSQL} >/dev/null 2>&[ $? != 0 ][ $1 -lt 1 || $1 -gt $2 ][ $? != 0 ][ -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
}


## list all file folder name,such as 20110928171259,20110927171259
time_folder_names(){
i=0
files=`ls -t $@`

for file in ${files}
do
array[$i]=$file
((i++))
done
}

## show folder list,and get the selected
func_folder_select(){
i=0
folders=`ls -t $@`
for folder in $folders
do
array[$i]=$folder
((i++))
done

# time_folder_names $@

len=${#array[@][ $len = 0 ];then
echo "No backuped scripts under $@,exit."
exit 1
fi

# have files
echo
echo "Please select the time"
for((index=0;index<$len;index++))
do
echo
echo "[`expr $index + 1`] "${array[$index][ $? = 1 ];then
echo "Input error.Exit now"
exit 1
fi

# selected folder name
time_select=${array[`expr $select - 1`]}
}


func_param(){
read -p "[$1] Sure to restore now?[yes or no][ "$REPLY" = "y" || "$REPLY" = "Y" || "$REPLY" = "yes" || "$REPLY" = "YES" ][ $? = 0 ];then
echo "[$1] Restore success!"
echo "[$1] Restore success!" >> ${LOG_FILE}
else
echo "[$1] Restore fail!"
echo "[$1][ -z $files ][ -z $files ];then
echo "No backup file.exit." >> ${LOG_FILE}
exit 1
fi
echo
echo "Database list below:"
echo
i=0
for file in $files
do
array_sql[$i]=$file
echo "[`expr $i + 1`] "${file%.*}
((i++))
done
len=${#array_sql[@][ $? = 1 ];then
echo "Input error.Exit now"
exit 1
fi

# restore
cd $@/$time_select
tgz_file=${array_sql[`expr $select - 1`][ ! -d $dir ];then
log_failure_msg "directory error!"
exit 1
fi
echo
func_folder_select $dir
echo
echo "Please select the restore type:"
PS3="Please select: "
select t in "Restore all" "Restore one db" "exit"
do
case $t in
'Restore all')
restore_all $dir/$time_select
exit $?
;;
'Restore one db')
restore_one $dir
exit $?
;;
*)
exit 1
;;
esac
done
}


####### main
main(){
p_echo
check_status
if [ $# = 0 ];then
menu
else
func_param $@
fi
}
main $@
[/size]

[size=large]mysql_supervise.sh[/size]
[size=medium]

#!/bin/sh

PID_MYSQL=`ps -ef|grep mysqld|grep -v grep|grep 3306|awk '{print $2}'`

PID_LOC=/opt/alu/logs/sysmgt/
mode=$1 # start or stop

basename=`basename "$0"`

if [ $# = 0 ]; then
echo "Usage:$basename {start|stop}"
exit 1
fi
[ $# -ge 1 ] && shift

case "$mode" in
'start')
if [ -n "${PID_MYSQL}" ]; then
echo "MySQL is running (${PID_MYSQL})"
exit 1
fi

# delete old pid file if exists
if
ls -lrt $PID_LOC|grep mysql >/dev/null 2>&1
then
rm -rf ${PID_LOC}mysql.*
fi

/etc/init.d/mysql start
PID_MYSQL=`ps -ef|grep mysqld|grep -v grep|grep 3306|awk '{print $2}'`
touch "${PID_LOC}mysql."${PID_MYSQL}
echo ${PID_MYSQL}
;;

'stop')
if [ -z "${PID_MYSQL}" ]; then
echo "MySQL is not running"
exit 1
fi
/etc/init.d/mysql stop
if
ls "${PID_LOC}mysql."${PID_MYSQL} >/dev/null 2>&1
then
rm -f "${PID_LOC}mysql."${PID_MYSQL}
fi
;;

*)
echo "Usage:$basename {start|stop}"
exit 1
;;
esac
[/size]