使用导入和导出迁移 MySQL 数据库Migrate your MySQL database by using import and export

备注

将要查看的是 Azure Database for MySQL 的新服务。You are viewing the new service of Azure Database for MySQL. 若要查看经典 MySQL Database for Azure 的文档,请访问此页To view the documentation for classic MySQL Database for Azure, please visit this page.

本文介绍通过使用 MySQL Workbench 将数据导入和导出到 Azure Database for MySQL 服务器的两种常用方法。This article explains two common approaches to importing and exporting data to an Azure Database for MySQL server by using MySQL Workbench.

开始之前Before you begin

若要逐步执行本操作方法指南,需要:To step through this how-to guide, you need:

使用常用工具Use common tools

使用常用的实用工具和工具(如 MySQL Workbench 或 mysqldump)进行远程连接,并向/从 Azure Database for MySQL 导入/导出数据。Use common utilities and tools such as MySQL Workbench or mysqldump to remotely connect and import or export data into Azure Database for MySQL.

在具有 Internet 连接的客户端计算机上使用此类工具连接到 Azure Database for MySQL。Use such tools on your client machine with an Internet connection to connect to Azure Database for MySQL. 在 Azure Database for MySQL 中配置 SSL 连接中所述,使用具有 SSL 加密的连接是最安全的做法。Use an SSL-encrypted connection for best security practices, as described in Configure SSL connectivity in Azure Database for MySQL.

迁移到 Azure Database for MySQL 时,无需将导入和导出文件移到任何特殊的云位置。You do not need to move your import and export files to any special cloud location when migrating to Azure Database for MySQL.

在 Azure Database for MySQL 服务器上创建数据库Create a database on the Azure Database for MySQL server

在要迁移数据的 Azure Database for MySQL 服务器上创建空数据库。Create an empty database on the Azure Database for MySQL server where you want to migrate the data. 使用 MySQL Workbench、Toad 或 Navicat 等工具创建数据库。Use a tool such as MySQL Workbench, Toad, or Navicat to create the database. 数据库名称可与包含转储数据的数据库名称相同,或可以创建一个不同名称的数据库。The database can have the same name as the database that contains the dumped data, or you can create a database with a different name.

若要获取连接,请在 Azure Database for MySQL 的“概述”中找到连接信息 。To get connected, locate the connection information in the Overview of your Azure Database for MySQL.

在 Azure 门户中找到连接信息

将连接信息添加到 MySQL Workbench。Add the connection information to MySQL Workbench.

MySQL Workbench 连接字符串

确定何时使用导入和导出技术(而不是转储和还原技术)Determine when to use import and export techniques instead of a dump and restore

在以下情况下,使用 MySQL 工具将数据库导入和导出到 Azure MySQL 数据库中。Use MySQL tools to import and export databases into Azure MySQL Database in the following scenarios. 而在其他情况下,使用转储和还原可能更有益。In other scenarios, you might benefit from using the dump and restore approach instead.

  • 需要有选择性地选择要从现有 MySQL 数据库导入到 Azure MySQL 数据库的几个表时,最好使用导入和导出技术。When you need to selectively choose a few tables to import from an existing MySQL database into Azure MySQL Database, it's best to use the import and export technique. 这样做,可以在迁移过程中省略任何不需要的表,从而节省时间和资源。By doing so, you can omit any unneeded tables from the migration to save time and resources. 例如,使用带有 mysqlpump--include-tables--exclude-tables switch 语句以及带有 mysqldump--tables switch 语句。For example, use the --include-tables or --exclude-tables switch with mysqlpump and the --tables switch with mysqldump.
  • 移动表以外的数据库对象时,显式创建这些对象。When you're moving the database objects other than tables, explicitly create those objects. 包括约束(主键、外键、索引)、视图、函数、过程、触发器和想要迁移的任何其他数据库对象。Include constraints (primary key, foreign key, indexes), views, functions, procedures, triggers, and any other database objects that you want to migrate.
  • 从 MySQL 数据库以外的外部数据源迁移数据时,使用 mysqlimport 创建平面文件并导入它们。When you're migrating data from external data sources other than a MySQL database, create flat files and import them by using mysqlimport.

将数据加载到 Azure Database for MySQL 时,请确保数据库中的所有表都使用 InnoDB 存储引擎。Make sure that all tables in the database use the InnoDB storage engine when you're loading data into Azure Database for MySQL. Azure Database for MySQL 仅支持 InnoDB 存储引擎,因此不支持备选存储引擎。Azure Database for MySQL supports only the InnoDB storage engine, so it doesn't support alternative storage engines. 如果你的表需要备选存储引擎,确保先将它们转换为使用 InnoDB 引擎格式,再迁移到 Azure Database for MySQL。If your tables require alternative storage engines, be sure to convert them to use the InnoDB engine format before the migration to Azure Database for MySQL.

例如,如果你有使用 MyISAM 引擎的 WordPress 或 Web 应用,需首先通过将数据迁移到 InnoDB 表中的方式来转换表。For example, if you have a WordPress or web app that uses the MyISAM engine, first convert the tables by migrating the data into InnoDB tables. 然后还原到 Azure Database for MySQL。Then restore to Azure Database for MySQL. 使用子句 ENGINE=INNODB 设置用于创建表的引擎,然后在迁移之前将数据传输到兼容表中。Use the clause ENGINE=INNODB to set the engine for creating a table, and then transfer the data into the compatible table before the migration.

INSERT INTO innodb_table SELECT * FROM myisam_table ORDER BY primary_key_columns

导入和导出的性能建议Performance recommendations for import and export

  • 加载数据之前创建聚集索引和主键。Create clustered indexes and primary keys before loading data. 以主键顺序加载数据。Load data in primary key order.
  • 延迟创建辅助索引,直到数据加载完毕。Delay creation of secondary indexes until after data is loaded. 加载后创建所有辅助索引。Create all secondary indexes after loading.
  • 加载前禁用外键约束。Disable foreign key constraints before loading. 禁用外键检查可以显著提高性能。Disabling foreign key checks provides significant performance gains. 启用约束并在加载后验证数据,确保引用完整性。Enable the constraints and verify the data after the load to ensure referential integrity.
  • 并行加载数据。Load data in parallel. 避免太多将导致达到资源限制的并行度,并通过使用 Azure 门户中提供的指标监视资源。Avoid too much parallelism that would cause you to hit a resource limit, and monitor resources by using the metrics available in the Azure portal.
  • 适当时使用已分区表。Use partitioned tables when appropriate.

使用 MySQL Workbench 进行导入和导出Import and export by using MySQL Workbench

在 MySQL Workbench 中导入和导出数据有两种方法。There are two ways to export and import data in MySQL Workbench. 每种方法的用途不同。Each serves a different purpose.

对象浏览器上下文菜单中的表数据导出和导入向导Table data export and import wizards from the object browser's context menu

对象浏览器上下文菜单上的 MySQL Workbench 向导

表数据向导支持使用 CSV 和 JSON 文件的导入和导出操作。The wizards for table data support import and export operations by using CSV and JSON files. 它们包括多个配置选项,如分隔符、列选择和编码选择。They include several configuration options, such as separators, column selection, and encoding selection. 可以对本地或远程连接的 MySQL 服务器执行每个向导。You can perform each wizard against local or remotely connected MySQL servers. 导入操作包括表、列和类型映射。The import action includes table, column, and type mapping.

右键单击表,便可以从对象浏览器的上下文菜单中访问这些向导。You can access these wizards from the object browser's context menu by right-clicking a table. 然后选择“表数据导出向导” 或“表数据导入向导” 。Then choose either Table Data Export Wizard or Table Data Import Wizard.

表数据导出向导Table Data Export Wizard

下面的示例将表导出到 CSV 文件:The following example exports the table to a CSV file:

  1. 右键单击要导出的数据库的表。Right-click the table of the database to be exported.
  2. 选择“表数据导出向导” 。Select Table Data Export Wizard. 选择要导出的列、行偏移量(如果存在)、计数(如果存在)。Select the columns to be exported, row offset (if any), and count (if any).
  3. 在“选择要导出的数据” 页上,单击“下一步” 。On the Select data for export page, click Next. 选择文件路径、CSV 或 JSON 文件类型。Select the file path, CSV, or JSON file type. 此外选择行分隔符、封闭字符串的方法和字段分隔符。Also select the line separator, method of enclosing strings, and field separator.
  4. 在“选择输出文件位置” 页上,单击“下一步” 。On the Select output file location page, click Next.
  5. 在“导出数据” 页上,单击“下一步” 。On the Export data page, click Next.

表数据导入向导Table Data Import Wizard

下面的示例将从 CSV 文件导入表:The following example imports the table from a CSV file:

  1. 右键单击要导入的数据库的表。Right-click the table of the database to be imported.
  2. 浏览要导入的 CSV 文件并选择它,然后单击“下一步” 。Browse to and select the CSV file to be imported, and then click Next.
  3. 选择目标表(新的或现有的),然后选择或取消选择“导入前截断表” 复选框。Select the destination table (new or existing), and select or clear the Truncate table before import check box. 单击“下一步”。 Click Next.
  4. 选择编码和要导入的列,然后单击“下一步” 。Select encoding and the columns to be imported, and then click Next.
  5. 在“导入数据” 页上,单击“下一步” 。On the Import data page, click Next. 向导相应地导入数据。The wizard imports the data accordingly.

导航器窗格中的 SQL 数据导出和导入向导SQL data export and import wizards from the Navigator pane

使用向导导出或导入从 MySQL Workbench 或从 mysqldump 命令生成的 SQL。Use a wizard to export or import SQL generated from MySQL Workbench or generated from the mysqldump command. 从“导航器” 窗格或通过从主菜单中选择“服务器” 访问这些向导。Access these wizards from the Navigator pane or by selecting Server from the main menu. 然后选择“数据导出” 或“数据导入” 。Then select Data Export or Data Import.

数据导出Data Export

使用导航器窗格导出 MySQL Workbench 数据

可以使用“数据导出” 选项卡导出 MySQL 数据。You can use the Data Export tab to export your MySQL data.

  1. 选择想导出的每个架构,根据需要从每个架构中选择特定的架构对象/表,并生成导出。Select each schema that you want to export, optionally choose specific schema objects/tables from each schema, and generate the export. 配置选项包括导出到项目文件夹或自包含的 SQL 文件、转储存储的例程和事件,或跳过表数据。Configuration options include export to a project folder or self-contained SQL file, dump stored routines and events, or skip table data.

    或者,使用“导出结果集” 将 SQL 编辑器中的特定结果集导出为其他格式,例如 CSV、JSON、HTML 和 XML。Alternatively, use Export a Result Set to export a specific result set in the SQL editor to another format, such as CSV, JSON, HTML, and XML.

  2. 选择要导出的数据库对象,并配置相关选项。Select the database objects to export, and configure the related options.

  3. 单击“刷新” 加载当前对象。Click Refresh to load the current objects.

  4. 或者,打开可以完善导出操作的“高级选项” 选项卡。Optionally, open the Advanced Options tab to refine the export operation. 例如,添加表锁、使用 replace 而不是 insert 语句、使用反撇号字符将标识符括起来。For example, add table locks, use replace instead of insert statements, and quote identifiers with backtick characters.

  5. 单击“开始导出” 开始导出过程。Click Start Export to begin the export process.

数据导入Data Import

使用管理导航器的 MySQL Workbench 数据导入

使用“数据导入” 选项卡能够从数据导出操作或从 mysqldump 命令中导入或还原导出的数据。You can use the Data Import tab to import or restore exported data from the data export operation or from the mysqldump command.

  1. 选择项目文件夹或自包含的 SQL 文件,选择要向其中进行导入的架构,或选择“新建” 定义新架构。Choose the project folder or self-contained SQL file, choose the schema to import into, or choose New to define a new schema.
  2. 单击“开始导入” 开始导入过程。Click Start Import to begin the import process.

后续步骤Next steps