Linux 重命名 MySQL 数据库

Rename MySQL database

提问by Dhileepan

I created a database with the name of hrms. Now I need to change database name to sunhrm. But, It is disabled in MySQL workbench. Can I do that on the Linux server itself?

我创建了一个名为hrms. 现在我需要将数据库名称更改为sunhrm. 但是，它在 MySQL 工作台中被禁用。我可以在 Linux 服务器上这样做吗？

采纳答案by New Alexandria

I don't think you can do this. I think you'll need to dump that database, create the newly named one and then import the dump.

我不认为你能做到这一点。我认为您需要转储该数据库，创建新命名的数据库，然后导入转储。

If this is a live system you'll need to take it down. If you cannot, then you will need to setup replication from this database to the new one.

如果这是一个实时系统，您需要将其关闭。如果不能，则需要设置从该数据库到新数据库的复制。

If you want to see the commands to do this, @jan has the details.

如果您想查看执行此操作的命令，@jan 有详细信息。

回答by Luke Wyatt

In short no. It is generally thought to be too dangerous to rename a database. MySQL had that feature for a bit, but it was removed. You would be better off using the workbench to export both the schema and data to SQL then changing the CREATE DATABASE name there before you run/import it.

总之没有。通常认为重命名数据库太危险了。MySQL 曾经有过该功能，但后来被删除了。您最好使用工作台将架构和数据导出到 SQL，然后在运行/导入之前更改 CREATE DATABASE 名称。

回答by Cristian Porta

You can do it by RENAME statement for each table in your "current_db" after create the new schema "other_db"

在创建新架构“other_db”后，您可以通过 RENAME 语句为“current_db”中的每个表执行此操作

RENAME TABLE current_db.tbl_name TO other_db.tbl_name

Source Rename Table Syntax

源重命名表语法

回答by jan

In case you need to do that from the command line, just copy, adapt & paste this snippet:

如果您需要从命令行执行此操作，只需复制、调整和粘贴此代码段：

mysql -e "CREATE DATABASE \`new_database\`;"
for table in `mysql -B -N -e "SHOW TABLES;" old_database`
do 
  mysql -e "RENAME TABLE \`old_database\`.\`$table\` to \`new_database\`.\`$table\`"
done
mysql -e "DROP DATABASE \`old_database\`;"

回答by Dhileepan

First backup the old database called HRMS and edit the script file with replace the word HRMS to SUNHRM. After this step import the database file to the mysql

首先备份名为 HRMS 的旧数据库并编辑脚本文件，将 HRMS 一词替换为 SUNHRM。在这一步之后将数据库文件导入到mysql

回答by user1972813

For impatient mysql users (like me), the solution is:

对于不耐烦的mysql用户（比如我），解决办法是：

/etc/init.d/mysql stop
mv /var/lib/mysql/old_database /var/lib/mysql/new_database 
/etc/init.d/mysql start

回答by jetole

You can create a new database exactly as the previous database existed and then drop the old database when you're done. Use the mysqldump tool to create a .sql backup of the database via mysqldump orig_db > orig_db.sqlor if you need to use a username and password then run mysqldump -u root -p orig_db > orig_db.sql. orig_db is the name of the database you want to "rename", root would be the user you're logging in as and orig_db.sql would be the file created containing the backup. Now create a new, empty database with the name you want for the database. For example, mysql -u root -p -e "create database new_db". Once that's done, then run mysql -u root -p new_db < orig_db.sql. new_db now exists as a perfect copy of orig_db. You can then drop the original database as you now have it existing in the new database with the database name you wanted.

您可以完全按照先前数据库的存在创建一个新数据库，然后在完成后删除旧数据库。使用 mysqldump 工具创建数据库的 .sql 备份，mysqldump orig_db > orig_db.sql或者如果您需要使用用户名和密码，然后运行mysqldump -u root -p orig_db > orig_db.sql。orig_db 是您要“重命名”的数据库的名称，root 将是您登录的用户，而 orig_db.sql 将是创建的包含备份的文件。现在使用您想要的数据库名称创建一个新的空数据库。例如，mysql -u root -p -e "create database new_db"。完成后，然后运行mysql -u root -p new_db < orig_db.sql。new_db 现在作为 orig_db 的完美副本存在。然后，您可以删除原始数据库，因为您现在已将其以所需的数据库名称存在于新数据库中。

The short, quick steps without all the above explanation are:

没有上述所有解释的简短而快速的步骤是：

1. mysqldump -u root -p original_database > original_database.sql
2. mysql -u root -p -e "create database my_new_database"
3. mysql -u root -p my_new_database < original_database.sql
4. mysql -u root -p -e drop database originl_database

1. mysqldump -u root -p original_database > original_database.sql
2. mysql -u root -p -e "create database my_new_database"
3. mysql -u root -p my_new_database < original_database.sql
4. mysql -u root -p -e drop database originl_database

Hope this helps and this is a reliable means to accomplish it without using some ad-hoc method that will corrupt your data and create inconsistencies.

希望这会有所帮助，这是一种可靠的方法来完成它，而无需使用一些会破坏您的数据并造成不一致的临时方法。

回答by Hryhorii Hrebiniuk

It's possible to copy database via mysqldump command without storing dump into file:

可以通过 mysqldump 命令复制数据库而不将转储存储到文件中：

1. mysql -u root -p -e "create database my_new_database"
2. mysqldump -u root -p original_database | mysql -u root -p my_new_database
3. mysql -u root -p -e "drop database original_database"

1. mysql -u root -p -e "create database my_new_database"
2. mysqldump -u root -p original_database | mysql -u root -p my_new_database
3. mysql -u root -p -e "drop database original_database"

回答by Adarsha

I used following method to rename the database

我使用以下方法重命名数据库

1. take backup of the file using mysqldump or any DB tool eg heidiSQL,mysql administrator etc

2. Open back up (eg backupfile.sql) file in some text editor.

3. Search and replace the database name and save file.

4. Restore the edited SQL file

1. 使用 mysqldump 或任何数据库工具（例如 heidiSQL、mysql 管理员等）备份文件

2. 在一些文本编辑器中打开备份（例如 backupfile.sql）文件。

3. 搜索并替换数据库名称并保存文件。

4. 恢复编辑后的 ​​SQL 文件

回答by Sathish D

Well there are 2 methods:

那么有2种方法：

Method 1: A well-known method for renaming database schema is by dumping the schema using Mysqldump and restoring it in another schema, and then dropping the old schema (if needed).

方法 1：重命名数据库模式的一种众所周知的方法是使用 Mysqldump 转储模式并将其恢复到另一个模式中，然后删除旧模式（如果需要）。

From Shell

从 Shell

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

Although the above method is easy, it is time and space consuming. What if the schema is more than a 100GB?There are methods where you can pipe the above commands together to save on space, however it will not save time.

上述方法虽然简单，但费时费力。如果架构超过100GB 怎么办？有一些方法可以将上述命令组合在一起以节省空间，但它不会节省时间。

To remedy such situations, there is another quick method to rename schemas, however, some care must be taken while doing it.

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

Method 2: MySQL has a very good feature for renaming tables that even works across different schemas. This rename operation is atomic and no one else can access the table while its being renamed. This takes a short time to complete since changing a table's name or its schema is only a metadata change. Here is procedural approach at doing the rename:

方法 2：MySQL 有一个非常好的重命名表的功能，它甚至可以跨不同模式工作。此重命名操作是原子操作，在重命名表时，其他人无法访问该表。这需要很短的时间才能完成，因为更改表的名称或其架构只是元数据更改。这是进行重命名的程序方法：

1. Create the new database schema with the desired name.
2. Rename the tables from old schema to new schema, using MySQL's “RENAME TABLE” command.
3. Drop the old database schema.

1. 创建具有所需名称的新数据库模式。
2. 使用 MySQL 的“RENAME TABLE”命令将表从旧模式重命名为新模式。
3. 删除旧的数据库架构。

If there are views, triggers, functions, stored proceduresin the schema, those will need to be recreated too. MySQL's “RENAME TABLE” fails if there are triggers exists on the tables. To remedy this we can do the following things :

如果views, triggers, functions, stored procedures模式中有，则也需要重新创建。如果表上存在触发器，MySQL 的“RENAME TABLE”将失败。为了解决这个问题，我们可以做以下事情：

1)Dump the triggers, events and stored routines in a separate file.This done using -E, -R flags (in addition to -t -d which dumps the triggers) to the mysqldump command. Once triggers are dumped, we will need to drop them from the schema, for RENAME TABLE command to work.

1)Dump the triggers, events and stored routines in a separate file.这使用 -E、-R 标志（除了 -t -d 转储触发器）到 mysqldump 命令完成。一旦触发器被转储，我们需要将它们从架构中删除，以便 RENAME TABLE 命令工作。

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

2)Generate a list of only “BASE” tables. These can be found using a query on information_schema.TABLES table.

2)生成仅包含“BASE”表的列表。这些可以使用对 information_schema.TABLES 表的查询找到。

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

3)Dump the views in an out file. Views can be found using a query on the same information_schema.TABLES table.

3)将视图转储到输出文件中。可以使用对相同 information_schema.TABLES 表的查询找到视图。

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

4)Drop the triggers on the current tables in the old_schema.

4)在 old_schema 中删除当前表上的触发器。

mysql> DROP TRIGGER <trigger_name>;
...

5)Restore the above dump files once all the “Base” tables found in step #2 are renamed.

5)一旦在步骤#2 中找到的所有“基本”表都被重命名，就恢复上述转储文件。

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

Intricacies with above methods : We may need to update the GRANTSfor users such that they match the correct schema_name. These could fixed with a simple UPDATEon mysql.columns_priv, mysql.procs_priv, mysql.tables_priv, mysql.db tables updating the old_schema nameto new_schemaand calling “Flush privileges;”. Although “method 2″ seems a bit more complicated than the “method 1″, this is totally scriptable. A simple bash script to carry out the above steps in proper sequence, can help you save space and time while renaming database schemas next time.

上述方法的复杂性：我们可能需要更新GRANTS用户，以便他们匹配正确的 schema_name。这些可以通过一个简单的UPDATEon mysql.columns_priv, mysql.procs_priv, mysql.tables_priv, mysql.db 表来修复，更新old_schema nametonew_schema并调用“Flush privileges;”。尽管“ method 2”看起来比“ method 1”复杂一些，但这完全可以编写脚本。一个简单的 bash 脚本按适当的顺序执行上述步骤，可以帮助您在下次重命名数据库模式时节省空间和时间。

The Percona Remote DBA team have written a script called “rename_db” that works in the following way :

Percona 远程 DBA 团队编写了一个名为“rename_db”的脚本，其工作方式如下：

[root@dba~]# /tmp/rename_db
rename_db <server> <database> <new_database>

To demonstrate the use of this script, used a sample schema “emp”, created test triggers, stored routines on that schema. Will try to rename the database schema using the script, which takes some seconds to complete as opposed to time consuming dump/restore method.

为了演示此脚本的使用，使用了示例模式“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               |
+--------------------+

As you can see in the above output the database schema “emp” was renamed to “emp_test” in less than a second. Lastly, This is the script from Percona that is used above for “method 2″.

正如您在上面的输出中看到的，数据库模式“emp”在不到一秒钟的时间内被重命名为“emp_test”。最后，这是来自 Percona 的脚本，用于上面的“ method 2”。

#!/bin/bash
# Copyright 2013 Percona LLC and/or its affiliates
set -e
if [ -z "" ]; then
    echo "rename_db <server> <database> <new_database>"
    exit 1
fi
db_exists=`mysql -h  -e "show databases like ''" -sss`
if [ -n "$db_exists" ]; then
    echo "ERROR: New database already exists "
    exit 1
fi
TIMESTAMP=`date +%s`
character_set=`mysql -h  -e "show create database \G" -sss | grep ^Create | awk -F'CHARACTER SET ' '{print }' | awk '{print }'`
TABLES=`mysql -h  -e "select TABLE_NAME from information_schema.tables where table_schema='' and TABLE_TYPE='BASE TABLE'" -sss`
STATUS=$?
if [ "$STATUS" != 0 ] || [ -z "$TABLES" ]; then
    echo "Error retrieving tables from "
    exit 1
fi
echo "create database  DEFAULT CHARACTER SET $character_set"
mysql -h  -e "create database  DEFAULT CHARACTER SET $character_set"
TRIGGERS=`mysql -h   -e "show triggers\G" | grep Trigger: | awk '{print }'`
VIEWS=`mysql -h  -e "select TABLE_NAME from information_schema.tables where table_schema='' and TABLE_TYPE='VIEW'" -sss`
if [ -n "$VIEWS" ]; then
    mysqldump -h   $VIEWS > /tmp/_views${TIMESTAMP}.dump
fi
mysqldump -h   -d -t -R -E > /tmp/_triggers${TIMESTAMP}.dump
for TRIGGER in $TRIGGERS; do
    echo "drop trigger $TRIGGER"
    mysql -h   -e "drop trigger $TRIGGER"
done
for TABLE in $TABLES; do
    echo "rename table .$TABLE to .$TABLE"
    mysql -h   -e "SET FOREIGN_KEY_CHECKS=0; rename table .$TABLE to .$TABLE"
done
if [ -n "$VIEWS" ]; then
    echo "loading views"
    mysql -h   < /tmp/_views${TIMESTAMP}.dump
fi
echo "loading triggers, routines and events"
mysql -h   < /tmp/_triggers${TIMESTAMP}.dump
TABLES=`mysql -h  -e "select TABLE_NAME from information_schema.tables where table_schema='' and TABLE_TYPE='BASE TABLE'" -sss`
if [ -z "$TABLES" ]; then
    echo "Dropping database "
    mysql -h   -e "drop database "
fi
if [ `mysql -h  -e "select count(*) from mysql.columns_priv where db=''" -sss` -gt 0 ]; then
    COLUMNS_PRIV="    UPDATE mysql.columns_priv set db='' WHERE db='';"
fi
if [ `mysql -h  -e "select count(*) from mysql.procs_priv where db=''" -sss` -gt 0 ]; then
    PROCS_PRIV="    UPDATE mysql.procs_priv set db='' WHERE db='';"
fi
if [ `mysql -h  -e "select count(*) from mysql.tables_priv where db=''" -sss` -gt 0 ]; then
    TABLES_PRIV="    UPDATE mysql.tables_priv set db='' WHERE db='';"
fi
if [ `mysql -h  -e "select count(*) from mysql.db where db=''" -sss` -gt 0 ]; then
    DB_PRIV="    UPDATE mysql.db set db='' WHERE db='';"
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