#!/bin/bash
##############################################
#
# 手动备份数据库
#
##############################################
export HOSTNAME="prd-mysql002"
export DUMP_PRD_MYSQL="mysqldump -h$HOSTNAME -P3306 -uroot -p123456"
export CONNECT_PRD_MYSQL="mysql -h$HOSTNAME -P3306 -uroot -p123456"
export BACK_DIR="/backup/deply_mysql/"
export BACKUP_FILE=""
#
# 帮助 -h
# @param
#
usage() {
echo -e "\n\t-d database table 备份表"
echo -e "\t-s database table 查看表的大小";
echo -e "\t\t-s ? 显示库"
echo -e "\t\t-s database ? 显示表\n"
exit
}
#
# 输出显示蓝色
# @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
# @return 输出格式如:20181017
#
dir_time() {
echo $(date "+%Y%m%d")
}
#
# 获取库名和表名
# @param $1 database name
# @param $2 table name
#
set_database_table_name() {
export DATABASE=$1
export TABLE=$2
}
#
# 检查数据库是否存在
# @param $1 数据库名称
#
check_database_exists() {
local dbname=$1
query=$($CONNECT_PRD_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_help() {
if [ "$DATABASE" == "?" ] ; then
echo "show databases";
show_database
exit 0
elif [ "$TABLE" == "?" ] ; then
echo "show tables"
show_tables $DATABASE
exit 0
elif [ -z "$DATABASE" ] ; then
usage
exit 1
elif [ -z "$TABLE" ] ; then
usage
exit 1
fi
}
#
# 创建备份目录
# @param
#
get_directory() {
local databasename=$1
local directory=$BACK_DIR$databasename/$(dir_time)
if [ ! -d $directory ]; then
mkdir $directory -p
fi
echo $directory
}
#
# 显示数据库
# @param
#
show_database() {
$CONNECT_PRD_MYSQL -e "show databases"
}
#
# 显示数据库下面的表
# @param $1 数据库名称
#
show_tables() {
local dbname=$1
check_database_exists $dbname
$CONNECT_PRD_MYSQL -e "use $dbname;show tables"
}
#
# 显示表大小
# @param $1 数据库名称
# @param $2 表名称
#
show_table_size() {
local databasename=$1
local tablename=$2
local query=$($CONNECT_PRD_MYSQL -e "use information_schema;select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='$databasename' and table_name='$tablename';" | grep MB)
echo_blue $query
}
#
# 备份表
# @param $1 数据库名称
# @param $2 表名称
# @param $3 备份存放的目录
#
backup_table() {
local databasename=$1
local tablename=$2
local backupdir=$3
local backupfile=$backupdir/$tablename".sql"
$DUMP_PRD_MYSQL $databasename $tablename > $backupfile
# echo "$DUMP_PRD_MYSQL $databasename $tablename > $backupfile"
if [ "$?" -eq 0 ] ; then
echo $backupfile
fi
}
#
# 备份数据
# @param $1 数据库名称
# @param $2 表名称
#
confirm_option() {
local databasename=$1
local tablename=$2
echo -e "\n$tablename size is:"
echo -e "\t"$(show_table_size $databasename $tablename)
read -p "确定要备份 $(echo_blue $databasename) 的 $(echo_blue $tablename) 表吗? y/n:" answer
case $answer in
y|Y)
# 开始备份
echo -e "start backup ..."
BACKUP_FILE=$(backup_table $databasename $tablename $(get_directory $databasename))
echo -e "$BACKUP_FILE size is:\n\t" $(echo_blue $(du -sh $BACKUP_FILE))
echo -e "backup is OK"
;;
n|N)
echo_red "备份已取消!"
;;
esac
}
if [ -z "$1" ]; then
usage
fi
while [ -n "$1" ]
do
case "$1" in
-d|--database)
set_database_table_name $2 $3
show_help
confirm_option $2 $3
shift 2
;;
-s|--showtablesize)
set_database_table_name $2 $3
show_help
show_table_size $2 $3
shift 2
;;
-h|--help)
usage
;;
*)
usage
;;
esac
shift
done