Mysql百萬級資料遷移實戰筆記

艾逗筆發表於2018-06-30
MySql筆記

背景

上個月跟朋友一起做了個微信小程式，趁著5.20節日的熱度，兩個禮拜內迅速積累了一百多萬使用者，我們在小程式頁面增加了收集formid的埋點，用於給微信使用者傳送模板訊息通知。

這個小程式一開始的後端邏輯是用douchat框架寫的，使用框架自帶的dc_mp_fans表儲存微信端授權登入的使用者資訊，使用dc_mp_tempmsg表儲存formid。截止到目前，收集到的資料超過380萬，很大一部分formid都已經成功使用給使用者傳送過模板通知，起到了較好的二次推廣的效果。

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

Mysql百萬級資料遷移實戰筆記

方案選擇

  • mysqldump遷移

平常開發中，我們比較經常使用的資料備份遷移方式是用mysqldump工具匯出一個sql檔案，再在新資料庫中匯入sql來完成資料遷移。試驗發現，通過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的命令，以匯入匯出檔案的形式完成了百萬級資料的遷移。

遷移過程

  1. 在源資料庫中匯出資料檔案
select * from dc_mp_fans into outfile '/data/fans.txt';
複製程式碼
  1. 複製資料檔案到目標伺服器
zip fans.zip /data/fans.txt
scp fans.zip root@ip:/data/ 
複製程式碼
  1. 在目標資料庫匯入檔案
unzip /data/fans.zip
複製程式碼
load 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

    Mysql百萬級資料遷移實戰筆記

  • wxa_fans

    Mysql百萬級資料遷移實戰筆記

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

總結

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

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

在新系統展示資料的效果：

Mysql百萬級資料遷移實戰筆記

下一篇將寫一寫前段時間通過指令碼20分鐘內給18萬微信小程式粉絲群發模板通知的經歷。敬請期待。

轉載請註明原文連結：idoubi.cc

相關文章