Oracle/雲MySQL/MsSQL“大遷移”真相及最優方案

最近一段時間碰到一些資料遷移的專案，如：Oracle遷移到MySQL，MsSQL遷移到MySQL，雲MySQL遷移到本地MySQL。對於這方面做了系統的整理。包括：遷移方案的選擇、如何跳出遷移遇到的坑、怎樣修改MySQL引數獲取最大效能，加入分庫分表的需求如何實現？文章的最後，作者做了很多可行性的總結，碼字不易，如果對您有幫助，感謝轉發。

遷移方案的選擇：

---

拋開業務邏輯的因素，根據不同的版本、不同平臺、不同停機時間需求，有不同的可選路徑決定遷移方

法和工具：

 

遷移方法	優點	缺點
SQL LOAD	操作簡單、速度快、選擇資料範圍靈活	需自定義開發批次操作、對於CLOB等特殊欄位無法支援
OGG	商用軟體，廣泛的資料庫平臺支援、靈活的複製架構、基於日誌的實時資料同步、穩定性高	對維護技能有一定的要求、費用高
ETL 軟體	使用方便簡單、定時同步	批次處理大量表需定製化配置
MYSQL移植工具	安裝簡單、可自動建立表	不可定製、技術支援較弱
定製遷移工具	可高度定製，保證最佳效能和最短停機時間	暫無

由於不同的資料庫版本、不同的元件安裝、不同的應用開發特徵都會導致遷移計劃的複雜性和差異性。

調研中，除了OGG，有幾個MySQL遷移的工具，推薦的比較多，但是收費的。

【工具：OGG (goldengate) 

同時支援Oracle，Mssql 遷移到 MySQL 上

引數：filter，COMPUTE 進行分庫分表邏輯】

● SQLyog

   ()

● Navicat Premium

  ()

● Mss2sql

   ()

● DB2DB

   ()

選擇遷移軟體，必須要考慮 軟體易用性， 處理速度和記憶體佔用，資料完整性。這部分很重要。

以上四款軟體中：

1. 最不推薦使用的是 Navicat Premium，主要原因是資料的完整性表現較差，轉換後的數

據不能立即用於生產環境，需要程式設計師仔細自行查詢原因和分析。

2. SQLyog 有較好的資料完整性，但整體處理速度非常的慢，如果資料較大的情況下，需要浪費非常多寶

貴的時間。比較推薦的是

3. DB2DB，處理速度，資料完整性，整體表現較好，操作起來實在方便。

我本人趨向於自己寫python指令碼。

遷移中會存在哪些細節上的問題？

---

1. 字符集

字符集轉化：Oracle字符集AL32UTF8，ZHS16GBK，轉換成MySQL支援的字符集Latin1，utf8，utf8mb4(emoji的表情符)

Mysql對於字符集裡有兩個概念：一個是"Character set"另一個是"Collations"。

Collations：Mysql對字元的比較，排序規則 

Character set：字元的編碼方式

2. 欄位型別

Oracle Row， Clob，BINARY_DOUBLE型別轉化成MySQL支援的欄位型別。

如：Oracle CLOB欄位最大長度4G對應MySQL LONGTEXT 等等，但要是把資料這些資料遷移到MySQL上，可以想象到會發生什麼事情。

3. 主鍵

有些源表沒有設定主鍵， 但對於MySQL來說主鍵的意思非常大，特別是複製環節裡。

4. 遷移時間和資料量 

對於現在線上不間斷提供的業務非常重要，按照這個指標可以制定全量或者增量方式進行遷移。

5. 考慮因素

除了以上內容源資料庫還有賬號、檢視、儲存過程、函式、觸發器，索引等，同樣也很重要，都是需要考慮的一個因素。

6. 校驗資料

這一關最後門卡，當資料遷移完成後，如何確保資料的正確遷移、沒有遺漏和錯誤是一個很難的問題。這裡的難不是實現起來困難，而是要把它自動化，達到節省人力的目標有點難，因為兩者的資料型別不同，資料量偏大，寫一些指令碼去做檢查效果不大。

資料的完整性驗證是十分重要的，千萬不要怕驗證到錯誤後要花好長時候去抽取同步的操作這一步。因為一旦沒有驗證到錯誤，讓資料進行了使用卻亂掉了，後果將更嚴重。

一般場景下都是對應查詢資料行數count來判斷資料的是否存在問題。或則 是用create_time時間欄位進行驗證資料。或則抽取部分資料進行驗證。還有匯入過程中的log和警告 ，errors 等資訊。

MySQL一些效能引數

可以在匯入資料的時候預先修改一些引數，來獲取最大效能的處理，比如可以把自適應hash關掉，Doublewrite關掉，然後調整快取區，log檔案的大小，把能變大的都變大，把能關的都關掉來獲取最大的效能，接下來說幾個常用的：

1. innodb_flush_log_at_trx_commit

• 如果innodb_flush_log_at_trx_commit設定為0，log buffer將每秒一次地寫入log file中，並且log file的flush(刷到磁碟)操作同時進行。該模式下，在事務提交時，不會主動觸發寫入磁碟的操作。

• 如果innodb_flush_log_at_trx_commit設定為1，每次事務提交時MySQL都會把log buffer的資料寫入log file，並且flush(刷到磁碟)中去。

• 如果innodb_flush_log_at_trx_commit設定為2，每次事務提交時MySQL都會把log buffer的資料寫入log file。但是flush(刷到磁碟)的操作並不會同時進行。該模式下，MySQL會每秒執行一次 flush(刷到磁碟)操作。

注意：由於程式排程策略問題,這個“每秒執行一次 flush(刷到磁碟)操作”並不是保證100%的“每秒”。

2. sync_binlog

• sync_binlog 的預設值是0，像作業系統刷其它檔案的機制一樣，MySQL不會同步到磁碟中去，而是依賴作業系統來重新整理binary log。

• 當sync_binlog =N (N>0) ，MySQL 在每寫N次 二進位制日誌binary log時，會使用fdatasync()函式將它的寫二進位制日誌binary log同步到磁碟中去。

注意：如果啟用了autocommit，那麼每一個語句statement就會有一次寫操作；否則每個事務對應一個寫操作。

3. max_allowed_packet

• 在導大容量資料特別是CLOB資料時，可能會出現異常：“Packets larger than max_allowed_packet are not allowed”。這是由於MySQL資料庫有一個系統引數max_allowed_packet，其預設值為1048576(1M)，可以透過如下語句在資料庫中查詢其值：show VARIABLES like '%max_allowed_packet%'; 

• 修改此引數的方法是在MySQL資料夾找到my.cnf檔案，在my.cnf檔案[MySQLd]中新增一行：max_allowed_packet=16777216

4. innodb_log_file_size

InnoDB日誌檔案太大，會影響MySQL崩潰恢復的時間，太小會增加IO負擔，所以我們要調整合適的日誌大小。在資料匯入時先把這個值調大一點。避免無謂的buffer pool的flush操作。但也不能把innodb_log_file_size開得太大，會明顯增加 InnoDB的log寫入操作，而且會造成作業系統需要更多的Disk Cache開銷。

5. innodb_log_buffer_size

InnoDB用於將日誌檔案寫入磁碟時的緩衝區大小位元組數。為了實現較高寫入吞吐率，可增大該引數的預設值。一個大的log buffer讓一個大的事務執行，不需要在事務提交前寫日誌到磁碟，因此，如果你有事務比如update、insert或者delete 很多的記錄，讓log buffer 足夠大來節約磁碟I/O。

6. innodb_buffer_pool_size

這個引數主要快取InnoDB表的索引、資料、插入資料時的緩衝。為InnoDN加速最佳化首要引數。一般讓它等於你所有的innodb_log_buffer_size的大小就可以，innodb_log_file_size要越大越好。 

7. innodb_buffer_pool_instances

InnoDB緩衝池拆分成的區域數量。對於數GB規模緩衝池的系統，透過減少不同執行緒讀寫緩衝頁面的爭用，將緩衝池拆分為不同例項有助於改善併發性。

分庫分表方案

---

現在加難度加入分庫分表需求。

這種情況建議選擇傳統的方式寫一個遷移程式，讀源資料庫，透過中介軟體寫入目標庫db1,db2,db3裡

如果源資料來源設計的合理完全可以用全量+增量方式實現。如下圖所示

雖然這種方式很靈活，自行控制，但也有缺點，所有業務邏輯，分庫分表方案，驗證都需要手動編寫

下次可以在不同的平臺下使用。

現在業界比較常用的分庫分表的中介軟體有兩種：

• proxy形，如：基於阿里開源的Cobar產品而研發的mycat， 需要部署另外伺服器，作為分庫分表的代理，對外服務，包含分庫分表的配置資訊，現在版本是mycat2.0。 

• client形式，如噹噹出的sharding-jdbc，現在有京東金融進行維護，現在版本sharding-jdbc4.0開發中。是jar包，使用非常方便。我個人趨向於Sharding-JDBC，這種方式，無需額外部署，替換原有jdbc，DBA也無需改變原有的運維方式，減輕了DBA的任務。

總結

---

1. 一定要選擇合適你的遷移工具，沒有哪一個工具是最好的。

2. 資料的檢驗非常重要，有的時候我們遷過去很開心，校驗時發生錯誤，這個時候必須要重來。

3. 重複地遷移是很正常的，合乎每次遷移可能需要很長時間，總會是有錯誤的，要做好再遷的心態。

4. 遷移過程中的日誌記錄非常重要，一段出現故障，可以再問題點開始繼續進行遷移。