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

本文根據(jù)馮帥老師在〖4月8日DBAplus社群上海數(shù)據(jù)庫(kù)技術(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ù)庫(kù)交互.

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

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

分享大綱：

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

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

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

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

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

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

因?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做了讀寫(xiě)分離.同樣的Oracle這邊因?yàn)橥瑫r(shí)還有應(yīng)用在跑,為了分散Oracle的壓力,所有的同步作業(yè)也是在備庫(kù)和異機(jī)房的OGG端進(jìn)行的操作.

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

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

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

1、SQL LOAD

我們?cè)谧钤绲臅r(shí)候只是進(jìn)行某個(gè)項(xiàng)目的遷移工作,因?yàn)闀r(shí)間的關(guān)系并沒(méi)有進(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,不過(guò)我還是更推薦spool,大家應(yīng)該都用過(guò)spool,他可以結(jié)合set把內(nèi)容輸出到指定的文件中,然后選擇合理的行列分隔符,就可以產(chǎn)生csv文件了.

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

我們來(lái)看一個(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)了出來(lái)看一下.

接著我又在MySQL創(chuàng)建一張一樣的表把數(shù)據(jù)load了進(jìn)去.load的語(yǔ)法不是我們今天要分享的重點(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.

官方文檔上有明確的說(shuō)明：

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

2、Python

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

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

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

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

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

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

3、OGG

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

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

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

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

2、異構(gòu)數(shù)據(jù)庫(kù)之間不支持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ù)制,通過(guò)解析源數(shù)據(jù)庫(kù)在線日志或歸檔日志獲得數(shù)據(jù)的增量變化,再將這些變化應(yīng)用到目標(biāo)數(shù)據(jù)庫(kù),那MySQL官方?jīng)]有提供工具呢?答應(yīng)是肯定的.

MySQL官方同樣也提供一個(gè)用于異構(gòu)之間的數(shù)據(jù)遷移工具,從MySQL到其它數(shù)據(jù)庫(kù),或者從其它數(shù)據(jù)庫(kù)到MySQL都是可以的.這個(gè)工具就是MySQL Migration Toolkit.這個(gè)工具可以單獨(dú)被下載,也被集成到了MySQL wrokbench里.不過(guò)如果單獨(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的類型若有問(wèn)題,需要人為參與一下進(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è)人感覺(jué)它的適用場(chǎng)景就是一次性導(dǎo)入的小批量的數(shù)據(jù).

5、KETTLE

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

它是一款純Java編寫(xiě)的軟件,就像它的名字(水壺)一樣,是用來(lái)把各種數(shù)據(jù)放到一個(gè)壺里,然后以一種指定的格式流出.當(dāng)然你也可以使用DS(datastage)或者Informatica.不過(guò)這兩個(gè)是收費(fèi)的,而kettle是免費(fèi)開(kā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)就是配置起來(lái)比OGG快,但是同樣可以通過(guò)job做到實(shí)時(shí)同步,處理速度和Python旗鼓相當(dāng),卻不用自己來(lái)寫(xiě)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)試.

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

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

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

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

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

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

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

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

接下來(lái)我們來(lái)看一下其它對(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里沒(méi)有這個(gè)功能.通過(guò)事件調(diào)度和存儲(chǔ)過(guò)程模擬物化視圖,實(shí)現(xiàn)的難點(diǎn)在于更新物化視圖,如果要求實(shí)時(shí)性高的更新,并且表太大的話,可能會(huì)有一些性能問(wèn)題.

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

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

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

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

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

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

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

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

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

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

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

4?分頁(yè)語(yǔ)句

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

5?JOIN

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

6?group by語(yǔ)句

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

7?bitmap位圖索引

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

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

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

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

9?角色

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

10?表情和特殊字符

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

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

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

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

我們的數(shù)據(jù)校檢工作主要分為在導(dǎo)入過(guò)程中的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)如果沒(méi)有問(wèn)題了,可以使用應(yīng)用程序?qū)ιa(chǎn)數(shù)據(jù)庫(kù)的副本進(jìn)行測(cè)試,在備庫(kù)上進(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)證是十分重要的,千萬(wàn)不要怕驗(yàn)證到錯(cuò)誤后要花好長(zhǎng)時(shí)候去抽取同步的操作這一步.因?yàn)橐坏](méi)有驗(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ǔ)法,以及應(yīng)該在數(shù)據(jù)上執(zhí)行何種確認(rèn)檢查.

• TRADITIONAL

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

• ONLY_FULL_GROUP_BY

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

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

簡(jiǎn)單來(lái)說(shuō),在ONLY_FULL_GROUP_BY模式下,target list中的值要么是來(lái)自于聚集函數(shù)的結(jié)果,要么是來(lái)自于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版本都沒(méi)關(guān)系.

• explicit_defauls_for_timestamp

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

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

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

此時(shí)如果TIMESTAMP列沒(méi)有顯式的指定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ù),來(lái)獲取最大性能的處理,比如可以把自適應(yīng)hash關(guān)掉,Doublewrite關(guān)掉,然后調(diào)整緩存區(qū),log文件的大小,把能變大的都變大,把能關(guān)的都關(guān)掉來(lái)獲取最大的性能,我們接下來(lái)說(shuō)幾個(gè)常用的：

• innodb_flush_log_at_trx_commit

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

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

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

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

• sync_binlog

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

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

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

• 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ù)庫(kù)有一個(gè)系統(tǒng)參數(shù)max_allowed_packet,其默認(rèn)值為1048576(1M),可以通過(guò)如下語(yǔ)句在數(shù)據(jù)庫(kù)中查詢其值：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).避免無(wú)謂的buffer pool的flush操作.但也不能把 innodb_log_file_size開(kāi)得太大,會(huì)明顯增加 InnoDB的log寫(xiě)入操作,而且會(huì)造成操作系統(tǒng)需要更多的Disk Cache開(kāi)銷.

• innodb_log_buffer_size

InnoDB用于將日志文件寫(xiě)入磁盤(pán)時(shí)的緩沖區(qū)大小字節(jié)數(shù).為了實(shí)現(xiàn)較高寫(xiě)入吞吐率,可增大該參數(shù)的默認(rèn)值.一個(gè)大的log buffer讓一個(gè)大的事務(wù)運(yùn)行,不需要在事務(wù)提交前寫(xiě)日志到磁盤(pán),因此,如果你有事務(wù)比如update、insert或者delete 很多的記錄,讓log buffer 足夠大來(lái)節(jié)約磁盤(pán)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),通過(guò)減少不同線程讀寫(xiě)緩沖頁(yè)面的爭(zhēng)用,將緩沖池拆分為不同實(shí)例有助于改善并發(fā)性.

總結(jié)

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

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

?

 

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