#!/bin/bash
#############################################
#
# mysql手工导入,qa到dev
#
#############################################
export QA_HOSTNAME=192.168.8.98
export CONNECT_QA_MYSQL="mysql -h $QA_HOSTNAME -p3306 -uyunwei -p123456"
export DUMP_QA_MYSQL="mysqldump -h $QA_HOSTNAME -p3306 -uyunwei -p123456"
export CONNECT_DEV_MYSQL="mysql -h 192.168.8.190 -uinnodealing -p123456"
export DB_NAME=""
export BACK_DIR="/home/back/qa_mysql/"
export EXT=".sql.gz"
#
# 帮助 -h
# @param
#
usage() {
echo -e "\nUsage: $(basename $0) [OPTIONS] [database]"
echo -e "\t-d database backup and import database"
echo -e "\t-s database show database size"
echo -e "default backup directory is "$BACK_DIR"\n"
}
#
# 输出显示蓝色
# @param $1 string
#
echo_blue() {
echo -e "\033[36m$1\033[0m";
}
#
# 输出显示红色
# @param $1 string
#
echo_red() {
echo -e "\033[31m$1\033[0m"
}
#
# 检查数据库是否存在
# @param $1 数据库名称
#
check_database_exists() {
local dbname=$1
query=$($CONNECT_QA_MYSQL -e "SELECT information_schema.SCHEMATA.SCHEMA_NAME FROM information_schema.SCHEMATA where SCHEMA_NAME='${dbname}'" | grep $dbname)
if [ -z "$query" ]; then
echo $(echo_red "ERROR:database $dbname not exists!")
exit;
fi
}
#
# 显示数据库大小
# @param
#
show_database_size() {
local dbname=$1
echo $($CONNECT_QA_MYSQL -e "select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as $dbname from information_schema.TABLES where table_schema='${dbname}'" | grep MB)
}
#
# 备份数据库
# @param $1 数据库名称
# @return 1/0 1备份失败, 0备份成功
#
backup_database() {
local dbname=$1
local filename=$BACK_DIR$dbname$EXT
$DUMP_QA_MYSQL $dbname | gzip > $filename
if [ $? -eq 0 ];then
echo $filename
fi
}
#
# 导入数据库
# @param $1 要导入的数据库文件名
# @param $2 数据库名称
# @return 1/0
#
import_database() {
local filename=$1
local dbname=$2
gunzip
return $?
}
#
# 开始备份和导入数据
# @param $1
#
start_job() {
local dbname=$1
check_database_exists $dbname
echo -e "$dbname size: \n\t"$(echo_blue $(show_database_size $dbname))
echo "start backup:"
filename=$(backup_database $dbname)
if [ -n "$filename" ]; then
echo -e "\t"$(echo_blue $filename)
echo "start import:"
import_database $filename $dbname
if [ $? -eq 0 ]; then
echo -e "\t"$(echo_blue 'import is success')
else
echo -e "\t"$(echo_red 'import is failure')
fi
fi
}
# 显示帮助信息
if [ ! -n "$1" ]; then
usage
fi
# 获取参数
while getopts :d:s:h options
do
case "$options" in
d)
start_job $OPTARG
;;
s)
echo -e "$OPTARG size is "$(echo_blue $(show_database_size $OPTARG))
;;
h)
usage
;;
*)
usage
;;
esac
done