Mysql資料遷移方法

MySQL遷移通常使用的有三種方法：
1、資料庫直接匯出，複製檔案到新伺服器，在新伺服器上匯入。
2、使用第三方遷移工具。
3、資料檔案和庫表結構檔案直接複製到新伺服器，掛載到同樣配置的MySQL服務下。

第一種方案的優點：會重建資料檔案，減少資料檔案的佔用空間，相容性最好，匯出匯入很少發生問題，需求靈活。缺點:使用傳統匯出匯入時間佔用長。
第二種方案的優點：設定完成後傳輸無人值守，自動完成。缺點：不夠靈活，設定繁瑣，傳輸時間長，異常後很難從異常的位置繼續傳輸。
第三種方案的優點：時間佔用短，檔案可斷點傳輸，操作步驟少。缺點：新舊伺服器中MySQL版本及配置必須相同，可能引起未知問題。

假如資料庫遷移是因為業務瓶頸或專案改造等需要變動資料表結構的(比如分割槽分表)，我們便只能使用第一種方法了。

我一般使用MySQL的 SELECT INTO OUTFILE 、LOAD DATA INFILE 快速匯出匯入資料
【匯出匯出工作準備】

匯出前關閉日誌，避免資料備份過程中頻繁記錄日誌
刪除主鍵，關閉自動增長。在該表中主鍵其實作用不大，自動增長是需要的（mysql中自動增長的一列一定要為key，所以設定為主鍵），等待資料轉移結束後重新設定回來
刪除表中索引。在插入資料時索引的存在會很大程度上影響速度，所以先關閉，轉移後重新建立
Mysql系統引數調優，如下：（具體含義後面給出）
innodb_data_file_path           = ibdata1:1G:autoextend
innodb_file_per_table           = 1
innodb_thread_concurrency       = 20
innodb_flush_log_at_trx_commit  = 1
innodb_log_file_size            = 256M
innodb_log_files_in_group       = 3
innodb_max_dirty_pages_pct      = 50
innodb_lock_wait_timeout        = 120
key_buffer_size=400M
innodb_buffer_pool_size=4G
innodb_additional_mem_pool_size=20M
innodb_log_buffer_size=20M
query_cache_size=40M
read_buffer_size=4M
read_rnd_buffer_size=8M
tmp_table_size=16M
max_allowed_packet = 32M

【操作方法及結果】
（1）create table t2 as select * from t1
CREATE TABLE dn_location3   
PARTITION BY RANGE (UNIX_TIMESTAMP(UPLOADTIME)) 
 (   PARTITION p141109 VALUES LESS THAN (UNIX_TIMESTAMP('2014-11-09 00:00:00')), 
 PARTITION p141110 VALUES LESS THAN (UNIX_TIMESTAMP('2014-11-10 00:00:00')),    
PARTITION p141111 VALUES LESS THAN (UNIX_TIMESTAMP('2014-11-11 00:00:00')),    
PARTITION p141112 VALUES LESS THAN (UNIX_TIMESTAMP('2014-11-12 00:00:00'))  
)  
as select * from dn_location  
where uploadtime > '2014-08-04'; 
 
create table t2 as select * from dn_location2; 

as建立出來的t2表（新表）缺少t1表（源表）的索引資訊，只有表結構相同，沒有索引。
此方法效率較高，在前面的實驗環境下，42min內將一張表內4600W的資料轉到一張新的表中，在create新表時我新增了分割槽的操作，因此新表成功建立為分割槽表，這樣一步到位的既轉移了資料又建立了分割槽表。此方法平均速度：6570W條/h ，至於該方法其他需要注意的地方，暫時沒有去了解。
（2）使用MySQL的 SELECT INTO OUTFILE、LOAD DATA INFILE
LOAD DATA INFILE語句從一個文字檔案中以很高的速度讀入一個表中。當使用者一前一後地使用SELECT ... INTO OUTFILE 和LOAD DATA INFILE 將資料從一個資料庫寫到一個檔案中，然後再從檔案中將它讀入資料庫中時，兩個命令的欄位和行處理選項必須匹配。否則，LOAD DATA INFILE 將不能正確地解釋檔案內容。
假設使用者使用SELECT ... INTO OUTFILE 以逗號分隔欄位的方式將資料寫入到一個檔案中：
SELECT * INTO OUTFILE 'data.txt' FIELDS TERMINATED BY ',' FROM table2;
為了將由逗號分隔的檔案讀回時，正確的語句應該是：
LOAD DATA INFILE 'data.txt' INTO TABLE table2 FIELDS TERMINATED BY ',';
如果使用者試圖用下面所示的語句讀取檔案，它將不會工作，因為命令LOAD DATA INFILE 以定位符區分欄位值：
LOAD DATA INFILE 'data.txt' INTO TABLE table2 FIELDS TERMINATED BY '\t';
下面是我用來匯入匯出的命令：
select * into outfile 'ddd.txt' fields terminated by ',' from dn_location; 
load data infile 'ddd.txt' into table dn_location2  FIELDS TERMINATED BY ',';

透過該方法匯出的資料，是將各欄位（只有資料，不匯出表結構）資料存在一個檔案中，中間以逗號分隔，因為檔案中並不包含資料庫名或者表名，因此需要在匯入匯出的時候些明確。該方法在18分鐘內匯出1.6億條記錄，46min內匯入6472W條記錄，平均速度：8442W條/h。mysql官方文件也說明了，該方法比一次性插入一條資料效能快20倍