《一張思維導(dǎo)圖縱觀MySQL數(shù)據(jù)安全體系》要點：
本文介紹了一張思維導(dǎo)圖縱觀MySQL數(shù)據(jù)安全體系，希望對您有用。如果有疑問，可以聯(lián)系我們。

作者介紹 ：
楊奇龍,前阿里數(shù)據(jù)庫團隊資深DBA,主要負責(zé)淘寶業(yè)務(wù)線,經(jīng)歷多次雙十一,有海量業(yè)務(wù)訪問DB架構(gòu)設(shè)計經(jīng)驗.目前就職于有贊科技,負責(zé)數(shù)據(jù)庫運維工作,熟悉MySQL性能優(yōu)化、故障診斷、性能壓測.

簡介

和團隊內(nèi)部的同事一起溝通,討論了MySQL數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)安全性問題,主要針對MySQL丟數(shù)據(jù) 、主從不一致的場景 ,還有業(yè)務(wù)層面使用不得當(dāng)導(dǎo)致主備庫數(shù)據(jù)結(jié)構(gòu)不一樣的情況,本文是基于以上的討論和總結(jié)做的思維導(dǎo)圖.

思維導(dǎo)圖

內(nèi)容展示

OS

• BBU：數(shù)據(jù)庫服務(wù)器要配置BBU,BBU在電源供應(yīng)出現(xiàn)問題的時候,為RAID控制器緩存提供電源.當(dāng)電源斷電時,BBU電力可以使控制器內(nèi)緩存中的數(shù)據(jù)可以保存一定時間(根據(jù)BBU的型號而決定).用戶只需要在BBU電力耗盡之前恢復(fù)正常供電,緩存中的數(shù)據(jù)即可被完整的寫回RAID中,避免斷電導(dǎo)致數(shù)據(jù)丟失
• 防止OS異常斷電導(dǎo)致數(shù)據(jù)無法正常落盤
• 磁盤禁用cache,MySQL的 O_DIRECT 方式可以跳過pagecache寫數(shù)據(jù)

單機

(1)redo log

innodb_flush_log_at_timeout

< 5.6.6： 每隔一秒將redo log buffer中的數(shù)據(jù)刷新到磁盤

>= 5.6.6：每隔innodb_flush_log_at_timeout秒將數(shù)據(jù)刷新到磁盤中去

(2)binlog

sync_binlog? =1

(3)innodb buffer data

不同的flush mathod刷數(shù)據(jù)的圖形展示.圖片來自hatemysql.com.

(4)InnoDB 落盤

MySQL數(shù)據(jù)落盤的路徑,圖片來自李春hatemysql.com.

主從不一致

• 主庫insert之后再回滾 ,主備庫自增主鍵不一致
• 使用replace into操作,導(dǎo)致主備庫自增主鍵不一致
• set session sql_log_bin=0

業(yè)務(wù)架構(gòu)

常見的雙寫

“丟”數(shù)據(jù)的場景

(1)slave_skip_counter 不合理

slave_skip_counter =1
slave_skip_counter >1

(2)DB Crash,OS正常

innodb_flush_log_at_trx_commit=0
事務(wù)提交時,不刷新緩存,系統(tǒng)刷新的頻率是1s,故會丟失1s的數(shù)據(jù).

innodb_flush_log_at_trx_commit=1
事務(wù)提交時,會刷新到磁盤,保證事務(wù)落盤,故不丟數(shù)據(jù).

innodb_flush_log_at_trx_commit=2
事務(wù)提交時,刷新到os cache,系統(tǒng)沒有crash,數(shù)據(jù)無丟失.

(3)DB正常,OS Crash

帶有 BBU

innodb_flush_log_at_trx_commit=0
事務(wù)提交時,不刷新緩存,系統(tǒng)刷新的頻率是1s,故會丟失1s的數(shù)據(jù).

innodb_flush_log_at_trx_commit=1
事務(wù)提交時,會刷新到磁盤,保證事務(wù)落盤,故不丟數(shù)據(jù).

innodb_flush_log_at_trx_commit=2
事務(wù)提交時,刷新到os cache,系統(tǒng)沒有crash,數(shù)據(jù)無丟失.

(4)slave非實時寫redo和binlog丟失數(shù)據(jù)

在slave機器上會存在三個文件來保證事件的正確重放：relay log、 relay log info、 master info.

(5)異步模式

• 事務(wù)T1寫入binlog buffer；
• dumper線程通知slave有新的事務(wù)T1；
• binlog buffer進行checkpoint；
• slave因為網(wǎng)絡(luò)不穩(wěn)定,一直沒有收到t1；master掛掉,slave提升為新的master,t1丟失.

(6)semi sysnc

after_commit

比如主庫操作update t1 set val=1 where id=10將val從5修改為1 .

1. 會話session1在主庫提交update t1 set val=1 where id=10 ；commit；
2. 主庫根據(jù)二階段提交將數(shù)據(jù)持久化到innodb和提交日志binlog；
3. 同步日志到slave ,并等待slave 返回ack信息,等待的實際時間以 rpl_semi_sync_master_timeout 為準,超過該設(shè)置時間則超時,主庫返回給客戶端成功寫入信息.
4. 接收到來自slave的ack信息,返回成功給OK客戶端.

分析：

• 第四步之前,master還未收到slave的ack信息,此時由于事務(wù)已經(jīng)提交,除了session1,其他會話是可以看到 val=1.
• 主庫服務(wù)器down或者主庫實例crash,此時發(fā)生HA切換.
• 主庫未接收到slave的ack信息,slave接收到日志并落盤,應(yīng)用binlog更新.t1.val=1,此時業(yè)務(wù)切換到slave上能獲取到一致的數(shù)據(jù).
• 如果在slave還未接收到binlog并且主庫掛了,因為主庫已經(jīng)提交,此時主庫t1.val是1而從庫t1.val是5,主備不一致.

  after_sync

  比如主庫操作update t1 set val=1 where id=10將val從5修改為1.

  1. 會話session1在主庫提交 :update t1 set val=1 where id=10；commit；
  2. 主庫將事務(wù)寫入binlog.
  3. 將binlog同步給slave,不提交.
  4. 等待slave返回ack信息,等待的實際時間以rpl_semi_sync_master_timeout為準,如果超時master改為異步模式.
  5. 接收到來自slave的ack信息,主庫進行提交并且返回成功給OK客戶端.

  分析：

  • 如果在第3步等待slave ack的過程中,主庫發(fā)生crash(此時t1.val=5),HA 切換到slave,應(yīng)用查詢slave .如果slave接收到binlog并發(fā)送ack給master,則t1.val=1.
  • 如果slave響應(yīng)主庫,但是主庫crash ,此時因為主庫還沒提交t1.val=1, slave t1.val=5,但是主庫啟動恢復(fù)之后t1.val會變成5,主備還是一致的.
  • 如果slave未接收到事務(wù)和響應(yīng)主庫,此時t1.val=5,無論哪種狀態(tài),對于所有客戶端數(shù)據(jù)庫都是一致,事務(wù)都沒有丟失.

    知識點：兩階段提交

    第一階段是先prepare、再同步寫redo log,第二階段同步寫binlog、再commit,如果在寫入commit標(biāo)志時崩潰,則恢復(fù)時,會重新對commit標(biāo)志進行寫入.

    HA切換

    (6)主從

    binlog_format

    ROW(最安全)
    MIXED(不推薦)
    STATEMENT(不推薦)

    sync_binlog

    =0：由os系統(tǒng)的刷新機制來控制,刷新數(shù)據(jù)到磁盤的頻率
    =1：每次commit刷新到磁盤
    >1：每N次提交刷新到磁盤

    innodb_support_xa

    版本要打開,保證binlog提交的順序,否則亂序的binlog在恢復(fù)或者slave應(yīng)用的時候會有問題,及以后廢棄,始終支持兩階段提交.

    crash safe

    crash-safe就是將relay-info.log的信息保存在InnoDB的事務(wù)表中,這時執(zhí)行relay log中的事務(wù)和寫relay info在一個事務(wù)中,就能得到原子性保證.從而避免已執(zhí)行的binlog位點和寫入relay log info的位點信息不一致的情況發(fā)生.

    IO thread

    master-info-repository=TABLE
    sync_master_info=N：每N個event刷新一次表

    SQL thread

    relay-log-info-repository=TABLE
    sync_relay_info=N：每N個event刷新一次表

    relay-log-recovery

    當(dāng)slave從庫宕機后,假如relay-log損壞了,導(dǎo)致一部分中繼日志沒有處理,則自動放棄所有未執(zhí)行的relay-log,并且重新從master上獲取日志,這樣就保證了relay-log的完整性.

    relay_log_info_repository = TABLE
    relay_log_recovery = 1

    http://mysqlserverteam.com/relay-log-recovery-when-sql-threads-position-is-unavailable/

    semi_sync

    • after commit：master把每一個事務(wù)寫到二進制日志并保存到磁盤上,并且提交(commit)事務(wù),再把事務(wù)發(fā)送給從庫,開始等待slave的應(yīng)答.響應(yīng)后master返回結(jié)果給客戶端,客戶端才可繼續(xù).
    • after sync：master把每一個事務(wù)寫到二進制日志并保存磁盤上,并且把事務(wù)發(fā)送給從庫,開始等待slave的應(yīng)答.確認slave響應(yīng)后,再提交(commit)事務(wù)到存儲引擎,并返回結(jié)果給客戶端,客戶端才可繼續(xù).

    GTID

    相比位點復(fù)制,能減少不一致的概率

    參考資料

    • MySQL數(shù)據(jù)丟失討論http://hatemysql.com/?p=395
    • 細看InnoDB數(shù)據(jù)落盤http://hatemysql.com/?p=503
    • MySQL5.7 深度解析：Loss-Less半同步復(fù)制技術(shù)
    • MySQL 5.7 Replication相關(guān)新功能說明

    原文來自微信公眾號：DBAplus社群

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