如何通過mysqldump工具將PolarDB-X資料匯入匯出 - PolarDB

本文介紹了通過mysqldump工具將PolarDB-X資料匯出的情境和詳細操作步驟。

工具介紹

PolarDB-X支援MySQL官方資料匯出工具mysqldump，mysqldump能夠匯出表結構資訊和表內資料，並轉化成SQL語句的格式方便使用者直接匯入。

說明

mysqldump適合小資料量（低於1000萬）的離線匯出。如果需要完成更巨量資料量或者即時的資料移轉任務，建議使用DTS。

文法

mysqldump工具匯出資料的命令：

mysqldump -h ip -P port -u user -pPassword --default-character-set=char-set --net_buffer_length=10240 --no-create-db --no-create-info --skip-add-locks --skip-lock-tables --skip-tz-utc --set-charset  [--hex-blob] [--no-data] database [table1 table2 table3...] > dump.sql

mysqldump的參數說明可通過mysqldump --help命令查看或查詢MySQL 官方文檔，常用參數說明如下：

參數名	說明
ip	PolarDB-X執行個體的IP。
port	PolarDB-X執行個體的連接埠。
user	PolarDB-X的使用者名稱。
password	PolarDB-X的密碼，注意前面有個-p，之間沒有空格。
char-set	指定的編碼。
--hex-blob	使用十六進位格式匯出二進位字串欄位。如果有位元據就必須使用本選項。影響的欄位類型包括BINARY、VARBINARY、BLOB。
--no-data	不匯出資料。
table	指定匯出某個表。預設匯出該資料庫所有的表。
--no-create-info	不匯出建表資訊。
--net_buffer_length	傳輸緩衝區大小。影響Insert語句的長度，預設值1046528。

樣本

下面從不同情境介紹mysqldump工具的使用執行個體。

PolarDB-X和MySQL之間資料轉送時不推薦匯出表結構，因為PolarDB-X包含分庫分表功能，CREATE TABLE（DRDS模式）中的拆分函數等與MySQL不相容，不相容的關鍵字包括：

DBPARTITION BY hash(partition_key)
TBPARTITION BY hash(partition_key)
TBPARTITIONS N
BROADCAST

如果要匯出表結構，需在匯出的SQL語句檔案中修改建表語句，才能正確匯入。所以推薦只匯出表內資料，手動登入資料庫進行建表操作，然後再匯入資料。

情境一：從MySQL匯入到PolarDB-X

從MySQL匯入資料到PolarDB-X，請按照以下步驟進行操作。

從MySQL中匯出資料到檔案。

輸入以下命令，從MySQL中匯出表內資料（不推薦匯出表結構），假設匯出檔案為dump.sql。

mysqldump -h ip -P port -u user -pPassword --default-character-set=char-set --net_buffer_length=204800 --no-create-db --no-create-info --skip-add-locks --skip-lock-tables --skip-tz-utc --set-charset --hex-blob database [table1 table2 table3...] > dump.sql

登入PolarDB-X，手動建立目標表，關於PolarDB-X建表語句的文法請參見CREATE TABLE（DRDS模式）。如果未加--no-create-info參數，匯出的dump.sql檔案中包含MySQL端的建表語句，也可在檔案中修改建表語句。
匯入資料檔案到PolarDB-X中。您可以通過如下兩種方式匯入資料檔案到PolarDB-X：
- 通過mysql -h ip -P port -u user -pPassword --default-character-set=char-set命令登入目標PolarDB-X，執行source /yourpath/dump.sql命令將資料匯入到目標PolarDB-X。
- 直接通過mysql -h ip -P port -u user -pPassword --default-character-set=char-set< /yourpath/dump.sql命令將資料匯入到目標PolarDB-X。
說明
- 上述兩個命令中default-character-set要設定成實際的資料編碼。如果是Windows平台，source命令指定的檔案路徑需要對分隔字元轉義。
- 第一種方式會把所有的步驟回顯到螢幕上，速度略慢，但是可以觀察匯入處理程序。
- 匯入時由於某些PolarDB-X和MySQL實現上的不同，可能會報錯，錯誤資訊類似ERROR 1231 (HY000): [a29ef6461c00000][10.117.207.130:3306][****]Variable @saved_cs_client can't be set to the value of @@character_set_client。此類錯誤資訊並不影響匯入資料的正確性。

情境二：從一個PolarDB-X匯入到另一個PolarDB-X

假設您之前有一個測試環境的PolarDB-X，測試完畢以後，需要把測試過程中的一些表結構和資料匯入到生產環境的PolarDB-X中，那麼可以按照以下步驟進行操作。

從源PolarDB-X中匯出資料到文字檔。請參見情境一步驟一。
匯入資料檔案到PolarDB-X。請參見情境一步驟三。
手動建立Sequence對象。
mysqldump並不會匯出PolarDB-X中的Sequence對象，所以如果在源PolarDB-X中使用了Sequence對象，並且需要在目標PolarDB-X中繼續使用相同的Sequence對象，則需要手動在目標PolarDB-X中建立同名的Sequence的對象。具體步驟如下：
1. 在源PolarDB-X上執行SHOW SEQUENCES，擷取當前PolarDB-X中Sequence對象的狀態。
2. 在目標PolarDB-X資料庫上通過CREATE SEQUENCE命令建立新的Sequence對象。
Sequence命令詳情請參見Sequence。

情境三：從PolarDB-X匯出資料到MySQL

從PolarDB-X匯出資料到MySQL，和在PolarDB-X之間相互匯入資料的過程類似，也分為以下幾個步驟。

從源PolarDB-X中匯出資料到文字檔。請參見情境一步驟一。

登入MySQL，手動建立目標表。如果匯出資料包含建表語句，則需要在匯出檔案中修改建表語句，刪除PolarDB-X中不相容MySQL的關鍵字等資訊。

例如PolarDB-X中的某個拆分表：

CREATE TABLE `table_name` (
    `id` int(11) NOT NULL,
    `k` int(11) NOT NULL DEFAULT '0',
    ...
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 dbpartition by hash(`id`);

需去掉MySQL不相容的拆分函數語句，改成：

CREATE TABLE `table_name` (
    `id` int(11) NOT NULL,
    `k` int(11) NOT NULL DEFAULT '0',
    ...
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

匯入資料檔案到PolarDB-X。請參見情境一步驟三。

常見問題

問題：mysqldump報錯when trying to dump tablespace。
原因：PolarDB-X沒有tablespace（資料表空間）的概念。
解決方案：mysqldump加入參數--no-tablespaces。
問題：mysqldump報錯SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of-bucket-specified"') FROM information_schema.COLUMN_STATISTICS ...。
原因：查詢列統計資訊時，因不相容的JSON格式報錯。
解決方案：mysqldump加入參數--column-statistics=0，跳過匯出統計資訊。
問題：mysqldump報錯Couldn't execute 'SHOW TRIGGERS ...'。
原因：PolarDB-X不支援triggers（觸發器）。
解決方案：mysqldump加入參數--skip-triggers。
問題：無主鍵表匯入時，報錯Duplicate entry '0' for key 'PRIMARY'。
原因：Dump出的檔案中設定了NO_AUTO_VALUE_ON_ZERO的sql_mode，導致主鍵衝突。
解決方案：將dump出來檔案中設定sql_mode中的NO_AUTO_VALUE_ON_ZERO欄位刪掉。
問題：mysqldump匯出資料時出現中文亂碼的處理方法。
解決方案：若您的執行個體版本為5.4.19及以上，您可以使用高許可權帳號將COMPATIBLE_CHARSET_VARIABLES設定為true，然後進行mysqldump匯出。
```
SET GLOBAL COMPATIBLE_CHARSET_VARIABLES=true;
```