如何重命名 MySQL 数据库(更改架构名称)?
如何快速重命名 MySQL 数据库(更改其架构名称)?
通常我只是转储数据库并使用新名称重新导入它。 对于非常大的数据库来说,这不是一个选项。 显然重命名{数据库| SCHEMA} db_name TO new_db_name;
做了坏事,仅存在于少数版本中,总体来说是一个坏主意。
How do I quickly rename a MySQL database (change its schema name)?
Usually I just dump a database and re-import it with a new name. This is not an option for very big databases. Apparently RENAME {DATABASE | SCHEMA} db_name TO new_db_name;
does bad things, exists only in a handful of versions, and is a bad idea overall.
This needs to work with InnoDB, which stores things very differently than MyISAM.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(30)
这里的大多数答案都是错误的,原因有两个:
Percona 有一篇关于如何做好此操作的博客文章:
https://www.percona.com/blog/ 2013/12/24/renaming-database-schema-mysql/
和 Simon R Jones 发布(制作?)的脚本执行了该文章中建议的操作。 我修复了脚本中发现的错误。 您可以在这里看到它:
https://gist.github.com/ryantm/76944318b0473ff25993ef2a7186213d
这里是它的副本:
将其保存到名为
rename_db
的文件中,并使用chmod +x rename_db
使脚本可执行,然后像./rename_db localhost old_db new_db< /代码>
Most of the answers here are wrong for one of two reasons:
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:
Save it to a file called
rename_db
and make the script executable withchmod +x rename_db
then use it like./rename_db localhost old_db new_db
MySQL 目前不支持通过其命令界面重命名数据库,但如果您有权访问 MySQL 存储其数据库的目录,则可以重命名数据库。 对于默认的 MySQL 安装,这通常位于 MySQL 安装目录下的 Data 目录中。 在Data目录下找到要重命名的数据库名称并重命名。 不过,重命名目录可能会导致一些权限问题。 意识到。
注意:您必须先停止MySQL,然后才能重命名数据库。
我建议创建一个新数据库(使用您想要的名称)并将您需要的数据从旧数据库导出/导入到新数据库。 很简单。
MySQL does not support the renaming of a database through its command interface at the moment, but you can rename the database if you have access to the directory in which MySQL stores its databases. For default MySQL installations this is usually in the Data directory under the directory where MySQL was installed. Locate the name of the database you want to rename under the Data directory and rename it. Renaming the directory could cause some permissions issues though. Be aware.
Note: You must stop MySQL before you can rename the database
I would recommend creating a new database (using the name you want) and export/import the data you need from the old to the new. Pretty simple.
对于 Mac 用户,Sequel Pro 在“数据库”菜单中有一个“重命名数据库”选项。
http://www.sequelpro.com/
For those who are Mac users, Sequel Pro has a Rename Database option in the Database menu.
http://www.sequelpro.com/
似乎没有人提到这一点,但这里有另一种方法:
然后对每个表执行:
然后,如果您愿意,
这种方法的优点是在网络流量接近零的服务器上进行整个传输,因此它会比转储/恢复。
如果您确实有存储过程/视图/等,您可能也想传输它们。
Seems noone mentioned this but here is another way:
then for each table do:
then, if you want to,
This approach would have the advantage of doing the entire transfer on server with near zero network traffic, so it will go a lot faster than a dump/restore.
If you do have stored procedures/views/etc you might want to transfer them as well.
可以将一个数据库中的所有表重命名为另一个数据库下的表,而无需执行完整转储和恢复。
然而,目标数据库中的任何触发器都不会满意。 您需要先删除它们,然后在重命名后重新创建它们。
It is possible to rename all tables within a database to be under another database without having to do a full dump and restore.
However any triggers in the target db will not be happy. You'll need to drop them first then recreate them after the rename.
对于 Mac 用户,您可以使用
Sequel Pro
(免费),它只提供重命名数据库的选项。 虽然它不会删除旧数据库。打开相关数据库后,只需单击:
Database
-->重命名数据库...
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...
为了您的方便,下面是一个小的 shell 脚本,必须使用两个参数执行:db-name 和 new db-name。
如果您不使用主目录中的 .my.cnf 文件,您可能需要将登录参数添加到 mysql 行。 执行此脚本前请先做好备份。
For your convenience, below is a small shellscript that has to be executed with two parameters: db-name and new db-name.
You might need to add login-parameters to the mysql-lines if you don't use the .my.cnf-file in your home-directory. Please make a backup before executing this script.
这是我编写的一个批处理文件,用于从命令行自动执行它,但它适用于 Windows/MS-DOS。
语法是 rename_mysqldb database newdatabase -u [用户] -p[密码]
Here is a batch file I wrote to automate it from the command line, but it for Windows/MS-DOS.
Syntax is rename_mysqldb database newdatabase -u [user] -p[password]
最简单的方法是使用 HeidiSQL 软件。 它是免费且开源的。 它可以在 Windows 和任何带有 Wine 的 Linux 上运行(在 Linux 上运行 Windows 应用程序, BSD、Solaris 和 Mac OS X)。
要下载 HeidiSQL,请转至 http://www.heidisql.com/download.php。
要下载 Wine,请访问 http://www.winehq.org/。
要在 HeidiSQL 中重命名数据库,只需右键单击数据库名称并选择“编辑”。 然后输入新名称并按“确定”。
就是这么简单。
The simplest method is to use HeidiSQL software. It's free and open source. It runs on Windows and on any Linux with Wine (run Windows applications on Linux, BSD, Solaris and Mac OS X).
To download HeidiSQL, goto http://www.heidisql.com/download.php.
To download Wine, goto http://www.winehq.org/.
To rename a database in HeidiSQL, just right click on the database name and select 'Edit'. Then enter a new name and press 'OK'.
It is so simple.
TodoInTX 的存储过程不太适合我。 这是我的尝试:
TodoInTX's stored procedure didn't quite work for me. Here's my stab at it:
您不能这样做是有原因的。 (尽管尝试了所有答案)
您需要转储该数据库中的所有对象类型,创建新命名的对象类型,然后导入转储。 如果这是一个实时系统,您需要将其关闭。 如果不能,那么您将需要设置从该数据库到新数据库的复制。
如果您想查看可以执行此操作的命令,@satishD 有详细信息,其中传达了一些您需要围绕这些挑战制定与您的目标数据库相匹配的策略。
There is a reason you cannot do this. (despite all the attempted answers)
You'll need to dump all object types in 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 that could do this, @satishD has the details, which conveys some of the challenges around which you'll need to build a strategy that matches your target database.
如果您有许多表要移动,这里是生成重命名 SQL 脚本的快速方法。
Here is a quick way to generate renaming sql script, if you have many tables to move.
ALTER DATABASE
是MySQL 提出的解决此问题的方法和 RENAME DATABASE 已被删除。来自 13.1.32 RENAME DATABASE 语法< /em>:
该语句是在 MySQL 5.1.7 中添加的,但在 MySQL 5.1.23 中被发现存在危险并被删除。
ALTER DATABASE
is the proposed way around this by MySQL andRENAME DATABASE
is dropped.From 13.1.32 RENAME DATABASE Syntax:
This statement was added in MySQL 5.1.7, but it was found to be dangerous and was removed in MySQL 5.1.23.
在 MySQL Administrator 中执行以下操作:
旧模式。
在步骤 3 中创建。
架构并选择新数据库
架构。
好的,删除旧的。
In MySQL Administrator do the following:
the old schema.
created in step 3.
Schema and select the new database
schema.
good, delete the old one.
下面是一个单行 Bash 片段,用于将所有表从一个模式移动到另一个模式:
开头的 History 命令只是确保包含密码的 MySQL 命令不会保存到 shell 历史记录中。
确保
db_user
对旧架构拥有读/写/删除权限,并对新架构拥有读/写/创建权限。Here is a one-line Bash snippet to move all tables from one schema to another:
The history command at the start simply ensures that the MySQL commands containing passwords aren't saved to the shell history.
Make sure that
db_user
has read/write/drop permissions on the old schema, and read/write/create permissions on the new schema.我是这样做的:
备份您现有的数据库。 它会给你一个 db.zip.tmp 然后在命令提示符下写下以下内容
I did it this way:
Take backup of your existing database. It will give you a db.zip.tmp and then in command prompt write following
TodoInTx 的解决方案和 user757945 的改编解决方案在 MySQL 5.5.16 上都不适合我,所以这是我的改编版本:
希望它可以帮助处于我情况的人! 注意:
@sql_string
随后将在会话中徘徊。 如果不使用它,我就无法编写这个函数。Neither TodoInTx's solution nor user757945's adapted solution worked for me on MySQL 5.5.16, so here is my adapted version:
Hope it helps someone who is in my situation! Note:
@sql_string
will linger in the session afterwards. I was not able to write this function without using it.在 phpmyadmin 中,您可以轻松重命名数据库,
要求删除旧表并重新加载表数据,然后单击“确定”,
您的数据库已重命名
in phpmyadmin you can easily rename the database
ask to drop old table and reload table data click OK in both
Your database is renamed
使用这几个简单的命令:
或者按照 @Pablo Marin-Garcia 的建议使用以下命令来减少 I/O:
Use these few simple commands:
Or to reduce I/O use the following as suggested by @Pablo Marin-Garcia:
对于InnoDB,以下操作似乎可行:创建新的空数据库,然后将每个表依次重命名为新数据库:
之后您将需要调整权限。
对于 shell 中的脚本编写,您可以使用以下任一选项:
或
注意:
-p
和密码之间没有空格。 如果您的数据库没有密码,请删除-u username -ppassword
部分。如果某个表有触发器,则无法使用上述方法将其移动到另一个数据库(将导致
触发器错误架构
错误)。 如果是这种情况,请使用传统方法克隆数据库,然后删除旧数据库:<代码>mysqldump old_db | mysql new_db
如果你有存储过程,你可以随后复制它们:
<代码>mysqldump -R old_db | mysql new_db
For InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database:
You will need to adjust the permissions after that.
For scripting in a shell, you can use either of the following:
OR
Notes:
-p
and the password. If your database has no password, remove the-u username -ppassword
part.If some table has a trigger, it cannot be moved to another database using above method (will result
Trigger in wrong schema
error). If that is the case, use a traditional way to clone a database and then drop the old one:mysqldump old_db | mysql new_db
If you have stored procedures, you can copy them afterwards:
mysqldump -R old_db | mysql new_db
您可以使用 SQL 生成 SQL 脚本,将源数据库中的每个表传输到目标数据库。
您必须在运行从命令生成的脚本之前创建目标数据库。
您可以使用这两个脚本中的任何一个(我最初建议使用前者,有人“改进”了我的答案以使用
GROUP_CONCAT
。您可以选择,但我更喜欢原来的):或
($1 和 $2 是源代码和目标)
这将生成一个 SQL 命令,然后您必须运行该命令。
请注意,
GROUP_CONCAT
有一个默认长度限制,对于具有大量表的数据库可能会超出该限制。 您可以通过运行SET SESSION group_concat_max_len = 100000000;
(或其他一些大数字)来更改该限制。You can use SQL to generate an SQL script to transfer each table in your source database to the destination database.
You must create the destination database before running the script generated from the command.
You can use either of these two scripts (I originally suggested the former and someone "improved" my answer to use
GROUP_CONCAT
. Take your pick, but I prefer the original):or
($1 and $2 are source and target respectively)
This will generate a SQL command that you'll have to then run.
Note that
GROUP_CONCAT
has a default length limit that may be exceeded for databases with a large number of tables. You can alter that limit by runningSET SESSION group_concat_max_len = 100000000;
(or some other large number).我认为解决方案更简单,是一些开发人员建议的。 phpMyAdmin 有一个用于此的操作。
从 phpMyAdmin 中,选择您要选择的数据库。 在选项卡中,有一个名为“操作”的选项卡,请转到“重命名”部分。 就这样。
正如许多人所建议的那样,它会使用新名称创建一个新数据库,将旧数据库的所有表转储到新数据库中并删除旧数据库。
I think the solution is simpler and was suggested by some developers. phpMyAdmin has an operation for this.
From phpMyAdmin, select the database you want to select. In the tabs there's one called Operations, go to the rename section. That's all.
It does, as many suggested, create a new database with the new name, dump all tables of the old database into the new database and drop the old database.
在 MySQL 中模拟缺少的
RENAME DATABASE
命令:创建新数据库
创建重命名查询:
运行该输出
删除旧数据库
它取自模拟丢失的重命名数据库MySQL 中的命令。
Emulating the missing
RENAME DATABASE
command in MySQL:Create a new database
Create the rename queries with:
Run that output
Delete old database
It was taken from Emulating The Missing RENAME DATABASE Command in MySQL.
您可以使用以下 shell 脚本:
参考:如何重命名 MySQL 数据库?< /em>
它正在工作:
You may use this shell script:
Reference: How to rename a MySQL database?
It's working:
三个选项:
创建新数据库,关闭服务器,将文件从一个数据库文件夹移动到另一个数据库文件夹,然后重新启动服务器。 请注意,只有当所有表都是 MyISAM 时,这才有效。
创建新数据库,使用 CREATE TABLE ... LIKE 语句,然后使用 INSERT ... SELECT * FROM 语句。
使用 mysqldump 并重新加载该文件。
Three options:
Create the new database, bring down the server, move the files from one database folder to the other, and restart the server. Note that this will only work if ALL of your tables are MyISAM.
Create the new database, use CREATE TABLE ... LIKE statements, and then use INSERT ... SELECT * FROM statements.
Use mysqldump and reload with that file.
简单的方法
切换到数据库目录:
关闭MySQL...这很重要!
好的,这种方法不适用于 InnoDB 或 BDB 数据库。
重命名数据库:
...或表...
重新启动 MySQL
完成...
好的,这种方式不适用于 InnoDB 或 BDB 数据库。 在这种情况下,您必须转储数据库并重新导入它。
The simple way
Change to the database directory:
Shut down MySQL... This is important!
Okay, this way doesn't work for InnoDB or BDB-Databases.
Rename database:
...or the table...
Restart MySQL
Done...
OK, this way doesn't work with InnoDB or BDB databases. In this case you have to dump the database and re-import it.
进行完整重命名的最简单、万无一失的方法(包括在最后删除旧数据库,因此它是重命名而不是副本):
步骤:
Simplest bullet-and-fool-proof way of doing a complete rename (including dropping the old database at the end so it's a rename rather than a copy):
Steps:
我最近才发现一种非常好的方法,可以与 MyISAM 和 InnoDB 配合使用,而且速度非常快:
我不记得我在哪里读到它,但功劳归于其他人而不是我。
I've only recently came across a very nice way to do it, works with MyISAM and InnoDB and is very fast:
I don't remember where I read it but credit goes to someone else not me.
这就是我使用的:
This is what I use:
有两种方法:
方法 1: 重命名数据库架构的一种众所周知的方法是使用 Mysqldump 转储架构并将其恢复到另一个架构中,然后删除旧架构(如果需要)。
From Shell
上面的方法虽然简单,但是比较费时间和空间。 如果架构超过100GB怎么办?有一些方法可以将上述命令连接在一起以节省空间,但不会节省时间。
为了解决这种情况,还有另一种快速方法来重命名模式,但是,执行此操作时必须小心。
方法2: MySQL有一个非常好的重命名表的功能,甚至可以跨不同的模式工作。 此重命名操作是原子性的,在重命名时没有其他人可以访问该表。 这需要很短的时间才能完成,因为更改表的名称或其架构只是元数据更改。 以下是执行重命名的过程方法:
使用所需名称创建新的数据库模式。
使用 MySQL 的“RENAME TABLE”命令将表从旧模式重命名为新模式。
删除旧的数据库架构。
如果架构中存在视图、触发器、函数、存储过程,那么这些也需要重新创建
。 如果表上存在触发器,MySQL 的“RENAME TABLE”将失败。 为了解决这个问题,我们可以执行以下操作:1)
将触发器、事件和存储例程转储到单独的文件中。
这可以使用 -E、-R 标志(在除了转储触发器的 -t -d 之外)到 mysqldump 命令。 一旦触发器被转储,我们需要将它们从模式中删除,以便 RENAME TABLE 命令起作用。2) 生成仅包含“BASE”表的列表。 可以使用
information_schema.TABLES
表上的查询找到这些内容。3) 将视图转储到输出文件中。 可以使用同一
information_schema.TABLES
表上的查询来查找视图。4) 删除 old_schema 中当前表上的触发器。
5) 重命名步骤 #2 中找到的所有“基”表后,恢复上述转储文件。
上述方法的复杂性:我们可能需要更新用户的 GRANTS,以便它们匹配正确的 schema_name。 这些可以通过对 mysql.columns_priv、mysql.procs_priv、mysql.tables_priv、mysql.db 表进行简单的 UPDATE 来修复,将 old_schema 名称更新为 new_schema 并调用“刷新权限;”。 尽管“方法 2”看起来比“方法 1”复杂一些,但这完全是可以编写脚本的。 一个简单的 bash 脚本可以按正确的顺序执行上述步骤,可以帮助您在下次重命名数据库模式时节省空间和时间。
Percona Remote DBA 团队编写了一个名为“rename_db”的脚本,其工作方式如下:
为了演示该脚本的使用,使用了示例模式“emp”,创建了测试触发器,并在该模式上存储了例程。 将尝试使用脚本重命名数据库模式,这需要几秒钟才能完成,而不是耗时的转储/恢复方法。
正如您在上面的输出中看到的,数据库模式“emp”在不到一秒的时间内被重命名为“emp_test”。 最后,这是上面用于“方法 2”的 Percona 脚本。
Well there are 2 methods:
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).
From Shell
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.
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:
Create the new database schema with the desired name.
Rename the tables from old schema to new schema, using MySQL’s “RENAME TABLE” command.
Drop the old database schema.
If there are views, triggers, functions, stored procedures in 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 :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.2) Generate a list of only “BASE” tables. These can be found using a query on
information_schema.TABLES
table.3) Dump the views in an out file. Views can be found using a query on the same
information_schema.TABLES
table.4) Drop the triggers on the current tables in the old_schema.
5) Restore the above dump files once all the “Base” tables found in step #2 are renamed.
Intricacies with above methods : We may need to update the GRANTS for users such that they match the correct schema_name. These could fixed with a simple UPDATE on mysql.columns_priv, mysql.procs_priv, mysql.tables_priv, mysql.db tables updating the old_schema name to new_schema and 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.
The Percona Remote DBA team have written a script called “rename_db” that works in the following way :
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.
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″.