Mysql如何實(shí)現(xiàn)數(shù)據(jù)遷移

195次閱讀

共計(jì) 2643 個(gè)字符，預(yù)計(jì)需要花費(fèi) 7 分鐘才能閱讀完成。

這篇文章將為大家詳細(xì)講解有關(guān) Mysql 如何實(shí)現(xiàn)數(shù)據(jù)遷移，丸趣 TV 小編覺得挺實(shí)用的，因此分享給大家做個(gè)參考，希望大家閱讀完這篇文章后可以有所收獲。

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)備】

導(dǎo)出前關(guān)閉日志，避免數(shù)據(jù)備份過程中頻繁記錄日志
刪除主鍵，關(guān)閉自動(dòng)增長。在該表中主鍵其實(shí)作用不大，自動(dòng)增長是需要的（mysql 中自動(dòng)增長的一列一定要為 key，所以設(shè)置為主鍵），等待數(shù)據(jù)轉(zhuǎn)移結(jié)束后重新設(shè)置回來
刪除表中索引。在插入數(shù)據(jù)時(shí)索引的存在會(huì)很大程度上影響速度，所以先關(guān)閉，轉(zhuǎn)移后重新建立
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)槊?LOAD 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 倍

關(guān)于“Mysql 如何實(shí)現(xiàn)數(shù)據(jù)遷移”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，使各位可以學(xué)到更多知識(shí)，如果覺得文章不錯(cuò)，請把它分享出去讓更多的人看到。

正文完