數(shù)據(jù)庫作為數(shù)字化用戶的核心資產(chǎn),其遷移是一項(xiàng)復(fù)雜且重要的任務(wù),特別是在VMware平臺(tái)替換及IT基礎(chǔ)設(shè)施更新?lián)Q代之時(shí),尤其需要保障數(shù)據(jù)庫遷移過程的平穩(wěn)、流暢。

深信服推出的數(shù)據(jù)庫管理平臺(tái)(DMP)是為關(guān)系型數(shù)據(jù)庫量身打造的運(yùn)維管理解決方案,它整合了數(shù)據(jù)庫日常運(yùn)維所需的各項(xiàng)功能,包括但不限于數(shù)據(jù)庫的創(chuàng)建、實(shí)時(shí)監(jiān)控、數(shù)據(jù)備份以及災(zāi)難恢復(fù)等。此外,DMP 還配備了先進(jìn)的數(shù)據(jù)庫遷移工具DTS,使企業(yè)能夠?qū)?shù)據(jù)庫從VMware平臺(tái)或物理服務(wù)器無縫遷移至深信服的云計(jì)算環(huán)境中,確保了遷移過程的高效率、安全性和可靠性。

深信服為滿足用戶不同場(chǎng)景下的遷移需求,提供豐富的MySQL數(shù)據(jù)庫遷移方案:

SCMT 信服云遷移工具 能夠?qū)崿F(xiàn)針對(duì)常見單機(jī)數(shù)據(jù)庫的遷移,支持點(diǎn)對(duì)點(diǎn)模式、熱備模式等多種遷移方式,操作簡(jiǎn)單,對(duì)業(yè)務(wù)影響小。

DTS 數(shù)據(jù)庫遷移工具 是深信服數(shù)據(jù)庫管理平臺(tái)DMP針對(duì)遷移場(chǎng)景開發(fā)的專用工具,支持主從同步遷移,通過配置MySQL的主從復(fù)制,將數(shù)據(jù)從主庫同步到從庫,然后進(jìn)行角色切換。通常情況下采用全量+增量的遷移方式,但是當(dāng)5.6 -> 8.0跨版本遷移時(shí),由于會(huì)存在遷移后sql語法不兼容的情況,因此需要采用全量遷移的方式。

物理備份 / 邏輯備份遷移 ,面對(duì)DMP平臺(tái)無法滿足特定的遷移條件或要求時(shí),深信服將協(xié)調(diào)專業(yè)的數(shù)據(jù)庫專家DBA來制定和執(zhí)行定制化的物理備份/邏輯備份遷移方案。

本文重點(diǎn)介紹使用 DMP 的 DTS 工具對(duì) MySQL 數(shù)據(jù)庫進(jìn)行全量加增量的數(shù)據(jù)遷移方式,也是目前較為推薦的MySQL遷移方式。它利用mydumper/myloader邏輯備份恢復(fù)技術(shù)與MySQL主從復(fù)制原理,通過與數(shù)據(jù)庫內(nèi)部組件的緊密協(xié)作,實(shí)現(xiàn)數(shù)據(jù)的高效遷移。

遷移支持版本:

MySQL 5.6 → MySQL 8.0 ? ? ? 全量遷移

MySQL 5.6-5.7 → MySQL 5.7 ? 全量+增量遷移

MySQL 5.7、8.0 → MySQL 8.0 ?全量+增量遷移

遷移架構(gòu)支持:

MySQL 單機(jī) → MySQL 單機(jī)

MySQL 主從 → MySQL 主從

MySQL 單機(jī) → MySQL 主從

MySQL 主從 → MySQL 單機(jī)

一、 DTS? 遷移技術(shù)原理

本文重點(diǎn)介紹使用DMP的DTS工具對(duì)MySQL數(shù)據(jù)庫進(jìn)行全量加增量的數(shù)據(jù)遷移方式,也是目前較為推薦的MySQL遷移方式,支持跨版本(5.6-5.7)、支持跨平臺(tái)遷移。

DMP的DTS支持mydumper + 主從復(fù)制方式遷移,mydumper是一個(gè)用于MySQL的開源熱備份工具,它可以在不鎖定表的情況下進(jìn)行數(shù)據(jù)備份。使用mydumper和主從復(fù)制方式進(jìn)行數(shù)據(jù)遷移的基本原理如下:

源、目標(biāo)數(shù)據(jù)庫初始化數(shù)據(jù)并建立主從關(guān)系;

從庫會(huì)生成兩個(gè)線程,一個(gè)I/O線程,一個(gè)SQL線程;

I/O線程會(huì)去請(qǐng)求主庫的binlog,并將得到的binlog寫到本地的relay-log(中繼日志)文件中;

主庫會(huì)生成一個(gè)log dump線程,用來給從庫I/O線程傳輸binlog;

SQL線程,會(huì)讀取relay-log文件中的日志,并解析成sql語句逐一執(zhí)行。

深信服DTS數(shù)據(jù)遷移工具,通過自動(dòng)化和標(biāo)準(zhǔn)化的數(shù)據(jù)遷移策略,大幅度降低操作難度并提升遷移效率。該工具通過直觀的可視化界面,為用戶提供了一站式服務(wù),包括目標(biāo)數(shù)據(jù)庫的構(gòu)建、遷移前的詳盡檢查、實(shí)時(shí)監(jiān)控遷移過程以及高效切換控制。這種集成化的方法不僅簡(jiǎn)化了數(shù)據(jù)庫的創(chuàng)建和性能優(yōu)化,還確保了用戶能夠精確地掌握并優(yōu)化整個(gè)遷移流程,以適應(yīng)企業(yè)對(duì)數(shù)據(jù)庫遷移的復(fù)雜和多變需求。

二、 DTS? 遷移注意事項(xiàng)

增量遷移階段采用GTID模式的主從同步方式,在遷移前源端需開啟BINLOG,格式為ROW,且打開GTID,否則只能進(jìn)行全量遷移,不能做“全量+增量”模式遷移。

由于mydumper工具不支持遷移觸發(fā)器trigger,如源端數(shù)據(jù)庫有觸發(fā)器且需要遷移到目標(biāo)端數(shù)據(jù)庫,需在遷移完成后手動(dòng)遷移觸發(fā)器trigger。

“全量遷移”類型任務(wù),在全量備份階段,源端會(huì)出現(xiàn)元數(shù)據(jù)鎖,阻塞DDL語句,因此在此階段源庫無法執(zhí)行DDL語句;同樣的,“全量+增量遷移”類型任務(wù),在源庫導(dǎo)出階段期間,源庫也無法執(zhí)行DDL語句。

MySQL 5.7到MySQL 8.0跨版本“全量+增量遷移”類型任務(wù)時(shí),不支持源庫執(zhí)行語句:grant all privileges on *.* to user@'%' identified by 'password';。

“全量+增量遷移”類型任務(wù)遷移過程中,無法同步源庫的創(chuàng)建用戶、修改用戶權(quán)限操作,所以在遷移過程中應(yīng)避免增刪改用戶權(quán)限。

源端存在的空庫(database下無任何數(shù)據(jù)庫對(duì)象)不會(huì)被遷移。

三、 遷移過程及注意事項(xiàng)

( 一 ) 遷移時(shí)間評(píng)估

根據(jù)遷移的數(shù)據(jù)量和遷移過程中的操作,整個(gè)遷移過程時(shí)間分布如下:

主從復(fù)制遷移步驟概覽

( 二 ) 源庫信息收集

在遷移前需要了解源環(huán)境和目標(biāo)環(huán)境的硬件差異,可以評(píng)估遷移的可行性和風(fēng)險(xiǎn),包括CPU、內(nèi)存、磁盤基礎(chǔ)設(shè)施的配置和利用率,基于硬件信息的收集,可以合理規(guī)劃遷移策略。

硬件信息收集示意

數(shù)據(jù)庫信息收集是確保遷移過程中數(shù)據(jù)一致性的關(guān)鍵。通過收集數(shù)據(jù)庫的版本、數(shù)據(jù)量和配置等信息,可以制定詳細(xì)的數(shù)據(jù)遷移計(jì)劃和驗(yàn)證方案。在遷移過程中,可以通過比較源數(shù)據(jù)庫和目標(biāo)數(shù)據(jù)庫的數(shù)據(jù)差異來及時(shí)發(fā)現(xiàn)并解決問題,確保數(shù)據(jù)的完整性和一致性?；跀?shù)據(jù)庫信息的收集,可以制定詳細(xì)的遷移計(jì)劃,包括遷移的時(shí)間窗口、備份和恢復(fù)策略、遷移驗(yàn)證和回滾計(jì)劃等,減少遷移過程中的不確定性和風(fēng)險(xiǎn),確保遷移的順利進(jìn)行。

數(shù)據(jù)庫信息收集示意

( 三 ) 目標(biāo)數(shù)據(jù)庫配置規(guī)劃

核心業(yè)務(wù)系統(tǒng)數(shù)據(jù)庫在遷移至深信服云計(jì)算平臺(tái)時(shí),可能存在CPU和內(nèi)存配置緊張,或資源過剩的情況,需要對(duì)原服務(wù)器進(jìn)行配置變更評(píng)估。評(píng)估原則如下:

深信服平臺(tái)物理主頻建議要高于原服務(wù)器或者保持持平且不低于2.0GHhz,禁止云平臺(tái)的性能低于原操作系統(tǒng)的主頻。

合理的CPU和內(nèi)存平均利用率在30%-70%之間,業(yè)務(wù)高峰時(shí)也應(yīng)保持在80%以內(nèi),當(dāng)原VMware平臺(tái)使用率超過70%時(shí),考慮在深信服主機(jī)增加配置。

單實(shí)例數(shù)據(jù)庫服務(wù)器配置建議16C-32C,如果32C還不能滿足業(yè)務(wù)需求,建議優(yōu)化數(shù)據(jù)庫,排查慢SQL語句;或更改數(shù)據(jù)庫架構(gòu)為集群架構(gòu),不建議再通過增加服務(wù)器配置來承載業(yè)務(wù)。

集群數(shù)據(jù)庫服務(wù)器建議配置16C-32C,如果32C還不能滿足業(yè)務(wù)需求,建議優(yōu)化數(shù)據(jù)庫,排查慢SQL語句;或?yàn)榧涸黾有碌墓?jié)點(diǎn),以承載更多的業(yè)務(wù)訪問,不建議再通過增加服務(wù)器配置來承載業(yè)務(wù)。

數(shù)據(jù)庫內(nèi)存在遷移上云時(shí)建議增加,不建議降低,隨意降低數(shù)據(jù)庫服務(wù)器內(nèi)存可能會(huì)導(dǎo)致數(shù)據(jù)庫無法啟動(dòng)。配置建議在16G-64G的區(qū)間,具體配置需要通過專業(yè)的DBA進(jìn)行計(jì)算,遷移時(shí)不可隨意更改數(shù)據(jù)庫服務(wù)器內(nèi)存配置。

源端數(shù)據(jù)庫的磁盤使用率不高于70%的情況下,遷移過來后可保持原狀。如果源端磁盤使用率高于70%,在擴(kuò)容時(shí)需考慮到未來3-5年的業(yè)務(wù)增量進(jìn)行測(cè)算。

單實(shí)例數(shù)據(jù)庫創(chuàng)建完成后只能修改數(shù)據(jù)盤/日志盤的大小,不能擴(kuò)容數(shù)量。例如源數(shù)據(jù)庫配置了4塊1T磁盤,后面擴(kuò)盤時(shí)只能擴(kuò)大小,例如擴(kuò)容到4塊2T磁盤。

集群數(shù)據(jù)庫服務(wù),只能增加數(shù)據(jù)盤/日志盤的數(shù)量,不建議擴(kuò)容大小。例如源數(shù)據(jù)庫配置了4塊1T磁盤,后面擴(kuò)盤只能擴(kuò)數(shù)量,例如擴(kuò)容到8塊1T。

如果是P2V遷移的系統(tǒng),磁盤大小配置和原物理的保持一致,數(shù)據(jù)文件和日志文件所在的磁盤為提高IO的吞吐,建議將磁盤進(jìn)行預(yù)分配。

( 四 ) 切換與回退設(shè)計(jì)

在正式執(zhí)行數(shù)據(jù)遷移之前,建議將源庫克隆出測(cè)試庫進(jìn)行一次遷移測(cè)試。這一步驟至關(guān)重要,因?yàn)椴煌奈锢憝h(huán)境可能會(huì)導(dǎo)致遷移所需的時(shí)間出現(xiàn)差異。通過測(cè)試遷移,不僅可以評(píng)估遷移過程中可能遇到的時(shí)間問題,而且可以驗(yàn)證遷移方案的可行性和有效性。此外,遷移測(cè)試還有助于識(shí)別潛在的問題和風(fēng)險(xiǎn),從而在正式遷移之前采取相應(yīng)的預(yù)防措施。

數(shù)據(jù)庫切換前必須確認(rèn)業(yè)務(wù)系統(tǒng)已完全停止對(duì)數(shù)據(jù)庫的訪問和寫入。在進(jìn)行切換時(shí),DMP允許用戶選擇是否在切換過程中自動(dòng)關(guān)閉源數(shù)據(jù)庫。通常情況下,為了確保業(yè)務(wù)順利上線,我們會(huì)在業(yè)務(wù)系統(tǒng)上線前連接源數(shù)據(jù)庫進(jìn)行數(shù)據(jù)驗(yàn)證,此時(shí)無需自動(dòng)關(guān)閉源數(shù)據(jù)庫。然而,如果無法確保源數(shù)據(jù)庫的數(shù)據(jù)寫入操作已完全停止,或者在切換過程中擔(dān)心源數(shù)據(jù)有變化,那么在進(jìn)行切換時(shí)選擇自動(dòng)關(guān)閉源數(shù)據(jù)庫將是一個(gè)更為穩(wěn)妥的措施。

數(shù)據(jù)庫遷移完成后,應(yīng)更新業(yè)務(wù)系統(tǒng)連接地址,以確保通過目標(biāo)數(shù)據(jù)庫的服務(wù)IP進(jìn)行訪問。在網(wǎng)絡(luò)環(huán)境中,如果存在訪問控制策略,應(yīng)在遷移前調(diào)整策略,以避免影響業(yè)務(wù)訪問。如果是白名單模式,應(yīng)允許最底層的全禁止策略;如果是黑名單模式,則應(yīng)在最上層添加允許所有策略。待業(yè)務(wù)系統(tǒng)完全遷移后,再重新啟用相應(yīng)的訪問控制策略。

在數(shù)據(jù)庫成功遷移并經(jīng)過業(yè)務(wù)驗(yàn)證之后,建議立即進(jìn)行全面?zhèn)浞?。這樣,在目標(biāo)數(shù)據(jù)庫遇到無法迅速解決的問題時(shí),可以迅速恢復(fù)到遷移后的狀態(tài)。同時(shí),建議保留源數(shù)據(jù)庫的運(yùn)行狀態(tài)(但不要關(guān)閉服務(wù)器),以便在新平臺(tái)出現(xiàn)問題時(shí),能夠迅速切換回源數(shù)據(jù)庫繼續(xù)提供服務(wù)。

在數(shù)據(jù)庫遷移和切換過程中,必須確保源數(shù)據(jù)庫環(huán)境的完整性不受破壞。如果在切換過程中遇到異常,或者在業(yè)務(wù)驗(yàn)證階段發(fā)現(xiàn)問題,應(yīng)立即聯(lián)系深信服產(chǎn)品線專家和數(shù)據(jù)庫管理員(DBA)尋求支持。在允許的時(shí)間范圍內(nèi),應(yīng)優(yōu)先診斷問題,調(diào)整遷移參數(shù)或系統(tǒng)配置,以迅速恢復(fù)遷移流程。

在數(shù)據(jù)庫遷移過程中,如果遇到無法在停機(jī)窗口期內(nèi)迅速解決的異常問題,應(yīng)立即回退到源數(shù)據(jù)庫環(huán)境。在回退之前,需要分析失敗的原因,并根據(jù)分析結(jié)果重新制定遷移計(jì)劃。在決定回退時(shí),要確保在遷移過程中沒有新的業(yè)務(wù)數(shù)據(jù)寫入到新數(shù)據(jù)庫,以避免在回退過程中丟失最新的業(yè)務(wù)數(shù)據(jù)。

如切換后發(fā)現(xiàn)業(yè)務(wù)有問題,不得不回切至源數(shù)據(jù)庫,可以利用割接后的增量日志,生成SQL文件,與用戶相關(guān)人員溝通后,可以在源端執(zhí)行增量還原。

四、 遷移過程說明

( 一 ) 創(chuàng)建遷移任務(wù)

此處以全量+增量遷移任務(wù),整庫遷移的方式為例,以下是具體的操作步驟:

使用DTS遷移工具新建遷移任務(wù),遷移前請(qǐng)確保源庫已開啟binlog,并開啟GTID,GTID(Global Transaction ID,全局事務(wù)ID),用來強(qiáng)化數(shù)據(jù)庫的主備一致性、故障恢復(fù),以及容錯(cuò)能力。用于取代過去傳統(tǒng)的主從復(fù)制(即:基于binlog和position的復(fù)制)。若遷移任務(wù)為全量遷移情況,則無須開啟此參數(shù)。

( 二 ) 數(shù)據(jù)遷移過程

在確認(rèn)源數(shù)據(jù)庫和目標(biāo)數(shù)據(jù)庫的配置之后,接下來需要為數(shù)據(jù)庫遷移設(shè)置實(shí)例參數(shù)、遷移服務(wù)(DTS-VM,用于執(zhí)行遷移任務(wù)的工具,包括數(shù)據(jù)導(dǎo)出與導(dǎo)入、日志抽取與重放等;不會(huì)占用遷移配額,遷移完成后將自動(dòng)刪除該云主機(jī)并釋放對(duì)應(yīng)的資源)配置。當(dāng)啟動(dòng)DTS工具執(zhí)行遷移任務(wù)時(shí),它將自動(dòng)進(jìn)行一系列預(yù)檢查,包括驗(yàn)證源和目標(biāo)數(shù)據(jù)庫之間的連通性、用戶權(quán)限、數(shù)據(jù)庫架構(gòu)、數(shù)據(jù)庫版本兼容性、字符集、存儲(chǔ)引擎、系統(tǒng)信息、遷移數(shù)據(jù)量等。預(yù)檢查中發(fā)現(xiàn)的“不通過項(xiàng)”將直接影響遷移任務(wù)的執(zhí)行,必須在遷移前解決;而“告警項(xiàng)”則通常不會(huì)妨礙遷移過程,可以在人工審核后選擇忽略,繼續(xù)執(zhí)行遷移任務(wù)。

首先進(jìn)行全量遷移過程,DTS會(huì)完成以下動(dòng)作:源端數(shù)據(jù)庫全量導(dǎo)出、目標(biāo)端數(shù)據(jù)庫全量恢復(fù)。全量遷移過程中對(duì)源庫業(yè)務(wù)不會(huì)產(chǎn)生影響,建議在業(yè)務(wù)低峰期執(zhí)行,或者減少并發(fā)數(shù)并時(shí)刻觀察對(duì)生產(chǎn)業(yè)務(wù)產(chǎn)生的影響。所有DTS操作過程都會(huì)添加時(shí)間戳顯示在前端,運(yùn)維人員可實(shí)時(shí)監(jiān)控整個(gè)遷移過程。

在首次全量備份成功完成后,DTS系統(tǒng)將進(jìn)入持續(xù)性的增量同步階段。增量同步的核心任務(wù)是實(shí)時(shí)進(jìn)行主從同步。增量遷移過程中,DTS會(huì)完成以下動(dòng)作:設(shè)置源&目標(biāo)端主從關(guān)系,重置主庫、設(shè)置GTID、主從同步、檢查主從同步狀態(tài)。在此過程中,目標(biāo)端會(huì)持續(xù)獲取源端binlog日志文件信息,并利用SQL Thread進(jìn)行回放,從而實(shí)現(xiàn)增量同步。這種增量同步操作不會(huì)對(duì)源數(shù)據(jù)庫的業(yè)務(wù)運(yùn)行造成任何影響。

根據(jù)深信服在用戶端的遷移實(shí)踐經(jīng)驗(yàn),使用千兆遷移網(wǎng)絡(luò)時(shí),全量數(shù)據(jù)遷移的理想速率為30MB/s,這使得每小時(shí)大約能夠遷移100GB的數(shù)據(jù)。然而,遷移速率受多種因素影響,包括源數(shù)據(jù)庫的數(shù)據(jù)結(jié)構(gòu)、物理網(wǎng)絡(luò)條件以及帶寬限制。因此,實(shí)際遷移速度需要根據(jù)具體情況進(jìn)行評(píng)估和調(diào)整。

( 三 ) 停庫切換過程

數(shù)據(jù)庫遷移切換過程需要停庫中斷業(yè)務(wù),在確定了停機(jī)時(shí)間后,應(yīng)向各業(yè)務(wù)部門發(fā)布維護(hù)通知,停止業(yè)務(wù)和應(yīng)用對(duì)源數(shù)據(jù)庫的訪問,避免產(chǎn)生數(shù)據(jù)丟失等意外情況產(chǎn)生。同時(shí)需協(xié)調(diào)業(yè)務(wù)人員、運(yùn)維人員、應(yīng)用廠商、深信服廠商等多方工作人員協(xié)助保障遷移切換和業(yè)務(wù)驗(yàn)證工作。

全量遷移任務(wù)待任務(wù)執(zhí)行完成后,即數(shù)據(jù)庫遷移完畢,完成切換,業(yè)務(wù)可訪問新實(shí)例進(jìn)行業(yè)務(wù)驗(yàn)證;全量+增量遷移任務(wù),需手動(dòng)執(zhí)行割接,割接完成后,業(yè)務(wù)訪問新實(shí)例進(jìn)行業(yè)務(wù)驗(yàn)證。

在數(shù)據(jù)庫切換流程完全執(zhí)行完畢后,所有源端數(shù)據(jù)將被成功遷移至目標(biāo)端數(shù)據(jù)庫。此時(shí),可以對(duì)源端和目標(biāo)端數(shù)據(jù)庫進(jìn)行連接,以進(jìn)行數(shù)據(jù)的檢查和校驗(yàn),確保數(shù)據(jù)庫狀態(tài)的一致性。完成數(shù)據(jù)校驗(yàn)后,應(yīng)協(xié)調(diào)業(yè)務(wù)團(tuán)隊(duì)成員進(jìn)行業(yè)務(wù)訪問測(cè)試。這一測(cè)試過程至關(guān)重要,它確保了從業(yè)務(wù)角度來看,系統(tǒng)能夠正常工作,滿足業(yè)務(wù)需求。

五、 附錄

( 一 ) 準(zhǔn)備遷移用戶

建議使用數(shù)據(jù)庫全權(quán)限用戶如root@'%'(和root@'localhost'不是同一個(gè)用戶)進(jìn)行遷移。如果源端不能使用全權(quán)限數(shù)據(jù)庫用戶執(zhí)行遷移,需在源端創(chuàng)建遷移用戶。創(chuàng)建用戶及賦權(quán)語句如下:

注意: 遷移用戶的密碼中特殊字符僅支持:()`~!@#$^&*_-+=|{}[]:<>.?/。

MySQL5.6、5.7、8.0 全量遷移用戶權(quán)限

mysql> create user dtsuser@'%' identified with mysql_native_password by 'dtspassword';

mysql> grant select,event,show view,lock tables,reload on *.* to dtsuser@'%';

MySQL5.6、5.7、8.0 全量+增量遷移用戶權(quán)限

mysql> create user dtsuser@'%' identified with mysql_native_password by 'dtspassword';

mysql> grant select,event,show view,lock tables,replication slave,replication client,reload on *.* to dtsuser@'%';

( 二 ) 在線開 啟 GTID

GTID(Global Transaction ID,全局事務(wù)ID),用來強(qiáng)化數(shù)據(jù)庫的主備一致性、故障恢復(fù),以及容錯(cuò)能力。用于取代過去傳統(tǒng)的主從復(fù)制(即:基于binlog和position的復(fù)制)。

若遷移任務(wù)為全量+增量遷移情況,則必須開啟此參數(shù)。

以下操作主從均需要執(zhí)行:

1. 開啟 GTID 預(yù)檢查

mysql> set @@global.enforce_gtid_consistency=WARN;

開啟此參數(shù)后,需觀察MySQL錯(cuò)誤日志,若有違反GTID規(guī)則的事務(wù)會(huì)有告警,應(yīng)及時(shí)調(diào)整。

設(shè)置告警后,部分操作會(huì)被告警,請(qǐng)注意調(diào)整業(yè)務(wù)或關(guān)閉GTID,例如:

( 1 ) ?執(zhí)行 CREATE TABLE ... SELECT 語句:

(MySQL8.0.21以后對(duì)于支持原子DDL的存儲(chǔ)引擎,例如InnoDB引擎,支持該操作)

例如:

create table t1 select * from sbtest3;

查看錯(cuò)誤日志:

2023-06-19T11:44:05.956128+08:00 82810 [Warning] Statement violates GTID consistency: CREATE TABLE ... SELECT.

修改:

create table t1 like sbtest3;

insert into t1 select * from sbtest3;

( 2 ) ?在事務(wù)中執(zhí)行 CREATE TEMPORARY TABLE or DROP TEMPORARY TABLE 語句:

例如:

begin;

select * from sbtest3 for update;

create temporary table t2(id int);

查看錯(cuò)誤日志:

2023-06-19T11:52:42.254719+08:00 82810 [Warning] Statement violates GTID consistency: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context. ?These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions.

修改:

避免在事務(wù)中執(zhí)行創(chuàng)建或刪除臨時(shí)表。

2. 開啟 GTID 校驗(yàn)

mysql> set @@global.enforce_gtid_consistency=ON;

這一步一旦執(zhí)行,違反GTID的操作都會(huì)被拒絕,比如 create table as select,所以上一步WARN階段確保無違反GTID規(guī)則的事務(wù)。

3. 開啟 GTID_MODE

mysql> set @@global.gtid_mode=OFF_PERMISSIVE;

觀察ongoing_anonymous_transaction_count值:

mysql> show global status like '%ongoing_anonymous_transaction_count%';

確認(rèn)已經(jīng)沒有匿名的事物,建議多觀察一段時(shí)間,如果不為0,強(qiáng)行修改可能會(huì)導(dǎo)致數(shù)據(jù)丟失。

4.GTID_MODE 設(shè)置為 ON_PERMISSIVE

mysql> set @@global.gtid_mode=ON_PERMISSIVE;

5.GTID_MODE 設(shè)置為 ON

mysql> set @@global.gtid_mode=ON;

6. 從庫執(zhí)行(若源端為單機(jī),忽略此步驟)

mysql> stop slave;

mysql> change master to master_auto_position=1;

mysql> start slave;

mysql> show slave status\G

這一步,所有老的relay log都清理掉了,新relay log包含的全是GTID操作Event。

7. 修改配置文件(永久生效)

若未添加至配置文件,則數(shù)據(jù)庫重啟后參數(shù)失效,GTID關(guān)閉。

主從均執(zhí)行

# vim /etc/my.cnf

在mysqld下添加以下內(nèi)容

[mysqld]

gtid_mode=ON

enforce_gtid_consistency=ON

( 三 ) 修改 BINLOG_FORMAT

BINLOG_FORMAT 是 MySQL 中的一個(gè)參數(shù),用于指定二進(jìn)制日志文件的格式 。MySQL的復(fù)制方式與binlog(二進(jìn)制日志文件)格式一一對(duì)應(yīng)。

mysql 復(fù)制主要有三種方式 :

基于SQL語句的復(fù)制(statement-based replication, SBR);

基于行的復(fù)制(row-based replication, RBR);

混合模式復(fù)制(mixed-based replication, MBR)。

對(duì)應(yīng)的,binlog的格式也有三種:STATEMENT,ROW,MIXED。

修改BINLOG_FORMAT的步驟如下:

1. 先在從庫執(zhí)行、再去主庫執(zhí)行

mysql> set global binlog_format=ROW;

2. 修改配置文件(主從都修改)

# vim /etc/my.cnf

在mysqld下添加以下內(nèi)容

[mysqld]

binlog_format=ROW

( 四 ) 手動(dòng)遷移觸發(fā)器 trigger

1. 檢查詢命令默認(rèn)業(yè)務(wù)觸發(fā)器沒有創(chuàng)建在系統(tǒng)數(shù)據(jù)庫中,所以排除系統(tǒng)數(shù)據(jù)庫 sys、mysql、information_schema、performance_schema。

mysql> select TRIGGER_SCHEMA,count(*) as tiggers_cnt from information_schema.`TRIGGERS` where TRIGGER_SCHEMA not in ('sys','mysql','information_schema','performance_schema') group by TRIGGER_SCHEMA;

如上命令執(zhí)行后有結(jié)果,如圖所示,源端業(yè)務(wù)數(shù)據(jù)庫sakila、test分別有6、1個(gè)觸發(fā)器,則需要遷移。

如上命令執(zhí)行后查不到數(shù)據(jù),則表示業(yè)務(wù)數(shù)據(jù)庫中無觸發(fā)器需要遷移。

2. 方法一:(推薦)

1.在目標(biāo)端數(shù)據(jù)庫后臺(tái)執(zhí)行如下命令導(dǎo)出源端觸發(fā)器。注意:在-B參數(shù)后面添加需要導(dǎo)出的業(yè)務(wù)數(shù)據(jù)庫(即上一章節(jié)查詢出來的TRIGGER_SCHEMA)的名字,如有多個(gè)使用空格分隔。

-h:源端數(shù)據(jù)庫ip地址,如“10.5.54.66”。

-P:源端數(shù)據(jù)庫端口號(hào),如“3306”。

-u:源端數(shù)據(jù)庫遷移賬號(hào),如“root”

-p:源端數(shù)據(jù)庫遷移賬號(hào)密碼,如“Admin-123”。

# mysqldump -h10.5.54.66 -P3306 -uroot -pAdmin-123 --single-transaction --set-gtid-purged=OFF --default-character-set=utf8mb4 --add-drop-trigger --no-create-db=true --no-create-info=true --no-data=true -B sakila test > ./tri.sql

注意: 導(dǎo)出源端觸發(fā)器用戶需要有“trigger”權(quán)限。

(2)導(dǎo)入到目標(biāo)端數(shù)據(jù)庫。

# mysql -uroot -pQwer@123 -S/run/sock/mysql.sock < ./tri.sql

(3)檢查觸發(fā)器是否遷移成功

在目標(biāo)端執(zhí)行命令查詢,參考“Part.5 附錄中第4節(jié) 手動(dòng)遷移觸發(fā)器trigger的檢查源端是否存在觸發(fā)器”。

3. 方法二

1.在目標(biāo)端用root用戶登錄RDS主節(jié)點(diǎn),訪問源端數(shù)據(jù)庫導(dǎo)出業(yè)務(wù)數(shù)據(jù)庫觸發(fā)器DDL語句。

# cd

# rm -rf trigdump.sql

# touch trigdump.sql

# mysql -h10.5.54.66 -P3306 -uroot -pAdmin-123 <<'EOF'

tee trigdump.sql

SELECT

CONCAT("DROP TRIGGER IF EXISTS `",

TRIGGER_SCHEMA,

"`.`",

TRIGGER_NAME,

"`;\nDELIMITER ;;\nCREATE TRIGGER `",

TRIGGER_SCHEMA,

"`.`",

TRIGGER_NAME,

"` ",

ACTION_TIMING,

" ",

EVENT_MANIPULATION,

" ON `",

EVENT_OBJECT_SCHEMA,

"`.`",

EVENT_OBJECT_TABLE,

"` FOR EACH ROW\n",

ACTION_STATEMENT,

";;\nDELIMITER ;") AS TRIG

FROM

information_schema.TRIGGERS

WHERE

TRIGGER_SCHEMA IN ('sakila','test')\G

notee

exit

EOF

# sed -i '/^*/d' trigdump.sql

# sed -i 's/TRIG: //' trigdump.sql

# echo "COMMIT;" >> trigdump.sql

(2)導(dǎo)入觸發(fā)器至目標(biāo)端主節(jié)點(diǎn)

# mysql -uroot -p -S/run/sock/mysql.sock < trigdump.sql

(3)檢查觸發(fā)器是否遷移成功

在目標(biāo)端執(zhí)行命令查詢,參考“Part.5 附錄中第4節(jié) 手動(dòng)遷移觸發(fā)器trigger的檢查源端是否存在觸發(fā)器”。