如何快速重命名MySQL数据库(更改模式名称)？

脚步:

将行复制到记事本中.

将所有对"olddbname","newdbname","mypassword"(+可选"root")的引用替换为您的等价物.

在命令行上逐个执行(在提示时输入"y").

这是我使用的:

$ 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;

MySQL目前不支持通过其命令界面重命名数据库,但如果您可以访问MySQL存储其数据库的目录,则可以重命名数据库.对于默认的MySQL安装,这通常位于安装MySQL的目录下的Data目录中.在Data目录下找到要重命名的数据库的名称,然后重命名它.重命名目录可能会导致一些权限问题.意识到.

注意:必须先停止MySQL,然后才能重命名数据库

我建议创建一个新的数据库(使用您想要的名称),并将您需要的数据从旧的导出/导入到新的.很简单.

在PHPMyAdmin中重命名数据库时,它会创建转储,然后删除并使用新名称重新创建数据库.

那么有两种方法:

方法1:一种众所周知的重命名数据库模式的方法是使用Mysqldump转储模式并在另一个模式中恢复,然后删除旧模式(如果需要).

来自壳牌

 mysqldump emp > emp.out
 mysql -e "CREATE DATABASE employees;"
 mysql employees < emp.out 
 mysql -e "DROP DATABASE emp;"

尽管上述方法容易,但是耗费时间和空间.如果架构超过100GB怎么办？有些方法可以将上述命令组合在一起以节省空间,但不会节省时间.

为了解决这种情况,还有另一种重命名模式的快速方法,但是,在执行此操作时必须小心.

方法2: MySQL具有非常好的功能,可以重命名甚至适用于不同模式的表.此重命名操作是原子操作,在重命名时,没有其他人可以访问该表.这需要很短的时间才能完成,因为更改表的名称或其架构只是元数据更改.这是重命名的程序方法:

使用所需名称创建新数据库模式.使用MySQL的"RENAME TABLE"命令将表从旧模式重命名为新模式.删除旧的数据库架构. If there are views, triggers, functions, stored procedures in the schema, those will need to be recreated too.如果表上存在触发器,MySQL的"RENAME TABLE"将失败.为了解决这个问题,我们可以做以下事情:

1) Dump the triggers, events and stored routines in a separate file.使用-E,-R标志(除了转储触发器的-t -d)到mysqldump命令完成.一旦触发器被转储,我们将需要从模式中删除它们,以使RENAME TABLE命令起作用.

 $ mysqldump  -d -t -R -E > stored_routines_triggers_events.out

2)生成仅"BASE"表的列表.可以使用information_schema.TABLES表上的查询找到这些.

 mysql> select TABLE_NAME from information_schema.tables where 
    table_schema='' and TABLE_TYPE='BASE TABLE';

3)将视图转储到out文件中.可以使用同一个information_schema.TABLES表上的查询找到视图.

mysql> select TABLE_NAME from information_schema.tables where 
   table_schema='' and TABLE_TYPE='VIEW';
 $ mysqldump    … > views.out

4)删除old_schema中当前表的触发器.

mysql> DROP TRIGGER ;
...

5)重命名步骤#2中找到的所有"基础"表后,恢复上述转储文件.

mysql> RENAME TABLE .table_name TO .table_name;
...
$ mysql  < views.out
$ mysql  < stored_routines_triggers_events.out

使用上述方法的复杂性:我们可能需要为用户更新GRANTS,使其与正确的schema_name匹配.这些可以通过mysql.columns_priv,mysql.procs_priv,mysql.tables_priv,mysql.db表上的简单UPDATE修复,将old_schema名称更新为new_schema并调用"Flush privileges;".虽然"方法2"似乎比"方法1"复杂一点,但这完全可以编写脚本.一个简单的bash脚本以正确的顺序执行上述步骤,可以帮助您在下次重命名数据库模式时节省空间和时间.

Percona远程DBA团队编写了一个名为"rename_db"的脚本,其工作方式如下:

[root@dba~]# /tmp/rename_db
rename_db   

为了演示此脚本的使用,使用了示例模式"emp",创建了测试触发器,在该模式上存储了例程.将尝试使用脚本重命名数据库模式,这需要几秒钟才能完成,而不是耗时的转储/恢复方法.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| emp                |
| mysql              |
| performance_schema |
| test               |
+--------------------+


[root@dba ~]# time /tmp/rename_db localhost emp emp_test
create database emp_test DEFAULT CHARACTER SET latin1
drop trigger salary_trigger
rename table emp.__emp_new to emp_test.__emp_new
rename table emp._emp_new to emp_test._emp_new
rename table emp.departments to emp_test.departments
rename table emp.dept to emp_test.dept
rename table emp.dept_emp to emp_test.dept_emp
rename table emp.dept_manager to emp_test.dept_manager
rename table emp.emp to emp_test.emp
rename table emp.employees to emp_test.employees
rename table emp.salaries_temp to emp_test.salaries_temp
rename table emp.titles to emp_test.titles
loading views
loading triggers, routines and events
Dropping database emp

real    0m0.643s
user    0m0.053s
sys     0m0.131s


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| emp_test           |
| mysql              |
| performance_schema |
| test               |
+--------------------+

正如您在上面的输出中所看到的,数据库模式"emp"在不到一秒的时间内被重命名为"emp_test".最后,这是来自Percona的脚本,上面用于"方法2".

#!/bin/bash
# Copyright 2013 Percona LLC and/or its affiliates
set -e
if [ -z "$3" ]; then
    echo "rename_db   "
    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 "show create database $2\G" -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 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

对于那些Mac用户,Sequel Pro在Database菜单中有一个Rename Database选项. http://www.sequelpro.com/

脚步 :

点击http:// localhost/phpmyadmin /

选择您的数据库

单击"操作"选项卡

将有一个标签为"重命名数据库".添加新名称并选中"调整权限".

点击Go.

可以将数据库中的所有表重命名为另一个数据库,而无需执行完全转储和还原.

DROP PROCEDURE IF EXISTS mysql.rename_db;
DELIMITER ||
CREATE PROCEDURE mysql.rename_db(IN old_db VARCHAR(100), IN new_db VARCHAR(100))
BEGIN
SELECT CONCAT('CREATE DATABASE ', new_db, ';') `# create new database`;
SELECT CONCAT('RENAME TABLE `', old_db, '`.`', table_name, '` TO `', new_db, '`.`', table_name, '`;') `# alter table` FROM information_schema.tables WHERE table_schema = old_db;
SELECT CONCAT('DROP DATABASE `', old_db, '`;') `# drop old database`;
END||
DELIMITER ;

$ time mysql -uroot -e "call mysql.rename_db('db1', 'db2');" | mysql -uroot

但是目标数据库中的任何触发器都不会满意.您需要先删除它们,然后在重命名后重新创建它们.

mysql -uroot -e "call mysql.rename_db('test', 'blah2');" | mysql -uroot
ERROR 1435 (HY000) at line 4: Trigger in wrong schema

由于以下两个原因之一,这里的大多数答案都是错误的:

您不能只使用RENAME TABLE,因为可能有视图和触发器.如果有触发器,则RENAME TABLE失败

如果你想"快速"(按照问题中的要求)重命名一个大数据库,你不能使用mysqldump

Percona有一篇关于如何做到这一点的博客文章:https: //www.percona.com/blog/2013/12/24/renaming-database-schema-mysql/

和Simon R Jones发布的脚本(制作？),它完成了该帖子的建议.我修复了我在脚本中发现的错误.你可以在这里看到它:

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

这是它的副本:

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

将它保存到一个名为rename_db的文件中,chmod +x rename_db然后使脚本可执行,然后使用它./rename_db localhost old_db new_db

这是我编写的一个批处理文件,用于从命令行自动执行它,但它适用于Windows/MS-DOS.

语法是rename_mysqldb数据库newdatabase -u [user] -p [password]

:: ***************************************************************************
:: 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=

TodoInTX的存储过程并不适合我.这是我的抨击:

-- stored procedure rename_db: Rename a database my means of table copying.
-- Caveats: 
-- Will clobber any existing database with the same name as the 'new' database name.
-- ONLY copies tables; stored procedures and other database objects are not copied.
-- Tomer Altman (taltman@ai.sri.com)

delimiter //
DROP PROCEDURE IF EXISTS rename_db;
CREATE PROCEDURE rename_db(IN old_db VARCHAR(100), IN new_db VARCHAR(100))
BEGIN
    DECLARE current_table VARCHAR(100);
    DECLARE done INT DEFAULT 0;
    DECLARE old_tables CURSOR FOR select table_name from information_schema.tables where table_schema = old_db;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    SET @output = CONCAT('DROP SCHEMA IF EXISTS ', new_db, ';'); 
    PREPARE stmt FROM @output;
    EXECUTE stmt;

    SET @output = CONCAT('CREATE SCHEMA IF NOT EXISTS ', new_db, ';');
    PREPARE stmt FROM @output;
    EXECUTE stmt;

    OPEN old_tables;
    REPEAT
        FETCH old_tables INTO current_table;
        IF NOT done THEN
        SET @output = CONCAT('alter table ', old_db, '.', current_table, ' rename ', new_db, '.', current_table, ';');
        PREPARE stmt FROM @output;
        EXECUTE stmt;

        END IF;
    UNTIL done END REPEAT;

    CLOSE old_tables;

END//
delimiter ;

我提出了一个关于服务器故障的问题,试图通过使用MySQL代理恢复非常大的数据库来避免停机.我没有任何成功,但我最终意识到我想要的是RENAME DATABASE功能,因为由于数据库的大小,dump/import不是一个选项.

MySQL内置了一个RENAME TABLE功能,所以我最终编写了一个简单的Python脚本来为我完成这项工作.我已将它发布在GitHub上,以防它可能对其他人有用.

为方便起见,下面是一个小shellcript,必须使用两个参数执行:db-name和new db-name.

如果不在主目录中使用.my.cnf文件,则可能需要向mysql行添加login-parameters.请在执行此脚本之前进行备份.

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

最简单的方法是使用HeidiSQL软件.它是免费和开源的.它可以在Windows和任何带有Wine的 Linux上运行(在Linux,BSD,Solaris和Mac OS X上运行Windows应用程序).

要下载HeidiSQL,请转到http://www.heidisql.com/download.php.

要下载Wine,请转到http://www.winehq.org/.

要在HeidiSQL中重命名数据库,只需右键单击数据库名称并选择"编辑".然后输入新名称并按"确定".

这很简单.

对于Mac用户，您可以使用Sequel Pro（免费），它仅提供重命名数据库的选项。虽然它不会删除旧的数据库。

一旦打开相关的数据库，只需单击：Database->Rename database...

似乎没有人提到这，但这是另一种方式：

create database NewDatabaseName like OldDatabaseName;

然后为每个表执行以下操作：

create NewDatabaseName.tablename like OldDatabaseName.tablename;
insert into NewDataBaseName.tablename select * from OldDatabaseName.tablename;

然后，如果您愿意，

drop database OldDatabaseName;

这种方法的优势是可以在网络流量几乎为零的情况下在服务器上进行整个传输，因此比转储/还原要快得多。

如果您确实有存储过程/视图/等，则可能也要转移它们。