使用自訂匯入功能來設定從大型外部資料庫的複製作業

本頁面說明如何使用自訂匯入作業,設定外部伺服器複製。如需從大型外部資料庫複製資料,建議採用這些步驟。

您必須完成本頁的所有步驟。完成後,您就能像管理及監控其他 Cloud SQL 執行個體一樣,管理及監控副本。

這項程序僅適用於設定為使用全域交易 ID (GTID) 複製功能的外部伺服器。您必須先將外部伺服器的資料載入 Cloud SQL 副本,才能啟動複製作業。如果不使用 GTID 複製功能,Cloud SQL 就無法找出要從哪個二進位記錄位置開始複製。如果無法使用以 GTID 為依據的複製作業,則必須設定傾印工具,在傾印程序期間建立全域唯讀鎖定。

事前準備

開始之前,請先設定外部伺服器、建立來源代表執行個體,並設定 Cloud SQL 副本。

更新複製功能使用者的權限

外部伺服器上的複製使用者已設為接受來自任何主機 (%) 的連線。建議您更新這個使用者帳戶,使其只能與 Cloud SQL 副本搭配使用。在來源資料庫伺服器上開啟終端機,然後輸入下列指令:

mysql 用戶端

    UPDATE mysql.user
    SET Host='NEW_HOST' WHERE Host='OLD_HOST' AND User='USERNAME';
    GRANT REPLICATION SLAVE, EXECUTE ON *.*
    TO 'GCP_USERNAME'@'HOST';
    FLUSH PRIVILEGES;

範例

UPDATE mysql.user
SET Host='192.0.2.0' WHERE Host='%' AND User='replicationUser';
GRANT REPLICATION SLAVE, EXECUTE ON *.*
TO 'gcp_user'@'gmail.com';
FLUSH PRIVILEGES;
屬性 說明
NEW_HOST 指定 Cloud SQL 副本的傳出 IP。
OLD_HOST 目前指派給 Host 的值,您想要變更這個值。
USERNAME 外部伺服器上的複製使用者帳戶。
GCP_USERNAME GCP 使用者帳戶的使用者名稱。
HOST GCP 使用者帳戶的主機名稱。

將 Cloud SQL 副本設為主要執行個體

Cloud SQL 副本執行個體為唯讀,因此如要執行自訂匯入作業,您必須將 Cloud SQL 副本升級為獨立執行個體。初始資料匯入完成後,您會將執行個體降級回副本。

執行自訂轉儲和匯入作業

本節說明如何建立 dump 檔案,並使用 mydumper 或 mysqldump 用戶端公用程式,將檔案匯入最終的 Cloud SQL 副本。

傾印資料時,您可能需要排除 MySQL 一般資料庫,包括 mysql 和 sys (如果來源執行個體中存在這些資料庫)。否則資料匯入作業會失敗。請參閱「如何排除 (或納入) 資料庫?」。

使用 mydumper 和 myloader

如要建立 dump 檔案並匯入 Cloud SQL,請按照下列步驟操作:

  1. 使用 mydumper 建立外部伺服器資料庫的 dump 檔案。

       $ mydumper -u USERNAME -p PASSWORD \
                  --threads=16 -o ./backup \
                  -h HOST \
                  --no-locks \
                  --regex '^(?!(mysql\.|sys\.))'
    屬性 說明
    USERNAME 外部伺服器上具備資料庫讀取權限的複製使用者帳戶或使用者帳戶名稱。
    PASSWORD 複製使用者密碼。
    HOST 外部伺服器的 IPv4 或 DNS 位址。
  2. 使用 myloader 將資料匯入 Cloud SQL 執行個體。

     $ myloader -u REPLICA_USERNAME -p REPLICA_PASSWORD \
                --threads=16 \
                -d ./backup -h HOST -o
    屬性 說明
    REPLICA_USERNAME Cloud SQL 執行個體上的使用者帳戶。
    REPLICA_PASSWORD Cloud SQL 執行個體使用者密碼。
    HOST Cloud SQL 執行個體的 IPv4。
  3. 記下資料轉儲的 GTID 或二進位記錄資訊。使用預存程序設定複製作業時,您需要這項資訊。

    如要取得資料轉儲的 GTID 或二進位記錄資訊,請執行下列指令:

      sudo cat ./backup/metadata

使用mysqldump

  1. 使用 mysqldump 建立傾印:

    mysqldump

    mysqldump \
        --host=EXTERNAL_HOST \
        --port=EXTERNAL_PORT \
        --user=USERNAME\
        --password=PASSWORD \
        --databases=DATABASE_LIST  \
        --hex-blob \
        --master-data=EXTERNAL_DATA  \
        --no-autocommit \
        --default-character-set=utf8mb4 \
        --single-transaction \
        GTID_PURGED \
        ADD_DROP_TABLE \
        ROUTINES \
        COMPRESS \
        GZIP
    屬性 說明
    EXTERNAL_HOST 外部伺服器的 IPv4 或 DNS 位址。
    EXTERNAL_PORT 外部伺服器的通訊埠。如果外部伺服器託管於 Cloud SQL,則為 3306。
    USERNAME 外部伺服器上具備資料庫讀取權限的複製使用者帳戶或使用者帳戶名稱。
    USER_PASSWORD 複製使用者密碼。
    DATABASE_LIST 外部伺服器上所有資料庫的清單 (以空格分隔),但不包括系統資料庫 (sys、mysql、performance_schema 和 information_schema)。請使用 SHOW DATABASES MySQL 指令列出資料庫。
    EXTERNAL_DATA 如果外部伺服器不支援 GTID,且您有權存取該伺服器的全域讀取鎖定,請使用 --master-data=1。否則,請勿使用這項屬性。
    GTID_PURGED 如果外部伺服器支援 GTID,請使用 --set-gtid-purged=on,否則請勿使用這項屬性。
    ADD_DROP_TABLE 如要在每個 CREATE TABLE 陳述式前加上 DROP TABLE 陳述式,請加入 --add-drop-table。
    ROUTINES 如要在傾印資料庫的輸出內容中顯示預存常式 (例如程序和函式),請加入 --routines。
    COMPRESS 如要壓縮 Cloud SQL 副本和外部伺服器之間傳送的所有資訊,請使用 --compress。
    GZIP 如要進一步壓縮 dump 檔案,請使用 | gzip。如果資料庫包含無法妥善壓縮的資料,例如二進位不可壓縮資料或 JPG 圖片,請勿使用這項功能。

    範例

    mysqldump \
        --host=192.0.2.1 \
        --port=3306 \
        --user=replicationUser \
        --password \
        --databases guestbook journal \
        --hex-blob \
        --master-data=1 \
        --no-autocommit \
        --default-character-set=utf8mb4 \
        --single-transaction \
        --compress \
        | gzip
  2. 記下資料轉儲的 GTID 或二進位記錄資訊。您需要這項資訊,才能使用 Cloud SQL 預存程序設定複製作業。

    如要查看 GTID,請尋找類似下列內容的行:

       SET @@GLOBAL.GTID_PURGED='32eb1e6a-17b6-11ea-a39e-06d94ac3ec98:1-33496';

    如果是二進位記錄,請尋找類似下列內容的行:

       CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin-changelog.033877', MASTER_LOG_POS=360;
  3. 在 dump 檔案中,移除下列需要超級權限的程式碼行。 Cloud SQL 使用者不具備超級權限,因此這些指令行會導致匯入失敗。

    以 GTID 為基礎的複製模式:移除傾印中的 SET GTID_PURGED 陳述式,以及工作階段變數設定陳述式。例如:

       ...
       SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
       SET @@SESSION.SQL_LOG_BIN= 0;
       ...
       SET @@GLOBAL.GTID_PURGED='32eb1e6a-17b6-11ea-a39e-06d94ac3ec98:1-33496';
       ...
       SET @@SESSION.SQL_LOG_BIN=@MYSQLDUMP_TEMP_LOG_BIN;

    如果是以二進位記錄為基礎的複製作業,請移除 CHANGE MASTER 陳述式。例如:

       ...
       CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin-changelog.033877', MASTER_LOG_POS=360;
        ...
  4. 使用 mysql CLI 將資料匯入 Cloud SQL 副本:

    mysql

    mysql -h REPLICA_HOST -u REPLICA_USER \
    -p REPLICA_DATABASE_NAME RESULT_FILE
    屬性 說明
    REPLICA_HOST MySQL 伺服器所在的主機。
    REPLICA_USER 連線至伺服器時要使用的 MySQL 使用者名稱。
    REPLICA_DATABASE_NAME 資料所在的資料庫名稱。
    RESULT_FILE 要匯入的 dump 檔案名稱。

    範例

      mysql -h 255.255.255.255 -u replica_username -p replica_db < result.sql
    

您也可以使用 Cloud de Confiance bucket 匯入 dump 檔案。請參閱「將 SQL dump 檔案中的資料匯入 Cloud SQL」。

降級 Cloud SQL 執行個體

如要將 Cloud SQL 執行個體降級為 Cloud SQL 副本,請對執行個體使用 demoteMaster 方法。

  1. 準備要求 JSON 檔案,並在當中指定要降級的執行個體名稱。

    來源 JSON

     {
        "demoteMasterContext": {
          "masterInstanceName": SOURCE_REPRESENTATION_INSTANCE_NAME,
          "skipReplicationSetup": true
          }
     }
    屬性 說明
    SOURCE_REPRESENTATION_INSTANCE_NAME 來源表示法執行個體的名稱。

    範例

       {
         "demoteMasterContext": {
           "masterInstanceName": "cloudsql-source-instance",
           "skipReplicationSetup": true
         }
       }
  2. 開啟終端機,並使用下列指令叫用 demoteMaster:

    curl

      gcloud auth login
      ACCESS_TOKEN="$(gcloud auth print-access-token)"
      curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
        --header 'Content-Type: application/json' \
        --data @JSON_PATH \
        -X POST \
      https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT-ID/instances/INSTANCE-NAME/demoteMaster
    屬性 說明
    JSON_PATH JSON 檔案的路徑。
    PROJECT_ID Cloud de Confiance中的專案 ID。
    INSTANCE-NAME 要降級的執行個體名稱。

    範例

       gcloud auth login
       ACCESS_TOKEN="$(gcloud auth print-access-token)"
       curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
         --header 'Content-Type: application/json' \
         --data @./source.json \
         -X POST \
       https://sqladmin.googleapis.com/sql/v1beta4/projects/MyProject/instances/cloudsql-replica-instance/demoteMaster

完成後應看到的畫面

為確保執行個體設定正確,請前往 Cloud SQL 執行個體頁面。

您應該會看到來源代表執行個體和 Cloud SQL 副本。類似以下範例:

執行個體 ID 類型 公開 IP
(-) source-representation-instance MySQL 外部主要執行個體 10.68.48.3:3306
     replica-instance MySQL 唯讀副本 34.66.48.59

在 Cloud SQL 執行個體上啟動複製作業

這個步驟會使用 Cloud SQL 預存程序。呼叫 demoteMaster 要求後,系統會安裝 Cloud SQL 預存程序。呼叫 promoteReplica 後,系統會移除這些物件。

  1. 登入副本執行個體。詳情請參閱「使用本機電腦的資料庫用戶端連線」。
  2. 使用 mysql.resetMaster 預存程序重設複製設定。

     mysql> call mysql.resetMaster();
  3. 設定複製作業。這個步驟需要您先前記下的 GTID 或二進位記錄資訊。

    GTID

    1. 使用 mysql.skipTransactionWithGtid(GTID_TO_SKIP) 預存程序設定 gtid_purged 欄位。
    屬性 說明
    GTID_TO_SKIP 要設定的 GTID 集值。

    例如:

        mysql> call mysql.skipTransactionWithGtid('32eb1e6a-17b6-11ea-a39e-06d94ac3ec98:1-33496');

    1. 執行 mysql.setupExternalSourceAutoPosition(HOST, PORT, USER_NAME, USER_PASSWORD, MASTER_AUTO_POSITION, USE_SSL, USE_SSL_CLIENT_AUTH) 預存程序。
    屬性 說明
    HOST 來源端點。
    PORT 來源通訊埠。
    USER_NAME 來源使用者。
    USER_PASSWORD 來源使用者密碼。
    MASTER_AUTO_POSITION master_auto_position 參數的值。可能的值為 0、1。
    USE_SSL 是否要使用 SSL 複製功能。可能的值為 true、false。如果 true,您必須在 DemoteMaster 要求中設定 caCertificate 欄位。
    USE_SSL_CLIENT_AUTH 是否要使用 SSL 用戶端驗證。可能的值為 true、false。如果 true,您必須在 demoteMaster 要求中設定 clientKey 和 clientCertificates 欄位。
        mysql> call mysql.setupExternalSourceAutoPosition('1.1.1.1', 3306, \
        'USERNAME', 'PASSWORD', \
        /* master_auto_position= */ 1,false, false); \

    binlog

    執行 mysql.setupExternalSource(HOST, PORT, USER_NAME, USER_PASSWORD, SOURCE_LOG_NAME, SOURCE_LOG_POS, USE_SSL, USE_SSL_CLIENT_AUTH) 預存程序。

    屬性 說明
    HOST 來源端點。
    PORT 來源通訊埠。
    USER_NAME 來源使用者。
    USER_PASSWORD 來源使用者密碼。
    SOURCE_LOG_NAME 來源資料庫執行個體上二進位記錄的名稱,其中包含複製資訊。
    SOURCE_LOG_POS mysql_binary_log_file_name 二進位記錄中的位置,複製作業會從該處開始讀取複製資訊。
    USE_SSL 是否要使用 SSL 複製。可能的值為 true、false。如果 true,您必須在 DemoteMaster 要求中設定 caCertificate 欄位。
    USE_SSL_CLIENT_AUTH 是否要使用 SSL 用戶端驗證。可能的值為 true、false。如果 true,您必須在 demoteMaster 要求中設定 clientKey 和 clientCertificates 欄位。
        mysql> call mysql.setupExternalSource('1.1.1.1', 3306, \
        'user_name', 'password', 'mysql-bin-changelog.033877', 360, \
        false, false);
  4. 使用 mysql.startReplication() 預存程序,從外部資料庫啟動複製作業。

       mysql> call mysql.startReplication();
  5. 驗證複製狀態。確認 Slave_IO_Running 和 Slave_SQL_Running 欄位都顯示 YES。

       mysql> show slave status\G

    這個指令的輸出內容大致如下:

       *************************** 1. row ***************************
                       Slave_IO_State: Waiting for master to send event
                          Master_Host: 1.1.1.1
                          Master_User: user_name
                          Master_Port: 3306
                        Connect_Retry: 60
                      Master_Log_File: mysql-bin-changelog.000001
                  Read_Master_Log_Pos: 1
                       Relay_Log_File: relay-log.000002
                        Relay_Log_Pos: 1
                Relay_Master_Log_File: mysql-bin-changelog.000001
                     Slave_IO_Running: Yes
                    Slave_SQL_Running: Yes
                      Replicate_Do_DB:
                  Replicate_Ignore_DB:
                   Replicate_Do_Table:
               Replicate_Ignore_Table:
              Replicate_Wild_Do_Table:
          Replicate_Wild_Ignore_Table: mysql.%
                           Last_Errno: 0
                           Last_Error:
                         Skip_Counter: 0
                  Exec_Master_Log_Pos: 412
                      Relay_Log_Space: 752
                      Until_Condition: None
                       Until_Log_File:
                        Until_Log_Pos: 0
                   Master_SSL_Allowed: No
                   Master_SSL_CA_File:
                   Master_SSL_CA_Path:
                      Master_SSL_Cert:
                    Master_SSL_Cipher:
                       Master_SSL_Key:
                Seconds_Behind_Master: 0
        Master_SSL_Verify_Server_Cert: No
                        Last_IO_Errno: 0
                        Last_IO_Error:
                       Last_SQL_Errno: 0
                       Last_SQL_Error:
          Replicate_Ignore_Server_Ids:
                     Master_Server_Id: 1509941531
                          Master_UUID: 1cb2c80e-90f0-11eb-9ea3-02389b1c2e6f
                 Master_Info_File: mysql.slave_master_info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all r
               Master_Retry_Count: 86400
                      Master_Bind:
          Last_IO_Error_Timestamp:
         Last_SQL_Error_Timestamp:
                   Master_SSL_Crl:
               Master_SSL_Crlpath:
               Retrieved_Gtid_Set:
                Executed_Gtid_Set: 478af53c-bd24-11eb-be72-42010a80002a:1-226
                    Auto_Position: 0
       1 row in set (0.00 sec)

繼續複製

從外部伺服器啟動複製作業後,您需要監控複製作業,然後完成遷移。詳情請參閱「監控複製作業」。

疑難排解

問題 疑難排解
Lost connection to MySQL server during query when dumping table。 來源可能已無法使用,或傾印內容包含過大的封包。

確認外部主要執行個體可供連線。您也可以修改來源執行個體上的 net_read_timeout 和 net_write_timeout 旗標值,停止錯誤。如要進一步瞭解這些旗標允許的值,請參閱「設定資料庫旗標」。

如要進一步瞭解如何使用 mysqldump 旗標進行代管匯入遷移作業,請參閱「允許和預設的初始同步旗標」

初始轉儲失敗,並顯示逾時或連線中斷錯誤 (例如 Dump timeout 或 Lost connection to MySQL server)。 如果一或多個 DDL 陳述式與平行轉儲程序互動,導致程序無限期等待,就可能發生這項錯誤。

解決方法:在初始轉儲階段,請勿在來源資料庫上執行 DDL 陳述式。執行任何 DDL 陳述式之前,請重新啟動遷移作業,並等待初始轉儲階段完成。

初始資料遷移作業成功,但未複製任何資料。

可能原因之一是來源資料庫定義了複製標記,導致部分或所有資料庫變更未複製。

確認複製旗標 (例如 binlog-do-db、binlog-ignore-db、replicate-do-db 或 replicate-ignore-db) 的設定方式不會相互衝突。

在主要執行個體上執行 show master status 指令,查看目前的設定。

初始資料遷移作業成功,但資料複製作業在一段時間後停止運作。

建議做法:

  • 在 Cloud de Confiance 控制台的 Cloud Monitoring 部分,查看副本執行個體的複製指標。
  • MySQL IO 執行緒或 SQL 執行緒的錯誤,可在 Cloud Logging 的 mysql.err 記錄檔中找到。
  • 連線至副本執行個體時,也可能發生這項錯誤。 執行 SHOW REPLICA STATUS 指令,並檢查輸出內容中是否有下列欄位:
    • Replica_IO_Running
    • Replica_SQL_Running
    • Last_IO_Error
    • Last_SQL_Error

如果看到 Unknown database DATABASE_NAME on query. Error_code: MY-001049 錯誤,表示複製的 SQL 陳述式嘗試參照未選取遷移的資料庫,導致複製失敗。如要復原複製作業,請根據訊息判斷錯誤是否由下列其中一個物件的 DDL 造成:

  • 事件或日常安排。您可以在副本上執行 CALL mysql.skipReplicationError(),防止事件或常式複製,並繼續複製。
  • 外鍵限制或檢視表。確定陳述式修改的物件位於哪個資料庫後,您可以選擇兩種復原方式。您可以從所選的遷移資料庫中移除該資料庫,也可以在副本上執行 CALL mysql.skipReplicationError(),略過檢視表的限制或傳播,繼續複製作業。
mysqld check failed: data disk is full。 副本執行個體的資料磁碟已滿。

增加副本執行個體的磁碟大小。您可以手動增加磁碟大小,或啟用儲存空間自動增加功能。

查看複製記錄

驗證複製設定時,系統會產生記錄。

如要查看這些記錄,請按照下列步驟操作:

  1. 前往 Cloud de Confiance 控制台的記錄檢視器。

    前往記錄檢視器

  2. 從「Instance」(執行個體) 下拉式選單中選取 Cloud SQL 副本。
  3. 選取 replication-setup.log 記錄檔。

如果 Cloud SQL 副本無法連線至外部伺服器,請確認下列事項:

  • 外部伺服器上的任何防火牆都已設為允許來自 Cloud SQL 副本傳出 IP 位址的連線。
  • SSL/TLS 設定正確。
  • 複製使用者、主機和密碼正確無誤。

後續步驟