《從Oracle遷移到MySQL的各種坑及自救方案》要點(diǎn)：
本文介紹了從Oracle遷移到MySQL的各種坑及自救方案，希望對(duì)您有用。如果有疑問，可以聯(lián)系我們。

本文根據(jù)馮帥老師在〖4月8日DBAplus社群上海數(shù)據(jù)庫技術(shù)沙龍〗現(xiàn)場(chǎng)演講內(nèi)容整理而成.

講師介紹

馮帥

點(diǎn)融網(wǎng)高級(jí)DBA

• 獲有Oracle OCM、MySQL OCP,目前從事MySQL相關(guān)的運(yùn)維和架構(gòu)工作,擅長(zhǎng)異構(gòu)數(shù)據(jù)庫交互.

當(dāng)企業(yè)內(nèi)部使用的數(shù)據(jù)庫種類繁雜時(shí),或者有需求更換數(shù)據(jù)庫種類時(shí),都可能會(huì)做很多數(shù)據(jù)遷移的工作.有些遷移很簡(jiǎn)單,有些遷移可能就會(huì)很復(fù)雜,大家有沒有考慮過為了順利完成復(fù)雜的數(shù)據(jù)庫遷移任務(wù),都需要考慮并解決哪些問題呢?

在以前的工作中,我遷移過Oracle到Informix、Oracle和SQLServer、Oracle到MySQL. 在目前的公司又因?yàn)槿的關(guān)系,做了大量的遷移工作,栽了不少坑,所以和大家交流一下在遷移的過程中的一些實(shí)踐.

分享大綱：

1. 去O前的準(zhǔn)備與考慮
2. 確定目標(biāo)數(shù)據(jù)庫
3. 表和數(shù)據(jù)對(duì)象的遷移及工具比較
4. 其它對(duì)象的遷移
5. 一些性能參數(shù)

一、去O前的準(zhǔn)備與考慮

因?yàn)槌杀绢A(yù)算等多方面原因,公司決定要去O,在去O之前首先要決定拿什么來替代Oracle,拿什么工具將源數(shù)據(jù)庫的數(shù)據(jù)導(dǎo)到目標(biāo)數(shù)據(jù)庫、怎么導(dǎo)等的.導(dǎo)的過程的增量數(shù)據(jù)怎么處理.導(dǎo)的時(shí)候源數(shù)據(jù)和目標(biāo),以及數(shù)據(jù)的數(shù)據(jù)類型差異如何處理,像視圖、存儲(chǔ)過程、觸發(fā)器這種數(shù)據(jù)庫對(duì)象之間的不同怎么解決,導(dǎo)的時(shí)候如何不影響源數(shù)據(jù)庫性能.導(dǎo)完以后的數(shù)據(jù)比對(duì)以及數(shù)據(jù)無誤后應(yīng)用的性能問題都是要考慮的.

二、確定目標(biāo)數(shù)據(jù)庫

在我們做數(shù)據(jù)遷移之前先確認(rèn)的就是target database ,就是要遷到什么數(shù)據(jù)庫上,經(jīng)過了一些調(diào)研,從速度、流行度等多個(gè)方面選擇最終了MySQL.因?yàn)橄嘈疟籓racle收購(gòu)后表現(xiàn)會(huì)越來越好.

當(dāng)然也想過使用PosgreSQL,不過做了一個(gè)測(cè)試,發(fā)現(xiàn)MySQL5.7的QPS在比同樣配置的PG要高,基于在線事務(wù)對(duì)性能的要求,最終還是選擇了MySQL.選擇了MySQL以后,對(duì)于MySQL的分支和版本的選擇也很頭痛.Percona增加了很多性能相關(guān)補(bǔ)丁,MariaDB支持更多的引擎,官方的版本也能滿足目前的需求,從保守的原則上,我們的核心數(shù)據(jù)庫最終還是使用了官方的版本,一些不是太核心的數(shù)據(jù)庫,其它的分支也有在用.

因?yàn)镸yCat的支持關(guān)系最終選擇的是5.6的版本(目前MyCat1.6對(duì)MySQL5.7的支持不是太好),為了達(dá)到像Oracle的DG／OGG一樣穩(wěn)定的架構(gòu),我們把MySQL的架構(gòu)做成了雙機(jī)房的MHA,并且用了MyCat做了讀寫分離.同樣的Oracle這邊因?yàn)橥瑫r(shí)還有應(yīng)用在跑,為了分散Oracle的壓力,所有的同步作業(yè)也是在備庫和異機(jī)房的OGG端進(jìn)行的操作.

三、表和數(shù)據(jù)對(duì)象的遷移及工具對(duì)比

在選擇了合適的DB來替換Oracle后,下一步就是選擇一個(gè)合適的遷移工具來做遷移.我們?cè)谶w移工具的選擇方面花費(fèi)了大量時(shí)間和精力.遷移是一個(gè)漫長(zhǎng)而困難的工作,我們?cè)谶w移的過程中也歷經(jīng)了不同的階段,使用了不同的方法.從最初級(jí)的load csv升級(jí)成自已寫的程序,再去找Oracle和MySQL官方推薦的工具,最后也嘗試了一些 ETL的工具,被這么多工具摧殘之后,幸運(yùn)的是能夠在不同的場(chǎng)情下使用不同的方式.

接下來我們對(duì)每一種都進(jìn)行一個(gè)簡(jiǎn)單的介紹和使用中遇到的一些問題.

1、SQL LOAD

我們?cè)谧钤绲臅r(shí)候只是進(jìn)行某個(gè)項(xiàng)目的遷移工作,因?yàn)闀r(shí)間的關(guān)系并沒有進(jìn)行遷移工具的選型以及使用,使用了最簡(jiǎn)單的方式就是SQL LOAD.

所有的操作步驟比把大象放進(jìn)冰箱還要簡(jiǎn)單,簡(jiǎn)單得只要分兩步,第一步把Oracle的數(shù)據(jù)導(dǎo)成CSV或者SQL,然后再load或者source到MySQL中就可以了.

把Oracle的數(shù)據(jù)導(dǎo)成CSV或者SQL可以用很多工具,比如SQL developer或者toad,不過我還是更推薦spool,大家應(yīng)該都用過spool,他可以結(jié)合set把內(nèi)容輸出到指定的文件中,然后選擇合理的行列分隔符,就可以產(chǎn)生csv文件了.

使用SQL LOAD的優(yōu)點(diǎn)就是速度快和超級(jí)簡(jiǎn)單,不過同樣的,它也會(huì)有很多弊端,它很難做成自動(dòng)化和全面普及到很多張表上,每有一張表的操作就要寫SQL拼CSV,然后還不能保證是一樣的分隔符,大多數(shù)時(shí)候?qū)ob字段操作也很麻煩.對(duì)類似于comments的評(píng)論字段也很難原樣的copy過去.

我們來看一個(gè)簡(jiǎn)單的例子：

第一步我先在Oracle這邊創(chuàng)建了一張表,很簡(jiǎn)單只有四列,然后insert了三條數(shù)據(jù)查看了一下內(nèi)容.

做了一些簡(jiǎn)單的可能會(huì)用到的查詢.

看一下導(dǎo)出用的spool的內(nèi)容,實(shí)際用的時(shí)候肯定會(huì)比這個(gè)更復(fù)雜,要對(duì)換行、time、lob等進(jìn)行更多的函數(shù)處理.然后把數(shù)據(jù)導(dǎo)了出來看一下.

接著我又在MySQL創(chuàng)建一張一樣的表把數(shù)據(jù)load了進(jìn)去.load的語法不是我們今天要分享的重點(diǎn),它的作用就是把file load into table.可以指定行列分隔符. 可以看到數(shù)據(jù)load進(jìn)去了三行,同時(shí)也給出了三個(gè)警告,第二行一個(gè),第三行兩個(gè),分別是int類型的列傳了一個(gè)空字符串和時(shí)間類型的被截取了.查看一下表里的數(shù)據(jù),發(fā)現(xiàn)和預(yù)期的不一樣.

然后把剛剛在Oracle那邊進(jìn)行的查詢?cè)俅尾樵円幌?發(fā)現(xiàn)結(jié)果都變得不一樣了.

這是因?yàn)樵贛ySQL里int類型如果插入的為空,結(jié)果會(huì)自動(dòng)轉(zhuǎn)成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.

我們?cè)倏匆幌掠胑tl工具遷移過來的數(shù)據(jù),可以發(fā)現(xiàn)數(shù)據(jù)被insert成了null ,符合了Oracle的意思,其實(shí)這就是sqlload時(shí)一些弊端,數(shù)據(jù)類型可能弄得不是原來的數(shù)據(jù)了.同樣的,我們也可以設(shè)置成嚴(yán)格的模式,int類型的不允許插入null,我們會(huì)在下面的sql_mode里講到.

2、Python

遷了部分?jǐn)?shù)據(jù)之后覺得load數(shù)據(jù)雖然簡(jiǎn)單和快,但是瑜不掩瑕,總是有這樣那樣的問題,遷移之后往往還會(huì)同時(shí)伴隨著大量的數(shù)據(jù)修復(fù)工作.

很快的,我們就棄用了這種操作,在這里要說明一下SQL LOAD的操作因?yàn)樗俣扔挚煊植灰蕾嚻渌M件,所以適用于數(shù)據(jù)類型并不復(fù)雜的單表操作,然后就寫了python代碼來接替它來完成數(shù)據(jù)遷移的操作,使用python的話其實(shí)也很簡(jiǎn)單,可以分為三步,第一步就是建立配置表,同時(shí)和MySQL的表進(jìn)行mapping,標(biāo)識(shí)出是全量的還是增量的,如果是增量的,以什么做為增量來處理.第二步就是根據(jù)mapping進(jìn)行code、code、code,最后根據(jù)不同的入?yún)懞胏rontab就可以進(jìn)行調(diào)度就可以了.

使用python處理的過程中可以對(duì)一些數(shù)據(jù)進(jìn)行轉(zhuǎn)換,也更加靈活地配置了一些選項(xiàng),實(shí)現(xiàn)了較強(qiáng)的邏輯控制,當(dāng)然也有一些缺點(diǎn)：它的速度慢了太多(不過也只比load慢,比起來后面要介紹的Java編寫的軟件還是快很多).對(duì)于異常的處理也花費(fèi)了大量的代碼邏輯,同時(shí)也要會(huì)寫代碼.

我們可以簡(jiǎn)單來看一下它的實(shí)現(xiàn)：

這一個(gè)代碼片斷,顯示了增量同步每一天的數(shù)據(jù)邏輯.

這是每天跑批之后生成的log,可以看出來把warning和error都列了出來,同時(shí)也對(duì)行數(shù)進(jìn)行了統(tǒng)計(jì).已經(jīng)可以說是一個(gè)不錯(cuò)的小型產(chǎn)品了.可看出來6w條數(shù)據(jù)用了4s和load來比算是慢的,但是和Java之類的比算是快的了.

3、OGG

因?yàn)閜ython開發(fā)的這一套東西雖然也不算太慢,但因?yàn)橐约河么a實(shí)現(xiàn)較強(qiáng)的邏輯,并且有些需求在Oracle的業(yè)務(wù)還沒有完全下線之前要實(shí)時(shí)地同步到MySQL里來,所以我們又研究了一下OGG的做法.先提前說一下,OGG的應(yīng)用場(chǎng)景就是那種要求實(shí)時(shí)并且可能需要回寫數(shù)據(jù)的.

OGG的用法說起來很簡(jiǎn)單,只要配置好Oracle端,配置好MySQL端,然后對(duì)應(yīng)的進(jìn)程起起來就可以了.但用過OGG的人都知道配置一套OGG本身就很麻煩了,異構(gòu)數(shù)據(jù)庫之間再進(jìn)行同步的話,調(diào)通并可用需要很久的配置時(shí)間,所以我大致說一下做法,除非真的有這種硬性需求,不然不推薦使用.

簡(jiǎn)單說一下用OGG的過程和注意事項(xiàng)：

1、 5.6版本需要12.1.2版本的OGG才支持

2、異構(gòu)數(shù)據(jù)庫之間不支持DDL復(fù)制

• 從Oracle同步到MySQL,屬于異構(gòu)架構(gòu),不支持DDL同步,包括添加和刪除字段,添加和刪除索引,重命名表,表分析統(tǒng)計(jì)數(shù)據(jù).
• 若是涉及到源端和目標(biāo)端DDL操作,需要進(jìn)行源端和目標(biāo)端同時(shí)手工操作.

3、必須要配置defgen,且文件必須放在相同的目錄.

4、如果要是雙向的話,就必須把MySQL端的binglog設(shè)置成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對(duì)MySQL只支持InnoDB引擎.所以,在創(chuàng)建MySQL端的表的時(shí)候,要指定表為InnoDB引擎.

create table MySQL (name char(10)) engine=innodb;

所有的幫助可以online help里去看

http://docs.Oracle.com/goldengate/c1221/gg-winux/GIMYS/system_requirements.htm#GIMYS122

4、MySQL Migration Toolkit

OGG是Oracle官方推薦的工具,使用原理就是基于日志的結(jié)構(gòu)化數(shù)據(jù)復(fù)制,通過解析源數(shù)據(jù)庫在線日志或歸檔日志獲得數(shù)據(jù)的增量變化,再將這些變化應(yīng)用到目標(biāo)數(shù)據(jù)庫,那MySQL官方?jīng)]有提供工具呢?答應(yīng)是肯定的.

MySQL官方同樣也提供一個(gè)用于異構(gòu)之間的數(shù)據(jù)遷移工具,從MySQL到其它數(shù)據(jù)庫,或者從其它數(shù)據(jù)庫到MySQL都是可以的.這個(gè)工具就是MySQL Migration Toolkit.這個(gè)工具可以單獨(dú)被下載,也被集成到了MySQL wrokbench里.不過如果單獨(dú)下載的話 只有windows的版本.

https://downloads.MySQL.com/archives/migration/

這是一個(gè)基于Java的程序,所以依賴于jar包,使用它的第一步就是load一個(gè)odbc.jar.接著就可以把源端和目標(biāo)端進(jìn)行配置連接,選擇要導(dǎo)入的對(duì)象(可以包含視圖,但是一般有子查詢的會(huì)報(bào)錯(cuò)),進(jìn)行導(dǎo)入就可以了.

使用它的優(yōu)點(diǎn)就是可以在MySQL端自動(dòng)創(chuàng)建表,但有可能自動(dòng)convert的類型若有問題,需要人為參與一下進(jìn)行處理,比如Oracle中通常會(huì)對(duì)Timestamp類型的數(shù)據(jù)設(shè)置默認(rèn)值sysdate,但在MySQL中是不能識(shí)別的.

缺點(diǎn)就是只有windows的平臺(tái)有,在導(dǎo)大數(shù)據(jù)量時(shí),極有可能就hang住了.所以個(gè)人感覺它的適用場(chǎng)景就是一次性導(dǎo)入的小批量的數(shù)據(jù).

5、KETTLE

上面提到的幾種工具都是一步一個(gè)坑使用過之后發(fā)現(xiàn)并沒有盡善盡美,總有這樣或者那樣的不足,接下來我們來推薦的就是終級(jí)必殺的好用的etl工具：KETTLE.

它是一款純Java編寫的軟件,就像它的名字(水壺)一樣,是用來把各種數(shù)據(jù)放到一個(gè)壺里,然后以一種指定的格式流出.當(dāng)然你也可以使用DS(datastage)或者Informatica.不過這兩個(gè)是收費(fèi)的,而kettle是免費(fèi)開源的.

這里只介紹它最簡(jiǎn)單的能滿足我們把數(shù)據(jù)從Oracle遷移到MySQL的功能.

同理,第一步把jar包load進(jìn)去,不同的是,這次要load的是MySQL的jar包.需要注意的是,如果你的MySQL版本不同可能需要load不同的jar包.第二步同也是配置連接信息,保證你的源和目標(biāo)都連接成功,最后一步就是簡(jiǎn)單的拖拖拽拽.最后run一下就可以了.

它的優(yōu)點(diǎn)就是配置起來比OGG快,但是同樣可以通過job做到實(shí)時(shí)同步,處理速度和Python旗鼓相當(dāng),卻不用自己來寫mapping關(guān)系,并且提供了圖形化界面.也能和Migration Toolkit一樣同時(shí)創(chuàng)建表(新增一個(gè)Java腳本),進(jìn)行類型轉(zhuǎn)換,但日志更詳細(xì).只是可能學(xué)習(xí)成本高一點(diǎn),要看的懂一些Java報(bào)錯(cuò)方便調(diào)試.

接下來我們簡(jiǎn)單看一個(gè)demo：

我們運(yùn)行spoon.sh之后可以打開這個(gè)界面.view一界顯示了這個(gè)轉(zhuǎn)換的名字、數(shù)據(jù)源、處理步驟等,中間區(qū)域是你拖拽出來的操作,一個(gè)輸入,一個(gè)輸出.這就是一個(gè)簡(jiǎn)單的數(shù)據(jù)遷移的所有步驟.

打開input的內(nèi)容,就是很簡(jiǎn)單的一條SQL在哪個(gè)源數(shù)據(jù)庫上抽取數(shù)據(jù),當(dāng)然這條SQL也可以是拖拽生成出來,類似于congos的拖拽生成報(bào)表.千萬要注意的是,不要加分號(hào)!

output的內(nèi)容就顯示豐富了很多,選擇目標(biāo)數(shù)據(jù)源,以及會(huì)自動(dòng)的mapping列的信息,還有在遷移之間要不要先清空,遷移過程中如果遇到問題了會(huì)不會(huì)中止.

這里就是顯示了它超越Migration tools的log最細(xì)粒度到行級(jí)別,可以更快地分析出問題.

這里則是詳細(xì)的日志輸出.一般如果定時(shí)跑批處理的話,把它重定向到具體的log里,然后當(dāng)做發(fā)送郵件.

四、其它對(duì)象的遷移

上面用了很長(zhǎng)的篇幅介紹了一下幾種遷移的工具,每種遷移的方式都是各有千秋,在合適的場(chǎng)景下選擇適合自己的方法進(jìn)行操作.不過剛剛遷移的都是表和數(shù)據(jù)對(duì)象.我們都知道在數(shù)據(jù)庫還有一些其它的對(duì)象,像視圖、物化視圖、存儲(chǔ)過程、函數(shù)、包,或者一個(gè)索引,同樣的SQL是不是也需要改寫,都是我們需要考慮到的一個(gè)因素.

接下來我們來看一下其它對(duì)象怎么遷移.

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、物化視圖

物化視圖用于預(yù)先計(jì)算并保存表連接或聚集等耗時(shí)較多的操作結(jié)果,這樣在執(zhí)行查詢時(shí),就可以避免進(jìn)行這些耗時(shí)的操作,而從快速得到結(jié)果.但是MySQL里沒有這個(gè)功能.通過事件調(diào)度和存儲(chǔ)過程模擬物化視圖,實(shí)現(xiàn)的難點(diǎn)在于更新物化視圖,如果要求實(shí)時(shí)性高的更新,并且表太大的話,可能會(huì)有一些性能問題.

3、Trigger、存儲(chǔ)過程、package

1)Oracle創(chuàng)建觸發(fā)器時(shí)允許or,但是MySQL不允許.所以遷移時(shí)如果有需要寫兩個(gè).

2)兩種數(shù)據(jù)庫定義變量的位置不同,而且MySQL里不支持%type.這個(gè)在Oracle中用得太頻繁了,是個(gè)好習(xí)慣.

3)elseif的邏輯分支語法不同,并且MySQL里也沒有for循環(huán).

4)在MySQL中不可以返回cursor,并且聲明時(shí)就要賦對(duì)象.

5)Oracle用包來把存儲(chǔ)過程分門別類,而且在package里可以定義公共的變量/類型,既方便了編程,又減少了服務(wù)器的編譯開銷.可MySQL里根本沒有這個(gè)概念.所以MySQL的函數(shù)也不可以重載.

6)預(yù)定義函數(shù).MySQL里沒有to_char() to_date()之類的函數(shù),也并不是所有的Oracle都是好的,就像substring()和load_file()這樣的函數(shù),MySQL有,Oracle卻沒有.

7)MySQL里可以使用set和=號(hào)給變量賦值,但不可以使用：=. 而且在MySQL里沒 || 來拼接字符串.

8)MySQL的注釋必須要求– 和內(nèi)容之間有一個(gè)空格.

9)MySQL存儲(chǔ)過程中只能使用leave退出當(dāng)前存儲(chǔ)過程,不可以使用return.

10)MySQL異常對(duì)象不同,MySQL同樣的可以定義和處理異常,但對(duì)象名字不一樣.

4?分頁語句

MySQL中使用的是limit關(guān)鍵字,但在Oracle中使用的是rownum關(guān)鍵字.所以每有的和分頁相關(guān)的語句都要進(jìn)行調(diào)整.

5?JOIN

如果你的SQL里有大量的(+),這絕對(duì)是一個(gè)很頭疼的問題.需要改寫.

6?group by語句

Oracle里在查詢字段出現(xiàn)的列一定要出現(xiàn)在group by后面,而MySQL里卻不用.只是這樣出來的結(jié)果可能并不是預(yù)期的結(jié)果.造成MySQL這種奇怪的特性的歸因于sql_mode的設(shè)置,一會(huì)會(huì)詳細(xì)說一下sql_mode.不過從Oracle遷移到MySQL的過程中,group by語句不會(huì)有跑不通的情況,反過來遷移可能就需要很長(zhǎng)的時(shí)間來調(diào)整了.

7?bitmap位圖索引

在Oracle里可以利用bitmap來實(shí)現(xiàn)布隆過濾,進(jìn)行一些查詢的優(yōu)化,同時(shí)這一特性也為Oracle一些數(shù)據(jù)倉(cāng)庫相關(guān)的操作提供了很好的支持,但在MySQL里沒有這種索引,所以以前在Oracle里利于bitmap進(jìn)行優(yōu)化的SQL可能在MySQL會(huì)有很大的性能問題.

目前也沒有什么較好的解決方案,可以嘗試著建btree的索引看是否能解決問題.要求MySQL提供bitmap索引在MySQL的bug庫里被人當(dāng)作一個(gè)中級(jí)的問題提交了上去,不過至今還是沒有解決.

8?分區(qū)表(Partitioned table)

需要特殊處理,與Oracle的做法不同,MySQL會(huì)將分區(qū)鍵視作主鍵和唯一鍵的一部分.為確保不對(duì)應(yīng)用邏輯和查詢產(chǎn)生影響,必須用恰當(dāng)?shù)姆謪^(qū)鍵重新定義目標(biāo)架構(gòu).

9?角色

MySQL8.0以前也沒有role的對(duì)象.在遷移過程中如果遇到的角色則是需要拼SQL來重新賦權(quán).不過MySQL更好的一點(diǎn)是MySQL的用戶與主機(jī)有關(guān).

10?表情和特殊字符

在Oracle里我們一般都選擇AL32UTF8的字符集,已經(jīng)可以支付生僻字和emoji的表情了,因?yàn)樵谶w移的時(shí)候有的表包含了大量的表情字符,在MySQL里設(shè)置了為utf8卻不行,導(dǎo)過去之后所有的都是問號(hào),后來改成了utf8mb4才解決問題,所以推薦默認(rèn)就把所有的DB都裝成utf8mb4吧.

Oracle和MySQL差異遠(yuǎn)遠(yuǎn)不止這些,像閃回、AWR這些有很多,這里只談一些和遷移工作相關(guān)的.

五、數(shù)據(jù)校驗(yàn)

當(dāng)數(shù)據(jù)遷移完成后,如何確保數(shù)據(jù)的正確遷移、沒有遺漏和錯(cuò)誤是一個(gè)很難的問題.這里的難不是實(shí)現(xiàn)起來困難,而是要把它自動(dòng)化,達(dá)到節(jié)省人力的目標(biāo)有點(diǎn)難,因?yàn)閮烧叩臄?shù)據(jù)類型不同,數(shù)據(jù)量偏大,寫一些腳本去做檢查效果不大.

我們的數(shù)據(jù)校檢工作主要分為在導(dǎo)入過程中的log和警告,在load的時(shí)候SHOW WARNINGS和errors,在使用Python、OGG、Kettle等工具時(shí)詳細(xì)去看每個(gè)errors信息.

1、count(*)

遷移或增量操作完成以后,用最簡(jiǎn)單的count(*)去檢查,在MySQL和Oracle上檢查進(jìn)行比對(duì).如果數(shù)據(jù)量一致,再進(jìn)行數(shù)據(jù)內(nèi)容的驗(yàn)證.由于數(shù)據(jù)量太大,只進(jìn)行了抽樣檢測(cè).人工的手動(dòng)檢驗(yàn)如果沒有問題了,可以使用應(yīng)用程序?qū)ιa(chǎn)數(shù)據(jù)庫的副本進(jìn)行測(cè)試,在備庫上進(jìn)行應(yīng)用程序的測(cè)試,從而進(jìn)行再一次的驗(yàn)檢.

2、etl工具

另外推薦的一種方式就是使用etl工具配置好MySQL和Oracle的數(shù)據(jù)源,分別對(duì)數(shù)據(jù)進(jìn)行抽取,然后生成cube,進(jìn)行多緯度的報(bào)表展現(xiàn).數(shù)據(jù)是否有偏差,可以一目了然看清.

數(shù)據(jù)的完整性驗(yàn)證是十分重要的,千萬不要怕驗(yàn)證到錯(cuò)誤后要花好長(zhǎng)時(shí)候去抽取同步的操作這一步.因?yàn)橐坏]有驗(yàn)證到錯(cuò)誤,讓數(shù)據(jù)進(jìn)行了使用卻亂掉了,后果將更嚴(yán)重.

3、SQL_MODE

https://dev.MySQL.com/doc/refman/5.5/en/sql-mode.html

MySQL服務(wù)器能夠工作在不同的SQL模式下,針對(duì)不同的客戶端,以不同的方式應(yīng)用這些模式.這樣應(yīng)用程序就能對(duì)服務(wù)器操作進(jìn)行量身定制,以滿足自己的需求.這類模式定義了MySQL應(yīng)支持的SQL語法,以及應(yīng)該在數(shù)據(jù)上執(zhí)行何種確認(rèn)檢查.

• TRADITIONAL

設(shè)置“嚴(yán)格模式”,限制可接受的數(shù)據(jù)庫輸入數(shù)據(jù)值(類似于其它數(shù)據(jù)庫服務(wù)器),該模式的簡(jiǎn)單描述是當(dāng)在列中插入不正確的值時(shí)“給出錯(cuò)誤而不是警告”.

• ONLY_FULL_GROUP_BY

在MySQL的sql_mode=default的情況下是非ONLY_FULL_GROUP_BY語義,也就是說一條select語句,MySQL允許target list中輸出的表達(dá)式是除聚集函數(shù)、group by column以外的表達(dá)式,這個(gè)表達(dá)式的值可能在經(jīng)過group by操作后變成undefined,無法確定(實(shí)際上MySQL的表現(xiàn)是分組內(nèi)第一行對(duì)應(yīng)列的值)

select? list中的所有列的值都是明確語義.

簡(jiǎn)單來說,在ONLY_FULL_GROUP_BY模式下,target list中的值要么是來自于聚集函數(shù)的結(jié)果,要么是來自于group by list中的表達(dá)式的值.

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校對(duì)規(guī)則屬于PADSPACE,MySQL對(duì)CHAR和VARCHAR值進(jìn)行比較都忽略尾部空格,和服務(wù)器配置以及MySQL版本都沒關(guān)系.

• explicit_defauls_for_timestamp

MySQL中TIMESTAMP類型和其它的類型有點(diǎn)不一樣(在沒有設(shè)置explicit_defaults_for_timestamp=1的情況下),在默認(rèn)情況下,如果TIMESTAMP列沒有顯式的指明null屬性,那么該列會(huì)被自動(dòng)加上not null屬性(而其他類型的列如果沒有被顯式的指定not null,那么是允許null值的),如果往這個(gè)列中插入null值,會(huì)自動(dòng)設(shè)置該列的值為current timestamp值,表中的第一個(gè)TIMESTAMP列,如果沒有指定null屬性或者沒有指定默認(rèn)值,也沒有指定ON UPDATE語句,那么該列會(huì)自動(dòng)被加上DEFAULT .

CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP屬性.第一個(gè)TIMESTAMP列之后的其它的TIMESTAMP類型的列,如果沒有指定null屬性,也沒有指定默認(rèn)值,那該列會(huì)被自動(dòng)加上DEFAULT ‘0000-00-00 00:00:00’屬性.如果insert語句中沒有為該列指定值,那么該列中插入’0000-00-00 00:00:00’,并且沒有warning.

如果我們啟動(dòng)時(shí)在配置文件中指定了explicit_defaults_for_timestamp=1,MySQL會(huì)按照如下的方式處理TIMESTAMP列.

此時(shí)如果TIMESTAMP列沒有顯式的指定not null屬性,那么默認(rèn)的該列可以為null,此時(shí)向該列中插入null值時(shí),會(huì)直接記錄null,而不是current timestamp.并且不會(huì)自動(dòng)的為表中的第一個(gè)TIMESTAMP列加上DEFAULT CURRENT_TIMESTAMP 和ON UPDATE CURRENT_TIMESTAMP屬性,除非你在建表時(shí)顯式的指明.

六、一些性能參數(shù)

我們可以在導(dǎo)入數(shù)據(jù)的時(shí)候預(yù)先的修改一些參數(shù),來獲取最大性能的處理,比如可以把自適應(yīng)hash關(guān)掉,Doublewrite關(guān)掉,然后調(diào)整緩存區(qū),log文件的大小,把能變大的都變大,把能關(guān)的都關(guān)掉來獲取最大的性能,我們接下來說幾個(gè)常用的：

• innodb_flush_log_at_trx_commit

如果innodb_flush_log_at_trx_commit設(shè)置為0,log buffer將每秒一次地寫入log file中,并且log file的flush(刷到磁盤)操作同時(shí)進(jìn)行.該模式下,在事務(wù)提交時(shí),不會(huì)主動(dòng)觸發(fā)寫入磁盤的操作.

如果innodb_flush_log_at_trx_commit設(shè)置為1,每次事務(wù)提交時(shí)MySQL都會(huì)把log buffer的數(shù)據(jù)寫入log file,并且flush(刷到磁盤)中去.

如果innodb_flush_log_at_trx_commit設(shè)置為2,每次事務(wù)提交時(shí)MySQL都會(huì)把log buffer的數(shù)據(jù)寫入log file.但是flush(刷到磁盤)的操作并不會(huì)同時(shí)進(jìn)行.該模式下,MySQL會(huì)每秒執(zhí)行一次 flush(刷到磁盤)操作.

注意：由于進(jìn)程調(diào)度策略問題,這個(gè)“每秒執(zhí)行一次 flush(刷到磁盤)操作”并不是保證100%的“每秒”.

• sync_binlog

sync_binlog 的默認(rèn)值是0,像操作系統(tǒng)刷其它文件的機(jī)制一樣,MySQL不會(huì)同步到磁盤中去,而是依賴操作系統(tǒng)來刷新binary log.

當(dāng)sync_binlog =N (N>0) ,MySQL 在每寫N次 二進(jìn)制日志binary log時(shí),會(huì)使用fdatasync()函數(shù)將它的寫二進(jìn)制日志binary log同步到磁盤中去.

注：如果啟用了autocommit,那么每一個(gè)語句statement就會(huì)有一次寫操作；否則每個(gè)事務(wù)對(duì)應(yīng)一個(gè)寫操作.

• max_allowed_packet

在導(dǎo)大容量數(shù)據(jù)特別是CLOB數(shù)據(jù)時(shí),可能會(huì)出現(xiàn)異常：“Packets larger than max_allowed_packet are not allowed”.這是由于MySQL數(shù)據(jù)庫有一個(gè)系統(tǒng)參數(shù)max_allowed_packet,其默認(rèn)值為1048576(1M),可以通過如下語句在數(shù)據(jù)庫中查詢其值：show VARIABLES like ‘%max_allowed_packet%’;

修改此參數(shù)的方法是在MySQL文件夾找到my.cnf文件,在my.cnf文件[MySQLd]中添加一行：max_allowed_packet=16777216

• innodb_log_file_size

InnoDB日志文件太大,會(huì)影響MySQL崩潰恢復(fù)的時(shí)間,太小會(huì)增加IO負(fù)擔(dān),所以我們要調(diào)整合適的日志大小.在數(shù)據(jù)導(dǎo)入時(shí)先把這個(gè)值調(diào)大一點(diǎn).避免無謂的buffer pool的flush操作.但也不能把 innodb_log_file_size開得太大,會(huì)明顯增加 InnoDB的log寫入操作,而且會(huì)造成操作系統(tǒng)需要更多的Disk Cache開銷.

• innodb_log_buffer_size

InnoDB用于將日志文件寫入磁盤時(shí)的緩沖區(qū)大小字節(jié)數(shù).為了實(shí)現(xiàn)較高寫入吞吐率,可增大該參數(shù)的默認(rèn)值.一個(gè)大的log buffer讓一個(gè)大的事務(wù)運(yùn)行,不需要在事務(wù)提交前寫日志到磁盤,因此,如果你有事務(wù)比如update、insert或者delete 很多的記錄,讓log buffer 足夠大來節(jié)約磁盤I/O.

• innodb_buffer_pool_size

這個(gè)參數(shù)主要緩存InnoDB表的索引、數(shù)據(jù)、插入數(shù)據(jù)時(shí)的緩沖.為InnoDN加速優(yōu)化首要參數(shù).一般讓它等于你所有的innodb_log_buffer_size的大小就可以,

innodb_log_file_size要越大越好.

• innodb_buffer_pool_instances

InnoDB緩沖池拆分成的區(qū)域數(shù)量.對(duì)于數(shù)GB規(guī)模緩沖池的系統(tǒng),通過減少不同線程讀寫緩沖頁面的爭(zhēng)用,將緩沖池拆分為不同實(shí)例有助于改善并發(fā)性.

總結(jié)

1. 一定要選擇合適你的遷移工具,沒有哪一個(gè)工具是最好的.
2. 數(shù)據(jù)的檢驗(yàn)非常重要,有的時(shí)候我們遷過去很開心,校驗(yàn)時(shí)發(fā)生錯(cuò)誤,這個(gè)時(shí)候必須要重來.
3. 重復(fù)地遷移是很正常的,合乎每次遷移可能需要很長(zhǎng)時(shí)間,總會(huì)是有錯(cuò)誤的,要做好再遷的心態(tài).

文章來自微信公眾號(hào)：DBAplus社群

?

 

轉(zhuǎn)載請(qǐng)注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/4248.html