將 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 必須知道資料庫只能供其內部應用程式存取,而且無法透過網際網路顯示或存取。Contoso needs to know that the database is accessible only to its internal applications and isn't visible or accessible via the internet.
監視Monitoring 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 資料庫會儲存公司人力資源部門的所有層面所使用的員工資料。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 資料庫移轉服務,將資料庫移轉至適用于 MySQL 的 Azure 資料庫實例。Use Azure Database Migration Service to migrate the database to an Azure Database for MySQL instance. 將所有應用程式和進程修改為使用新的適用于 MySQL 的 Azure 資料庫實例。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 Database,適用于 MySQL 的 Azure 資料庫允許 防火牆規則Similar to Azure SQL Database, Azure Database for MySQL allows for firewall rules.
  • 適用于 MySQL 的 azure 資料庫可以搭配 Azure 虛擬網路 使用,以防止實例可供公開存取。Azure Database for MySQL can be used with Azure Virtual Network to prevent the instance from being publicly accessible.
  • 適用于 MySQL 的 Azure 資料庫具有 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 選擇不移至適用于 MySQL 的 Azure 資料庫,因為它會考慮未來使用適用于 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 適用于 MySQL 的 Azure 資料庫提供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 針對適用于 MySQL 的 Azure 資料庫提供時間點還原和異地還原功能。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.

適用于 MySQL 的 Azure 資料庫有一些 限制,例如調整儲存體。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

準備Preparation

在遷移 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.

網路Network

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.

移轉Migration

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.

注意

適用于 MySQL 的 Azure 資料庫中支援 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 資料庫伺服器來源必須符合適用于 MySQL 的 Azure 資料庫所支援的版本。The MySQL database server source must match the version that Azure Database for MySQL supports. 適用于 MySQL 的 Azure 資料庫支援 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.

  • 使用 Premium 連線到虛擬網路的 SKU 來建立 Azure 資料庫移轉服務實例。Create 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 中的 [排除-觸發程式] 選項,以防止在匯入期間執行觸發程式,並改善效能。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 必須將連接字串更新為指向新的適用于 MySQL 的 Azure 資料庫。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.

安全性Security

Contoso 必須:Contoso needs to:

  • 確定其新的適用于 MySQL 的 Azure 資料庫實例和資料庫都是安全的。Ensure that its new Azure Database for MySQL instance and databases are secure. 如需詳細資訊,請參閱 適用于 MySQL 的 Azure 資料庫中的安全性For more information, see Security in Azure Database for MySQL.
  • 檢查防火牆和虛擬網路設定。Review the firewall and virtual network configurations.
  • 設定 Private Link,以在 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.

備份Backups

請確定使用異地還原來備份適用于 MySQL 的 Azure 資料庫實例,以便在發生區域性中斷的情況下,將備份用於配對的區域中。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.

重要

請確定適用于 MySQL 的 Azure 資料庫資源具有資源鎖定,以防止其遭到刪除。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

  • 適用于 MySQL 的 Azure 資料庫可以向上或向下調整。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.

結論Conclusion

在本文中,Contoso 將 MySQL 資料庫遷移至適用于 MySQL 的 Azure 資料庫實例。In this article, Contoso migrated its MySQL databases to an Azure Database for MySQL instance.