基本概述

目前常用的有3中數(shù)據(jù)導(dǎo)入與導(dǎo)出方法：

• 使用mysqldump工具：

  • 優(yōu)點(diǎn)：
    • 簡單易用，只需一條命令即可完成數(shù)據(jù)導(dǎo)出。
    • 可以導(dǎo)出表結(jié)構(gòu)和數(shù)據(jù)，方便完整備份。
    • 支持過濾條件，可以選擇導(dǎo)出部分?jǐn)?shù)據(jù)。
    • 生成的文件可以用于跨平臺、跨版本的數(shù)據(jù)遷移。
  • 缺點(diǎn)：
    • 導(dǎo)出的數(shù)據(jù)包含額外的INSERT語句，可能導(dǎo)致導(dǎo)入速度較慢。
    • 不能使用復(fù)雜的JOIN條件作為過濾條件。
  • 推薦場景：
    • 需要備份和遷移表結(jié)構(gòu)和數(shù)據(jù)。
    • 需要導(dǎo)出部分?jǐn)?shù)據(jù)到其他系統(tǒng)或進(jìn)行數(shù)據(jù)分析。

• 導(dǎo)出CSV文件：

  • 優(yōu)點(diǎn)：
    • CSV格式通用，易于在不同應(yīng)用程序間交換數(shù)據(jù)。
    • 可以利用文本編輯器查看和編輯數(shù)據(jù)。
    • 支持所有SQL寫法的過濾條件。
  • 缺點(diǎn)：
    • 導(dǎo)出的數(shù)據(jù)保存在服務(wù)器本地，可能受到secure_file_priv參數(shù)限制。
    • 每次只能導(dǎo)出一張表的數(shù)據(jù)。
    • 需要單獨(dú)備份表結(jié)構(gòu)。
  • 推薦場景：
    • 需要將數(shù)據(jù)導(dǎo)出到本地文件系統(tǒng)或共享網(wǎng)絡(luò)位置。
    • 需要將數(shù)據(jù)導(dǎo)入到其他非MySQL系統(tǒng)或應(yīng)用程序。

• 物理拷貝表空間：

  • 優(yōu)點(diǎn)：
    • 速度極快，尤其是對于大表數(shù)據(jù)的復(fù)制。
    • 可以直接復(fù)制整個表的數(shù)據(jù)，不需要逐條插入。
  • 缺點(diǎn)：
    • 需要服務(wù)器端操作，無法在客戶端完成。
    • 必須是全表拷貝，不能選擇性導(dǎo)出數(shù)據(jù)。
    • 僅限于InnoDB引擎的表。
  • 推薦場景：
    • 需要快速復(fù)制大表數(shù)據(jù)到另一個數(shù)據(jù)庫或服務(wù)器。
    • 源表和目標(biāo)表都使用InnoDB引擎。
    • 有服務(wù)器文件系統(tǒng)的訪問權(quán)限。

在選擇使用哪種方法時，還需要考慮數(shù)據(jù)的大小、是否需要跨平臺遷移、是否有權(quán)限訪問服務(wù)器文件系統(tǒng)、是否需要保留表結(jié)構(gòu)等因素。通常，如果需要快速遷移大量數(shù)據(jù)并且對數(shù)據(jù)的完整性有高要求，物理拷貝表空間是一個好選擇。如果數(shù)據(jù)量較小或者需要跨平臺遷移，使用mysqldump或?qū)С鯟SV文件可能更合適。

mysqldump工具

• 使用mysqldump導(dǎo)出數(shù)據(jù)：

  mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql
  
  -h: 指定MySQL服務(wù)器的主機(jī)名。$host: 替換為實(shí)際的主機(jī)名。
  -P: 指定MySQL服務(wù)器的端口號。$port: 替換為實(shí)際的端口號。
  -u: 指定登錄MySQL的用戶名。`$user`: 替換為實(shí)際的用戶名。
  --add-locks=0: 導(dǎo)出時不增加額外的鎖。
  --no-create-info: 不導(dǎo)出表結(jié)構(gòu)。
  --single-transaction: 在導(dǎo)出數(shù)據(jù)時不需要對表加表鎖。
  --set-gtid-purged=OFF: 不輸出與GTID相關(guān)的信息。
  db1: 指定要導(dǎo)出的數(shù)據(jù)庫名。
  t: 指定要導(dǎo)出的表名。
  --where="a>900": 導(dǎo)出滿足條件a>900的數(shù)據(jù)。
  --result-file=/client_tmp/t.sql: 指定導(dǎo)出結(jié)果的文件路徑。
  

• 將數(shù)據(jù)導(dǎo)入到目標(biāo)數(shù)據(jù)庫：

  mysql -h127.0.0.1 -P13000 -uroot db2 -e "source /client_tmp/t.sql"
  `-h`: 指定MySQL服務(wù)器的主機(jī)名。`root`: 使用root用戶登錄。
  `-P`: 指定MySQL服務(wù)器的端口號。
  `-u`: 指定登錄MySQL的用戶名。
  `db2`: 指定要導(dǎo)入數(shù)據(jù)的數(shù)據(jù)庫名。
  `-e`: 后面跟隨要執(zhí)行的命令。
  `"source /client_tmp/t.sql"`: 執(zhí)行source命令導(dǎo)入之前導(dǎo)出的SQL文件。
  

文件導(dǎo)入導(dǎo)出

• 導(dǎo)出為CSV文件：

  SELECT * FROM db1.t WHERE a > 900 INTO OUTFILE '/server_tmp/t.csv';
  
  SELECT * FROM db1.t: 指定要導(dǎo)出的查詢。
  WHERE a > 900: 導(dǎo)出滿足條件的數(shù)據(jù)。
  INTO OUTFILE '/server_tmp/t.csv': 指定導(dǎo)出結(jié)果的CSV文件路徑。
  

• 導(dǎo)入CSV文件到目標(biāo)表：

  LOAD DATA INFILE '/server_tmp/t.csv' INTO TABLE db2.t;
  
  LOAD DATA INFILE: 加載數(shù)據(jù)的命令。
  '/server_tmp/t.csv': 指定CSV文件的路徑。
  INTO TABLE db2.t: 指定要導(dǎo)入數(shù)據(jù)的目標(biāo)表。
  

在MySQL中secure_file_priv用于限制LOAD DATA INFILE和SELECT ... INTO OUTFILE這兩個命令生成或讀取文件的位置。這個參數(shù)的目的是為了增強(qiáng)安全性，防止意外或惡意地讀取或?qū)懭敕?wù)器上的敏感文件。

如果secure_file_priv被設(shè)置為空字符串（''）或者NULL，則表示沒有文件路徑限制，可以使用任意文件路徑。但是，這種設(shè)置降低了系統(tǒng)的安全性，因此不推薦在生產(chǎn)環(huán)境中使用。

物理拷貝表空間

• 物理拷貝表空間：

  • 首先創(chuàng)建一個相同結(jié)構(gòu)的空表：

    CREATE TABLE db2.r LIKE db1.t;
    

  • 然后丟棄表空間：

    ALTER TABLE db2.r DISCARD TABLESPACE;
    

  • 導(dǎo)出表文件：

    FLUSH TABLES db1.t FOR EXPORT;
    

  • 拷貝文件：

    cp /path/to/db1/t.ibd /path/to/db2/r.ibd
    cp /path/to/db1/t.cfg /path/to/db2/r.cfg
    

  • 解鎖表并導(dǎo)入表空間：

    UNLOCK TABLES;
    ALTER TABLE db2.r IMPORT TABLESPACE;

總結(jié) 

到此這篇關(guān)于MySQL數(shù)據(jù)導(dǎo)入導(dǎo)出的三種辦法總結(jié)的文章就介紹到這了,更多相關(guān)MySQL數(shù)據(jù)導(dǎo)入導(dǎo)出內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論