技術分享 | MySQL 的幾種資料遷移方案

Linksla發表於2024-02-05
MySql

1 需求背景

應用側的同學需要對資料進行匯出和匯入，於是跑來找 DBA 諮詢問題： MySQL 如何匯入大批次的資料？

應用側目前的方式：

  • mysqldump 工具
  • select outfile 語句
  • 圖形化管理工具（MySQL Workbench、Navicat 、DBeaver）

DBA 聽了覺得挺好的呀！

DBA 想了，我的資料庫我做主。通知應用側，目前先使用之前熟悉的方式進行，測試之後給建議。

Tips：為了防止匯入時出現大事務，造成主從延遲。

2 方案准備

待測方案：mysqldump、mydumper、select outfile 語句、 Util.dumpTables 、 Util.exportTable。

環境配置資訊

配置項 說明
MySQL 版本 5.7.39
磁碟隨機讀寫 100 MiB/sec
測試表名 test.t_order_info
行數 1000W
欄位數 6

建表語句



CREATE 
TABLE 
`t_order_info` (

  
`ID` 
bigint(
20) 
unsigned 
NOT 
NULL AUTO_INCREMENT 
COMMENT 
'自增主鍵ID',

  
`order_no` 
varchar(
64) 
NOT 
NULL 
DEFAULT 
'0000' 
COMMENT 
'訂單編號',

  
`order_status` 
varchar(
2) 
NOT 
NULL 
DEFAULT 
'01' 
COMMENT 
'訂單狀態: 00-異常、01-待處理、02-進行中、03-已完成',

  
`flag` 
tinyint(
4) 
NOT 
NULL 
DEFAULT 
'1' 
COMMENT 
'刪除標識: 1-正常、0-邏輯刪除',

  
`create_time` datetime 
NOT 
NULL 
DEFAULT 
CURRENT_TIMESTAMP 
COMMENT 
'建立時間',

  
`modify_time` datetime 
NOT 
NULL 
DEFAULT 
CURRENT_TIMESTAMP 
ON 
UPDATE 
CURRENT_TIMESTAMP 
COMMENT 
'更新時間',

  PRIMARY 
KEY (
`ID`),

  
UNIQUE 
KEY 
`IDX_ORDER_NO` (
`order_no`)

) 
ENGINE=
InnoDB AUTO_INCREMENT=
1 
DEFAULT 
CHARSET=utf8mb4 
COMMENT=
'訂單表'

匯出檔案

  • 包含資料結構和資料的  備份檔案 （mysqldump、mydumper、Util.dumpTables）
  • 只包含資料的  資料檔案 （select outfile、Util.exportTable）

匯出匯入命令

匯出 匯入
mysqldump source 或 mysql< xxx.sql
mydumper myloader
select outfile load data
Util.dumpTables Util.loadDump
Util.exportTable Util.importTable

3 方案測試

測試首先考慮的是  提升匯入效率，並新增了 MySQL Shell 的使用。

mysqldump

單表匯出（備份檔案）


mysqldump --default-character-set=utf8mb4 --master-data=2 --single-transaction --set-gtid-purged=off --hex-blob  --tables test t_order_info
  • --master-data=2 引數會在備份期間對所有表加鎖  FLUSH TABLES WITH READ LOCK，並執行  SHOW MASTER STATUS 語句以獲取二進位制日誌資訊。因此，在備份期間可能會影響資料庫的併發效能。如果您不需要進行主從複製，則可以考慮不使用  --master-data=2 引數。
  • --single-transaction 引數用於在備份期間“使用事務來確保資料一致性”，從而避免在備份期間鎖定表。[必須有]

備份檔案

檔案內容。



-- Table stricture for table `t_order_info`


--




DROP 
TABLE 
IF 
EXISTS 
`t_order_info`;


/*!40101 SET @saved_cs_client= @@character_set_client */;


/*!49101 SET character_set_client = utf8 */;


CREATE 
TABLE 
`t_order_info` (

  
`ID` 
bigint(
2) 
unsigned 
NOT 
NULL AUTO_INCREMENT 
COMMENT 
'自增主鍵ID',

  
`order_no` 
varchar(
64) 
NOT 
NULL 
DEFAULT 
`0000` 
COMMENT 
'訂單編號'，

  
`order_status` 
varchar(
2) 
NOT 
NULL 
DEFAULT 
'01' 
COMMENT 
'訂單狀態: 80-異常、81-待處理、2-進行中、03-已完成',

  
`flag` 
tinyint(
4) 
NOT 
NULL 
DEFAULT 
'1' 
COMMENT 
'刪除標識: 1-正常、0-邏輯刪除',

  
`create_time` datetime 
NOT 
NULL 
DEFAULT 
CURRENT_TIMESTAMP 
COMMENT 
'建立時間',

  
`modify_time` datetime 
NOT 
NULL 
DEFAULT 
CURRENT_TIMESTAMP 
ON 
UPDATE 
CURRENT_TIMESTAMP 
COMMENT 
'更新時間',

  PRIMARY 
KEY (
`ID`),

  UNIOUE 
KEY 
`IDX_ORDER_NO` (
`order no`)

) 
ENGINE=
InnODB AUTO_INCREMENT=
10129913 
DEFAULT 
CHARSET=utf8m 
COMMENT=
'訂單表';


/*!40101 SET character_set_client = @saved_cs_client */;




--


-- Dumping data for table `t_order_info`


--




LOCK 
TABLES 
`t_order_info` WRITE;


/*!40000 ALTER TABLE `t_order_info` DISABLE KEYS */;

檔案內容解釋：

  • 沒有建庫語句， 因為是單表備份。

  • 有刪除表，建立表的語句， 小心匯入目標庫時，刪除表的語句，造成資料誤刪。

  • INSERT 語句沒有欄位名稱， 匯入時表結構要一致。

  • 匯入過程中有  lock table write 操作， 匯入過程中相關表不可寫。

  • ALTER TABLE t_order_info DISABLE KEYS 此語句將禁用該表的所有非索引， 這可以提高插入大量資料時的效能。 對應的檔案末尾有  ALTER TABLE t_order_info  ENABLE KEYS;

用途，可以將備份檔案中的資料匯入自定義庫，“檔案內容解釋”部分遇到的問題可以使用下面引數解決。

  • --no-create-info 不包含建表語句（可以手動建立  create table tablename like dbname.tablename;）

  • --skip-add-drop-database 不包含刪庫語句

  • --skip-add-drop-table 不包含刪表語句

  • --skip-add-locks INSERT 語句前不包含  LOCK TABLES t_order_info WRITE;

  • --complete-insert INSERT 語句中包含 列名稱（新表的列有增加的時候）。

單表匯出備份資料（只匯出資料）。



mys
qldump --default-character-set=utf8mb4 --master-data=2 --single-transaction --set-gtid-purged=off --hex-blob --no-create-info --skip-add-drop-table --skip-add-locks --tables dbname tablename



// 部分資料匯出追加引數

--where="create_time>'2023-01-02'"

匯出單庫中的某表為 CSV。


// 可選不匯出表結構，

--no-create-info --skip-add-drop-database --skip-add-drop-table

/data/mysql/3306/base/bin/mysqldump -uadmin -p123456 -P3306 -h127.0.0.1 --default-character-set=utf8mb4 --single-transaction --set-gtid-purged=OFF  --triggers --routines --events --hex-blob --fields-terminated-by=',' --fields-enclosed-by='"' --lines-terminated-by='\n'  -T /data/mysql/3306/tmp test



//其中 test 後面也可以指定表名，不指定就是全庫。

test t_order_info t_order_info01

其中 --single-transaction --set-gtid-purged=OFF  --triggers --routines --events --hex-blob 

為了防止提示，可選

小結

1G 的備份檔案，測試結果如下：

  1. 使用  mysql< xxx.sql 匯入，耗時 5 分鐘。
  2. 使用用  source xxx.sql 匯入， 耗時 10 分鐘。

推薦第一種，都是單執行緒。

mydumper

  • 版本 0.14.4

多執行緒匯出


mydumper -u admin -p 123456 -P 3306 -h 127.0.0.1 -t 8 --trx-consistency-only -G -E -R --skip-tz-utc --verbose=3 --compress --no-schemas --rows=1000000  -T test.t_order_info  -o /backup

  

// 匯出時支援部分匯出追加引數



--
where=
"create_time>'2023-01-02'"

  

// 檔案輸出

test01.t_order_info.00000.dat 
# 包含 CSV 資料

test01.t_order_info.00000.sql 
# 包含 LOAD DATA 語句



// 匯入命令

LOAD DATA LOCAL INFILE 
'/data/mysql/3306/tmp/test01.t_order_info.00005.dat' REPLACE INTO TABLE `t_order_info` CHARACTER SET binary FIELDS TERMINATED BY 
',' ENCLOSED BY 
'"' ESCAPED BY 
'\\' LINES STARTING BY 
'' TERMINATED BY 
'\n' (`ID`,`order_no`,`order_status`,`flag`,`create_time`,`modify_time`);
  • 多執行緒匯入

myloader -u admin -p 123456 -P 3306 -h 127.0.0.1 --
enable-binlog -t 8 --verbose=3 -B 
test -d /backup

  

//  匯入主庫時需要新增 

--
enable-binlog



// 庫名可以自定義

-B 
test 

小結

耗時 2 分鐘，建議如下：

  • 在資料量大於 50G 的場景中，更推薦 mydumper。
  • 補充場景，支援匯出 CSV，也支援  --where 過濾。

mydumper -u admin -p 123456 -P 3306 -h 127.0.0.1 -t 8 --trx-consistency-only -G -E -R --skip-tz-utc --verbose=3 --
where=
"create_time>'2023-01-02'" --no-schemas --rows=1000000 --load-data --fields-terminated-by 
',' --fields-enclosed-by 
'"' --lines-terminated-by 
'\n' -T test.t_order_info  -o /backup

匯入命令同上，且可以按需手動進行  LOAD DATA。

SELECT OUTFILE 語句

Tips：適合於單表資料的匯出，不支援多表。

匯出命令，耗時 15 秒。



SELECT * 
from test01.t_order_info 
INTO 
OUTFILE 
"/data/mysql/3306/tmp/t_order_info0630_full.csv" 
CHARACTER 
SET utf8mb4 
FIELDS 
TERMINATED 
BY 
',' 
OPTIONALLY 
ENCLOSED 
BY 
'\'' LINES TERMINATED BY '\n
';

  

//  帶列名匯出，匯入時需新增 IGNORE 1 LINES;

SELECT *  INTO OUTFILE "/data/mysql/3306/tmp/t_order_info0630_full.csv" CHARACTER SET utf8mb4 FIELDS TERMINATED BY ',
' OPTIONALLY ENCLOSED BY '\
'' 
LINES 
TERMINATED 
BY 
'\n'  
from (
select 
'id',
'order_no',
'order_status',
'flag',
'create_time',
'modify_time' 
union 
all 
select * 
from test01.t_order_info) b;

匯入命令，耗時 3 分鐘。


mysql -uadmin -P3306  -h127.0.0.1 -p123456  
--local-infile


load 
data 
local 
infile 
'/data/mysql/3306/tmp/t_order_info0630_full.csv'  
into 
table test.t_order_info 
CHARACTER 
SET utf8mb4 
fields 
terminated 
by 
',' 
OPTIONALLY 
ENCLOSED 
BY 
'\'' lines terminated by '\n
';

小結

  • 支援跨表匯入。A 表的資料可以匯入 B 表，因為備份檔案中只有資料。
  • 可自定義匯出部分列，匯出匯入速度較快，最常用。

MySQL_Shell > dumpTables

單表匯出，耗時 4 秒。


util.dumpTables(
"test", [
"t_order_info"], 
"/backup") 

部分匯出。


util.dumpTables(
"test", [
"t_order_info"], 
"/backup", {
"where" : {
"test.t_order_info": 
"create_time>'2023-01-02'"}})

匯入，耗時 3 分鐘。


util.loadDump(
"/backup") 

注意：不支援部分匯入，不支援跨資料庫版本。

因為匯入時最大支援 2 個引數，可以將匯出的部分資料全部匯入到新的庫中。

匯入命令： util.loadDump("/backup",{schema: "test_new"})

小結

  • 支援跨庫匯入，A 庫的資料可以匯入 B 庫。表名需要一致。不支援增量到已有資料的表中。
  • 匯出時和  SELECT OUTFILE 同效，匯入時，比  LOAD DATA 快（預設 4 執行緒）。

注意：

  1. 部分匯出功能需要較新的 MySQL Shell 版本，如 8.0.33。
  2. LOAD DATA 單執行緒匯入 耗時 1h20min。

MySQL_Shell > exportTable

單表匯出，耗時 10 秒。


util.exportTable(
"test.t_order_info",   
"/backup/t_order_info.csv", {
defaultCharacterSet: 
"utf8mb4", 
fieldsOptionallyEnclosed: 
true, 
fieldsTerminatedBy: 
",", 
linesTerminatedBy: 
"\n", 
fieldsEnclosedBy: 
'"', 
defaultCharacterSet: 
"utf8mb4", 
showProgress: 
true, 
dialect: 
"csv"}) 

部分匯出。


util.exportTable(
"test.t_order_info",   
"/backup/t_order_info.csv",   {     
dialect: 
"csv",     
defaultCharacterSet: 
"utf8mb4",     
fieldsOptionallyEnclosed: 
true,     
fieldsTerminatedBy: 
",",     
linesTerminatedBy: 
"\n",     
fieldsEnclosedBy: 
'"',     
showProgress: 
true,     
where: 
"create_time>'2023-01-02'" } )

匯入，耗時 10 分鐘。


util.importTable(
"/backup/t_order_info.csv", { 
"characterSet": 
"utf8mb4",     
"dialect": 
"csv",     
"fieldsEnclosedBy": 
"\"",     
"fieldsOptionallyEnclosed": 
true,     
"fieldsTerminatedBy": 
",",     
"linesTerminatedBy": 
"\n",     
"schema": 
"test",     
"table": 
"t_order_info" }) 

部分匯入（不推薦使用）。


util.importTable(
"/backup/t_order_info.csv", {     
"characterSet": 
"utf8mb4",     
"dialect": 
"csv",     
"fieldsEnclosedBy": 
"\"",     
"fieldsOptionallyEnclosed": 
true,     
"fieldsTerminatedBy": 
",",     
"linesTerminatedBy": 
"\n",     
"schema": 
"test100",     
"table": 
"t_order_info" })util.importTable(
"/backup/t_order_info0630.csv", {      
"characterSet": 
"utf8mb4",     
"dialect": 
"csv",     
"fieldsEnclosedBy": 
"\"",     
"fieldsOptionallyEnclosed": 
true,     
"fieldsTerminatedBy": 
",",     
"linesTerminatedBy": 
"\n",     
"schema": 
"test",     
"table": 
"t_order_info" }) 

有報錯  MySQL Error 1205 (HY000): Lock wait timeout exceeded; try restarting transaction @ file bytes range [450000493, 500000518) 需要重複執行一次，才能保證資料完整。

根據報錯提示可以使用以下命令匯入：


LOAD DATA LOCAL INFILE 
'/backup/t_order_info0630.csv' INTO TABLE `
test`.`t_order_info` CHARACTER SET 
'utf8mb4' FIELDS TERMINATED BY 
',' OPTIONALLY ENCLOSED BY 
'\"' ESCAPED BY 
'\\' LINES STARTING BY 
'' TERMINATED BY 
'\n';

MySQL 5.7 也推薦直接使用  LOAD DATA。

小結

  • 支援跨庫匯入，A 庫的資料可以匯入 B 庫，表名需要一致。
  • 匯出時和  SELECT OUTFILE 同效。匯入時，比  LOAD DATA 快（預設 8 執行緒）。

4 總結

可以透過資料大小進行選用:

匯出 匯入 優點 推薦度（效率）
mysqldump source xxx.sql
MySQL< xxx.sql		 原生，可遠端 ⭐⭐⭐
資料量<10G
mydumper myloader 多執行緒 ⭐⭐⭐
資料量>50G
SELECT OUTFILE LOAD  DATA 最靈活 ⭐⭐
資料量<20G
Util.dumpTables Util.loadDump 原生，多執行緒 ⭐⭐⭐
資料量<50G
Util.exportTable Util.importTable 原生，單執行緒 ⭐
資料量<20G
  • MySQL< 匯入時，需要避免資料丟失。
  • 前 3 種都支援  WHERE 過濾，mydumper 是最快的。 SELECT OUTFILE 最常用（因為支援自定義匯出部分列）。
  • 前 2 種因為是備份工具，所以有 FTWRL 鎖。
  • Util.dumpTables 不支援增量到已有資料的表中，因為包含了庫表的後設資料資訊，像 mydumper。
  • Util.exportTable 備份是單執行緒，匯入是多執行緒，不推薦的原因是匯入容易出錯（多次匯入可解決）。
  • 使用建議：按照資料量選擇，全表備份最快用  Util.dumpTables，部分備份用  SELECT OUTFILE。
  • 測試之後再使用，匯出和匯入均需要進行資料驗證。


文章來源於愛可生開源社群  ，作者陳偉


來自 “ ITPUB部落格 ” ，連結：https://blog.itpub.net/70013542/viewspace-3006295/，如需轉載，請註明出處，否則將追究法律責任。

相關文章