教程:使用 DMS 将 RDS MySQL 联机迁移到 Azure Database for MySQLTutorial: Migrate RDS MySQL to Azure Database for MySQL online using DMS

可以使用 Azure 数据库迁移服务将 RDS MySQL 实例中的数据库迁移到 Azure Database for MySQL,在迁移期间,源数据库可保持联机状态。You can use Azure Database Migration Service to migrate databases from an RDS MySQL instance to Azure Database for MySQL while the source database remains online during migration. 换而言之,实现这种迁移只会对应用程序造成极短暂的停机。In other words, migration can be achieved with minimal downtime to the application. 本教程介绍如何在 Azure 数据库迁移服务中使用联机迁移活动将 Employees 示例数据库从 RDS MySQL 实例迁移到 Azure Database for MySQL。In this tutorial, you migrate the Employees sample database from an instance of RDS MySQL to Azure Database for MySQL by using the online migration activity in Azure Database Migration Service.

本教程介绍如何执行下列操作:In this tutorial, you learn how to:

  • 使用 mysqldump 和 mysql 实用工具迁移示例架构。Migrate the sample schema by using the mysqldump and mysql utilities.
  • 创建 Azure 数据库迁移服务的实例。Create an instance of Azure Database Migration Service.
  • 使用 Azure 数据库迁移服务创建迁移项目。Create a migration project by using Azure Database Migration Service.
  • 运行迁移。Run the migration.
  • 监视迁移。Monitor the migration.

Note

使用 Azure 数据库迁移服务执行联机迁移需要基于“高级”定价层创建实例。Using Azure Database Migration Service to perform an online migration requires creating an instance based on the Premium pricing tier. 有关详细信息,请参阅 Azure 数据库迁移服务定价页。For more information, see the Azure Database Migration Service pricing page.

Important

为获得最佳迁移体验,Azure 建议在目标数据库所在的 Azure 区域中创建 Azure 数据库迁移服务的实例。For an optimal migration experience, Azure recommends creating an instance of the Azure Database Migration Service in the same Azure region as the target database. 跨区域或地理位置移动数据可能会减慢迁移过程并引入错误。Moving data across regions or geographies can slow down the migration process and introduce errors.

Tip

使用 Azure 数据库迁移服务将数据库迁移到 Azure 时,可以进行脱机或联机迁移。 When you migrate databases to Azure by using Azure Database Migration Service, you can do an offline or an online migration. 使用脱机迁移时,应用程序停机时间从迁移开始时算起。With an offline migration, application downtime starts when the migration starts. 使用联机迁移时,停机时间仅限在迁移结束时进行转换的那段时间。With an online migration, downtime is limited to the time to cut over at the end of migration. 建议对脱机迁移进行测试,以便确定其停机时间是否可以接受;如果不能接受,请进行联机迁移。We suggest that you test an offline migration to determine whether the downtime is acceptable; if not, do an online migration.

本文介绍如何从 RDS MySQL 实例联机迁移到 Azure Database for MySQL。This article describes how to perform an online migration from an instance of RDS MySQL to Azure Database for MySQL.

先决条件Prerequisites

要完成本教程,需要:To complete this tutorial, you need to:

  • 确保源 MySQL 服务器正在运行受支持的 MySQL 社区版。Ensure that the source MySQL server is running a supported MySQL community edition. 若要确定 MySQL 实例的版本,请在 mysql 实用工具或 MySQL Workbench 中运行以下命令:To determine the version of your MySQL instance, in the mysql utility or MySQL Workbench, run the command:

    SELECT @@version;
    

    有关详细信息,请参阅支持的 Azure Database for MySQL 版本一文。For more information, see the article Supported Azure Database for MySQL versions.

  • 下载并安装 MySQL Employees 示例数据库Download and install the MySQL Employees sample database.

  • 创建 Azure Database for MySQL 的实例。Create an instance of Azure Database for MySQL.

  • 使用 Azure 资源管理器部署模型创建 Azure 数据库迁移服务的 Azure 虚拟网络,它将使用 ExpressRouteVPN 为本地源服务器提供站点到站点连接。Create an Azure Virtual Network for Azure Database Migration Service by using the Azure Resource Manager deployment model, which provides site-to-site connectivity to your on-premises source servers by using either ExpressRoute or VPN. 有关创建虚拟网络的详细信息,请参阅虚拟网络文档,尤其是提供了分步详细信息的快速入门文章。For more information about creating a virtual network, see the Virtual Network Documentation, and especially the quickstart articles with step-by-step details.

  • 确保虚拟网络网络安全组规则未阻止到 Azure 数据库迁移服务的以下入站通信端口:443、53、9354、445、12000。Ensure that your virtual network Network Security Group rules don't block the following inbound communication ports to Azure Database Migration Service: 443, 53, 9354, 445, and 12000. 有关虚拟网络 NSG 流量筛选的更多详细信息,请参阅使用网络安全组筛选网络流量一文。For more detail on virtual network NSG traffic filtering, see the article Filter network traffic with network security groups.

  • 配置 Windows 防火墙(或 Linux 防火墙)以允许数据库引擎访问。Configure your Windows Firewall (or your Linux firewall) to allow for database engine access. 对于 MySQL 服务器,允许端口 3306 进行连接。For MySQL server, allow port 3306 for connectivity.

Note

Azure Database for MySQL 仅支持 InnoDB 表。Azure Database for MySQL only supports InnoDB tables. 若要将 MyISAM 表转换为 InnoDB,请参阅将表从 MyISAM 转换为 InnoDB 一文。To convert MyISAM tables to InnoDB, please see the article Converting Tables from MyISAM to InnoDB .

设置 AWS RDS MySQL 用于复制Set up AWS RDS MySQL for replication

  1. 若要创建新的参数组,请遵照 AWS 在 MySQL 数据库日志文件的“二进制日志格式”部分中提供的说明操作。To create a new parameter group, follow the instructions provided by AWS in the article MySQL Database Log Files, in the Binary Logging Format section.
  2. 使用以下配置创建新的参数组:Create a new parameter group with the following configuration:
    • log_bin = ONlog_bin = ON
    • binlog_format = rowbinlog_format = row
    • binlog_checksum = NONEbinlog_checksum = NONE
  3. 保存新参数组。Save the new parameter group.
  4. 将新参数组与 RDS MySQL 实例相关联。Associate the new parameter group with the RDS MySQL instance. 可能需要重新启动。A reboot might be required.

迁移架构Migrate the schema

  1. 从源数据库提取架构并将其应用到目标数据库,以完成所有数据库对象(例如表架构、索引和存储过程)的迁移。Extract the schema from the source database and apply to the target database to complete migration of all database objects such as table schemas, indexes, and stored procedures.

    仅迁移架构的最简单方法是结合 --no-data 参数使用 mysqldump。The easiest way to migrate only the schema is to use mysqldump with the --no-data parameter. 用于迁移架构的命令是:The command to migrate the schema is:

    mysqldump -h [servername] -u [username] -p[password] --databases [db name] --no-data > [schema file path]
    

    例如,若要转储 Employees 数据库的架构文件,请使用以下命令:For example, to dump a schema file for the Employees database, use the following command:

    mysqldump -h 10.10.123.123 -u root -p --databases employees --no-data > d:\employees.sql
    
  2. 将架构导入目标服务(即 Azure Database for MySQL)。Import the schema to target service, which is Azure Database for MySQL. 若要还原架构转储文件,请运行以下命令:To restore the schema dump file, run the following command:

    mysql.exe -h [servername] -u [username] -p[password] [database]< [schema file path]
    

    例如,若要导入 Employees 数据库的架构:For example, to import the schema for the Employees database:

    mysql.exe -h shausample.mysql.database.chinacloudapi.cn -u dms@shausample -p employees < d:\employees.sql
    
  3. 如果架构中有外键,则迁移的初始加载和连续同步会失败。If you have foreign keys in your schema, the initial load and continuous sync of the migration will fail. 若要在目标 (Azure Database for MySQL) 中提取 drop foreign key 脚本和 add foreign key 脚本,请在 MySQL Workbench 中运行以下脚本:To extract the drop foreign key script and add foreign key script at the destination (Azure Database for MySQL), run the following script in MySQL Workbench:

    SET group_concat_max_len = 8192;
        SELECT SchemaName, GROUP_CONCAT(DropQuery SEPARATOR ';\n') as DropQuery, GROUP_CONCAT(AddQuery SEPARATOR ';\n') as AddQuery
        FROM
        (SELECT
        KCU.REFERENCED_TABLE_SCHEMA as SchemaName,
                    KCU.TABLE_NAME,
                    KCU.COLUMN_NAME,
                    CONCAT('ALTER TABLE ', KCU.TABLE_NAME, ' DROP FOREIGN KEY ', KCU.CONSTRAINT_NAME) AS DropQuery,
        CONCAT('ALTER TABLE ', KCU.TABLE_NAME, ' ADD CONSTRAINT ', KCU.CONSTRAINT_NAME, ' FOREIGN KEY (`', KCU.COLUMN_NAME, '`) REFERENCES `', KCU.REFERENCED_TABLE_NAME, '` (`', KCU.REFERENCED_COLUMN_NAME, '`) ON UPDATE ',RC.UPDATE_RULE, ' ON DELETE ',RC.DELETE_RULE) AS AddQuery
                    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU, information_schema.REFERENTIAL_CONSTRAINTS RC
                    WHERE
                      KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
                      AND KCU.REFERENCED_TABLE_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
      AND KCU.REFERENCED_TABLE_SCHEMA = 'SchemaName') Queries
      GROUP BY SchemaName;
    
  4. 运行查询结果中的 drop foreign key(第二列),以删除外键。Run the drop foreign key (which is the second column) in the query result to drop the foreign key.

  5. 如果数据中包含触发器(insert 或 update 触发器),该触发器会在从源复制数据之前在目标中强制实施数据完整性。If you have triggers (insert or update trigger) in the data, it will enforce data integrity in the target before replicating data from the source. 建议在迁移期间禁用目标的所有表中的触发器,然后在迁移完成后再启用这些触发器。The recommendation is to disable triggers in all the tables at the target during migration, and then enable the triggers after migration is complete.

    在目标数据库中禁用触发器:To disable triggers in target database:

    select concat ('alter table ', event_object_table, ' disable trigger ', trigger_name)
    from information_schema.triggers;
    
  6. 如果任何表中包含 ENUM 数据类型的实例,我们建议在目标表中将其暂时更新为“character varying”数据类型。If there are instances of the ENUM data type in any tables, we recommend temporarily updating to the ‘character varying’ datatype in the target table. 数据复制完成后,将数据类型还原为 ENUM。WHen data replication is complete, then revert the data type to ENUM.

注册 Microsoft.DataMigration 资源提供程序Register the Microsoft.DataMigration resource provider

  1. 登录到 Azure 门户,选择“所有服务”,然后选择“订阅”。Sign in to the Azure portal, select All services, and then select Subscriptions.

    显示门户订阅

  2. 选择要在其中创建 Azure 数据库迁移服务实例的订阅,再选择“资源提供程序”。Select the subscription in which you want to create the instance of Azure Database Migration Service, and then select Resource providers.

    显示资源提供程序

  3. 搜索“迁移”,然后选择“注册”。Search for migration, and then select Register.

    注册资源提供程序

创建 Azure 数据库迁移服务的实例Create an instance of Azure Database Migration Service

  1. 在 Azure 门户中,选择 + 创建资源,搜索 Azure 数据库迁移服务,然后从下拉列表选择Azure 数据库迁移服务In the Azure portal, select + Create a resource, search for Azure Database Migration Service, and then select Azure Database Migration Service from the drop-down list.

    Azure 市场

  2. 在“Azure 数据库迁移服务”屏幕上,选择“创建” 。On the Azure Database Migration Service screen, select Create.

    创建 Azure 数据库迁移服务实例

  3. 在“创建迁移服务”屏幕上,为服务、订阅以及新的或现有资源组指定名称。On the Create Migration Service screen, specify a name for the service, the subscription, and a new or existing resource group.

  4. 选择要在其中创建 Azure 数据库迁移服务实例的位置。Select the location in which you want to create the instance of Azure Database Migration Service.

  5. 选择现有虚拟网络或新建一个。Select an existing virtual network or create a new one.

    虚拟网络为 Azure 数据库迁移服务提供源 MySQL 实例和目标 Azure Database for MySQL 实例的访问权限。The virtual network provides Azure Database Migration Service with access to the source MySQL instance and the target Azure Database for MySQL instance.

    有关如何在 Azure 门户中创建虚拟网络的详细信息,请参阅使用 Azure 门户创建虚拟网络一文。For more information about how to create a virtual network in the Azure portal, see the article Create a virtual network using the Azure portal.

  6. 选择定价层;对于此联机迁移,请务必选择“高级:4vCores”定价层。Select a pricing tier; for this online migration, be sure to select the Premium: 4vCores pricing tier.

    配置 Azure 数据库迁移服务实例设置

  7. 选择“创建”来创建服务。Select Create to create the service.

创建迁移项目Create a migration project

创建服务后,在 Azure 门户中找到并打开它,然后创建一个新的迁移项目。After the service is created, locate it within the Azure portal, open it, and then create a new migration project.

  1. 在 Azure 门户中,选择“所有服务”,搜索 Azure 数据库迁移服务,然后选择“Azure 数据库迁移服务”。In the Azure portal, select All services, search for Azure Database Migration Service, and then select Azure Database Migration Services.

    查找 Azure 数据库迁移服务的所有实例

  2. 在“Azure 数据库迁移服务”屏幕上,搜索你创建的 Azure 数据库迁移服务实例名称,然后选择该实例。On the Azure Database Migration Services screen, search for the name of the Azure Database Migration Service instance that you created, and then select the instance.

    查找 Azure 数据库迁移服务实例

  3. 选择“+ 新建迁移项目”。Select + New Migration Project.

  4. 在“新建迁移项目”屏幕上指定项目名称,在“源服务器类型”文本框中选择“MySQL”,在“目标服务器类型”文本框中选择“AzureDbForMySQL”。 On the New migration project screen, specify a name for the project, in the Source server type text box, select MySQL, and then in the Target server type text box, select AzureDbForMySQL.

  5. 在“选择活动类型”部分选择“联机数据迁移”。 In the Choose type of activity section, select Online data migration.

    Important

    请确保选择“联机数据迁移”;此方案不支持脱机迁移。Be sure to select Online data migration; offline migrations are not supported for this scenario.

    创建数据库迁移服务项目

    Note

    也可以现在就选择“仅创建项目”来创建迁移项目,在以后再执行迁移。Alternately, you can choose Create project only to create the migration project now and execute the migration later.

  6. 选择“保存” 。Select Save.

  7. 选择“创建并运行活动”,以便创建项目并运行迁移活动。Select Create and run activity to create the project and run the migration activity.

    Note

    请在项目创建边栏选项卡中记下设置联机迁移所要满足的先决条件。Please make a note of the pre-requisites needed to set up online migration in the project creation blade.

指定源详细信息Specify source details

  • 在“迁移源详细信息”屏幕上,指定源 MySQL 实例的连接详细信息。On the Migration source detail screen, specify the connection details for the source MySQL instance.

    源详细信息

指定目标详细信息Specify target details

  1. 选择“保存”,然后在“目标详细信息”屏幕上指定目标 Azure Database for MySQL 服务器的连接详细信息,该服务器是提前预配的,具有使用 MySQLDump 部署的 Employees 架构。 Select Save, and then on the Target details screen, specify the connection details for the target Azure Database for MySQL server, which is pre-provisioned and has the Employees schema deployed using MySQLDump.

    选择目标

  2. 选择“保存”,然后在“映射到目标数据库”屏幕上,映射源和目标数据库以进行迁移。 Select Save, and then on the Map to target databases screen, map the source and the target database for migration.

    如果目标数据库包含的数据库名称与源数据库的相同,则 Azure 数据库迁移服务默认会选择目标数据库。If the target database contains the same database name as the source database, Azure Database Migration Service selects the target database by default.

    映射到目标数据库

  3. 选择“保存”,在“迁移摘要”屏幕上的“活动名称”文本框中指定迁移活动的名称,然后查看摘要,确保源和目标详细信息与此前指定的信息相符 。Select Save, on the Migration summary screen, in the Activity name text box, specify a name for the migration activity, and then review the summary to ensure that the source and target details match what you previously specified.

    迁移摘要

运行迁移Run the migration

  • 选择“运行迁移”。Select Run migration.

    迁移活动窗口随即出现,活动的“状态”为“正在初始化” 。The migration activity window appears, and the Status of the activity is Initializing.

监视迁移Monitor the migration

  1. 在迁移活动屏幕上选择“刷新”,以便更新显示,直到迁移的“状态”显示为“正在运行”。On the migration activity screen, select Refresh to update the display until the Status of the migration shows as Running.

    活动状态 - 正在运行

  2. 在“数据库名称”下选择特定数据库即可转到“完整数据加载”和“增量数据同步”操作的迁移状态。 Under DATABASE NAME, select a specific database to get to the migration status for Full data load and Incremental data sync operations.

    “完整数据加载”会显示初始加载迁移状态,而“增量数据同步”则会显示变更数据捕获 (CDC) 状态。 Full data load shows the initial load migration status, while Incremental data sync shows change data capture (CDC) status.

    库存屏幕 - 完整数据加载

    库存屏幕 - 增量数据同步

执行迁移直接转换Perform migration cutover

完成初始的完整加载后,数据库会被标记为“准备好交接”。After the initial Full load is completed, the databases are marked Ready to Cutover.

  1. 如果准备完成数据库迁移,请选择“启动直接转换”。When you're ready to complete the database migration, select Start Cutover.

    开始交接

  2. 确保停止传入源数据库的所有事务;等到“挂起的更改”计数器显示 0Make sure to stop all the incoming transactions to the source database; wait until the Pending changes counter shows 0.

  3. 选择“确认”,然后选择“应用”。Select Confirm, and the select Apply.

  4. 当数据库迁移状态显示“已完成”后,请将应用程序连接到新的目标 Azure Database for MySQL 数据库。When the database migration status shows Completed, connect your applications to the new target Azure Database for MySQL database.

将 MySQL 的本地实例联机迁移到 Azure Database for MySQL 的过程现已完成。Your online migration of an on-premises instance of MySQL to Azure Database for MySQL is now complete.

后续步骤Next steps