215_mysql_復(fù)制技術(shù)_GTID

一 復(fù)制的概述

1 復(fù)制： 將來自Mysql server的數(shù)據(jù)變更，通過其邏輯的二進(jìn)制日志，傳輸?shù)狡渌囊粋€(gè)或多個(gè)的Mysql server（slave/從庫）中，slave通過回放這些邏輯的二進(jìn)制日志來完成數(shù)據(jù)庫的同步

特點(diǎn)：

復(fù)制是異步的(主庫傳binlog到從庫后,并不關(guān)心從庫是否收到), 從庫是否收到也不影響主庫的讀寫

安全性無法得到100%保證，如果安全性訴求可以考慮半同步復(fù)制

2 數(shù)據(jù)同步的方法

傳統(tǒng)復(fù)制；基于File & position

GTID復(fù)制：利用GTID 尋找相對(duì)應(yīng)的binlog日志記錄，不關(guān)心其具體的position

3 數(shù)據(jù)同步類型

3.1 異步復(fù)制：一主多從庫

3.2 半同步復(fù)制： 主庫會(huì)話再提交事務(wù)之前，會(huì)等至少一個(gè)從庫返回收到binglog的ACK消息（確認(rèn)收到,并將事務(wù)的事件記錄到從庫的relay log中）

3.3 延遲復(fù)制： 避免邏輯損壞

3.4 同步復(fù)制：保證寫操作完全同步到其它數(shù)據(jù)節(jié)點(diǎn)，而不僅僅是binlog的接收(NDB cluster, Percona XtraDB cluster PXC, MySQL group Replication MGR)

4 復(fù)制格式

SBR (statement based replication) 記錄SQL語句的原始文本, 日志量較小，容易出現(xiàn)主備異常

PBR (row based replication) 記錄實(shí)際發(fā)生的SQL語句，日志量較大, 可以保證主從庫數(shù)據(jù)的一致性

Mix based replication? mysql自己判斷,不影響使用SBR, 影響一致性使用 PBR

二 復(fù)制基本原理

1 概述：

基于binlog(變更操作記錄日志 DDL DML 等) ，利用這些日志在從庫進(jìn)行回放,

2 復(fù)制小細(xì)節(jié)

2.1 主從之間復(fù)制，從庫攜帶所需的二進(jìn)制信息向主庫發(fā)起請(qǐng)求（主庫不主動(dòng)，主庫也不知道從庫要啥）

2.2 建立連接后，新增binlog 主庫主動(dòng)發(fā)送給從庫（主庫binlog dump發(fā)binlog從庫IO線程）從庫不知道主庫啥時(shí)候有數(shù)據(jù)變更

2.3 每個(gè)從庫的復(fù)制都是獨(dú)立運(yùn)行的(主庫binlog dump, 從庫IO/SQL線程)

3 復(fù)制過程

3.1 新建連接 從庫主動(dòng)獲取binlog

3.2 非新建連接, 數(shù)據(jù)變更-binlog --- binlog dump線程 –從庫IO 線程（主庫 show processlist 可以看到binlog dump線程）

3.3 binlog dump線程讀取binlog中的每個(gè)事件時(shí),會(huì)獲取binlog日志上的鎖，讀完釋放（不關(guān)心binglog是否發(fā)給了從庫）

3.4 從庫IO線程接收 主庫binlog-dump線程發(fā)來的binlog, 并將其寫入relay-log中

3.5? 從庫SQL線程讀取 relay-log，按讀取順序進(jìn)行回放寫入從庫

三 復(fù)制格式（binlog_format）

1 statement

優(yōu)點(diǎn)：日志量少，減少存儲(chǔ)空間, 包括數(shù)據(jù)變更語句，方便進(jìn)行數(shù)據(jù)庫審計(jì)

缺點(diǎn)：

執(zhí)行結(jié)果不確定的DML會(huì)導(dǎo)致主從異常 ;

UDF用戶自定義函數(shù)/存儲(chǔ)過程結(jié)果不確定;

DML語句中 不建議使用order by 的limit子句（主從執(zhí)行排序結(jié)果可能不同，結(jié)果不確定）

部分函數(shù)無法正確復(fù)制(LOAD_FILE(), UUID(), RAND()等)

Insert into select / 未使用索引的 update語句產(chǎn)生的杭鎖會(huì)多

2 row 模式

2.1 優(yōu)點(diǎn)

2.1.1 記錄數(shù)據(jù)行的變化，安全

2.1.2 insert into select 產(chǎn)生行鎖少， update/delete 語句中where條件，只會(huì)記錄滿足條件的SQL結(jié)果，

2.2 缺點(diǎn)：

2.2.1 生產(chǎn)日志量較多, 可以使用binlog_row_image=minimal 來減少二進(jìn)制日志

2.2.2? 如果blob值，row模式復(fù)制時(shí)間比statement時(shí)間長(zhǎng)

2.2.3 無法直接看到具體SQL語句， 可以在主庫中啟用：binlog_rows_query_log_events(binlog中寫入一個(gè)rows_query_log_event類型的事件記錄原始SQL文本)

2.3 row格式補(bǔ)充

Row的復(fù)制中，不會(huì)復(fù)制臨時(shí)表,臨時(shí)表侄女唄創(chuàng)建臨時(shí)表的線程訪問, 不記錄在binlog中, statement會(huì)記錄相關(guān)語句(5.7.25會(huì)記錄 DROP TEMPORARY TABLE IF EXISTS)

Row 格式：如果修改行數(shù)多,會(huì)將行數(shù)據(jù)變更拆分到多個(gè)事件中, 從庫回放時(shí)候可能會(huì)產(chǎn)生更多的行鎖減少了并發(fā)

Binlog編碼用的是 Base64，解碼時(shí)候： mysqlbinlog –base64-output=decode-rows –verbose選項(xiàng)

Slave_exec_mode= strict 默認(rèn)，如果是idempotent時(shí)如果主鍵發(fā)生錯(cuò)誤會(huì)被跳過，有可能導(dǎo)致主從失敗

不能使用 @@server_id ，row會(huì)記錄實(shí)際值而不是變量名， 如果堅(jiān)持使用 可以考慮用statement

推薦使用表級(jí)別復(fù)制（--replicate-do-table , --replicate-ignore-table）不推薦使用（--replicate-do-db, --replicate-ignore-db和--replicate-rewrite-db）

3 對(duì)安全/不安全語句的處理(某些函數(shù),RAND(), UUID()/自定義函數(shù)等)

Row：對(duì)安全/不安全處理一致

MIX: 不安全語句用row? 安全語句用statement

Statement? 對(duì)不安全的語句告警, 甚至拒絕執(zhí)行 （XA分布式事務(wù)， 語句執(zhí)行順序有不同會(huì)導(dǎo)致死鎖）

四 GTID復(fù)制 (Global Transaction Identifier 全局事務(wù)標(biāo)識(shí)符)

4.1 GTD 特性

GTID復(fù)制時(shí),主庫提交的事務(wù)可以被任意從庫應(yīng)用識(shí)別與跟蹤, 用GTID來定位對(duì)應(yīng)的二進(jìn)制日志文件和位置，可方便看主從的一致性

GTID Set信息在主庫和從庫中都會(huì)保存, 可以通過GTID SET信息來追蹤二進(jìn)制日志的來源，GTID不能重復(fù)，僅能執(zhí)行一次，后續(xù)重復(fù)的會(huì)被跳過執(zhí)行

5.7后新增 mysql.gtid.executed表來持久化GTID信息

4.2 GTID的格式和存儲(chǔ)

4.2.1 格式：GTID = source_id : transaction_id ?復(fù)制狀態(tài)表PERFORMANCE_SCHEMA.replicatioin_applier_status_by_worker表

Source_id: 標(biāo)識(shí)事務(wù)的源server，為master的 server_uuid

Transaction_id: master 上提交事務(wù)的順序

4.2.2 GTID SET: GTID SET 由一個(gè)或多個(gè)GTID列表/范圍組成的集合, 使用方式

1 系統(tǒng)變量 gtid_executed 和 gtid_purged存儲(chǔ)的值需要使用 GTID_SET

2 START SLAVE 子句 UNTIL SQL_BEFORE_GTIDS 和 UNTIL SQL_AFTER_GTIDS使用

3 內(nèi)置函數(shù) GTID_SUBSET() 和 GTID_SUBSTRACT() 的輸入?yún)?shù)使用 GTID SET

4.3 GTID的存儲(chǔ)

Mysql.gtid_executed 持久化GTID，包括： 實(shí)例的UUID, 對(duì)應(yīng)的GTID SET的起始和結(jié)束事務(wù)ID(順序號(hào)) reset master 時(shí)候 該表會(huì)被置空

4.3.1 GTID_mysql.gtid.executed存儲(chǔ)

1 未開啟，在每個(gè)事務(wù)未提交時(shí),刷一次GTID到 mysql.gtid.executed

2 開啟 log_bin & log_slave_updates 只有在binlog切換和關(guān)閉msyqld進(jìn)程時(shí)候 刷入 mysql.gitd.executed

3 如果意外宕機(jī)當(dāng)前的GTID不會(huì)刷入mysql.gitd.executed表中，恢復(fù)后會(huì)掃binlog補(bǔ)齊相關(guān)GTID 最后記錄的GTID不會(huì)被記錄

4 GTID的完整記錄由系統(tǒng)變量 gtid_executed全局提供, 始終使用@@global.gtid.executed 來表示最新的GTID狀態(tài)

5 通過系統(tǒng)變量 gtid_executed_compression_period(每隔多少事務(wù)壓縮一次), 當(dāng)設(shè)置為0時(shí), 表示關(guān)閉壓縮

6 負(fù)責(zé)壓縮該數(shù)據(jù)的線程是：thread/sql/compress.gitd_table (大部分時(shí)間在睡覺/休眠)

4.3.2 GTID的生命周期

1 主庫執(zhí)行事務(wù)并提交, 分配GTID (序號(hào)順序?qū)懭?

2 基于分配了GTID的事務(wù), 通過二進(jìn)制日志中的Gtid_log_event將GTID 寫入binlog做原子保留/mysqld關(guān)閉 寫入mysql.gtid.executed表中保留

3 分配的GTID 更新給全局變量 @@global.gtid.executed

4 ?binlog 傳到slave后，從庫讀取到的GTID并將其設(shè)置為系統(tǒng)變量 gtid_next， 表示從庫必須用此GTID記錄下一個(gè)事務(wù)（會(huì)話上下文中設(shè)置）

5 從庫驗(yàn)證GTID是否已經(jīng)被使用和屬于哪個(gè)線程,? @@global.gtid.owned表示每個(gè)GTID以及擁有它的線程ID, 如果GTID被重復(fù)持有，不會(huì)報(bào)錯(cuò),會(huì)自動(dòng)跳過;

6 如果GTID尚未使用，從庫會(huì)應(yīng)用該事務(wù)：binlog的 GTID EVENT的語句”SET @@SESSION.GTID_NEXT=XXX” 從庫會(huì)將gtid_next 設(shè)置為XXX

7 如果從庫開啟log_bin和log_slave_updates 從庫回放后會(huì)將這些GTID寫入自身binlog保留，切binlog或關(guān)閉mysqld線程時(shí)保存在mysql.gitd_executed

8 如果從庫未開啟log_bin和log_slave_updates，事務(wù)提交刷表, 5.7版本中DML是原子操作保留DDL不是，8.0 DDL&DML都是原子操作

9 從庫提交事務(wù)后，GTID通過非原子化方式添加到從庫的@@global.gtid.executed中

備注：

1 多線程復(fù)制的從庫（slave_parallel_workers >0）可以并行應(yīng)用事務(wù)，復(fù)制的事務(wù)可以無序提交, 除非設(shè)置(slave_preserve_commit_order=1)

2? 系統(tǒng)變量gtid_purged(@@global.gtid.purged) 中的GTID SET 表示已經(jīng)提交的且在server binlog內(nèi)不存在的GTID，SET @@global.gtid.purged設(shè)置GTID SET

4.3.3 GTID 自動(dòng)定位

CHANGE MASET TO?? 啟用 MASERT AUTO POSITION 選項(xiàng),激活自動(dòng)定位以鏈接主庫， 從庫開啟 log_bin, log_slave_updates

1 從庫發(fā)送一個(gè) GTID SET, 其中包括從庫已經(jīng)收到, 已提交或兩者都已完成的事務(wù).

2 此時(shí)GTID SET = @@global.gtid.executed中的GTID SET 與 接收事務(wù)（performance_schema.replication_connection_status表中received_transaction_set）并集

3 如果在復(fù)制期間，主庫發(fā)現(xiàn)從庫接收或提交了與主庫UUID相同的事務(wù)，但主庫本身沒有其記錄，會(huì)報(bào)錯(cuò) ER_SLAVE_HAS_MORE_GTIDS_THAN_MASTER

4.3.4 GTID 復(fù)制限制

1 GTID依賴事務(wù) 更新操作涉及非事務(wù)引擎,不能同時(shí)操作支持Innodb和MyISAM 引擎, 同時(shí)操作會(huì)導(dǎo)致多個(gè)GTID 分配給同一個(gè)事務(wù),主從 相同的表使用不同存儲(chǔ)引擎，如果非事務(wù)表定義了觸發(fā)器，可能導(dǎo)致事務(wù)與GTID的一一對(duì)應(yīng)關(guān)系破壞

2 使用GTID時(shí)候，create table 語句不允許使用 select 語句

3 臨時(shí)表(enfore_gtid_consisitency 為on) 時(shí)，事務(wù)/存儲(chǔ)過程/存儲(chǔ)函數(shù)/觸發(fā)器 不支持CREATE /DROP TEMPORARY TABLE語句

4 GTID模式下，如果對(duì)mysql升級(jí) mysql_upgrade升級(jí) 不能啟用binlog 記錄（--write-binlog選項(xiàng)）

4.4 GTID & Binlog關(guān)系

- Previous_gtid_log_event Previous_gtid_log_event 在每個(gè)binlog 頭部都會(huì)有每次binlog rotate的時(shí)候存儲(chǔ)在binlog頭部Previous-GTIDs 在binlog中只會(huì)存儲(chǔ)在這臺(tái)機(jī)器上執(zhí)行過的所有binlog，不包括手動(dòng)設(shè)置gtid_purged值。 換句話說，如果你手動(dòng)set global gtid_purged=xx； 那么xx是不會(huì)記錄在Previous_gtid_log_event中的。 - GTID和Binlog之間的關(guān)系是怎么對(duì)應(yīng)的呢? 如何才能找到GTID=? 對(duì)應(yīng)的binlog文件呢？ 假設(shè)有4個(gè)binlog: bin.001,bin.002,bin.003,bin.004 bin.001 : Previous-GTIDs=empty; binlog_event有: 1-40 bin.002 : Previous-GTIDs=1-40; binlog_event有: 41-80 bin.003 : Previous-GTIDs=1-80; binlog_event有: 81-120 bin.004 : Previous-GTIDs=1-120; binlog_event有: 121-160 假設(shè)現(xiàn)在我們要找GTID=$A，那么MySQL的掃描順序?yàn)? - 從最后一個(gè)binlog開始掃描（即: bin.004） - bin.004的Previous-GTIDs=1-120，如果$A=140 > Previous-GTIDs,那么肯定在bin.004中 - bin.004的Previous-GTIDs=1-120，如果$A=88 包含在Previous-GTIDs中,那么繼續(xù)對(duì)比上一個(gè)binlog文件 bin.003,然后再循環(huán)前面2個(gè)步驟，直到找到為止

4.5 持久化參數(shù)

重要參數(shù)如何持久化

1) 如何持久化gtid_executed??? (前提是log-bin=mysql-bin, log_slave_update=1 )

gtid_executed = mysql.gtid_executed??????? #正常情況下

或者

gtid_executed = mysql.gtid_executed + last_binlog中最后沒寫到mysql.gtid_executed中的gtid_event???? #恢復(fù)情況下

2) 如何持久化重置的gtid_purged值?

reset master; set global gtid_purged=$A:a-b;

由于有可能手動(dòng)設(shè)置過gtid_purged=$A:a-b, binlog.index中，last_binlog的Previous-GTIDs并不會(huì)包含$A:a-b

由于有可能手動(dòng)設(shè)置過gtid_purged=$A:a-b, binlog.index中，first_binlog的Previous-GTIDs肯定不會(huì)出現(xiàn)$A:a-b

重置的gtid_purged = @@global.gtid_executed(mysql.gtid_executed:注意，考慮到這個(gè)表的更新觸發(fā)條件，所以這里用@@global.gtid_executed代替?last_binlog的Previous-GTIDs? (last_binlog所有的gtid_event)

用 $reset_gtid_purged 來表示重置的gtid

3) 如何持久化gtid_purged? (前提是log-bin=mysql-bin, log_slave_update=1 )

1 gtid_purged=binlog.index:first_binlog的Previous-GTIDs + $reset_gtid_purged

- MySQL 5.6 版本，在my.cnf文件中添加: 1 gtid_mode=on (必選) #開啟gtid功能 2 log_bin=log-bin=mysql-bin (必選) #開啟binlog二進(jìn)制日志功能 3 log-slave-updates=1 (必選) #也可以將1寫為on 4 enforce-gtid-consistency=1 (必選) #也可以將1寫為on - MySQL 5.7或更高版本，在my.cnf文件中添加: gtid_mode=on (必選) enforce-gtid-consistency=1 （必選） log_bin=mysql-bin （可選） #高可用切換，最好開啟該功能 log-slave-updates=1 （可選） #高可用切換，最好打開該功能

gtid_executed 在當(dāng)前實(shí)例上執(zhí)行過的 GTID 集合，實(shí)際上包含了所有記錄到 binlog 中的事務(wù)。設(shè)置 set sql_log_bin=0 后執(zhí)行的事務(wù)不會(huì)生成 binlog 事件，也不會(huì)被記錄到 gtid_executed 中。執(zhí)行 RESET MASTER 可以將該變量置空。 gtid_purged binlog 不可能永遠(yuǎn)駐留在服務(wù)上，需要定期進(jìn)行清理(通過 expire_logs_days 可以控制定期清理間隔)，否則遲早它會(huì)把磁盤用盡。gtid_purged 用于記錄本機(jī)上已經(jīng)執(zhí)行過，但是已經(jīng)被清除了的 binlog 事務(wù)集合。它是 gtid_executed 的子集。只有 gtid_executed 為空時(shí)才能手動(dòng)設(shè)置該變量，此時(shí)會(huì)同時(shí)更新 gtid_executed 為和 gtid_purged 相同的值。 gtid_executed 為空意味著要么之前沒有啟動(dòng)過基于 GTID 的復(fù)制，要么執(zhí)行過 RESET MASTER。執(zhí)行 RESET MASTER 時(shí)同樣也會(huì)把 gtid_purged 置空，即始終保持 gtid_purged 是 gtid_executed 的子集。 gtid_next 會(huì)話級(jí)變量，指示如何產(chǎn)生下一個(gè)GTID。可能的取值如下: - AUTOMATIC: 自動(dòng)生成下一個(gè) GTID，實(shí)現(xiàn)上是分配一個(gè)當(dāng)前實(shí)例上尚未執(zhí)行過的序號(hào)最小的 GTID。 - ANONYMOUS: 設(shè)置后執(zhí)行事務(wù)不會(huì)產(chǎn)生GTID。 - 顯式指定的GTID: 可以指定任意形式合法的 GTID 值，但不能是當(dāng)前 gtid_executed 中的已經(jīng)包含的 GTID，否則下次執(zhí)行事務(wù)時(shí)會(huì)報(bào)錯(cuò)

MySQL SQL

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。