change - rename mysql schema




如何快速重命名MySQL數據庫(更改模式名稱)? (20)

簡單的方法

切換到數據庫目錄:

cd /var/lib/mysql/

關閉MySQL ...這很重要!

/etc/init.d/mysql stop

好的,這種方式不適用於InnoDB或BDB數據庫。

重命名數據庫:

mv old-name new-name

...或桌子...

cd database/

mv old-name.frm new-name.frm

mv old-name.MYD new-name.MYD

mv old-name.MYI new-name.MYI

重新啟動MySQL

/etc/init.d/mysql start

完成...

好的,這種方式不適用於InnoDB或BDB數據庫。 在這種情況下,您必須轉儲數據庫並重新導入它。

MySQL的MySQL手冊涵蓋了這一點。

通常我只是轉儲數據庫並用一個新名稱重新導入它。 對於非常大的數據庫,這不是一個選項。 顯然RENAME {DATABASE | SCHEMA} db_name TO new_db_name; RENAME {DATABASE | SCHEMA} db_name TO new_db_name; MySQL

這需要與InnoDB協同工作, InnoDB存儲的內容與MyISAM非常不同。


MySQL目前不支持通過其命令接口重命名數據庫,但如果您有權訪問MySQL存儲其數據庫的目錄,則可以重命名數據庫。 對於默認的MySQL安裝,這通常位於MySQL安裝目錄下的Data目錄中。 找到要在Data目錄下重命名的數據庫的名稱並重命名它。 重命名目錄可能會導致一些權限問題。 意識到。

注意:您必須先停止MySQL,然後才能重命名數據庫

我建議創建一個新的數據庫(使用你想要的名稱)並將你需要的數據從舊數據導出/導入到新數據庫中。 很簡單。


For mac users, you can use Sequel Pro (free), which just provide the option to rename Databases. Though it doesn't delete the old DB.

once open the relevant DB just click: Database --> Rename database...


Here is a quick way to generate renaming sql script, if you have many tables to move.

SELECT DISTINCT CONCAT('RENAME TABLE ', t.table_schema,'.', t.table_name, ' TO ',     
t.table_schema, "_archive", '.', t.table_name, ';' ) as Rename_SQL 
FROM information_schema.tables t
WHERE table_schema='your_db_name' ;

If you are using phpMyAdmin you can go to the "operations" tab once you have selected the database you want to rename. Then go to the last section "copy database to" (or something like that), give a name, and select the options below. In this case, I guess you must select "structure and data" and "create database before copying" checkboxes and, finally, press the "go" button in that section.

By the way, I'm using phpMyAdmin in Spanish so I'm not sure what the names of the sections are in English.


In MySQL Administrator do the following:

  1. Under Catalogs, create a new database schema.
  2. Go to Backup and create a backup of the old schema.
  3. Execute backup.
  4. Go to Restore and open the file created in step 3.
  5. Select 'Another Schema' under Target Schema and select the new database schema.
  6. Start Restore.
  7. Verify new schema and, if it looks good, delete the old one.

This works for all databases and works by renaming each table with maatkit mysql toolkit

Use mk-find to print and rename each table. The man page has many more options and examples

mk-find --dblike OLD_DATABASE --print --exec "RENAME TABLE %D.%N TO NEW_DATABASE.%N"

If you have maatkit installed ( which is very easy ), then this is the simplest way to do it.


in phpmyadmin you can easily rename the database

select database 

  goto operations tab

  in that rename Database to :

  type your new database name and click go

ask to drop old table and reload table data click OK in both

Your database is renamed


你可以使用這個shell腳本:

參考: 如何重命名MySQL數據庫?

#!/bin/bash
set -e # terminate execution on command failure

mysqlconn="mysql -u root -proot"
olddb=$1
newdb=$2
$mysqlconn -e "CREATE DATABASE $newdb"
params=$($mysqlconn -N -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES \
                           WHERE table_schema='$olddb'")
for name in $params; do
      $mysqlconn -e "RENAME TABLE $olddb.$name to $newdb.$name";
done;
$mysqlconn -e "DROP DATABASE $olddb"

它的工作:

$ sh rename_database.sh oldname newname

使用這幾個簡單的命令:

mysqldump -u username -p -v olddatabase > olddbdump.sql
mysqladmin -u username -p create newdatabase
mysql -u username -p newdatabase < olddbdump.sql

或者按照@Pablo Marin-Garcia的建議減少I / O使用:

mysqladmin -u username -p create newdatabase
mysqldump -u username -v olddatabase -p | mysql -u username -p -D newdatabase

在MySQL中模擬丟失的RENAME DATABASE命令:

  1. 創建一個新的數據庫
  2. 創建重命名查詢:

    SELECT CONCAT('RENAME TABLE ',table_schema,'.',table_name,
        ' TO ','new_schema.',table_name,';')
    FROM information_schema.TABLES
    WHERE table_schema LIKE 'old_schema';
    
  3. 運行該輸出

  4. 刪除舊數據庫

它來自於仿真MySQL中丟失的RENAME DATABASE命令


對於InnoDB,以下內容似乎有效:創建新的空數據庫,然後將每個表依次重新命名為新數據庫:

RENAME TABLE old_db.table TO new_db.table;

之後您需要調整權限。

對於在shell中編寫腳本,可以使用以下任一方法:

mysql -u username -ppassword old_db -sNe 'show tables' | while read table; \ 
    do mysql -u username -ppassword -sNe "rename table old_db.$table to new_db.$table"; done

要么

for table in `mysql -u root -ppassword -s -N -e "use old_db;show tables from old_db;"`; do mysql -u root -ppassword -s -N -e "use old_db;rename table old_db.$table to new_db.$table;"; done;

注意:選項-p和密碼之間沒有空格。 如果您的數據庫沒有密碼,請刪除-u username -ppassword部分。

另外,如果您有存儲過程,則可以在以後復制它們:

mysqldump -R old_db | mysql new_db

您可以使用SQL生成一個SQL腳本,將源數據庫中的每個表傳輸到目標數據庫。

您必須在運行由該命令生成的腳本之前創建目標數據庫。

你可以使用這兩個腳本中的任何一個(我最初建議前者和某人“改進”我的答案以使用GROUP_CONCAT 。請選擇,但我更喜歡原始的):

SELECT CONCAT('RENAME TABLE $1.', table_name, ' TO $2.', table_name, '; ')
FROM information_schema.TABLES 
WHERE table_schema='$1';

要么

SELECT GROUP_CONCAT('RENAME TABLE $1.', table_name, ' TO $2.', table_name SEPARATOR '; ')
FROM information_schema.TABLES 
WHERE table_schema='$1';

($ 1和$ 2分別是來源和目標)

這將生成一個SQL命令,您必須運行該命令。

請注意,對於具有大量表的數據庫, GROUP_CONCAT具有默認的長度限制。 您可以通過運行SET SESSION group_concat_max_len = 100000000;來更改該限制SET SESSION group_concat_max_len = 100000000; (或其他一些大數字)。


提出了一個關於服務器故障的問題,試圖通過使用MySQL代理恢復非常大的數據庫時避免停機。 我沒有取得任何成功,但最終我意識到我想要的是RENAME DATABASE功能,因為由於數據庫的大小,轉儲/導入不是選項。

MySQL內置了RENAME TABLE功能,因此我最終編寫了一個簡單的Python腳本來為我完成這項工作。 我已經將它發佈到GitHub上 ,以防其他人使用它。


我認為解決方案比較簡單,並且由一些開發人員提出。 phpMyAdmin對此有一個操作。

從phpMyAdmin,選擇你想要選擇的數據庫。 在標籤中有一個叫做操作,轉到重命名部分。 就這樣。

它的確如許多人所建議的那樣,使用新名稱創建一個新數據庫,將舊數據庫的所有表轉儲到新數據庫中並刪除舊數據庫。


最簡單的完全重命名的bullet-and-fool-proof方法(包括在最後刪除舊數據庫,因此它是重命名而不是副本)

mysqladmin -uroot -pmypassword create newdbname
mysqldump -uroot -pmypassword --routines olddbname | mysql -uroot -pmypassword newdbname
mysqladmin -uroot -pmypassword drop olddbname

腳步:

  1. 將行複製到記事本中。
  2. 用您的等價物替換對“olddbname”,“newdbname”,“mypassword”(+可選“root”)的所有引用。
  3. 在命令行上逐個執行(當出現提示時輸入“y”)。

為了您的方便,下面是一個小的shell腳本,必須用兩個參數執行:db-name和新的db-name。

如果您不在主目錄中使用.my.cnf文件,則可能需要將登錄參數添加到mysql-lines。 請在執行此腳本之前進行備份。

#!/usr/bin/env bash

mysql -e "CREATE DATABASE $2 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;"
for i in $(mysql -Ns $1 -e "show tables");do
    echo "$1.$i -> $2.$i"
    mysql -e "rename TABLE $1.$i to $2.$i"
done
mysql -e "DROP DATABASE $1"

由於以下兩個原因之一,大多數答案都是錯誤的:

  1. You cannot just use RENAME TABLE, because there might be views and triggers. If there are triggers, RENAME TABLE fails
  2. You cannot use mysqldump if you want to "quickly" (as requested in the question) rename a big database

Percona has a blog post about how to do this well: https://www.percona.com/blog/2013/12/24/renaming-database-schema-mysql/

and script posted (made?) by Simon R Jones that does what is suggested in that post. I fixed a bug I found in the script. You can see it here:

https://gist.github.com/ryantm/76944318b0473ff25993ef2a7186213d

Here is a copy of it:

#!/bin/bash
# Copyright 2013 Percona LLC and/or its affiliates
# @see https://www.percona.com/blog/2013/12/24/renaming-database-schema-mysql/
set -e
if [ -z "$3" ]; then
    echo "rename_db <server> <database> <new_database>"
    exit 1
fi
db_exists=`mysql -h $1 -e "show databases like '$3'" -sss`
if [ -n "$db_exists" ]; then
    echo "ERROR: New database already exists $3"
    exit 1
fi
TIMESTAMP=`date +%s`
character_set=`mysql -h $1 -e "SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = '$2'" -sss`
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 1
fi
echo "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 triggers\G" | 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}.dump
fi
mysqldump -h $1 $2 -d -t -R -E > /tmp/${2}_triggers${TIMESTAMP}.dump
for TRIGGER in $TRIGGERS; do
    echo "drop trigger $TRIGGER"
    mysql -h $1 $2 -e "drop trigger $TRIGGER"
done
for 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"
done
if [ -n "$VIEWS" ]; then
    echo "loading views"
    mysql -h $1 $3 < /tmp/${2}_views${TIMESTAMP}.dump
fi
echo "loading triggers, routines and events"
mysql -h $1 $3 < /tmp/${2}_triggers${TIMESTAMP}.dump
TABLES=`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"
fi
if [ `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';"
fi
if [ `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';"
fi
if [ `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';"
fi
if [ `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';"
fi
if [ -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

Save it to a file called rename_db and make the script executable with chmod +x rename_db then use it like ./rename_db localhost old_db new_db


這是我使用的:

$ mysqldump -u root -p olddb >~/olddb.sql
$ mysql -u root -p
mysql> create database newdb;
mysql> use newdb
mysql> source ~/olddb.sql
mysql> drop database olddb;

這裡是我寫的一個批處理文件,用於從命令行自動執行它,但是它用於Windows / MS-DOS。

語法是rename_mysqldb數據庫newdatabase -u [用戶] -p [密碼]

:: ***************************************************************************
:: FILE: RENAME_MYSQLDB.BAT
:: ***************************************************************************
:: DESCRIPTION
:: This is a Windows /MS-DOS batch file that automates renaming a MySQL database 
:: by using MySQLDump, MySQLAdmin, and MySQL to perform the required tasks.
:: The MySQL\bin folder needs to be in your environment path or the working directory.
::
:: WARNING: The script will delete the original database, but only if it successfully
:: created the new copy. However, read the disclaimer below before using.
::
:: DISCLAIMER
:: This script is provided without any express or implied warranties whatsoever.
:: The user must assume the risk of using the script.
::
:: You are free to use, modify, and distribute this script without exception.
:: ***************************************************************************

:INITIALIZE
@ECHO OFF
IF [%2]==[] GOTO HELP
IF [%3]==[] (SET RDB_ARGS=--user=root) ELSE (SET RDB_ARGS=%3 %4 %5 %6 %7 %8 %9)
SET RDB_OLDDB=%1
SET RDB_NEWDB=%2
SET RDB_DUMPFILE=%RDB_OLDDB%_dump.sql
GOTO START

:START
SET RDB_STEP=1
ECHO Dumping "%RDB_OLDDB%"...
mysqldump %RDB_ARGS% %RDB_OLDDB% > %RDB_DUMPFILE%
IF %ERRORLEVEL% NEQ 0 GOTO ERROR_ABORT
SET RDB_STEP=2
ECHO Creating database "%RDB_NEWDB%"...
mysqladmin %RDB_ARGS% create %RDB_NEWDB%
IF %ERRORLEVEL% NEQ 0 GOTO ERROR_ABORT
SET RDB_STEP=3
ECHO Loading dump into "%RDB_NEWDB%"...
mysql %RDB_ARGS% %RDB_NEWDB% < %RDB_DUMPFILE%
IF %ERRORLEVEL% NEQ 0 GOTO ERROR_ABORT
SET RDB_STEP=4
ECHO Dropping database "%RDB_OLDDB%"...
mysqladmin %RDB_ARGS% drop %RDB_OLDDB% --force
IF %ERRORLEVEL% NEQ 0 GOTO ERROR_ABORT
SET RDB_STEP=5
ECHO Deleting dump...
DEL %RDB_DUMPFILE%
IF %ERRORLEVEL% NEQ 0 GOTO ERROR_ABORT
ECHO Renamed database "%RDB_OLDDB%" to "%RDB_NEWDB%".
GOTO END

:ERROR_ABORT
IF %RDB_STEP% GEQ 3 mysqladmin %RDB_ARGS% drop %NEWDB% --force
IF %RDB_STEP% GEQ 1 IF EXIST %RDB_DUMPFILE% DEL %RDB_DUMPFILE%
ECHO Unable to rename database "%RDB_OLDDB%" to "%RDB_NEWDB%".
GOTO END

:HELP
ECHO Renames a MySQL database.
ECHO Usage: %0 database new_database [OPTIONS]
ECHO Options: Any valid options shared by MySQL, MySQLAdmin and MySQLDump.
ECHO          --user=root is used if no options are specified.
GOTO END    

:END
SET RDB_OLDDB=
SET RDB_NEWDB=
SET RDB_ARGS=
SET RDB_DUMP=
SET RDB_STEP=




rename