源地址

MySQL遷移通常使用的有三種方法：

1、數(shù)據(jù)庫直接導(dǎo)出，拷貝文件到新服務(wù)器，在新服務(wù)器上導(dǎo)入。
2、使用第三方遷移工具。
3、數(shù)據(jù)文件和庫表結(jié)構(gòu)文件直接拷貝到新服務(wù)器，掛載到同樣配置的MySQL服務(wù)下。

• 第一種方案的優(yōu)點(diǎn)：會(huì)重建數(shù)據(jù)文件，減少數(shù)據(jù)文件的占用空間，兼容性最好，導(dǎo)出導(dǎo)入很少發(fā)生問題，需求靈活。缺點(diǎn):使用傳統(tǒng)導(dǎo)出導(dǎo)入時(shí)間占用長。
• 第二種方案的優(yōu)點(diǎn)：設(shè)置完成后傳輸無人值守，自動(dòng)完成。缺點(diǎn)：不夠靈活，設(shè)置繁瑣，傳輸時(shí)間長，異常后很難從異常的位置繼續(xù)傳輸。
• 第三種方案的優(yōu)點(diǎn)：時(shí)間占用短，文件可斷點(diǎn)傳輸，操作步驟少。缺點(diǎn)：新舊服務(wù)器中MySQL版本及配置必須相同，可能引起未知問題。

假如數(shù)據(jù)庫遷移是因?yàn)闃I(yè)務(wù)瓶頸或項(xiàng)目改造等需要變動(dòng)數(shù)據(jù)表結(jié)構(gòu)的(比如分區(qū)分表)，我們便只能使用第一種方法了。

我一般使用MySQL的 SELECT INTO OUTFILE 、LOAD DATA INFILE 快速導(dǎo)出導(dǎo)入數(shù)據(jù)

【導(dǎo)出導(dǎo)出工作準(zhǔn)備】

1. 導(dǎo)出前關(guān)閉日志，避免數(shù)據(jù)備份過程中頻繁記錄日志
2. 刪除主鍵，關(guān)閉自動(dòng)增長。在該表中主鍵其實(shí)作用不大，自動(dòng)增長是需要的（mysql中自動(dòng)增長的一列一定要為key，所以設(shè)置為主鍵），等待數(shù)據(jù)轉(zhuǎn)移結(jié)束后重新設(shè)置回來
3. 刪除表中索引。在插入數(shù)據(jù)時(shí)索引的存在會(huì)很大程度上影響速度，所以先關(guān)閉，轉(zhuǎn)移后重新建立
4. Mysql系統(tǒng)參數(shù)調(diào)優(yōu)，如下：（具體含義后面給出）
   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

【操作方法及結(jié)果】

（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創(chuàng)建出來的t2表（新表）缺少t1表（源表）的索引信息，只有表結(jié)構(gòu)相同，沒有索引。
此方法效率較高，在前面的實(shí)驗(yàn)環(huán)境下，42min內(nèi)將一張表內(nèi)4600W的數(shù)據(jù)轉(zhuǎn)到一張新的表中，在create新表時(shí)我添加了分區(qū)的操作，因此新表成功創(chuàng)建為分區(qū)表，這樣一步到位的既轉(zhuǎn)移了數(shù)據(jù)又創(chuàng)建了分區(qū)表。此方法平均速度：6570W條/h ，至于該方法其他需要注意的地方，暫時(shí)沒有去了解。

（2）使用MySQL的 SELECT INTO OUTFILE、LOAD DATA INFILE

LOAD DATA INFILE語句從一個(gè)文本文件中以很高的速度讀入一個(gè)表中。當(dāng)用戶一前一后地使用SELECT ... INTO OUTFILE 和LOAD DATA INFILE 將數(shù)據(jù)從一個(gè)數(shù)據(jù)庫寫到一個(gè)文件中，然后再從文件中將它讀入數(shù)據(jù)庫中時(shí)，兩個(gè)命令的字段和行處理選項(xiàng)必須匹配。否則，LOAD DATA INFILE 將不能正確地解釋文件內(nèi)容。

假設(shè)用戶使用SELECT ... INTO OUTFILE 以逗號(hào)分隔字段的方式將數(shù)據(jù)寫入到一個(gè)文件中：
SELECT * INTO OUTFILE 'data.txt' FIELDS TERMINATED BY ',' FROM table2;
為了將由逗號(hào)分隔的文件讀回時(shí)，正確的語句應(yīng)該是：
LOAD DATA INFILE 'data.txt' INTO TABLE table2 FIELDS TERMINATED BY ',';
如果用戶試圖用下面所示的語句讀取文件，它將不會(huì)工作，因?yàn)槊頛OAD DATA INFILE 以定位符區(qū)分字段值：
LOAD DATA INFILE 'data.txt' INTO TABLE table2 FIELDS TERMINATED BY '\t';
下面是我用來導(dǎo)入導(dǎo)出的命令：

select * into outfile 'ddd.txt' fields terminated by ',' from dn_location;  
load data infile 'ddd.txt' into table dn_location2  FIELDS TERMINATED BY ','; 

通過該方法導(dǎo)出的數(shù)據(jù)，是將各字段（只有數(shù)據(jù)，不導(dǎo)出表結(jié)構(gòu)）數(shù)據(jù)存在一個(gè)文件中，中間以逗號(hào)分隔，因?yàn)槲募胁⒉话瑪?shù)據(jù)庫名或者表名，因此需要在導(dǎo)入導(dǎo)出的時(shí)候些明確。該方法在18分鐘內(nèi)導(dǎo)出1.6億條記錄，46min內(nèi)導(dǎo)入6472W條記錄，平均速度：8442W條/h。mysql官方文檔也說明了，該方法比一次性插入一條數(shù)據(jù)性能快20倍。