#!/bin/sh
 
##
## 功能：备份Mysql数据库
## 说明：该版本将备分所有数据库，指定不备份的数据库除外
##
 
## 服务器配置段
## 说明：1、以下配置信息全为数组各数组元数用空格分开，
##       2、以SERVER为准，PORT、USER、PASS、NO_BAK_DB一一对应，若是后三个参数都相应可以不填，
##          将以默认的GLOBALS参数为准，若其中一台SEVER的参数不同，其前面的的参数需补全方可
 
## 设置保留多少天的备份文件
DAY=15
 
SERVER=("192.168.0.11" "192.168.0.12")
PORT=()
USER=()
PASS=()
## 不用备份的数据库列表,用 "|" 分开，与上面的参数一一对应
NO_BAK_DB=()
 
GLOBALS_PORT="3306" 
GLOBALS_USER=""
GLOBALS_PASS=""
GLOBALS_NO_BAK_DB="test|mysql|information_schema"
 
DATE_PATH="/data/database"
MYSQLSHOW="/usr/local/mysql/bin/mysqlshow"
MYSQLDUMP="/usr/local/mysql/bin/mysqldump"
LOGFILE="/var/log/bak_mysql.log"
 
_SERVER_=""
_PORT_="${GLOBALS_PORT}"
_USER_="${GLOBALS_USER}"
_PASS_=""
_NO_BAK_DB_="${GLOBALS_NO_BAK_DB}"
_DATE_=`date +%Y%m%d%H`
 
check_dir ()
{
	# process: check_dir function
	# Syntax:  check_dir
	# Returns:
	#       N/A
 
	if [ -n "$1" ]; then
		_PATH_="$1"
	else
		echo "脚本内部发生错误"
		exit 1
	fi
 
	if [ ! -d "${_PATH_}" ]; then
	   mkdir -p "${_PATH_}"
	   make_log "Creating ${_PATH_}"
	fi
}
 
make_log ()
{
	# process: make_log function
	# Syntax:  make_log
	# Returns:
	#       N/A
 
	check_dir "${LOGFILE%/*}"
	echo -e "[$(date +%Y-%m-%d' '%H:%M:%S)] ${1}" >> "${LOGFILE}"
}
 
tar_sql ()
{
	# process: tar_sql function
	# Syntax:  tar_sql <path>
	# Returns:
	#       N/A
 
	cd "${1%/*}"
	make_log "PWD: ${1%/*}"
 
	make_log "tar zcf ${1##*/}.tgz ${1##*/} >> "${LOGFILE}" 2>&1"
	tar zcf ${1##*/}.tgz ${1##*/} >> "${LOGFILE}" 2>&1
 
	make_log "rm -rf ${1##*/} >> "${LOGFILE}" 2>&1"
	rm -rf ${1##*/} >> "${LOGFILE}" 2>&1
}
 
rm_tar ()
{
	# process: rm_tar function
	# Syntax:  rm_tar <path>
	# Returns:
	#       N/A
 
	for _sql_file_ in `find "${1}" -name "*[0-9].tgz" -type f -mtime +${DAY}`; do
		make_log "rm -f ${_sql_file_}"
		rm -f ${_sql_file_}
	done 
}
 
for((i=0;i<${#SERVER[@]};i++));do
	if ((${#PORT[@]} > 0 & i < ${#PORT[@]})); then
		if [ "${PORT[${i}]}" = "" ]; then
			_PORT_="--port=${GLOBALS_PORT}"
		else
			_PORT_="--port=${PORT[${i}]}"
		fi
	else
		_PORT_="--port=${GLOBALS_PORT}"
	fi
 
	if ((${#USER[@]} > 0 & i < ${#USER[@]})); then
		if [ "${USER[${i}]}" = "" ]; then
			_USER_="--user=${GLOBALS_USER}"
		else
			_USER_="--user=${USER[${i}]}"
		fi
	else
		_USER_="--user=${GLOBALS_USER}"
	fi
 
	if ((${#PASS[@]} > 0 & i < ${#PASS[@]})); then
		if [ "${PASS[${i}]}" = "" ]; then
			_PASS_=""
		else
			_PASS_="--password=${PASS[${i}]}"
		fi
	else
		_PASS_="--password=${GLOBALS_PASS}"
	fi
 
	if [ "${_PASS_}" != "" ]; then
		_LOG_PASS_="--password=xxxxxxx"
	else
		_LOG_PASS_=""
	fi
 
	if ((${#NO_BAK_DB[@]} > 0 & i < ${#NO_BAK_DB[@]})); then
		_NO_BAK_DB_="${NO_BAK_DB[${i}]}"
	else
		_NO_BAK_DB_="${GLOBALS_NO_BAK_DB}"
	fi
 
	_DATE_PATH_="${DATE_PATH}"
	DATE_PATH="${DATE_PATH}/${SERVER[${i}]}"
 
	check_dir "${DATE_PATH}/${SERVER[${i}]}_${_DATE_}"
 
	for db in `${MYSQLSHOW} --host=${SERVER[${i}]} ${_PORT_} ${_USER_} ${_PASS_} | awk '{++n;if(n>3&&NF>=3&&$2!~"^('${_NO_BAK_DB_}')$")print$2}'`; do
 
		make_log "${MYSQLDUMP} --host=${SERVER[${i}]} ${_PORT_} ${_USER_} ${_LOG_PASS_} -R --triggers --database ${db} > ${DATE_PATH}/${SERVER[${i}]}_${_DATE_}/${db}.sql"
 
		${MYSQLDUMP} --host=${SERVER[${i}]} ${_PORT_} ${_USER_} ${_PASS_} -R --triggers --database ${db} > ${DATE_PATH}/${SERVER[${i}]}_${_DATE_}/${db}.sql
 
	done
 
	tar_sql "${DATE_PATH}/${SERVER[${i}]}_${_DATE_}"
	rm_tar "${DATE_PATH}"
	DATE_PATH="${_DATE_PATH_}"
done