欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

淺談如何保證Mysql主從一致

 更新時間:2022年03月10日 09:33:24   作者:ZHY_ERIC  
這篇文章主要介紹了淺談如何保證Mysql主從一致

        為什么備庫執(zhí)行了 binlog 就可以跟主庫保持一致了呢?今天正式地和你介紹一下它。

        在最開始,MySQL 是以容易學(xué)習(xí)和方便的高可用架構(gòu),被開發(fā)人員青睞的。而它的幾乎所有的高可用架構(gòu),都直接依賴于 binlog。雖然這些高可用架構(gòu)已經(jīng)呈現(xiàn)出越來越復(fù)雜的趨勢,但都是從最基本的一主一備演化過來的。

MySQL 主備的基本原理

圖 1 MySQL 主備切換流程

        在狀態(tài) 1 中,客戶端的讀寫都直接訪問節(jié)點 A,而節(jié)點 B 是 A 的備庫,只是將 A 的更新都同步過來,到本地執(zhí)行。這樣可以保持節(jié)點 B 和 A 的數(shù)據(jù)是相同的。

        當需要切換的時候,就切成狀態(tài) 2。這時候客戶端讀寫訪問的都是節(jié)點 B,而節(jié)點 A 是 B 的備庫。

        在狀態(tài) 1 中,雖然節(jié)點 B 沒有被直接訪問,但是我依然建議你把節(jié)點 B(也就是備庫)設(shè)置成只讀(readonly)模式。這樣做,有以下幾個考慮:

  • 有時候一些運營類的查詢語句會被放到備庫上去查,設(shè)置為只讀可以防止誤操作;
  • 防止切換邏輯有 bug,比如切換過程中出現(xiàn)雙寫,造成主備不一致;
  • 可以用 readonly 狀態(tài),來判斷節(jié)點的角色。

        你可能會問,我把備庫設(shè)置成只讀了,還怎么跟主庫保持同步更新呢?

        這個問題,你不用擔(dān)心。因為 readonly 設(shè)置對超級 (super) 權(quán)限用戶是無效的,而用于同步更新的線程,就擁有超級權(quán)限。

        接下來,我們再看看節(jié)點 A 到 B 這條線的內(nèi)部流程是什么樣的。圖 2 中畫出的就是一個 update 語句在節(jié)點 A 執(zhí)行,然后同步到節(jié)點 B 的完整流程圖。

圖 2 主備流程圖

       圖 2 中,包含了在上一篇文章中講到的 binlog 和 redo log 的寫入機制相關(guān)的內(nèi)容,可以看到:主庫接收到客戶端的更新請求后,執(zhí)行內(nèi)部事務(wù)的更新邏輯,同時寫 binlog。

        備庫 B 跟主庫 A 之間維持了一個長連接。主庫 A 內(nèi)部有一個線程,專門用于服務(wù)備庫 B 的這個長連接。一個事務(wù)日志同步的完整過程是這樣的:

  • 在備庫 B 上通過 change master 命令,設(shè)置主庫 A 的 IP、端口、用戶名、密碼,以及要從哪個位置開始請求 binlog,這個位置包含文件名和日志偏移量。
  • 在備庫 B 上執(zhí)行 start slave 命令,這時候備庫會啟動兩個線程,就是圖中的 io_thread 和 sql_thread。其中 io_thread 負責(zé)與主庫建立連接。
  • 主庫 A 校驗完用戶名、密碼后,開始按照備庫 B 傳過來的位置,從本地讀取 binlog,發(fā)給 B。
  • 備庫 B 拿到 binlog 后,寫到本地文件,稱為中轉(zhuǎn)日志(relay log)。
  • sql_thread 讀取中轉(zhuǎn)日志,解析出日志里的命令,并執(zhí)行。

binlog 的三種格式對比

         binlog 有兩種格式,一種是 statement,一種是 row??赡苣阍谄渌Y料上還會看到有第三種格式,叫作 mixed,其實它就是前兩種格式的混合。

        為了便于描述 binlog 的這三種格式間的區(qū)別,創(chuàng)建了一個表,并初始化幾行數(shù)據(jù)。

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `t_modified`(`t_modified`)
) ENGINE=InnoDB;
 
insert into t values(1,1,'2018-11-13');
insert into t values(2,2,'2018-11-12');
insert into t values(3,3,'2018-11-11');
insert into t values(4,4,'2018-11-10');
insert into t values(5,5,'2018-11-09');

         如果要在表中刪除一行數(shù)據(jù)的話,我們來看看這個 delete 語句的 binlog 是怎么記錄的。

        下面這個語句包含注釋,如果你用 MySQL 客戶端來做這個實驗的話,要記得加 -c 參數(shù),否則客戶端會自動去掉注釋。

mysql> delete from t /*comment*/ where a>=4 and t_modified<='2018-11-10' limit 1;

         當 binlog_format=statement 時,binlog 里面記錄的就是 SQL 語句的原文。你可以用

mysql> show binlog events in 'master.000001';

         命令看 binlog 中的內(nèi)容。

圖 3 statement 格式 binlog 示例

  • 第一行 SET @@SESSION.GTID_NEXT='ANONYMOUS’你可以先忽略,后面文章會在介紹主備切換的時候再提到;
  • 第二行是一個 BEGIN,跟第四行的 commit 對應(yīng),表示中間是一個事務(wù);
  • 第三行就是真實執(zhí)行的語句了。可以看到,在真實執(zhí)行的 delete 命令之前,還有一個“use ‘test’”命令。這條命令不是我們主動執(zhí)行的,而是 MySQL 根據(jù)當前要操作的表所在的數(shù)據(jù)庫,自行添加的。這樣做可以保證日志傳到備庫去執(zhí)行的時候,不論當前的工作線程在哪個庫里,都能夠正確地更新到 test 庫的表 t。
  • use 'test’命令之后的 delete 語句,就是我們輸入的 SQL 原文了??梢钥吹?,binlog“忠實”地記錄了 SQL 命令,甚至連注釋也一并記錄了。
  • 最后一行是一個 COMMIT。你可以看到里面寫著 xid=61。你還記得這個 XID 是做什么用的嗎?

        為了說明 statement 和 row 格式的區(qū)別,我們來看一下這條 delete 命令的執(zhí)行效果圖:

圖 4 delete 執(zhí)行 warnings

         運行這條 delete 命令產(chǎn)生了一個 warning,原因是當前 binlog 設(shè)置的是 statement 格式,并且語句中有 limit,所以這個命令可能是 unsafe 的。

        這是因為 delete 帶 limit,很可能會出現(xiàn)主備數(shù)據(jù)不一致的情況。比如上面這個例子:

  • 如果 delete 語句使用的是索引 a,那么會根據(jù)索引 a 找到第一個滿足條件的行,也就是說刪除的是 a=4 這一行;
  • 但如果使用的是索引 t_modified,那么刪除的就是 t_modified='2018-11-09’也就是 a=5 這一行。

        由于 statement 格式下,記錄到 binlog 里的是語句原文,因此可能會出現(xiàn)這樣一種情況:在主庫執(zhí)行這條 SQL 語句的時候,用的是索引 a;而在備庫執(zhí)行這條 SQL 語句的時候,卻使用了索引 t_modified。因此,MySQL 認為這樣寫是有風(fēng)險的。

        那么,如果我把 binlog 的格式改為 binlog_format=‘row’, 是不是就沒有這個問題了呢?

圖 5 row 格式 binlog 示例 

         與 statement 格式的 binlog 相比,前后的 BEGIN 和 COMMIT 是一樣的。但是,row 格式的 binlog 里沒有了 SQL 語句的原文,而是替換成了兩個 event:Table_map 和 Delete_rows。

  • Table_map event,用于說明接下來要操作的表是 test 庫的表 t;
  • Delete_rows event,用于定義刪除的行為

        其實,我們通過圖 5 是看不到詳細信息的,還需要借助 mysqlbinlog 工具,用下面這個命令解析和查看 binlog 中的內(nèi)容。因為圖 5 中的信息顯示,這個事務(wù)的 binlog 是從 8900 這個位置開始的,所以可以用 start-position 參數(shù)來指定從這個位置的日志開始解析。

mysqlbinlog -vv data/master.000001 --start-position=8900;

圖 6 row 格式 binlog 示例的詳細信息 

        從這個圖中,我們可以看到以下幾個信息:

  • server id 1,表示這個事務(wù)是在 server_id=1 的這個庫上執(zhí)行的。
  • 每個 event 都有 CRC32 的值,這是因為我把參數(shù) binlog_checksum 設(shè)置成了 CRC32。
  • Table_map event 跟在圖 5 中看到的相同,顯示了接下來要打開的表,map 到數(shù)字 226。現(xiàn)在我們這條 SQL 語句只操作了一張表,如果要操作多張表呢?每個表都有一個對應(yīng)的 Table_map event、都會 map 到一個單獨的數(shù)字,用于區(qū)分對不同表的操作。
  • 我們在 mysqlbinlog 的命令中,使用了 -vv 參數(shù)是為了把內(nèi)容都解析出來,所以從結(jié)果里面可以看到各個字段的值(比如,@1=4、 @2=4 這些值)。
  • binlog_row_image 的默認配置是 FULL,因此 Delete_event 里面,包含了刪掉的行的所有字段的值。如果把 binlog_row_image 設(shè)置為 MINIMAL,則只會記錄必要的信息,在這個例子里,就是只會記錄 id=4 這個信息。
  • 最后的 Xid event,用于表示事務(wù)被正確地提交了。

        當 binlog_format 使用 row 格式的時候,binlog 里面記錄了真實刪除行的主鍵 id,這樣 binlog 傳到備庫去的時候,就肯定會刪除 id=4 的行,不會有主備刪除不同行的問題。

為什么會有 mixed 格式的 binlog?

        基于上面的信息,我們來討論一個問題:為什么會有 mixed 這種 binlog 格式的存在場景?推論過程是這樣的:

  • 因為有些 statement 格式的 binlog 可能會導(dǎo)致主備不一致,所以要使用 row 格式。
  • 但 row 格式的缺點是,很占空間。比如你用一個 delete 語句刪掉 10 萬行數(shù)據(jù),用 statement 的話就是一個 SQL 語句被記錄到 binlog 中,占用幾十個字節(jié)的空間。但如果用 row 格式的 binlog,就要把這 10 萬條記錄都寫到 binlog 中。這樣做,不僅會占用更大的空間,同時寫 binlog 也要耗費 IO 資源,影響執(zhí)行速度。
  • 所以,MySQL 就取了個折中方案,也就是有了 mixed 格式的 binlog。mixed 格式的意思是,MySQL 自己會判斷這條 SQL 語句是否可能引起主備不一致,如果有可能,就用 row 格式,否則就用 statement 格式。

        也就是說,mixed 格式可以利用 statment 格式的優(yōu)點,同時又避免了數(shù)據(jù)不一致的風(fēng)險。

        因此,如果你的線上 MySQL 設(shè)置的 binlog 格式是 statement 的話,那基本上就可以認為這是一個不合理的設(shè)置。你至少應(yīng)該把 binlog 的格式設(shè)置為 mixed。

        現(xiàn)在越來越多的場景要求把 MySQL 的 binlog 格式設(shè)置成 row。這么做的理由有很多,我來給你舉一個可以直接看出來的好處:恢復(fù)數(shù)據(jù)。

        接下來,我們就分別從 delete、insert 和 update 這三種 SQL 語句的角度,來看看數(shù)據(jù)恢復(fù)的問題。

        通過圖 6 你可以看出來,即使我執(zhí)行的是 delete 語句,row 格式的 binlog 也會把被刪掉的行的整行信息保存起來。所以,如果你在執(zhí)行完一條 delete 語句以后,發(fā)現(xiàn)刪錯數(shù)據(jù)了,可以直接把 binlog 中記錄的 delete 語句轉(zhuǎn)成 insert,把被錯刪的數(shù)據(jù)插入回去就可以恢復(fù)了。

        如果你是執(zhí)行錯了 insert 語句呢?那就更直接了。row 格式下,insert 語句的 binlog 里會記錄所有的字段信息,這些信息可以用來精確定位剛剛被插入的那一行。這時,你直接把 insert 語句轉(zhuǎn)成 delete 語句,刪除掉這被誤插入的一行數(shù)據(jù)就可以了。

        如果執(zhí)行的是 update 語句的話,binlog 里面會記錄修改前整行的數(shù)據(jù)和修改后的整行數(shù)據(jù)。所以,如果你誤執(zhí)行了 update 語句的話,只需要把這個 event 前后的兩行信息對調(diào)一下,再去數(shù)據(jù)庫里面執(zhí)行,就能恢復(fù)這個更新操作了。

        其實,由 delete、insert 或者 update 語句導(dǎo)致的數(shù)據(jù)操作錯誤,需要恢復(fù)到操作之前狀態(tài)的情況,也時有發(fā)生。MariaDB 的Flashback工具就是基于上面介紹的原理來回滾數(shù)據(jù)的。

        雖然 mixed 格式的 binlog 現(xiàn)在已經(jīng)用得不多了,但這里我還是要再借用一下 mixed 格式來說明一個問題,來看一下這條 SQL 語句:

mysql> insert into t values(10,10, now());

         如果我們把 binlog 格式設(shè)置為 mixed,你覺得 MySQL 會把它記錄為 row 格式還是 statement 格式呢?

        先不要著急說結(jié)果,我們一起來看一下這條語句執(zhí)行的效果。

圖 7 mixed 格式和 now() 

        可以看到,MySQL 用的居然是 statement 格式。你一定會奇怪,如果這個 binlog 過了 1 分鐘才傳給備庫的話,那主備的數(shù)據(jù)不就不一致了嗎?

        接下來,我們再用 mysqlbinlog 工具來看看:

圖 8 TIMESTAMP 命令 

        從圖中的結(jié)果可以看到,原來 binlog 在記錄 event 的時候,多記了一條命令:SET TIMESTAMP=1546103491。它用 SET TIMESTAMP 命令約定了接下來的 now() 函數(shù)的返回時間。

        因此,不論這個 binlog 是 1 分鐘之后被備庫執(zhí)行,還是 3 天后用來恢復(fù)這個庫的備份,這個 insert 語句插入的行,值都是固定的。也就是說,通過這條 SET TIMESTAMP 命令,MySQL 就確保了主備數(shù)據(jù)的一致性。我之前看過有人在重放 binlog 數(shù)據(jù)的

        之前看過有人在重放 binlog 數(shù)據(jù)的時候,是這么做的:用 mysqlbinlog 解析出日志,然后把里面的 statement 語句直接拷貝出來執(zhí)行。你現(xiàn)在知道了,這個方法是有風(fēng)險的。因為有些語句的執(zhí)行結(jié)果是依賴于上下文命令的,直接執(zhí)行的結(jié)果很可能是錯誤的。

        所以,用 binlog 來恢復(fù)數(shù)據(jù)的標準做法是,用 mysqlbinlog 工具解析出來,然后把解析結(jié)果整個發(fā)給 MySQL 執(zhí)行。類似下面的命令

mysqlbinlog master.000001 --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;

 循環(huán)復(fù)制問題

        我們可以認為正常情況下主備的數(shù)據(jù)是一致的。也就是說,圖 1 中 A、B 兩個節(jié)點的內(nèi)容是一致的。其實,圖 1 中的是 M-S 結(jié)構(gòu),但實際生產(chǎn)上使用比較多的是雙 M 結(jié)構(gòu),也就是圖 9 所示的主備切換流程。

圖 9 MySQL 主備切換流程 -- 雙 M 結(jié)構(gòu)

        對比圖 9 和圖 1,你可以發(fā)現(xiàn),雙 M 結(jié)構(gòu)和 M-S 結(jié)構(gòu),其實區(qū)別只是多了一條線,即:節(jié)點 A 和 B 之間總是互為主備關(guān)系。這樣在切換的時候就不用再修改主備關(guān)系。

        但是,雙 M 結(jié)構(gòu)還有一個問題需要解決。

        業(yè)務(wù)邏輯在節(jié)點 A 上更新了一條語句,然后再把生成的 binlog 發(fā)給節(jié)點 B,節(jié)點 B 執(zhí)行完這條更新語句后也會生成 binlog。(我建議你把參數(shù) log_slave_updates 設(shè)置為 on,表示備庫執(zhí)行 relay log 后生成 binlog)

        那么,如果節(jié)點 A 同時是節(jié)點 B 的備庫,相當于又把節(jié)點 B 新生成的 binlog 拿過來執(zhí)行了一次,然后節(jié)點 A 和 B 間,會不斷地循環(huán)執(zhí)行這個更新語句,也就是循環(huán)復(fù)制了。這個要怎么解決呢?

        從上面的圖 6 中可以看到,MySQL 在 binlog 中記錄了這個命令第一次執(zhí)行時所在實例的 server id。因此,我們可以用下面的邏輯,來解決兩個節(jié)點間的循環(huán)復(fù)制的問題:

  • 規(guī)定兩個庫的 server id 必須不同,如果相同,則它們之間不能設(shè)定為主備關(guān)系;
  • 一個備庫接到 binlog 并在重放的過程中,生成與原 binlog 的 server id 相同的新的 binlog;
  • 每個庫在收到從自己的主庫發(fā)過來的日志后,先判斷 server id,如果跟自己的相同,表示這個日志是自己生成的,就直接丟棄這個日志。

        按照這個邏輯,如果我們設(shè)置了雙 M 結(jié)構(gòu),日志的執(zhí)行流就會變成這樣:

  • 從節(jié)點 A 更新的事務(wù),binlog 里面記的都是 A 的 server id;
  • 傳到節(jié)點 B 執(zhí)行一次以后,節(jié)點 B 生成的 binlog 的 server id 也是 A 的 server id;
  • 再傳回給節(jié)點 A,A 判斷到這個 server id 與自己的相同,就不會再處理這個日志。所以,死循環(huán)在這里就斷掉了。

小結(jié)

        binlog 在 MySQL 的各種高可用方案上扮演了重要角色。今天介紹的可以說是所有 MySQL 高可用方案的基礎(chǔ)。在這之上演化出了諸如多節(jié)點、半同步、MySQL group replication 等相對復(fù)雜的方案。

思考題: 說到循環(huán)復(fù)制問題的時候,我們說 MySQL 通過判斷 server id 的方式,斷掉死循環(huán)。但是,這個機制其實并不完備,在某些場景下,還是有可能出現(xiàn)死循環(huán)?又應(yīng)該怎么解決呢?

        答案:一種場景是,在一個主庫更新事務(wù)后,用命令 set global server_id=x 修改了 server_id。等日志再傳回來的時候,發(fā)現(xiàn) server_id 跟自己的 server_id 不同,就只能執(zhí)行了。

        另一種場景是,有三個節(jié)點的時候,如圖 7 所示,trx1 是在節(jié)點 B 執(zhí)行的,因此 binlog 上的 server_id 就是 B,binlog 傳給節(jié)點 A,然后 A 和 A’搭建了雙 M 結(jié)構(gòu),就會出現(xiàn)循環(huán)復(fù)制。

到此這篇關(guān)于淺談如何保證Mysql主從一致的文章就介紹到這了,更多相關(guān)Mysql 主從一致內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!

相關(guān)文章

  • 有關(guān)mysql中sql的執(zhí)行順序的小問題

    有關(guān)mysql中sql的執(zhí)行順序的小問題

    在MySQL中我們可能會遇到一些關(guān)于執(zhí)行順序的問題,下面小編就來帶大家了解一下原因以及如何解決
    2019-05-05
  • mysql查詢結(jié)果命令行方式導(dǎo)出/輸出/寫入到文件的3種方法舉例

    mysql查詢結(jié)果命令行方式導(dǎo)出/輸出/寫入到文件的3種方法舉例

    這篇文章主要給大家介紹了關(guān)于mysql查詢結(jié)果命令行方式導(dǎo)出/輸出/寫入到文件的3種方法, 在使用MySQL進行數(shù)據(jù)庫操作的過程中,我們經(jīng)常需要將查詢結(jié)果導(dǎo)出到文件中以備后續(xù)分析和處理,需要的朋友可以參考下
    2023-08-08
  • MySql數(shù)據(jù)庫基礎(chǔ)之子查詢詳解

    MySql數(shù)據(jù)庫基礎(chǔ)之子查詢詳解

    所謂子查詢是指在一個查詢中嵌套了其他的若干查詢,即在一個SELECT查詢語句的WHERE或FROM子句中包含另一個SELECT查詢語句,下面這篇文章主要給大家介紹了關(guān)于MySQL子查詢的相關(guān)資料,需要的朋友可以參考下
    2022-09-09
  • 詳解Mysql中日期比較大小的方法

    詳解Mysql中日期比較大小的方法

    這篇文章主要介紹了Mysql中日期比較大小的方法,需要的朋友可以參考下
    2018-04-04
  • mysql視圖之管理視圖實例詳解【增刪改查操作】

    mysql視圖之管理視圖實例詳解【增刪改查操作】

    這篇文章主要介紹了mysql視圖之管理視圖,結(jié)合實例形式詳細分析了mysql視圖增刪改查操作具體實現(xiàn)技巧與相關(guān)操作注意事項,需要的朋友可以參考下
    2019-12-12
  • 手把手教你用SQL獲取年、月、周幾、日、時

    手把手教你用SQL獲取年、月、周幾、日、時

    時間處理是我們?nèi)粘i_發(fā)中經(jīng)常遇到的需求,下面這篇文章主要給大家介紹了關(guān)于如何用SQL獲取年、月、周幾、日、時的相關(guān)資料,文中通過圖文介紹的非常詳細,需要的朋友可以參考下
    2022-12-12
  • 詳解mysql權(quán)限和索引

    詳解mysql權(quán)限和索引

    本篇文章給大家詳細分析了mysql的權(quán)限和索引相關(guān)知識要點,有這方面需要的朋友參考學(xué)習(xí)下。
    2018-01-01
  • MySQL8.0數(shù)據(jù)庫開窗函數(shù)圖文詳解

    MySQL8.0數(shù)據(jù)庫開窗函數(shù)圖文詳解

    開窗函數(shù)為將要被操作的行的集合定義一個窗口,它對一組值進行操作,不需要使用GROUP BY子句對數(shù)據(jù)進行分組,能夠在同一行中同時返回基礎(chǔ)行的列和聚合列,這篇文章主要給大家介紹了關(guān)于MySQL8.0數(shù)據(jù)庫開窗函數(shù)的相關(guān)資料,需要的朋友可以參考下
    2023-06-06
  • MySQL系列之七 MySQL存儲引擎

    MySQL系列之七 MySQL存儲引擎

    存儲引擎是數(shù)據(jù)庫的核心,對于mysql來說,存儲引擎是以插件的形式運行的。雖然mysql支持種類繁多的存儲引擎,但是常用的就那么幾種。這篇文章主要給大家介紹MySQL存儲引擎的相關(guān)知識,一起看看吧
    2021-07-07
  • Mysql中find_in_set()函數(shù)用法詳解以及使用場景

    Mysql中find_in_set()函數(shù)用法詳解以及使用場景

    前幾天在sql查詢的時候,想要判斷數(shù)據(jù)庫中表的某一列中的值是否在List集合中,接觸到了find_in_set的使用,用起來方便快捷,下面這篇文章主要給大家介紹了關(guān)于Mysql中find_in_set()函數(shù)用法詳解以及使用場景的相關(guān)資料,需要的朋友可以參考下
    2023-03-03

最新評論