MySQL rename database如何做？

標籤：

雖然MySQL裡面有rename database的文法，但是只是在5.1.7 to 5.1.23提供的，其他版本並沒有，要想做rename操作該如何做呢？percona提供了一個shell

 

#!/bin/bash# Copyright 2013 Percona LLC and/or its affiliatesset -eif [ -z "$3" ]; then    echo "rename_db <server> <database> <new_database>"    exit 1fidb_exists=`mysql -h $1 -e "show databases like ‘$3‘" -sss`if [ -n "$db_exists" ]; then    echo "ERROR: New database already exists $3"    exit 1fiTIMESTAMP=`date +%s`character_set=`mysql -h $1 -e "show create database $2G" -sss | grep ^Create | awk -F‘CHARACTER SET ‘ ‘{print $2}‘ | awk ‘{print $1}‘`TABLES=`mysql -h $1 -e "select TABLE_NAME from information_schema.tables where table_schema=‘$2‘ and TABLE_TYPE=‘BASE TABLE‘" -sss`STATUS=$?if [ "$STATUS" != 0 ] || [ -z "$TABLES" ]; then    echo "Error retrieving tables from $2"    exit 1fiecho "create database $3 DEFAULT CHARACTER SET $character_set"mysql -h $1 -e "create database $3 DEFAULT CHARACTER SET $character_set"TRIGGERS=`mysql -h $1 $2 -e "show triggersG" | grep Trigger: | awk ‘{print $2}‘`VIEWS=`mysql -h $1 -e "select TABLE_NAME from information_schema.tables where table_schema=‘$2‘ and TABLE_TYPE=‘VIEW‘" -sss`if [ -n "$VIEWS" ]; then    mysqldump -h $1 $2 $VIEWS > /tmp/${2}_views${TIMESTAMP}.dumpfimysqldump -h $1 $2 -d -t -R -E > /tmp/${2}_triggers${TIMESTAMP}.dumpfor TRIGGER in $TRIGGERS; do    echo "drop trigger $TRIGGER"    mysql -h $1 $2 -e "drop trigger $TRIGGER"donefor TABLE in $TABLES; do    echo "rename table $2.$TABLE to $3.$TABLE"    mysql -h $1 $2 -e "SET FOREIGN_KEY_CHECKS=0; rename table $2.$TABLE to $3.$TABLE"doneif [ -n "$VIEWS" ]; then    echo "loading views"    mysql -h $1 $3 < /tmp/${2}_views${TIMESTAMP}.dumpfiecho "loading triggers, routines and events"mysql -h $1 $3 < /tmp/${2}_triggers${TIMESTAMP}.dumpTABLES=`mysql -h $1 -e "select TABLE_NAME from information_schema.tables where table_schema=‘$2‘ and TABLE_TYPE=‘BASE TABLE‘" -sss`if [ -z "$TABLES" ]; then    echo "Dropping database $2"    mysql -h $1 $2 -e "drop database $2"fiif [ `mysql -h $1 -e "select count(*) from mysql.columns_priv where db=‘$2‘" -sss` -gt 0 ]; then    COLUMNS_PRIV="    UPDATE mysql.columns_priv set db=‘$3‘ WHERE db=‘$2‘;"fiif [ `mysql -h $1 -e "select count(*) from mysql.procs_priv where db=‘$2‘" -sss` -gt 0 ]; then    PROCS_PRIV="    UPDATE mysql.procs_priv set db=‘$3‘ WHERE db=‘$2‘;"fiif [ `mysql -h $1 -e "select count(*) from mysql.tables_priv where db=‘$2‘" -sss` -gt 0 ]; then    TABLES_PRIV="    UPDATE mysql.tables_priv set db=‘$3‘ WHERE db=‘$2‘;"fiif [ `mysql -h $1 -e "select count(*) from mysql.db where db=‘$2‘" -sss` -gt 0 ]; then    DB_PRIV="    UPDATE mysql.db set db=‘$3‘ WHERE db=‘$2‘;"fiif [ -n "$COLUMNS_PRIV" ] || [ -n "$PROCS_PRIV" ] || [ -n "$TABLES_PRIV" ] || [ -n "$DB_PRIV" ]; then    echo "IF YOU WANT TO RENAME the GRANTS YOU NEED TO RUN ALL OUTPUT BELOW:"    if [ -n "$COLUMNS_PRIV" ]; then echo "$COLUMNS_PRIV"; fi    if [ -n "$PROCS_PRIV" ]; then echo "$PROCS_PRIV"; fi    if [ -n "$TABLES_PRIV" ]; then echo "$TABLES_PRIV"; fi    if [ -n "$DB_PRIV" ]; then echo "$DB_PRIV"; fi    echo "    flush privileges;"fi

 

來源：

https://www.percona.com/blog/2013/12/24/renaming-database-schema-mysql/ 

 

MySQL rename database如何做？