講師介紹
馮帥
點融網進階dba
獲有oracle ocm、mysql ocp，目前從事mysql相關的運維和架構工作，擅長異構資料庫互動。
當企業内部使用的資料庫種類繁雜時，或者有需求更換資料庫種類時，都可能會做很多資料遷移的工作。有些遷移很簡單，有些遷移可能就會很複雜，大家有沒有考慮過為了順利完成複雜的資料庫遷移任務，都需要考慮并解決哪些問題呢？
在以前的工作中，我遷移過oracle到informix、oracle和sqlserver、oracle到mysql。 在目前的公司又因為去o的關系，做了大量的遷移工作，栽了不少坑，是以和大家交流一下在遷移的過程中的一些實踐。
分享大綱：
去o前的準備與考慮
确定目标資料庫
表和資料對象的遷移及工具比較
其它對象的遷移
一些性能參數
一、去o前的準備與考慮
因為成本預算等多方面原因，公司決定要去o，在去o之前首先要決定拿什麼來替代oracle，拿什麼工具将源資料庫的資料導到目标資料庫、怎麼導等的。導的過程的增量資料怎麼處理。導的時候源資料和目标，以及資料的資料類型差異如何處理，像視圖、存儲過程、觸發器這種資料庫對象之間的不同怎麼解決，導的時候如何不影響源資料庫性能。導完以後的資料比對以及資料無誤後應用的性能問題都是要考慮的。
二、确定目标資料庫
在我們做資料遷移之前先确認的就是target database ，就是要遷到什麼資料庫上，經過了一些調研，從速度、流行度等多個方面選擇最終了mysql。因為相信被oracle收購後表現會越來越好。
當然也想過使用posgresql，不過做了一個測試，發現mysql5.7的qps在比同樣配置的pg要高，基于線上事務對性能的要求，最終還是選擇了mysql。選擇了mysql以後，對于mysql的分支和版本的選擇也很頭痛。percona增加了很多性能相關更新檔，mariadb支援更多的引擎，官方的版本也能滿足目前的需求，從保守的原則上，我們的核心資料庫最終還是使用了官方的版本，一些不是太核心的資料庫，其它的分支也有在用。
因為mycat的支援關系最終選擇的是5.6的版本（目前mycat1.6對mysql5.7的支援不是太好），為了達到像oracle的dg／ogg一樣穩定的架構，我們把mysql的架構做成了雙機房的mha，并且用了mycat做了讀寫分離。同樣的oracle這邊因為同時還有應用在跑，為了分散oracle的壓力，所有的同步作業也是在備庫和異機房的ogg端進行的操作。
三、表和資料對象的遷移及工具對比
在選擇了合适的db來替換oracle後，下一步就是選擇一個合适的遷移工具來做遷移。我們在遷移工具的選擇方面花費了大量時間和精力。遷移是一個漫長而困難的工作，我們在遷移的過程中也曆經了不同的階段，使用了不同的方法。從最初級的load csv更新成自已寫的程式，再去找oracle和mysql官方推薦的工具，最後也嘗試了一些 etl的工具，被這麼多工具摧殘之後，幸運的是能夠在不同的場情下使用不同的方式。
接下來我們對每一種都進行一個簡單的介紹和使用中遇到的一些問題。
1、sql load
我們在最早的時候隻是進行某個項目的遷移工作，因為時間的關系并沒有進行遷移工具的選型以及使用，使用了最簡單的方式就是sql load。
所有的操作步驟比把大象放進冰箱還要簡單，簡單得隻要分兩步，第一步把oracle的資料導成csv或者sql，然後再load或者source到mysql中就可以了。
把oracle的資料導成csv或者sql可以用很多工具，比如sql developer或者toad，不過我還是更推薦spool，大家應該都用過spool，他可以結合set把内容輸出到指定的檔案中，然後選擇合理的行列分隔符，就可以産生csv檔案了。
使用sql load的優點就是速度快和超級簡單，不過同樣的，它也會有很多弊端，它很難做成自動化和全面普及到很多張表上，每有一張表的操作就要寫sql拼csv，然後還不能保證是一樣的分隔符，大多數時候對lob字段操作也很麻煩。對類似于comments的評論字段也很難原樣的copy過去。
我們來看一個簡單的例子：
第一步我先在oracle這邊建立了一張表，很簡單隻有四列，然後insert了三條資料檢視了一下内容。
做了一些簡單的可能會用到的查詢。
看一下導出用的spool的内容，實際用的時候肯定會比這個更複雜，要對換行、time、lob等進行更多的函數處理。然後把資料導了出來看一下。
接着我又在mysql建立一張一樣的表把資料load了進去。load的文法不是我們今天要分享的重點，它的作用就是把file load into table.可以指定行列分隔符。 可以看到資料load進去了三行，同時也給出了三個警告，第二行一個，第三行兩個，分别是int類型的列傳了一個空字元串和時間類型的被截取了。檢視一下表裡的資料，發現和預期的不一樣。
然後把剛剛在oracle那邊進行的查詢再次查詢一下，發現結果都變得不一樣了。
這是因為在mysql裡int類型如果插入的為空，結果會自動轉成0。
官方文檔上有明确的說明：
an empty field value is interpreted different from a missing field:
for string types, the column is set to the empty string.
for numeric types, the column is set to 0.
for date and time types, the column is set to the appropriate “zero” value for the type.
我們再看一下用etl工具遷移過來的資料，可以發現資料被insert成了null ，符合了oracle的意思，其實這就是sqlload時一些弊端，資料類型可能弄得不是原來的資料了。同樣的，我們也可以設定成嚴格的模式，int類型的不允許插入null，我們會在下面的sql_mode裡講到。
2、python
遷了部分資料之後覺得load資料雖然簡單和快，但是瑜不掩瑕，總是有這樣那樣的問題，遷移之後往往還會同時伴随着大量的資料修複工作。
很快的，我們就棄用了這種操作，在這裡要說明一下sql load的操作因為速度又快又不依賴其它元件，是以适用于資料類型并不複雜的單表操作，然後就寫了python代碼來接替它來完成資料遷移的操作，使用python的話其實也很簡單，可以分為三步，第一步就是建立配置表，同時和mysql的表進行mapping，辨別出是全量的還是增量的，如果是增量的，以什麼做為增量來處理。第二步就是根據mapping進行code、code、code，最後根據不同的入參寫好crontab就可以進行排程就可以了。
使用python處理的過程中可以對一些資料進行轉換，也更加靈活地配置了一些選項，實作了較強的邏輯控制，當然也有一些缺點：它的速度慢了太多（不過也隻比load慢，比起來後面要介紹的java編寫的軟體還是快很多）。對于異常的處理也花費了大量的代碼邏輯，同時也要會寫代碼。
我們可以簡單來看一下它的實作：
這一個代碼片斷，顯示了增量同步每一天的資料邏輯。
這是每天跑批之後生成的log，可以看出來把warning和error都列了出來，同時也對行數進行了統計。已經可以說是一個不錯的小型産品了。可看出來6w條資料用了4s和load來比算是慢的，但是和java之類的比算是快的了。
3、ogg
因為python開發的這一套東西雖然也不算太慢，但因為要自己用代碼實作較強的邏輯，并且有些需求在oracle的業務還沒有完全下線之前要實時地同步到mysql裡來，是以我們又研究了一下ogg的做法。先提前說一下，ogg的應用場景就是那種要求實時并且可能需要回寫資料的。
ogg的用法說起來很簡單，隻要配置好oracle端，配置好mysql端，然後對應的程序起起來就可以了。但用過ogg的人都知道配置一套ogg本身就很麻煩了，異構資料庫之間再進行同步的話，調通并可用需要很久的配置時間，是以我大緻說一下做法，除非真的有這種硬性需求，不然不推薦使用。
簡單說一下用ogg的過程和注意事項：
1、 5.6版本需要12.1.2版本的ogg才支援
2、異構資料庫之間不支援ddl複制
從oracle同步到mysql，屬于異構架構，不支援ddl同步，包括添加和删除字段，添加和删除索引，重命名表，表分析統計資料。
若是涉及到源端和目标端ddl操作，需要進行源端和目标端同時手工操作。
3、必須要配置defgen，且檔案必須放在相同的目錄。
4、如果要是雙向的話，就必須把mysql端的binglog設定成row
binlog_format: this parameter sets the format of the logs. it must be set to the value of row, which directs the database to log dml statements in binary format. any other log format (mixed or statement) causes extract to abend.
5、goldengate對mysql隻支援innodb引擎。是以，在建立mysql端的表的時候，要指定表為innodb引擎。
create table mysql (name char(10)) engine=innodb;
4、mysql migration toolkit
ogg是oracle官方推薦的工具，使用原理就是基于日志的結構化資料複制，通過解析源資料庫線上日志或歸檔日志獲得資料的增量變化，再将這些變化應用到目标資料庫，那mysql官方沒有提供工具呢？答應是肯定的。
mysql官方同樣也提供一個用于異構之間的資料遷移工具，從mysql到其它資料庫，或者從其它資料庫到mysql都是可以的。這個工具就是mysql migration toolkit。這個工具可以單獨被下載下傳，也被內建到了mysql wrokbench裡。不過如果單獨下載下傳的話 隻有windows的版本。
這是一個基于java的程式，是以依賴于jar包，使用它的第一步就是load一個odbc.jar。接着就可以把源端和目标端進行配置連接配接，選擇要導入的對象（可以包含視圖，但是一般有子查詢的會報錯），進行導入就可以了。
使用它的優點就是可以在mysql端自動建立表，但有可能自動convert的類型若有問題，需要人為參與一下進行處理，比如oracle中通常會對timestamp類型的資料設定預設值sysdate，但在mysql中是不能識别的。
缺點就是隻有windows的平台有，在導大資料量時，極有可能就hang住了。是以個人感覺它的适用場景就是一次性導入的小批量的資料。
5、kettle
上面提到的幾種工具都是一步一個坑使用過之後發現并沒有盡善盡美，總有這樣或者那樣的不足，接下來我們來推薦的就是終級必殺的好用的etl工具：kettle。
它是一款純java編寫的軟體，就像它的名字（水壺）一樣，是用來把各種資料放到一個壺裡，然後以一種指定的格式流出。當然你也可以使用ds(datastage)或者informatica。不過這兩個是收費的，而kettle是免費開源的。
這裡隻介紹它最簡單的能滿足我們把資料從oracle遷移到mysql的功能。
同理，第一步把jar包load進去，不同的是，這次要load的是mysql的jar包。需要注意的是，如果你的mysql版本不同可能需要load不同的jar包。第二步同也是配置連接配接資訊，保證你的源和目标都連接配接成功，最後一步就是簡單的拖拖拽拽。最後run一下就可以了。
它的優點就是配置起來比ogg快，但是同樣可以通過job做到實時同步，處理速度和python旗鼓相當，卻不用自己來寫mapping關系，并且提供了圖形化界面。也能和migration toolkit一樣同時建立表（新增一個java腳本），進行類型轉換，但日志更詳細。隻是可能學習成本高一點，要看的懂一些java報錯友善調試。
接下來我們簡單看一個demo：
我們運作spoon.sh之後可以打開這個界面。view一界顯示了這個轉換的名字、資料源、處理步驟等，中間區域是你拖拽出來的操作，一個輸入，一個輸出。這就是一個簡單的資料遷移的所有步驟。
打開input的内容，就是很簡單的一條sql在哪個源資料庫上抽取資料，當然這條sql也可以是拖拽生成出來，類似于congos的拖拽生成報表。千萬要注意的是，不要加分号！
output的内容就顯示豐富了很多，選擇目标資料源，以及會自動的mapping列的資訊，還有在遷移之間要不要先清空，遷移過程中如果遇到問題了會不會中止。
這裡就是顯示了它超越migration tools的log最細粒度到行級别，可以更快地分析出問題。
這裡則是詳細的日志輸出。一般如果定時跑批處理的話，把它重定向到具體的log裡，然後當做發送郵件。
四、其它對象的遷移
上面用了很長的篇幅介紹了一下幾種遷移的工具，每種遷移的方式都是各有千秋，在合适的場景下選擇适合自己的方法進行操作。不過剛剛遷移的都是表和資料對象。我們都知道在資料庫還有一些其它的對象，像視圖、物化視圖、存儲過程、函數、包，或者一個索引，同樣的sql是不是也需要改寫，都是我們需要考慮到的一個因素。
接下來我們來看一下其它對象怎麼遷移。
1、view
在mysql裡view是不可以嵌套子查詢的：
create view v_test as select * from (select * from test) t;
error 1349 (hy000): view's select contains a subquery in the from clause
解決方法就是view的嵌套：
create view v_sub_test as select * from test;
query ok, 0 rows affected (0.02 sec)
create view v_test as select * from v_sub_test;
query ok, 0 rows affected (0.00 sec)
2、物化視圖
物化視圖用于預先計算并儲存表連接配接或聚集等耗時較多的操作結果，這樣在執行查詢時，就可以避免進行這些耗時的操作，而從快速得到結果。但是mysql裡沒有這個功能。通過事件排程和存儲過程模拟物化視圖，實作的難點在于更新物化視圖，如果要求實時性高的更新，并且表太大的話，可能會有一些性能問題。
3、trigger、存儲過程、package
1）oracle建立觸發器時允許or，但是mysql不允許。是以遷移時如果有需要寫兩個。
2）兩種資料庫定義變量的位置不同，而且mysql裡不支援%type。這個在oracle中用得太頻繁了，是個好習慣。
3）elseif的邏輯分支文法不同，并且mysql裡也沒有for循環。
4）在mysql中不可以傳回cursor，并且聲明時就要賦對象。
5）oracle用包來把存儲過程分門别類，而且在package裡可以定義公共的變量/類型，既友善了程式設計，又減少了伺服器的編譯開銷。可mysql裡根本沒有這個概念。是以mysql的函數也不可以重載。
6）預定義函數。mysql裡沒有to_char() to_date()之類的函數，也并不是所有的oracle都是好的，就像substring()和load_file()這樣的函數，mysql有，oracle卻沒有。
7）mysql裡可以使用set和=号給變量指派，但不可以使用：=。 而且在mysql裡沒 || 來拼接字元串。
8）mysql的注釋必須要求-- 和内容之間有一個空格。
9）mysql存儲過程中隻能使用leave退出目前存儲過程，不可以使用return。
10）mysql異常對象不同，mysql同樣的可以定義和處理異常，但對象名字不一樣。
4､分頁語句
mysql中使用的是limit關鍵字，但在oracle中使用的是rownum關鍵字。是以每有的和分頁相關的語句都要進行調整。
5､join
如果你的sql裡有大量的（+），這絕對是一個很頭疼的問題。需要改寫。
6､group by語句
oracle裡在查詢字段出現的列一定要出現在group by後面，而mysql裡卻不用。隻是這樣出來的結果可能并不是預期的結果。造成mysql這種奇怪的特性的歸因于sql_mode的設定，一會會詳細說一下sql_mode。不過從oracle遷移到mysql的過程中，group by語句不會有跑不通的情況，反過來遷移可能就需要很長的時間來調整了。
7､bitmap位圖索引
在oracle裡可以利用bitmap來實作布隆過濾，進行一些查詢的優化，同時這一特性也為oracle一些資料倉庫相關的操作提供了很好的支援，但在mysql裡沒有這種索引，是以以前在oracle裡利于bitmap進行優化的sql可能在mysql會有很大的性能問題。
目前也沒有什麼較好的解決方案，可以嘗試着建btree的索引看是否能解決問題。要求mysql提供bitmap索引在mysql的bug庫裡被人當作一個中級的問題送出了上去，不過至今還是沒有解決。
8､分區表（partitioned table）
需要特殊處理，與oracle的做法不同，mysql會将分區鍵視作主鍵和唯一鍵的一部分。為確定不對應用邏輯和查詢産生影響，必須用恰當的分區鍵重新定義目标架構。
9､角色
mysql8.0以前也沒有role的對象。在遷移過程中如果遇到的角色則是需要拼sql來重新賦權。不過mysql更好的一點是mysql的使用者與主機有關。
10､表情和特殊字元
在oracle裡我們一般都選擇al32utf8的字元集，已經可以支付生僻字和emoji的表情了，因為在遷移的時候有的表包含了大量的表情字元，在mysql裡設定了為utf8卻不行，導過去之後所有的都是問号，後來改成了utf8mb4才解決問題，是以推薦預設就把所有的db都裝成utf8mb4吧。
oracle和mysql差異遠遠不止這些，像閃回、awr這些有很多，這裡隻談一些和遷移工作相關的。
五、資料校驗
當資料遷移完成後，如何確定資料的正确遷移、沒有遺漏和錯誤是一個很難的問題。這裡的難不是實作起來困難，而是要把它自動化，達到節省人力的目标有點難，因為兩者的資料類型不同，資料量偏大，寫一些腳本去做檢查效果不大。
我們的資料校檢工作主要分為在導入過程中的log和警告，在load的時候show warnings和errors，在使用python、ogg、kettle等工具時詳細去看每個errors資訊。
1、count(*)
遷移或增量操作完成以後，用最簡單的count(*)去檢查，在mysql和oracle上檢查進行比對。如果資料量一緻，再進行資料内容的驗證。由于資料量太大，隻進行了抽樣檢測。人工的手動檢驗如果沒有問題了，可以使用應用程式對生産資料庫的副本進行測試，在備庫上進行應用程式的測試，進而進行再一次的驗檢。
2、etl工具
另外推薦的一種方式就是使用etl工具配置好mysql和oracle的資料源，分别對資料進行抽取，然後生成cube，進行多緯度的報表展現。資料是否有偏差，可以一目了然看清。
資料的完整性驗證是十分重要的，千萬不要怕驗證到錯誤後要花好長時候去抽取同步的操作這一步。因為一旦沒有驗證到錯誤，讓資料進行了使用卻亂掉了，後果将更嚴重。
3、sql_mode
<a href="https://dev.mysql.com/doc/refman/5.5/en/sql-mode.html">https://dev.mysql.com/doc/refman/5.5/en/sql-mode.html</a>
mysql伺服器能夠工作在不同的sql模式下，針對不同的用戶端，以不同的方式應用這些模式。這樣應用程式就能對伺服器操作進行量身定制，以滿足自己的需求。這類模式定義了mysql應支援的sql文法，以及應該在資料上執行何種确認檢查。
traditional
設定“嚴格模式”，限制可接受的資料庫輸入資料值（類似于其它資料庫伺服器），該模式的簡單描述是當在列中插入不正确的值時“給出錯誤而不是警告”。
only_full_group_by
在mysql的sql_mode=default的情況下是非only_full_group_by語義，也就是說一條select語句，mysql允許target list中輸出的表達式是除聚集函數、group by column以外的表達式，這個表達式的值可能在經過group by操作後變成undefined，無法确定(實際上mysql的表現是分組内第一行對應列的值)
select list中的所有列的值都是明确語義。
簡單來說，在only_full_group_by模式下，target list中的值要麼是來自于聚集函數的結果，要麼是來自于group by list中的表達式的值。
without regard to any trailing spaces
all mysql collations are of type padspace. this means that all char, varchar, and text values in mysql are compared without regard to any trailing spaces. “comparison” in this context does not include the like pattern-matching operator, for which trailing spaces are significant.
mysql校對規則屬于padspace，mysql對char和varchar值進行比較都忽略尾部空格，和伺服器配置以及mysql版本都沒關系。
explicit_defauls_for_timestamp
mysql中timestamp類型和其它的類型有點不一樣(在沒有設定explicit_defaults_for_timestamp=1的情況下），在預設情況下，如果timestamp列沒有顯式的指明null屬性，那麼該列會被自動加上not null屬性(而其他類型的列如果沒有被顯式的指定not null，那麼是允許null值的)，如果往這個列中插入null值，會自動設定該列的值為current timestamp值，表中的第一個timestamp列，如果沒有指定null屬性或者沒有指定預設值，也沒有指定on update語句，那麼該列會自動被加上default 。
current_timestamp和on update current_timestamp屬性。第一個timestamp列之後的其它的timestamp類型的列，如果沒有指定null屬性，也沒有指定預設值，那該列會被自動加上default '0000-00-00 00:00:00'屬性。如果insert語句中沒有為該列指定值，那麼該列中插入'0000-00-00 00:00:00'，并且沒有warning。
如果我們啟動時在配置檔案中指定了explicit_defaults_for_timestamp=1，mysql會按照如下的方式處理timestamp列。
此時如果timestamp列沒有顯式的指定not null屬性，那麼預設的該列可以為null，此時向該列中插入null值時，會直接記錄null，而不是current timestamp。并且不會自動的為表中的第一個timestamp列加上default current_timestamp 和on update current_timestamp屬性，除非你在建表時顯式的指明。
六、一些性能參數
我們可以在導入資料的時候預先的修改一些參數，來擷取最大性能的處理，比如可以把自适應hash關掉，doublewrite關掉，然後調整緩存區，log檔案的大小，把能變大的都變大，把能關的都關掉來擷取最大的性能，我們接下來說幾個常用的：
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%的“每秒”。
sync_binlog
sync_binlog 的預設值是0，像作業系統刷其它檔案的機制一樣，mysql不會同步到磁盤中去，而是依賴作業系統來重新整理binary log。
當sync_binlog =n (n>0) ，mysql 在每寫n次 二進制日志binary log時，會使用fdatasync()函數将它的寫二進制日志binary log同步到磁盤中去。
注：如果啟用了autocommit，那麼每一個語句statement就會有一次寫操作；否則每個事務對應一個寫操作。
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
innodb_log_file_size
innodb日志檔案太大，會影響mysql崩潰恢複的時間，太小會增加io負擔，是以我們要調整合适的日志大小。在資料導入時先把這個值調大一點。避免無謂的buffer pool的flush操作。但也不能把 innodb_log_file_size開得太大，會明顯增加 innodb的log寫入操作，而且會造成作業系統需要更多的disk cache開銷。
innodb_log_buffer_size
innodb用于将日志檔案寫入磁盤時的緩沖區大小位元組數。為了實作較高寫入吞吐率，可增大該參數的預設值。一個大的log buffer讓一個大的事務運作，不需要在事務送出前寫日志到磁盤，是以，如果你有事務比如update、insert或者delete 很多的記錄，讓log buffer 足夠大來節約磁盤i/o。
innodb_buffer_pool_size
這個參數主要緩存innodb表的索引、資料、插入資料時的緩沖。為innodn加速優化首要參數。一般讓它等于你所有的innodb_log_buffer_size的大小就可以，
innodb_log_file_size要越大越好。
innodb_buffer_pool_instances
innodb緩沖池拆分成的區域數量。對于數gb規模緩沖池的系統，通過減少不同線程讀寫緩沖頁面的争用，将緩沖池拆分為不同執行個體有助于改善并發性。
總結
一定要選擇合适你的遷移工具，沒有哪一個工具是最好的。
資料的檢驗非常重要，有的時候我們遷過去很開心，校驗時發生錯誤，這個時候必須要重來。
重複地遷移是很正常的，合乎每次遷移可能需要很長時間，總會是有錯誤的，要做好再遷的心态。
原文釋出時間為：2017-04-28
本文來自雲栖社群合作夥伴dbaplus