使用傾印和還原來將 MySQL 資料庫移轉至適用於 MySQL 的 Azure 資料庫 - 彈性伺服器

  • 發行項

適用於：適用於 MySQL 的 Azure 資料庫 - 單一伺服器適用於 MySQL 的 Azure 資料庫 - 彈性伺服器

重要

適用於 MySQL 的 Azure 資料庫單一伺服器已位於淘汰路徑上。 強烈建議您升級至適用於 MySQL 的 Azure 資料庫彈性伺服器。 如需移轉至適用於 MySQL 的 Azure 資料庫彈性伺服器詳細資訊，請參閱適用於 MySQL 的 Azure 資料庫單一伺服器發生什麼事？

本文說明兩個常見方法，讓您在適用於 MySQL 的 Azure 資料庫彈性伺服器中備份和還原資料庫。

  • 從命令列進行傾印和還原 (使用 mysqldump)。
  • 使用 phpMyAdmin 進行傾印和還原。

您也可以參閱資料庫移轉指南，取得將資料庫移轉至適用於 MySQL 的 Azure 資料庫彈性伺服器詳細資訊和使用案例。 此指南中提供的指引可引導您成功規劃及執行將 MySQL 移轉至 Azure 的作業。

開始之前

若要逐步執行本作法指南，您需要具備：

提示

如果您想要移轉大型資料庫 (資料庫大小超過 1 TB)，您可以考慮使用可支援平行匯出和匯入的社群工具，例如 mydumper/myloader。 了解如何移轉大型 MySQL 資料庫。

傾印和還原的常見使用案例

最常見的使用案例如下：

  • 從其他受控服務提供者移動 - 基於安全考量，大部分的受控服務提供者可能無法提供實體儲存體檔案的存取權，因此邏輯備份和還原是移轉的唯一選項。

  • 從內部部署環境或虛擬機器移轉 - 適用於 MySQL 的 Azure 資料庫彈性伺服器不支援還原實體備份，所以邏輯備份和還原會是「唯一」的方法。

  • 將備份儲存體從本機備援移至異地備援儲存體 - 適用於 MySQL 的 Azure 資料庫彈性伺服器僅允許在伺服器建立期間為備份設定本機備援或異地備援儲存體。 伺服器佈建完成之後，就無法變更備份儲存體備援選項。 若要將備份儲存體從本機備援儲存體移至異地備援儲存體，傾印和還原是唯一的選項。

  • 從其他儲存引擎移轉至 InnoDB - 適用於 MySQL 的 Azure 資料庫彈性伺服器僅支援 InnoDB 儲存引擎，因此不支援其他儲存引擎。 如果您的資料表是使用其他儲存引擎設定，請將您的資料表轉換成 InnoDB 引擎格式，然後再移轉至適用於 MySQL 的 Azure 資料庫彈性伺服器。

    例如，如果您的 WordPress 或 WebApp 使用 MyISAM 資料表，請先藉由移轉至 InnoDB 格式來轉換這些資料表，然後再還原至適用於 MySQL 的 Azure 資料庫彈性伺服器。 使用子句 ENGINE=InnoDB 以設定建立新資料表時使用的引擎，然後在還原之前將資料傳送到相容的資料表。

    INSERT INTO innodb_table SELECT * FROM myisam_table ORDER BY primary_key_columns

重要

  • 若要避免任何相容性問題，請確定當傾印資料庫時，在來源和目的地系統上使用相同版本的 MySQL。 例如，如果現有的 MySQL 伺服器是 5.7 版，則您應該移轉至已設定為執行 5.7 版的適用於 MySQL 的 Azure 資料庫彈性伺服器執行個體。 mysql_upgrade 命令在適用於 MySQL 伺服器的 Azure 資料庫彈性伺服器執行個體中無法運作，因此並不支援。
  • 如果您要在 MySQL 版本之間升級，請先將較低版本的資料庫傾印或匯出到自己環境中較高版本的 MySQL。 然後執行 mysql_upgrade，之後再嘗試移轉至適用於 MySQL 的 Azure 資料庫彈性伺服器執行個體。

效能考量

若要最佳化效能，請在傾印大型資料庫時注意這些考量：

  • 傾印資料庫時在 mysqldump 中使用 exclude-triggers 選項。 從傾印檔案排除觸發程序以避免在資料還原期間引發觸發程序命令。
  • 使用 single-transaction 選項將交易隔離模式設為 REPEATABLE READ，然後在傾印資料之前，將 START TRANSACTION 的 SQL 陳述式傳送到伺服器。 在單一交易中傾印許多資料表會導致在還原期間耗用某些額外的儲存體。 single-transaction 選項和 lock-tables 選項是互斥的，因為 LOCK TABLES 會導致隱含認可任何暫止交易。 若要傾印大型資料表，請結合 single-transaction 選項與 quick 選項。
  • 使用包含數個 VALUE 清單的 extended-insert 多個資料列語法。 這會產生較小的傾印檔案，並在重新載入檔案時加速插入。
  • 傾印資料庫時在 mysqldump 中使用 order-by-primary 選項，以便將資料以主索引鍵的順序編寫指令碼。
  • 傾印資料時在 mysqldump 中使用 disable-keys 選項，以在載入之前停用外部索引鍵限制式。 停用外部索引鍵檢查會提供效能提升。 啟用限制式並且確認載入之後的資料，以確保參考完整性。
  • 適當時使用資料分割資料表。
  • 平行載入資料。 避免會導致您達到資源限制的太多平行處理原則，以及使用 Azure 入口網站中可用的計量監視資源。
  • 傾印資料庫時在 mysqldump 中使用 defer-table-indexes 選項，以便在載入資料表資料之後建立索引。
  • 請將備份檔案複製到 Azure blob/存放區，並從該處執行還原，這樣應該會比在網際網路上執行還原更快。

在目標適用於 MySQL 伺服器的 Azure 資料庫彈性伺服器執行個體上建立資料庫

在您要移轉資料的目標適用於 MySQL 伺服器的 Azure 資料庫彈性伺服器執行個體上建立空白資料庫。 使用 MySQL Workbench 或 mysql.exe 之類的工具來建立資料庫。 資料庫名稱可以與包含傾印資料的資料庫名稱相同，或者您可以建立名稱不同的資料庫。

若要連線，在適用於 MySQL 的 Azure 資料庫彈性伺服器執行個體上，找到 [概觀] 中的連線資訊。

Find the connection information in the Azure portal

將連線資訊新增至 MySQL Workbench。

MySQL Workbench Connection String

準備目標適用於 MySQL 的 Azure 資料庫彈性伺服器執行個體，以快速載入資料

若要準備目標適用於 MySQL 的 Azure 資料庫彈性伺服器執行個體，以快速載入資料，則必須變更下列伺服器參數和設定。

  • max_allowed_packet – 設為 1073741824 (也就是 1GB)，以防止因為長資料列而造成任何溢位問題。
  • slow_query_log – 設定為 [關閉]，以關閉慢速查詢記錄。 這可排除在資料載入期間因慢速查詢記錄而造成的額外負荷。
  • query_store_capture_mode - 設定為 NONE，以關閉查詢存放區。 這可排除查詢存放區取樣活動所造成的額外負荷。
  • innodb_buffer_pool_size – 在移轉期間，從入口網站的定價層將伺服器擴大至 32 vCore 記憶體最佳化 SKU，以提高 innodb_buffer_pool_size。 Innodb_buffer_pool_size 只能藉由擴大適用於 MySQL 的 Azure 資料庫彈性伺服器執行個體的計算來增加。
  • innodb_io_capacity 和 innodb_io_capacity_max - 在 Azure 入口網站中將伺服器參數變更為 9000，以改善 IO 使用率，將移轉速度最佳化。
  • innodb_write_io_threads 和 innodb_write_io_threads - 在 Azure 入口網站中將伺服器參數變更為 4，以改善移轉的速度。
  • 擴大儲存層 – 適用於 MySQL 的 Azure 資料庫彈性伺服器的 IOPS 會隨著儲存層的成長而逐漸增加。 如需更快速的載入速度，您可以增加儲存層以增加佈建的 IOPS。 請記住，儲存體只能擴大，而不能縮小。

完成移轉之後，您可以將伺服器參數和計算層設定還原回其先前的值。

使用 mysqldump 公用程式進行傾印和還原

使用 mysqldump 從命令列建立備份檔案

若要在本機內部部署伺服器或虛擬機器中備份現有的 MySQL 資料庫，請執行下列命令：

mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]

提供的參數如下：

  • [uname] 您的資料庫使用者名稱
  • [pass] 您的資料庫密碼 (請注意 -p 與密碼之間沒有空格)
  • [dbname] 您的資料庫名稱
  • [backupfile.sql] 資料庫備份的檔案名稱
  • [--opt] mysqldump 選項

例如，若要將 MySQL 伺服器上使用者名稱為 'testuser' 且無密碼之名為 'testdb' 的資料庫備份到 testdb_backup.sql 檔案，請使用下列命令。 此命令會將 testdb 資料庫備份至名為 testdb_backup.sql 的檔案，其中包含重新建立資料庫所需的所有 SQL 陳述式。 請確定使用者名稱 'testuser' 對傾印的資料表至少有 SELECT 權限、對傾印的檢視具有 SHOW VIEW 權限、對傾印的觸發程序具有 TRIGGER 權限，且若未使用 --single-transaction 選項則具有 LOCK TABLES 權限。

GRANT SELECT, LOCK TABLES, SHOW VIEW ON *.* TO 'testuser'@'hostname' IDENTIFIED BY 'password';

現在，執行 mysqldump 以建立 testdb 資料庫的備份

mysqldump -u root -p testdb > testdb_backup.sql

若要在資料庫中選取要備份的特定資料表，請列出以空格分隔的資料表名稱。 例如，如果只要從 'testdb' 備份 table1 和 table2 資料表，請遵循下列範例：

mysqldump -u root -p testdb table1 table2 > testdb_tables_backup.sql

若要一次備份多個資料庫，請使用 --database 參數，並列出以空格分隔的資料庫名稱。

mysqldump -u root -p --databases testdb1 testdb3 testdb5 > testdb135_backup.sql

使用命令列還原 MySQL 資料庫

建立目標資料庫後，您可以使用 mysql 命令，從傾印檔案將資料還原至新建立的特定資料庫。

mysql -h [hostname] -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]

在此範例中，將資料還原至目標適用於 MySQL 伺服器的 Azure 資料庫彈性伺服器執行個體上新建立的資料庫。

以下是如何將此 mysql 用於單一伺服器的範例：

mysql -h mydemoserver.mysql.database.azure.com -u myadmin@mydemoserver -p testdb < testdb_backup.sql

以下是如何將此 mysql 用於彈性伺服器的範例：

mysql -h mydemoserver.mysql.database.azure.com -u myadmin -p testdb < testdb_backup.sql

注意

您也可以使用 MySQL Workbench 用戶端公用程式還原 MySQL 資料庫。

使用 PHPMyAdmin 傾印和還原

依照下列步驟，使用 PHPMyadmin 來傾印和還原資料庫。

注意

對於單一伺服器，使用者名稱的格式必須是 'username@servername'，但對於彈性伺服器，則可以直接使用 'username'。如果對彈性伺服器使用 'username@servername'，連線將會失敗。

使用 PHPMyadmin 匯出

若要匯出，您可以使用一般工具 phpMyAdmin，而您可能已在環境中本機安裝此工具。 使用 PHPMyAdmin 匯出 MySQL 資料庫：

  1. 開啟 phpMyAdmin。
  2. 選取您的資料庫。 選取左邊清單中的資料庫名稱。
  3. 選取 [匯出] 連結。 新的分頁隨即出現，以供檢視資料庫的傾印。
  4. 在 [匯出] 區域中，選取 [全選] 連結來選擇資料庫中的資料表。
  5. 在 [SQL 選項] 區域中，選取適當的選項。
  6. 依序選取 [另存新檔] 和對應的壓縮選項，然後選取 [執行] 按鈕。 接著應該會出現一個對話方塊，提示您在本機儲存檔案。

使用 PHPMyAdmin 匯入

匯入資料庫的程序與匯出類似。 執行下列動作：

  1. 開啟 phpMyAdmin。
  2. 在 phpMyAdmin 設定頁面上選取 [新增]，新增適用於 MySQL 的 Azure 資料庫彈性伺服器執行個體。 提供連線詳細資料和登入資訊。
  3. 建立已適當命名的資料庫，然後在畫面左邊選取它。 若要重寫現有的資料庫，請選取資料庫名稱、選取資料表名稱旁的所有核取方塊，然後選取 [卸除] 以刪除現有的資料表。
  4. 選取 SQL 連結，以顯示您可以在其中輸入 SQL 命令或上傳 SQL 檔案的分頁。
  5. 您可以使用瀏覽按鈕來尋找資料庫檔案。
  6. 選取 [執行] 按鈕以匯出備份、執行 SQL 命令，並重新建立您的資料庫。

已知問題

如需已知問題、秘訣與技巧，建議您查看我們的技術社群部落格 \(英文\)。

下一步