MySQL 如何将整个数据库复制到另一个服务器数据库？

How to copy the whole database to another server database?

提问by Vinoth

I am currently working on a Windows application with a MySQL backend. Now I want to copy the old database structure and data to a new database server.

我目前正在开发一个带有 MySQL 后端的 Windows 应用程序。现在我想将旧的数据库结构和数据复制到新的数据库服务器。

How can I solve this problem using MySQL workbench?

如何使用 MySQL Workbench 解决此问题？

Thank you!

谢谢！

回答by Alberto León

1. Open MySQL Workbench
2. Create the old server's connection (if you haven't it)
3. Create the new server's connection (if you haven't it)
4. Go to Server Administration and click Manage Import / Export
5. Select old server
6. Select all schemas in Export to Disk tab
7. In options select Export to Self-Contained File, wait until it finished
8. Back to Server Administration and click Manage Import / Export
9. Select new server
10. Switch to "Import from Disk"
11. Click "import from Self-contained File

1. 打开 MySQL 工作台
2. 创建旧服务器的连接（如果还没有）
3. 创建新服务器的连接（如果还没有）
4. 转到服务器管理并单击管理导入/导出
5. 选择旧服务器
6. 在导出到磁盘选项卡中选择所有架构
7. 在选项中选择导出到自包含文件，等到它完成
8. 返回服务器管理并单击管理导入/导出
9. 选择新服务器
10. 切换到“从磁盘导入”
11. 点击“从自包含文件导入

This is the way with only MySQL Workbech, some times you haven't it installed in new server, like in development to production scenario, in this way you should execute the mysqlimport utility.

这是只有 MySQL Workbech 的方式，有时您还没有在新服务器中安装它，例如在开发到生产场景中，这样您应该执行 mysqlimport 实用程序。

回答by SherylHohman

Using MySQL Workbench [Migration Wizard]

使用 MySQL Workbench [迁移向导]

To directly copy a database from one server to another (even a local one) without creating intermediary export/dump files, you can do so within MySQL Workbench using its Migration Wizard. Go to Database--> Migration Wizard.

要将数据库从一台服务器直接复制到另一台服务器（甚至是本地服务器）而不创建中间导出/转储文件，您可以在 MySQL Workbench 中使用它的Migration Wizard 执行此操作。转到Database--> Migration Wizard。

From there you can select the source and destination connections if you've previously saved them to your Connections list, or you can create a new connection on the fly (be sure to check the box add it to your Connections list).
You'll also choose the database type, or select their generic version (don't remember what it's called) and let it figure out what kind of database servers you have. Generally you can select MySQL for either MySQL or MariaDB databases, as MariaDB is generally designed to be a "binary drop-in replacement" to MySQL.

如果您之前已将它们保存到您的连接列表，您可以从那里选择源和目标连接，或者您可以动态创建新连接（确保选中将其添加到您的连接列表的框）。
您还将选择数据库类型，或选择它们的通用版本（不记得它叫什么）并让它确定您拥有哪种类型的数据库服务器。通常，您可以为 MySQL 或 MariaDB 数据库选择 MySQL，因为 MariaDB 通常被设计为 MySQL 的“二进制替代品”。

When it asks you to select which databases, it's my understanding that INFORMATION_SCHEMAis read only, and should not be selected. Otherwise, unless you have specific needs, probably select all the other tables, if you are, say, cloning the server (and perhaps its related website).

当它要求您选择哪些数据库时，我的理解INFORMATION_SCHEMA是只读的，不应选择。否则，除非您有特定需求，否则可能会选择所有其他表，例如，如果您要克隆服务器（可能还有其相关网站）。

From there you'll let MySQL Workbench do its thing. Keep clicking next while it goes through the process of reverse engineering your schema from the old to the new servers, until it finally copies the data over.

从那里你会让 MySQL Workbench 做它的事情。在它完成从旧服务器到新服务器对架构进行逆向工程的过程时，继续单击下一步，直到它最终将数据复制过来。

There are many points where you can make changes before proceeding. Also you have the option of viewing potential issues and warnings it notes during the process.

在继续之前，您可以进行许多更改。您还可以选择查看在此过程中记录的潜在问题和警告。

When it's done, you'll still need to add users (manually?) for the cloned database. There is probably an automated way to clone the users table over as well, but I don't know about it. You can do this from the command line if nothing else. Since I use this for my local dev, I only have myself to add anyway.

完成后，您仍然需要为克隆的数据库添加用户（手动？）。也可能有一种自动化的方法来克隆用户表，但我不知道。如果没有别的，您可以从命令行执行此操作。由于我将它用于本地开发人员，因此无论如何我只能添加自己。

I'm not sure it's any faster using this tool than exporting and importing the databases in the usual manner. But it's sure easier.
This tool is actually designed for more complex migrations between differing kinds of databases (eg PostgressSQL and MySQL) rather than as a general tool for, say, retrieving a copy of your live database down to your local dev server or vice versa.

我不确定使用此工具是否比以通常方式导出和导入数据库更快。但这肯定更容易。
这个工具实际上是为在不同类型的数据库（例如 PostgressSQL 和 MySQL）之间进行更复杂的迁移而设计的，而不是作为一个通用工具，例如，将您的实时数据库的副本检索到您的本地开发服务器，反之亦然。

Under the same menu, there is Database-> Schema Transfer Wizard, which I understood to be used when you need to update to a new server version. I'm not clear specifically the differences between the two tools, or their actual use cases.

在同一个菜单下，有Database-> Schema Transfer Wizard，据我所知，当您需要更新到新的服务器版本时会使用它。我不清楚这两种工具之间的区别，或者它们的实际用例。

Instructions on how to do standard Import / Export operations, in MySQL workbench, the docs are SQL Data Export and Import Wizard, and Table Data Export and Import Wizard.

关于如何执行标准导入/导出操作的说明，在 MySQL 工作台中，文档是SQL 数据导出和导入向导，以及表数据导出和导入向导。

It would be cool if it were possible to directly import / export between dev and live servers, simply by choosing Connections, rather than saving then reading in dump files.
I think it can be done via the command line though.

如果可以直接在开发服务器和实时服务器之间导入/导出，只需选择连接，而不是保存然后读取转储文件，那将会很酷。
我认为它可以通过命令行完成。

回答by agold

To copy a database directly, without doing an export and import first, you can use the MySQL Utilitiescommand mysqdbcopy:

要直接复制数据库，无需先进行导出和导入，您可以使用MySQL Utilities命令mysqdbcopy：

mysqldbcopy --source=root:root@localhost --destination=root:root@localhost world:world_clone

You can either run the command in a Windows cmdor the MySQL Utilities Console.

您可以在 Windowscmd或MySQL 实用程序控制台中运行该命令。

回答by Christine

If you installed phpmyadmin, you can use following steps:

如果您安装了phpmyadmin，则可以使用以下步骤：

1. In the phpmyadminof source server, using the "Export" function, make sure

1. 在源服务器的phpmyadmin中，使用“导出”功能，确保

a) In the Export Method, choose "Custom - display all possible options"

a) 在导出方法中，选择“自定义 - 显示所有可能的选项”

b) In the "Format-specific options", make sure "structure and data " are selected.

b) 在“格式特定选项”中，确保选择“结构和数据”。

c) In the "Object creation options", make sure " ADD CREATE DATABASE / USE statement" also marked.

c) 在“对象创建选项”中，确保“ ADD CREATE DATABASE/USE 语句”也被标记。

Export the SQL file.

导出 SQL 文件。

1. scpthe exported file from the source server to the target server

2. In the phpmyadmin of the target server, using the "Import" function, import the SQL file to the database. There was no custom option need to select during the import process.

1. scp从源服务器导出的文件到目标服务器

2. 在目标服务器的phpmyadmin中，使用“导入”功能，将SQL文件导入到数据库中。在导入过程中不需要选择自定义选项。