数据库重命名脚本
#!/bin/bash
echo -e "\033[34m 此脚本功能为修改数据库名称（需要新建数据库后将数据迁移到新数据库），请在能执行mysql命令的服务器执行！！ \033[0m"
echo -e "\033[34m 警告！！执行本操作前请备份数据库 \033[0m"
function printConsole(){
echo ""
echo -e "\033[34m 1.重命名TEST数据库 \033[0m"
echo -e "\033[34m 2.重命名自定义数据库 \033[0m"
echo ""
echo ====================================
echo ""
read -p "Select Number(Ctrl+C to EXIT):" num
echo ""
export NUM="$num"
}
function renameTest(){
read -p "请输入TEST系统MySQL IP：（默认localhost）" testMysqlHost
if [ -z "${testMysqlHost}" ];then
testMysqlHost='localhost'
fi
export TEST_MYSQL_HOST="$testMysqlHost"
read -p "请输入TEST系统MYSQL PORT：（默认3306）" testMysqlPort
if [ -z "${testMysqlPort}" ];then
testMysqlPort='3306'
fi
export TEST_MYSQL_PORT="$testMysqlPort"
read -p "请输入TEST系统Mysql用户名：" testMysqlUserName
export TEST_MYSQL_USER_NAME="$testMysqlUserName"
read -p "请输入TEST系统Mysql密码：" testMysqlPassword
export TEST_MYSQL_PASSWORD="$testMysqlPassword"
ifCreateNew='Y'
export IF_CREATE_NEW_DB="$ifCreateNew"
ifDeleteOld='Y'
export IF_DELETE_OLD_DB="$ifDeleteOld"
echo -e "\033[34m 重命名test auth数据库 \033[0m"
export OLD_DB_NAME="test-auth"
export NEW_DB_NAME="test_auth"
rename
echo -e "\033[34m 重命名test db数据库 \033[0m"
export OLD_DB_NAME="test-db"
export NEW_DB_NAME="test_db"
rename
echo -e "\033[34m 重命名test nacos数据库 \033[0m"
export OLD_DB_NAME="test-register-config"
export NEW_DB_NAME="test_register_config"
rename
}
function configParam(){
read -p "请输入MySQL IP：（默认localhost）" testMysqlHost
if [ -z "${testMysqlHost}" ];then
testMysqlHost='localhost'
fi
export TEST_MYSQL_HOST="$testMysqlHost"
read -p "请输入MYSQL PORT：（默认3306）" testMysqlPort
if [ -z "${testMysqlPort}" ];then
testMysqlPort='3306'
fi
export TEST_MYSQL_PORT="$testMysqlPort"
read -p "请输入Mysql用户名：" testMysqlUserName
export TEST_MYSQL_USER_NAME="$testMysqlUserName"
read -p "请输入Mysql密码：" testMysqlPassword
export TEST_MYSQL_PASSWORD="$testMysqlPassword"
read -p "请输入旧数据库名称：" olddb
export OLD_DB_NAME="$olddb"
read -p "请输入新数据库名称：" newdb
export NEW_DB_NAME="$newdb"
read -p "是否需要创建新数据库（ Y/N 默认 Y ）：" ifCreateNew
if [ -z "${ifCreateNew}" ];then
ifCreateNew='Y'
fi
export IF_CREATE_NEW_DB="$ifCreateNew"
read -p "是否需要删除旧数据库（ Y/N 默认 N 建议手动删除 ）：" ifDeleteOld
if [ -z "${ifDeleteOld}" ];then
ifDeleteOld='N'
fi
export IF_DELETE_OLD_DB="$ifDeleteOld"
}
function rename(){
mysqlconn="mysql -u ${TEST_MYSQL_USER_NAME} -p${TEST_MYSQL_PASSWORD} -S /var/lib/mysql/mysql.sock -h ${TEST_MYSQL_HOST} -P ${TEST_MYSQL_PORT}"
#创建新数据库
if [[ ${IF_CREATE_NEW_DB} == "Y" ]];then
$mysqlconn -e "CREATE DATABASE ${NEW_DB_NAME} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
fi
#查询数据库表
params=$($mysqlconn -N -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='${OLD_DB_NAME}'")
tableParma="\`"
#数据库表迁移
for name in $params
do
$mysqlconn -e "RENAME TABLE $tableParam${OLD_DB_NAME}$tableParam.$tableParam$name$tableParam to $tableParam${NEW_DB_NAME}$tableParam.$tableParam$name$tableParam"
$mysqlconn -e "ALTER TABLE ${NEW_DB_NAME}.$name CHARACTER SET = utf8mb4, COLLATE = utf8mb4_unicode_ci"
echo -e "\033[34m 数据表$name 已迁移至新数据库${NEW_DB_NAME} \033[0m"
done
#删除旧数据库
if [[ $ifDeleteOld == "Y" ]];then
$mysqlconn -e "DROP DATABASE $tableParam${OLD_DB_NAME}$tableParam"
fi
#$mysqlconn -e "DROP DATABASE $olddb"
}
function callback(){
case $NUM in
1)
renameTest
printConsole
callback
;;
2)
configParam
rename
printConsole
callback
;;
*)
echo -e "\033[31m 输入错误，请重新输入！！ \033[0m"
printConsole
callback
esac
}
printConsole
callback