数据库重命名脚本

由于原本的数据库命名不规范，需要进行重新命名，最终确定方案为新建数据库后迁移表，以下为脚本。

#!/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