匯入及匯出資料的最佳做法

本頁面提供使用 Cloud SQL 匯入及匯出資料的最佳做法。如需將資料匯入 Cloud SQL 的逐步說明,請參閱「匯入資料」。

如要從 Cloud SQL 匯出資料,以便在您管理的 MySQL 執行個體中使用,請參閱「使用 SQL dump 檔案匯出及匯入」或「使用 CSV 檔案匯出及匯入」。

匯入及匯出的最佳作法

匯入及匯出資料時,請考量下列最佳做法:

匯入和匯出時使用相同的 SQL 模式

SQL 模式設定會影響 Cloud SQL 解讀 SQL 查詢的方式。舉例來說,如果您從未啟用嚴格 SQL 的資料庫匯出資料,然後嘗試匯入 Cloud SQL (預設會啟用嚴格 SQL),匯入作業可能會失敗。最佳做法是在匯入時使用與匯出時相同的 SQL 模式。

請查看來源和目標資料庫的 SQL 模式,確認兩者相容。請特別留意啟用嚴格 SQL 模式的旗標。如果資料庫未設定嚴格 SQL 模式,您可能需要在 Cloud SQL 中移除該模式。如果移除 Strict SQL,則必須設定其他旗標。

如要確認 Cloud SQL 執行個體已設定所需模式,請執行 SELECT @@GLOBAL.sql_mode;。

請勿使用 Cloud Storage「要求者付費」值區

您無法使用已啟用付款者為要求者功能的 Cloud Storage bucket,從 Cloud SQL 匯入及匯出資料。

盡可能降低匯出作業對效能的影響

從 Cloud SQL 執行標準匯出作業時,資料庫會保持上線狀態。如果匯出的資料較小,影響可能微乎其微。不過,如果資料庫中有大型資料庫或大型物件 (例如 BLOB),匯出作業可能會降低資料庫效能。這可能會影響對資料庫執行查詢和作業所需的時間。匯出作業開始後,即使資料庫回應速度變慢,也無法停止。

如要避免匯出時回應速度緩慢,您可以:

  1. 從唯讀副本匯出資料。如果您經常匯出資料 (每天或更頻繁),但匯出的資料量不大,這可能是個不錯的選擇。如要從唯讀副本執行匯出作業,請在唯讀副本執行個體上使用 Cloud de Confiance by S3NS 控制台、gcloud 或 REST API 匯出函式。如要進一步瞭解如何建立及管理唯讀副本,請參閱「建立唯讀副本」。

  2. 使用無伺服器匯出功能。使用無伺服器匯出功能時,Cloud SQL 會建立獨立的臨時執行個體,用於卸載匯出作業。卸載匯出作業後,主要執行個體上的資料庫就能繼續以平常的效能速率提供查詢及執行作業。資料匯出完成後,系統會自動刪除臨時執行個體。如果您要一次性匯出大型資料庫,這個選項可能是不錯的選擇。使用 Cloud de Confiance by S3NS 控制台、gcloud或 REST API 匯出函式,並搭配 offload 旗標,即可執行無伺服器匯出作業。

    在無伺服器匯出作業期間,您可以執行其他作業,例如執行個體編輯、匯入和容錯移轉。不過,如果您選取 delete,匯出作業會在您刪除執行個體後停止,且不會匯出任何資料。

    請參閱下表,瞭解無伺服器匯出作業執行期間,哪些作業可能會遭到封鎖:
    目前作業 新作業 遭到封鎖?
    任何作業 無伺服器匯出 是
    無伺服器匯出 無伺服器匯出以外的任何作業 否
    無伺服器匯出以外的任何作業 無伺服器匯出以外的任何作業 是

    無伺服器匯出作業需要建立臨時執行個體,因此所需時間比標準匯出作業長。至少需要五分鐘以上,但如果是較大的資料庫,可能需要更久。決定要使用的匯出類型前,請先考量對時間、效能和成本的影響。

建立 SQL dump 檔案時,請使用正確的旗標

將資料匯出至 SQL 轉儲檔案時,如果未使用正確的旗標,匯入作業可能會失敗。如要瞭解如何建立 SQL dump 檔案,以便匯入 Cloud SQL,請參閱「建立 SQL dump 檔案」。

壓縮資料以減少費用

Cloud SQL 支援匯入及匯出已壓縮及未壓縮的檔案。壓縮功能可大幅節省 Cloud Storage 的儲存空間,並降低儲存成本,特別是在匯出大型執行個體時。

匯出 SQL 傾印或 CSV 檔案時,請使用 .gz 副檔名壓縮資料。匯入副檔名為 .gz 的檔案時,系統會自動解壓縮。

減少長時間執行的匯入和匯出程序

視處理的資料量而定,匯入 Cloud SQL 和從 Cloud SQL 匯出可能需要很長時間才能完成。這可能會有下列影響:

  • 您無法停止長時間執行的 Cloud SQL 執行個體作業。
  • 每個執行個體一次只能執行一項匯入或匯出作業,且長時間執行的匯入或匯出作業會阻擋其他作業,例如每日自動備份。無伺服器匯出功能可讓您執行其他作業,包括編輯執行個體、匯入、容錯移轉,以及解除每日自動備份作業的封鎖。

如要縮短每項作業的完成時間,可以使用 Cloud SQL 匯入或匯出功能,分批處理較少量的資料。

如要匯出,您可以從唯讀副本執行匯出作業,或使用無伺服器匯出功能,盡可能降低對資料庫效能的影響,並在匯出作業執行期間,允許其他作業在執行個體上執行。

如需更多提示,請參閱「診斷 Cloud SQL 執行個體的問題」。

使用 InnoDB

InnoDB 是 MySQL 執行個體唯一支援的儲存引擎。

您可以透過一組 sed 指令碼連結 mysqldump 的輸出檔案,將資料表從 MyISAM 轉換成 InnoDB,方法如下:

mysqldump --databases [DATABASE_NAME] \
-h [INSTANCE_IP] -u [USERNAME] -p [PASSWORD] \
--hex-blob --default-character-set=utf8mb4 | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/g' > [DATABASE_FILE].sql

包含中繼資料且其中有 DEFINER 子句的 MySQL 匯入和遷移工作

MySQL 匯入或遷移工作不會遷移使用者資料,因此如果來源和 dump 檔案包含使用者以 DEFINER 子句定義的中繼資料,匯入或遷移作業會失敗,因為使用者尚未存在。

如要找出中繼資料中存在哪些 DEFINER 值,請使用下列查詢 (或在 dump 檔案中搜尋),並檢查是否有 root%localhost 或目標執行個體中不存在的使用者項目。

SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.EVENTS;
SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.ROUTINES;
SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.TRIGGERS;
SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.VIEWS;

如要從包含這類中繼資料的來源執行匯入或遷移工作,您可以採取下列任一做法:

  • 在開始匯入或遷移工作前,請先在目標 Cloud SQL 執行個體建立使用者。
  • 在 MySQL 來源執行個體或 dump 檔案中,將 DEFINER 子句更新為 INVOKER,再開始匯入或遷移工作。

驗證匯入的資料庫

匯入作業完成後,請連線至資料庫並執行適當的資料庫指令,確認內容正確無誤。例如,連線並列出資料庫、資料表和特定項目。

已知限制

如需已知限制清單,請參閱「匯入及匯出資料的問題」。

自動執行匯出作業

雖然 Cloud SQL 未提供內建方法來自動匯出資料庫,但您可以使用多個 Cloud de Confiance by S3NS元件建構自己的自動化工具。詳情請參閱這篇教學課程。

疑難排解

以下各節說明匯入和匯出作業的疑難排解。

排解匯入作業問題

問題 疑難排解
HTTP Error 409: Operation failed because another operation was already in progress。 執行個體已有待處理的作業。系統一次只能執行一項作業。請待目前的作業完成後再提出要求。
匯入作業時間過長。 有效連線數量過多可能會干擾匯入作業。

關閉未使用的作業。檢查 Cloud SQL 執行個體的 CPU 和記憶體用量,確保有足夠的可用資源。如要確保有最多資源可供匯入作業使用,最好的方法是在開始作業前重新啟動執行個體。

重新啟動:

  • 關閉所有連線。
  • 結束可能耗用資源的工作。
如果 dump 檔案中有一或多位參照的使用者不存在,匯入作業就會失敗。 匯入 dump 檔案前,如果資料庫使用者擁有傾印資料庫中的物件,或已取得傾印資料庫中的物件存取權,這類使用者都必須存在於目標資料庫中。如果沒有,匯入作業就無法以原始擁有權或權限重新建立物件。

匯入前,請先建立資料庫使用者。

匯入作業失敗,並顯示資料表不存在的錯誤。 資料表可能會參照其他資料表的外鍵,而且其中一或多個資料表在匯入作業期間可能仍不存在 (取決於匯入順序)。

建議做法:

在傾印檔案開頭加入下列指令行:

SET FOREIGN_KEY_CHECKS=0;
  

此外,請在 dump 檔案結尾加入以下這行:

SET FOREIGN_KEY_CHECKS=1;
  

這些設定會在匯入作業進行期間停用資料完整性檢查,並在資料載入後重新啟用。這不會影響資料庫中資料的完整性,因為資料在建立 dump 檔案時已通過驗證。

排解匯出作業問題

問題 疑難排解
HTTP Error 409: Operation failed because another operation was already in progress. 執行個體已有待處理的作業。系統一次只能執行一項作業。請待目前的作業完成後再提出要求。
HTTP Error 403: The service account does not have the required permissions for the bucket. 請確認值區存在,且 Cloud SQL 執行個體 (執行匯出作業) 的服務帳戶具備 Storage Object Creator 角色 (roles/storage.objectCreator),可將匯出內容載入值區。請參閱「Cloud Storage 的 IAM 角色」。
CSV 匯出成功,但 SQL 匯出失敗。 CSV 和 SQL 格式的匯出方式不同。SQL 格式會匯出整個資料庫,可能需要較長時間才能完成。CSV 格式可讓您定義要將資料庫的哪些元素納入匯出作業。

使用 CSV 匯出功能,只匯出所需內容。

匯出時間過長。 Cloud SQL 不支援並行同步作業。

使用匯出卸載。概括來說,在匯出卸載作業中,Cloud SQL 不會在來源執行個體上發出匯出作業,而是啟動卸載執行個體來執行匯出作業。匯出卸載功能有多項優點,包括提升來源執行個體的效能,以及在匯出作業執行期間解除管理作業的封鎖。如果使用匯出卸載功能,總延遲時間可能會增加,增加的時間長度取決於啟動卸載執行個體所需的時間。一般來說,如果匯出作業的大小合理,延遲時間就不會太長。不過,如果匯出作業規模夠小,您可能會發現延遲時間增加。

您希望自動匯出。 Cloud SQL 不提供自動匯出的方法。

您可以使用 Cloud Scheduler、Pub/Sub 和 Cloud Run functions 等 Cloud de Confiance by S3NS產品,建構自己的自動匯出系統,類似這篇自動備份文章。

後續步驟