您现在访问的是微软AZURE全球版技术文档网站,若需要访问由世纪互联运营的MICROSOFT AZURE中国区技术文档网站,请访问 https://docs.azure.cn.

将 MySQL 数据库迁移到 AzureMigrate MySQL databases to Azure

本文介绍虚构公司 Contoso 如何计划并将其本地 MySQL 开源数据库平台迁移到 Azure。This article demonstrates how the fictional company Contoso planned and migrated its on-premises MySQL open-source database platform to Azure.

业务驱动因素Business drivers

IT 领先团队与业务合作伙伴密切合作,以了解他们希望在此迁移中实现的目标。The IT leadership team has worked closely with business partners to understand what they want to achieve with this migration. 它们需要:They want to:

  • 提高可用性。Increase availability. Contoso 在其 MySQL 本地环境中存在可用性问题。Contoso has had availability issues with its MySQL on-premises environment. 业务要求使用此数据存储的应用程序更可靠。The business requires the applications that use this data store to be more reliable.
  • 提高效率。Increase efficiency. Contoso 需要删除不必要的过程,并简化开发人员和用户的过程。Contoso needs to remove unnecessary procedures and streamline processes for developers and users. 业务需要快速而不是浪费时间或金钱,以更快地提供客户需求。The business needs IT to be fast and not waste time or money to deliver faster on customer requirements.
  • 提高灵活性。Increase agility. Contoso IT 需要对业务需求更加敏感。Contoso IT needs to be more responsive to the needs of the business. 它必须比 marketplace 中的更改更快地在全球经济中实现成功。It must react faster than the changes in the marketplace to enable success in a global economy. It 不得成为企业阻止。It mustn't become a business blocker.
  • 纵向.Scale. 当业务成功发展时,Contoso IT 必须提供以相同步调增长的系统。As the business grows successfully, Contoso IT must provide systems that grow at the same pace.

迁移目标Migration goals

Contoso 云团队制定了本次迁移的目标。The Contoso cloud team has pinned down goals for this migration. 这些目标用于确定最佳迁移方式。These goals were used to determine the best migration method.

要求Requirements 详细信息Details
可用性Availability 目前,内部工作人员在使用 MySQL 实例的宿主环境时遇到了困难。Currently internal staff are having a hard time with the hosting environment for the MySQL instance. Contoso 希望数据库层的可用性接近99.99%。Contoso wants to have close to 99.99 percent availability for the database layer.
伸缩性Scalability 本地数据库主机的容量快。The on-premises database host is quickly running out of capacity. Contoso 需要一种方法来扩展其实例的当前限制,或在业务环境改变为节省成本的情况下向下缩放。Contoso needs a way to scale its instances past current limitations or scale down if the business environment changes to save on costs.
“性能”Performance Contoso 人力资源 (HR) 部门每天、每周和每月运行各种报告。The Contoso human resources (HR) department runs various reports daily, weekly, and monthly. 当它运行这些报表时,它会遇到与面向员工的应用程序有关的重大性能问题。When it runs these reports, it experiences significant performance issues with the employee-facing application. 它需要在不影响应用程序性能的情况下运行报表。It needs to run the reports without affecting application performance.
安全性Security Contoso 需要知道,只能对其内部应用程序访问数据库,而不能通过 internet 进行查看或访问。Contoso needs to know that the database is accessible only to its internal applications and isn't visible or accessible via the internet.
MonitoringMonitoring Contoso 目前使用工具来监视 MySQL 数据库服务器的指标,并在 CPU、内存或存储出现问题时提供通知。Contoso currently uses tools to monitor the metrics of the MySQL database server and provide notifications when CPU, memory, or storage have issues. 该公司希望在 Azure 中使用这一功能。The company wants to have this same capability in Azure.
业务连续性Business continuity HR 数据存储是 Contoso 日常操作的重要组成部分。The HR data store is an important part of Contoso's daily operations. 如果它已损坏或需要还原,则该公司希望尽量缩短停机时间。If it became corrupted or needed to be restored, the company wants to minimize downtime as much as possible.
AzureAzure Contoso 想要将应用程序移动到 Azure,而不在 Vm 上运行它。Contoso wants to move the application to Azure without running it on VMs. Contoso 想要使用 Azure 平台即服务 (PaaS) 用于数据层的服务。Contoso wants to use Azure platform as a service (PaaS) services for the data tier.

解决方案设计Solution design

固定目标和需求后,Contoso 设计和审查部署解决方案并识别迁移过程。After pinning down goals and requirements, Contoso designs and reviews a deployment solution and identifies the migration process. 还会标识用于迁移的工具和服务。The tools and services that it will use for migration are also identified.

当前应用程序Current application

MySQL 数据库存储用于公司 HR 部门的所有方面的员工数据。The MySQL database stores employee data that's used for all aspects of the company's HR department. 基于灯泡的应用程序用作处理员工 HR 请求的前端。A LAMP-based application is used as the front end to handle employee HR requests. Contoso 在世界各地有100000的员工,因此运行时间很重要。Contoso has 100,000 employees worldwide, so uptime is important.

建议的解决方案Proposed solution

使用 Azure 数据库迁移服务将数据库迁移到 Azure Database for MySQL 的实例。Use Azure Database Migration Service to migrate the database to an Azure Database for MySQL instance. 将所有应用程序和进程修改为使用新的 Azure Database for MySQL 实例。Modify all applications and processes to use the new Azure Database for MySQL instance.

数据库注意事项Database considerations

作为解决方案设计过程的一部分,Contoso 在 Azure 中查看了用于托管其 MySQL 数据的功能。As part of the solution design process, Contoso reviewed the features in Azure for hosting its MySQL data. 以下注意事项有助于公司决定使用 Azure:The following considerations helped the company decide to use Azure:

  • 类似于 Azure SQL 数据库,Azure Database for MySQL 允许 防火墙规则Similar to Azure SQL Database, Azure Database for MySQL allows for firewall rules.
  • Azure Database for MySQL 可用于 Azure 虚拟网络 ,以防止实例被公开访问。Azure Database for MySQL can be used with Azure Virtual Network to prevent the instance from being publicly accessible.
  • Azure Database for MySQL 具有 Contoso 必须为其审计员满足的符合性和隐私证书要求。Azure Database for MySQL has the required compliance and privacy certifications that Contoso must meet for its auditors.
  • 将使用读取副本增强报表和应用程序的处理性能。Report and application processing performance will be enhanced by using read replicas.
  • 仅 (使用 Azure Private Link) 无公共访问权限时,才能向内部网络流量公开服务。Ability to expose the service to internal network traffic only (no public access) by using Azure Private Link.
  • Contoso 选择不迁移到 Azure Database for MySQL 因为正在考虑使用 MariaDB 列存储和图形数据库模型。Contoso chose not to move to Azure Database for MySQL because it's considering using the MariaDB ColumnStore and graph database model in the future.
  • 除了 MySQL 功能,Contoso 是真正的开源项目的 proponent,并选择不使用 MySQL。Aside from MySQL features, Contoso is a proponent of true open-source projects and chose not to use MySQL.
  • 从应用程序到数据库的 带宽和延迟 足以根据所选网关 (Azure ExpressRoute 或站点到站点 VPN) 。The bandwidth and latency from the application to the database will be sufficient enough based on the chosen gateway (either Azure ExpressRoute or Site-to-Site VPN).

解决方案评审Solution review

Contoso 通过将利弊清单放置在一起来评估其建议的设计。Contoso evaluates the proposed design by putting together a pros and cons list.

注意事项Consideration 详细信息Details
优点Pros Azure Database for MySQL 提供99.99% 的财务支持服务级别协议 (SLA) 实现 高可用性Azure Database for MySQL offers a 99.99 percent financially backed service-level agreement (SLA) for high availability.

在每个季度的高峰负载期间,Azure 提供扩展或缩减功能。Azure offers the ability to scale up or down during peak load times each quarter. Contoso 可以通过购买 预留容量来更好地保存。Contoso can save even more by purchasing reserved capacity.

Azure 为 Azure Database for MySQL 提供了时间点还原和异地还原功能。Azure provides point-in-time restore and geo-restore capabilities for Azure Database for MySQL.

缺点Cons Contoso 仅限 Azure 支持的 MySQL 发行版本,目前为10.2 和10.3。Contoso is limited to the MySQL release versions that are supported in Azure, which are currently 10.2 and 10.3.

Azure Database for MySQL 存在一些 限制,例如缩小存储。Azure Database for MySQL has some limitations, such as scaling down storage.

建议的体系结构Proposed architecture

关系图显示了方案体系结构。 图1:方案体系结构。Diagram shows the scenario architecture. Figure 1: Scenario architecture.

迁移过程Migration process


在迁移 MySQL 数据库之前,需要确保这些实例满足成功迁移的所有 Azure 先决条件。Before you can migrate your MySQL databases, you need to ensure that those instances meet all the Azure prerequisites for a successful migration.

支持的版本Supported versions

MySQL 使用 x.y.z 版本控制方案,其中 x 是主要版本, y 是次版本, z 是修补程序版本。MySQL uses the x.y.z versioning scheme, where x is the major version, y is the minor version, and z is the patch version.

Azure 目前支持 MySQL 版本10.2.25 和10.3.16。Azure currently supports MySQL versions 10.2.25 and 10.3.16.

Azure 会自动管理修补程序更新的升级。Azure automatically manages upgrades for patch updates. 例如,10.2.21 到10.2.23。Examples are 10.2.21 to 10.2.23. 不支持次要和主要版本升级。Minor and major version upgrades aren't supported. 例如,不支持从 MySQL 10.2 升级到 MySQL 10.3。For example, upgrading from MySQL 10.2 to MySQL 10.3 isn't supported. 如果要从10.2 升级到10.3,请创建转储,并将其还原到使用新引擎版本创建的服务器。If you want to upgrade from 10.2 to 10.3, take a dump and restore it to a server created with the new engine version.


Contoso 需要设置一个从其本地环境到其 MySQL 数据库所在虚拟网络的虚拟网络网关连接。Contoso needs to set up a virtual network gateway connection from its on-premises environment to the virtual network where its MySQL database is located. 此连接允许本地应用程序在连接字符串更新时通过网关访问数据库。This connection allows the on-premises application to access the database over the gateway when the connection strings are updated.

关系图显示了迁移过程。 图2:迁移过程。Diagram shows the migration process. Figure 2: The migration process.


Contoso 管理员使用 Azure 数据库迁移服务迁移数据库,并按照分步 迁移教程操作。Contoso admins migrate the database by using Azure Database Migration Service and following the step-by-step migration tutorial. 他们可以使用 MySQL 5.6 或5.7 执行联机、脱机和混合 (预览版) 迁移。They can perform online, offline, and hybrid (preview) migrations by using MySQL 5.6 or 5.7.


Azure Database for MySQL 中支持 MySQL 8.0。MySQL 8.0 is supported in Azure Database for MySQL. 数据库迁移服务工具尚不支持该版本。The Database Migration Service tool doesn't yet support that version.

总的来说,它们必须执行以下任务:As a summary, they must do the following tasks:

  • 确保满足所有迁移先决条件:Ensure all migration prerequisites are met:

    • MySQL 数据库服务器源必须与 Azure Database for MySQL 支持的版本相匹配。The MySQL database server source must match the version that Azure Database for MySQL supports. Azure Database for MySQL 支持 MySQL 社区版、InnoDB 存储引擎,以及跨源和目标与相同版本的迁移。Azure Database for MySQL supports MySQL Community Edition, the InnoDB storage engine, and migration across source and target with the same versions.
    • 启用 my.ini (Windows) 或 my.cnf (Unix) 中的二进制日志记录。Enable binary logging in my.ini (Windows) or my.cnf (Unix). 启用二进制日志记录失败会导致迁移向导中出现以下错误: Error in binary logging. Variable binlog_row_image has value 'minimal.' please change it to 'full'. 有关详细信息,请参阅 MySQL 文档Failure to enable binary logging causes the following error in the Migration Wizard: Error in binary logging. Variable binlog_row_image has value 'minimal.' please change it to 'full'. For more information, see the MySQL documentation.
    • 用户必须具有 ReplicationAdmin 角色。User must have the ReplicationAdmin role.
    • 迁移没有外键和触发器的数据库架构。Migrate the database schemas without foreign keys and triggers.
  • 创建通过 ExpressRoute 或 VPN 连接到本地网络的虚拟网络。Create a virtual network that connects via ExpressRoute or a VPN to your on-premises network.

  • 使用连接到虚拟网络的 SKU 创建 Azure 数据库迁移服务实例 PremiumCreate an Azure Database Migration Service instance with a Premium SKU that's connected to the virtual network.

  • 确保实例可以通过虚拟网络访问 MySQL 数据库。Ensure that the instance can access the MySQL database via the virtual network. 请确保在虚拟网络级别、网络 VPN 和托管 MySQL 的计算机上,允许所有传入端口从 Azure 到 MySQL。Make sure that all incoming ports are allowed from Azure to MySQL at the virtual network level, the network VPN, and the machine that hosts MySQL.

  • 创建新的数据库迁移服务项目:Create a new Database Migration Service project:

    屏幕截图显示了如何创建新的数据库迁移服务项目 图3: Azure 数据库迁移服务项目。Screenshot shows how to create a new Database Migration Service project Figure 3: An Azure Database Migration Service project.

使用本机工具进行迁移Migration by using native tools

作为使用 Azure 数据库迁移服务的一种替代方法,Contoso 可以使用常见的实用程序和工具(如 MySQL 工作台、mysqldump、Toad 或 Navicat)来连接数据并将数据迁移到 Azure Database for MySQL。As an alternative to using Azure Database Migration Service, Contoso can use common utilities and tools such as MySQL Workbench, mysqldump, Toad, or Navicat to connect to and migrate data to Azure Database for MySQL.

  • 转储和还原 mysqldump:Dump and restore with mysqldump:
    • 在 mysqldump 中使用 exclude-trigger 选项可防止触发器在导入期间执行并提高性能。Use the exclude-triggers option in mysqldump to prevent triggers from executing during import and improve performance.
    • 使用单事务选项将转换隔离模式设置为 REPEATABLE READ ,并在 START TRANSACTION 转储数据之前发送 SQL 语句。Use the single-transaction option to set the translation isolation mode to REPEATABLE READ, and send a START TRANSACTION SQL statement before you dump data.
    • 使用 mysqldump 中的 "禁用密钥" 选项可在加载前禁用外键约束。Use the disable-keys option in mysqldump to disable foreign key constraints before load. 删除约束可以提高性能。Removing constraints provides performance gains.
    • 使用 Azure Blob 存储来存储备份文件并执行还原,以便进行更快的还原。Use Azure Blob Storage to store the backup files and perform the restore from there for faster restore.
    • 更新应用程序连接字符串。Update application connection strings.
    • 迁移数据库后,Contoso 必须将连接字符串更新为指向新的 Azure Database for MySQL。After the database is migrated, Contoso must update the connection strings to point to the new Azure Database for MySQL.

迁移后的清理Clean up after migration

迁移后,Contoso 需要备份本地数据库以进行保留,并停用本地 MySQL 数据库服务器。After migration, Contoso needs to back up the on-premises database for retention purposes and retire the on-premises MySQL database server.

查看部署Review the deployment

Azure 中存在已迁移的资源后,Contoso 需要全面运营并保护其新基础设施。With the migrated resources in Azure, Contoso needs to fully operationalize and secure its new infrastructure.


Contoso 需要:Contoso needs to:

  • 请确保其新的 Azure Database for MySQL 实例和数据库是安全的。Ensure that its new Azure Database for MySQL instance and databases are secure. 有关详细信息,请参阅 Azure Database for MySQL 中的安全性For more information, see Security in Azure Database for MySQL.
  • 查看防火墙和虚拟网络配置。Review the firewall and virtual network configurations.
  • 设置专用链接,以便将所有数据库流量保存在 Azure 和本地网络中。Set up Private Link so that all database traffic is kept inside Azure and the on-premises network.
  • 为标识启用 Microsoft Defender。Enable Microsoft Defender for Identity.


确保使用异地还原备份 Azure Database for MySQL 实例,以便在发生区域性服务中断的情况下,可以在配对的区域中使用备份。Ensure that the Azure Database for MySQL instances are backed up by using geo-restore, so that backups can be used in a paired region if a regional outage occurs.


确保 Azure Database for MySQL 资源具有资源锁,以防止删除该资源。Ensure that the Azure Database for MySQL resource has a resource lock to prevent it from being deleted. 无法还原已删除的服务器。Deleted servers can't be restored.

许可和成本优化Licensing and cost optimization

  • Azure Database for MySQL 可以向上或向下缩放。Azure Database for MySQL can be scaled up or down. 监视服务器和数据库的性能非常重要,可确保满足要求,同时最大限度地降低成本。Monitoring the performance of the server and databases is important to ensure your requirements are met while minimizing costs.
  • CPU 和存储都有相关的成本。Both CPU and storage have costs associated. 可以使用多个定价层。Several pricing tiers are available. 确保为每个数据工作负荷选择适当的定价计划。Be sure the appropriate pricing plan is selected for each data workload.
  • 每个读取副本根据所选的计算和存储计费。Each read replica is billed based on the compute and storage selected.
  • 使用保留容量节省成本。Use reserved capacity to save on costs.


在本文中,Contoso 将其 MySQL 数据库迁移到 Azure Database for MySQL 的实例。In this article, Contoso migrated its MySQL databases to an Azure Database for MySQL instance.