重點記錄Mysql百萬級的資料遷移過程！

#                                                        

假如有這麼一個場景，開發了一個小程序，趁著雙十一活動的熱度，一個月後迅速積累了一百多萬用戶，我們在小程序頁面增加了收集formid的埋點，用於發送範本訊息通知給微信用戶。

隨著資料量的增大，之前使用的伺服器空間開始有點不夠用，最近新寫了一個專門用於做小程式後台開發的框架，於是想把原來的資料遷移到新系統的資料庫。買了一台4核心8G的機器，開始做資料遷移。下面對遷移過程做一個簡單的記錄。

方案選擇

#mysqldump遷移

平常開發中，我們比較經常使用的資料備份遷移方式是用mysqldump工具匯出一個sql文件，再在新資料庫中匯入sql來完成資料遷移。 【推薦：mysql影片教學】

試驗發現，透過mysqldump導出百萬級量的資料庫成sql文件，大概耗時幾分鐘，導出的sql文件大小在1G左右，然後再把這個1G的sql檔案透過scp指令複製到另一台伺服器，大概也需要耗時幾分鐘。在新伺服器的資料庫中透過source指令來匯入數據，我跑了一整晚都沒有把數據匯入進來，cpu跑滿。

腳本遷移

直接透過命令列操作資料庫進行資料的匯出和匯入是比較便捷的方式，但是資料量較大的情況下往往會比較耗時，對伺服器效能要求也比較高。如果對資料遷移時間要求不是很高，可以嘗試寫腳本來遷移資料。雖然沒有實際嘗試，但我想過大概有兩種腳本方案。

第一種方式，在遷移目標伺服器跑一個遷移腳本，遠端連接來源資料伺服器的資料庫，透過設定查詢條件，分塊讀取來源數據，並在讀取後寫入目標資料庫。這種遷移方式效率可能會比較低，資料匯出和匯入相當於是一個同步的過程，需要等到讀取完了才能寫入。如果查詢條件設計得合理，也可以透過多執行緒的方式啟動多個遷移腳本，達到並行遷移的效果。

第二種方式，可以結合redis建構一個「生產 消費」的遷移方案。來源資料伺服器可以作為資料生產者，在來源資料伺服器上跑一個多執行緒腳本，並行讀取資料庫裡面的數據，並且把資料寫入到redis佇列。目標伺服器作為一個消費者，在目標伺服器上也跑一個多線程腳本，遠端連接redis，並行讀取redis隊列裡面的數據，並把讀取到的數據寫入到目標資料庫。這種方式相對於第一種方式，是一種非同步方案，資料匯入和資料匯出可以同時進行，透過redis做資料的中轉站，效率會有較大的提升。

這裡你也可以使用go語言來寫遷移腳本，利用其原生的並發特性，可以達到並行遷移資料的目的，提升遷移效率。

檔案遷移

第一種遷移方案效率太低，第二種遷移方案編碼代價較高，透過比較和在網路上找的資料分析，我最後選擇了通過mysql的。

select data into outfile file.txt、load data infile file.txt into table

的命令，以匯入匯出檔案的形式完成了百萬級資料的遷移。

遷移過程

在來源資料庫中匯出資料檔案

select * from dc_mp_fans into outfile '/data/fans.txt';

複製資料檔案到目標伺服器

zip fans.zip /data/fans.txtscp fans.zip root@ip:/data/

在目標資料庫導入檔案

unzip /data/fans.zipload data infile '/data/fans.txt' into table wxa_fans(id,appid,openid,unionid,@dummy,created_at,@dummy,nickname,gender,avatar_url,@dummy,@dummy,@dummy,@dummy,language,country,province,city,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy);

按照這麼幾個步驟操作，幾分鐘內就完成了一個百萬級資料表的跨伺服器遷移工作。

注意項目

• mysql安全項設定

在mysql執行load data infile和into outfile指令都需要在mysql開啟了secure_file_priv選項， 可以透過show global variables like '%secure%';查看mysql是否開啟了此選項，預設值Null識別不允許執行匯入匯出指令。

透過vim /etc/my.cnf修改mysql設定項，將secure_file_priv的值設為空：

[mysqld]  secure_file_priv=''

則可透過指令匯入匯出資料檔。

• 匯入匯出的資料表欄位不對應

上面範例的從來源資料庫的dc_mp_fans資料表遷移資料到目標資料庫的wxa_fans表，兩個資料表的欄位分別為： dc_mp_fans

wxa_fans

#在匯入資料的時候，可以透過設定欄位名稱來匹配目標欄位的數據，可以透過@dummy丟棄掉不需要的目標欄位資料。

總結

結合本次資料遷移經歷，總結起來就是：

• 小資料量可以使用mysqldump指令進行導入匯出，這種方式簡單便捷。
• 資料量較大，且有足夠的遷移耐心時，可以選擇自己寫腳本，選擇合適的平行方案遷移數據，這種方式編碼成本較高。
• 資料量較大，且希望能在短時間內完成資料遷移時，可以透過mysql導入匯出檔案的方式來遷移，這種方式效率較高。             

#

以上是重點記錄Mysql百萬級的資料遷移過程！的詳細內容。更多資訊請關注PHP中文網其他相關文章！