Mysql如何實現數據遷移

這篇文章將為大家詳細講解有關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倍

關于“Mysql如何實現數據遷移”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。