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

可以使用 Azure 数据库迁移服务在尽量缩短停机时间的情况下,将数据库从本地 MySQL 实例迁移到 Azure Database for MySQLYou can use Azure Database Migration Service to migrate the databases from an on-premises MySQL instance to Azure Database for MySQL with minimal downtime. 换句话说,可以在尽量减少应用程序故障时间的情况下进行迁移。In other words, migration can be achieved with minimum downtime to the application. 本教程介绍如何在 Azure 数据库迁移服务中使用联机迁移活动将 Employees 示例数据库从 MySQL 5.7 的本地实例迁移到 Azure Database for MySQL。In this tutorial, you migrate the Employees sample database from an on-premises instance of MySQL 5.7 to Azure Database for MySQL by using an online migration activity in Azure Database Migration Service.

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

  • 使用 mysqldump 实用程序迁移示例架构。Migrate the sample schema using mysqldump utility.
  • 创建 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.

Important

为获得最佳迁移体验,Azure 建议在目标数据库所在的 Azure 区域中创建 Azure 数据库迁移服务的实例。For an optimal migration experience, Azure recommends creating an instance of 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.

先决条件Prerequisites

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

  • 下载并安装 MySQL 社区版 5.6 或 5.7。Download and install MySQL community edition 5.6 or 5.7. 本地 MySQL 版本必须与 Azure Database for MySQL 版本相符。The on-premises MySQL version must match with Azure Database for MySQL version. 例如,MySQL 5.6 只能迁移到 Azure Database for MySQL 5.6,不能升级到 5.7。For example, MySQL 5.6 can only migrate to Azure Database for MySQL 5.6 and not upgraded to 5.7.

  • 在 Azure Database for MySQL 中创建一个实例Create an instance in Azure Database for MySQL. 有关如何使用 Azure 门户连接和创建数据库的详细信息,请参阅使用 MySQL Workbench 进行连接并查询数据一文。Refer to the article Use MySQL Workbench to connect and query data for details about how to connect and create a database using the Azure portal.

  • 使用 Azure 资源管理器部署模型创建 Azure 数据库迁移服务的 Azure 虚拟网络,该网络将使用 ExpressRouteVPN 提供与本地源服务器的站点到站点连接。Create an Azure Virtual Network for Azure Database Migration Service by using 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.

    Note

    在设置虚拟网络期间,如果将 ExpressRoute 与 Azure 的网络对等互连一起使用,请将以下服务终结点添加到将在其中预配服务的子网:During virtual network setup, if you use ExpressRoute with network peering to Azure, add the following service endpoints to the subnet in which the service will be provisioned:

    • 目标数据库终结点(例如,SQL 终结点、Cosmos DB 终结点等)Target database endpoint (for example, SQL endpoint, Cosmos DB endpoint, and so on)
    • 存储终结点Storage endpoint
    • 服务总线终结点Service bus endpoint

    Azure 数据库迁移服务缺少 Internet 连接,因此必须提供此配置。This configuration is necessary because Azure Database Migration Service lacks internet connectivity.

  • 确保虚拟网络网络安全组规则未阻止到 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, 12000. 有关虚拟网络 NSG 流量筛选的更多详细信息,请参阅使用网络安全组筛选网络流量一文。For more detail on virtual network NSG traffic filtering, see the article Filter network traffic with network security groups.

  • 配置针对数据库引擎访问的 Windows 防火墙Configure your Windows Firewall for database engine access.

  • 打开 Windows 防火墙,使 Azure 数据库迁移服务能够访问源 MySQL 服务器(默认情况下为 TCP 端口 3306)。Open your Windows firewall to allow Azure Database Migration Service to access the source MySQL Server, which by default is TCP port 3306.

  • 在源数据库的前面使用了防火墙设备时,可能需要添加防火墙规则以允许 Azure 数据库迁移服务访问要迁移的源数据库。When using a firewall appliance in front of your source database(s), you may need to add firewall rules to allow Azure Database Migration Service to access the source database(s) for migration.

  • 为 Azure Database for MySQL 创建服务器级防火墙规则,以允许 Azure 数据库迁移服务访问目标数据库。Create a server-level firewall rule for Azure Database for MySQL to allow Azure Database Migration Service access to the target databases. 提供用于 Azure 数据库迁移服务的虚拟网络子网范围。Provide the subnet range of the virtual network used for Azure Database Migration Service.

  • 源 MySQL 必须使用支持的 MySQL 社区版。The source MySQL must be on supported MySQL community edition. 若要确定 MySQL 实例的版本,请在 MySQL 实用程序或 MySQL Workbench 中运行以下命令:To determine the version of MySQL instance, in the MySQL utility or MySQL Workbench, run the following command:

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

  • 通过以下配置在源数据库的 my.ini (Windows) 或 my.cnf (Unix) 文件中启用二进制日志记录。Enable binary logging in the my.ini (Windows) or my.cnf (Unix) file in source database by using the following configuration:

    • server_id = 1 或更高版本(仅适用于 MySQL 5.6)server_id = 1 or greater (relevant only for MySQL 5.6)
    • log-bin =<path>(仅适用于 MySQL 5.6)。例如:log-bin = E:\MySQL_logs\BinLoglog-bin =<path> (relevant only for MySQL 5.6) For example: log-bin = E:\MySQL_logs\BinLog
    • binlog_format = rowbinlog_format = row
    • Expire_logs_days = 5(建议不要使用零;仅适用于 MySQL 5.6)Expire_logs_days = 5 (it's recommended to not use zero; relevant only for MySQL 5.6)
    • Binlog_row_image = full(仅适用于 MySQL 5.6)Binlog_row_image = full (relevant only for MySQL 5.6)
    • log_slave_updates = 1log_slave_updates = 1
  • 用户必须具有 ReplicationAdmin 角色的以下权限:The user must have the ReplicationAdmin role with the following privileges:

    • 复制客户端 - 仅仅是“更改处理”任务所需的。REPLICATION CLIENT - Required for Change Processing tasks only. 换句话说,“仅完整加载”任务不需要此权限。In other words, Full Load only tasks don't require this privilege.
    • 复制副本 - 仅仅是“更改处理”任务所需的。REPLICATION REPLICA - Required for Change Processing tasks only. 换句话说,“仅完整加载”任务不需要此权限。In other words, Full Load only tasks don't require this privilege.
    • 超级 - 仅在 MySQL 5.6.6 之前的版本中需要。SUPER - Only required in versions earlier than MySQL 5.6.6.

迁移示例架构Migrate the sample schema

若要完成所有数据库对象(例如表架构、索引和存储过程),需从源数据库提取架构并将其应用到此数据库。To complete all the database objects like table schemas, indexes and stored procedures, we need to extract schema from the source database and apply to the database. 若要提取架构,可以将 mysqldump 与 --no-data 参数配合使用。To extract schema, you can use mysqldump with the --no-data parameter.

假设本地系统中有 MySQL Employees 示例数据库,则使用 mysqldump 进行架构迁移时所需的命令如下 :Assuming you have MySQL Employees sample database in the on-premises system, the command to do schema migration using mysqldump is:

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

例如:For example:

mysqldump -h 10.10.123.123 -u root -p --databases employees --no-data > d:\employees.sql

若要将架构导入到 Azure Database for MySQL 目标,请运行以下命令:To import schema to Azure Database for MySQL target, run the following command:

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

例如:For example:

mysql.exe -h shausample.mysql.database.chinacloudapi.cn -u dms@shausample -p employees < d:\employees.sql

如果架构中有外键,则迁移的初始加载和连续同步会失败。If you have foreign keys in your schema, the initial load and continuous sync of the migration will fail. 请在 MySQL Workbench 中执行以下脚本,以便提取 DROP FOREIGN KEY 脚本和 ADD FOREIGN KEY 脚本。Execute the following script in MySQL Workbench to extract the drop foreign key script and add foreign key script.

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;

运行查询结果中的 DROP FOREIGN KEY(第二列),以便删除外键。Run the drop foreign key (which is the second column) in the query result to drop foreign key.

Important

如果使用备份导入数据,请在执行 mysqldump 时手动删除或使用 --skip-definer 命令删除 CREATE DEFINER 命令。If importing data using a backup, remove the CREATE DEFINER commands manually or by using the --skip-definer command when performing a mysqldump. DEFINER 需要使用超级特权来创建,并在 Azure Database for MySQL 中受到限制。DEFINER requires super privileges to create and is restricted in Azure Database for MySQL.

如果数据中有触发器(插入或更新触发器),该触发器会赶在源中的已复制数据之前在目标中强制实施数据完整性。If you have a trigger in the data (insert or update trigger), it will enforce data integrity in the target ahead of the replicated 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 done.

若要禁用目标数据库中的触发器,请使用以下命令:To disable triggers in the target database, use the following command:

SELECT Concat('DROP TRIGGER ', Trigger_Name, ';') FROM  information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = 'your_schema';

注册 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.

    注册资源提供程序

创建 DMS 实例Create a DMS instance

  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. 选择现有虚拟网络或新建一个。Select an existing virtual network or create a new one.

    虚拟网络为 Azure 数据库迁移服务提供了对源 SQL Server 和目标 Azure SQL 数据库实例的访问权限。The virtual network provides Azure Database Migration Service with access to the source SQL Server and the target Azure SQL Database 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.

  5. 选择定价层。Select a pricing tier.

    有关成本和定价层的详细信息,请参阅价格页For more information on costs and pricing tiers, see the pricing page.

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

  6. 选择“创建” 来创建服务。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 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, in the Target server type text box, select AzureDbForMySQL.

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

    创建数据库迁移服务项目

    Note

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

  6. 选择“保存”,记下成功使用 DMS 迁移数据需要满足的要求,然后选择“创建和运行活动”。 Select Save, note the requirements to successfully use DMS to migrate data, and then select Create and run activity.

指定源详细信息Specify source details

  1. 在“添加源详细信息” 屏幕上,指定源 MySQL 实例的连接详细信息。On the Add Source Details screen, specify the connection details for the source MySQL instance.

    “添加源详细信息”屏幕

指定目标详细信息Specify target details

  1. 选择“保存”,然后在“目标详细信息”屏幕中指定目标 Azure Database for MySQL 服务器的连接详细信息,这是使用 mysqldump 向其部署 Employees 架构的 Azure Database for MySQL 的提前预配实例 。Select Save, and then on the Target details screen, specify the connection details for the target Azure Database for MySQL server, which is the pre-provisioned instance of Azure Database for MySQL to which the Employees schema was deployed by 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.

    映射到目标数据库

    Note

    尽管可以在此步骤中选择多个数据库,但 Azure 数据库迁移服务的每个实例最多支持四个数据库进行并发迁移。Though you can select multiple databases in this step, each instance of Azure Database Migration Service supports up to four databases for concurrent migration. 此外,订阅中每个区域的 Azure 数据库迁移服务有两个实例的限制。Also, there is a limit of two instances of Azure Database Migration Service per region in a subscription. 例如,如果有 40 个数据库要迁移,那么只能同时迁移其中的 8 个,而且只有在已创建了两个 Azure 数据库迁移服务实例的情况下才能如此。For example, if you have 40 databases to migrate, you can only migrate eight of them concurrently, and only if you have created two instances of Azure Database Migration Service.

  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 Complete.

    活动状态 - 完成

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

    完整数据加载会显示初始加载迁移状态,而增量数据同步则会显示变更数据捕获 (CDC) 状态。Full data load will show the initial load migration status while Incremental data sync will show 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 SQL 数据库。 When the database migration status shows Completed, connect your applications to the new target Azure SQL Database.

后续步骤Next steps