用Python開發(fā)MySQL增強(qiáng)半同步BinlogServer（T3實(shí)戰(zhàn)篇）

導(dǎo)讀：

作者：曾永偉，知數(shù)堂10期學(xué)員，多年JAVA物流行業(yè)開發(fā)管理經(jīng)驗(yàn)和PHP/Python跨境電商開發(fā)管理經(jīng)驗(yàn)，對數(shù)據(jù)庫系統(tǒng)情有獨(dú)鐘，善于運(yùn)用SQL編程簡化業(yè)務(wù)邏輯，去年開始正式從業(yè)MySQL DBA, 專注于DB系統(tǒng)自動化運(yùn)維、MySQL云上實(shí)踐。

一、概述

通過前兩篇的基礎(chǔ)，我們已經(jīng)能夠深入理解二進(jìn)制與MySQL的協(xié)議關(guān)系了，并且能夠結(jié)合自己的需求，從官方文檔找出相應(yīng)的文檔來實(shí)現(xiàn)自己的功能，希望大家魚漁雙收。

需要特別強(qiáng)調(diào)的是，通過文中的例子和思路，要學(xué)會把一個復(fù)雜的問題進(jìn)行以拆分，細(xì)化成一個一個的小任務(wù)后再逐個去實(shí)現(xiàn)，特別是對接觸編碼不久的同學(xué)特別重要，從Hello World模式開始編碼在任何階段都適用（它是一個最小無依賴的可執(zhí)行單元），因?yàn)樗芘懦姸喔蓴_項(xiàng)，讓你可以專注自己的小目標(biāo)進(jìn)行編碼和測試。

目前我們已經(jīng)能夠用Socket和MySQL進(jìn)行基本通信了，也能夠處理Binlog文件的Event了，離我們的BinlogServer僅有一步之遙了，這一步就是通過Socket讀取Binlog Event并保存到本地。

這一篇之所以叫實(shí)戰(zhàn)篇，就是希望你不要停在只看不練的階段，這里再重提一下基礎(chǔ)篇的要點(diǎn)：

只有會認(rèn)真看文檔的DBA才是好DBA，只會認(rèn)真看代碼的Engineer,一定不是好Engineer。代碼一定要運(yùn)行起來，On Runtime才會有價值，才會讓你變成好Engineer. _

二、Python學(xué)習(xí)方法論

在這面在分享一個快速提高Python Coding的方法：

Reinventing the wheel with github

從產(chǎn)品設(shè)計(jì)和生產(chǎn)的層面來講，我們要避免重復(fù)造輪子，作為一個Engineer，在眾多的學(xué)習(xí)方法中，重復(fù)造輪子是最好的方式，沒有之一。

Github有太多的優(yōu)秀項(xiàng)目值得我們學(xué)習(xí)，我們都可以把它們當(dāng)成"輪子"重復(fù)制造一遍，深入學(xué)習(xí)它們的制造"工藝"，然后用到自己的項(xiàng)目中。如果你還沒有找到一個合適的"輪子"，那就從py-mysql-binlogserver開始吧，它配備了完整入門教程，沒有第三方依賴，非常適合學(xué)習(xí)（項(xiàng)目的初衷也為了準(zhǔn)備一個公司內(nèi)部分享的Python實(shí)戰(zhàn)項(xiàng)目）。

"輪子"找到了，剩下就事情就是需要你去"搬磚"了，一行一行的搬到自己的環(huán)境中，不一定要照抄，最好是在理解的基礎(chǔ)上，能用變通的方式來實(shí)現(xiàn)，舉一反三，達(dá)到相同的效果，相信用這種方式你很快就能學(xué)到很多東西，并能夠在此項(xiàng)目的基礎(chǔ)上做出更多的功能，如MySQL實(shí)時同步或遷移工具，到時候你會發(fā)現(xiàn)要將MySQL的熱點(diǎn)數(shù)據(jù)真正實(shí)時地同步到NoSQL中是多么容易。

三、學(xué)習(xí)使用tcpdump和Wireshark

在通信篇，我們完成了和MySQL Server的基本通信：認(rèn)證和執(zhí)行SQL查詢，接下來我們就需要分析出MySQL半同步復(fù)制的流程和具體網(wǎng)包內(nèi)容，然后使用Python的Socket來模擬網(wǎng)絡(luò)收發(fā)包，最終從Master上把Binlog Event獲取回來。

抓包工具組合tcpdump和Wireshark對特別重要工具對我們非常有幫助，它對程序的分析和排錯起到最重要的作用。在實(shí)現(xiàn)BinlogServer之前，必須要先用一個正常的復(fù)制環(huán)境中dump出一個正常無誤的數(shù)據(jù)包進(jìn)行參考，當(dāng)自己的程序出現(xiàn)問題，則可以一個包一個包的進(jìn)行對比，有時甚至要一個字節(jié)一個字節(jié)對行對比，方能快速地找出程序的問題。

1、使用tcpdump抓包

首先我們需要建立一個MySQL的復(fù)制環(huán)境，一主一從即可，在從庫Change Master之后，start slave之前，使用tcpdump記錄下所發(fā)生的一切：

# Master
$ tcpdump -i enp0s8 port 3306  -w /tmp/3306-repl.cap
    
# Slave
mysql> start slave;
    
# Master
mysql>  do some trx;
    
# Slave
mysql> stop slave;
    
# Master, 中斷tcpdump，得到 3306-repl.cap 文件。

2、使用Wireshark看包

直接用Wireshark打開/tmp/3306-repl.cap， 我們先分析出start slave后，從庫都發(fā)送了哪些包：

# 第一步部分，先認(rèn)證
Server  Greeting
Login  Request
    
# 第二部分， 從庫發(fā)起各種查詢和指令， 這里可先不用關(guān)心每一個是做什么用的
Statement: SELECT UNIX_TIMESTAMP()
Statement: SELECT @@GLOBAL.SERVER_ID
Statement: SET @master_heartbeat_period=  30000001024
Statement: SET @master_binlog_checksum=  @@global.binlog_checksum
Statement: SELECT @@GLOBAL.GTID_MODE
Statement: SELECT @@GLOBAL.SERVER_UUID
Statement: SET @slave_uuid=  'ba66414c-d10d-11e9-b4b0-0800275ae9e7'
Command:  Register  Slave  (21)
Statement: SELECT @@global.rpl_semi_sync_master_enabled
Statement: SET @rpl_semi_sync_slave=  1
Command:  Send  Binlog GTID (30)

可以發(fā)現(xiàn)，除了Command: Register Slave 和 Command: Register Slave 外，其它Statement都是普通query,在上一節(jié)中我們都實(shí)現(xiàn)了。 其中發(fā)送Register Slave后，就可以在主庫用show slave hosts進(jìn)行查看了，發(fā)送Send Binlog GTID之后，Master就源源不斷的把Binlog Event發(fā)送過來了。如果執(zhí)行了SET @rplsemisync_slave= 1后，Master將會啟用半同步協(xié)議進(jìn)行傳輸并等待從庫發(fā)送ACK直到超時。

四、異步Binlog dump協(xié)議

上面的抓包是一個標(biāo)準(zhǔn)的啟動半同步復(fù)制的流程，有一些query不是必須的（如:驗(yàn)證Master狀態(tài)，注冊Slave,設(shè)置心跳值等），接下來我們先模擬一個最簡單的Binlog dump流程：

# learn_packet4_dump.py
    
# 封裝binlog dump包
def get_dump_pos(log_file, log_pos, server_id):
    """
    https://dev.mysql.com/doc/internals/en/com-binlog-dump.html
    1              [12] COM_BINLOG_DUMP
    4              binlog-pos
    2              flags
    4              server-id
    string[EOF]    binlog-filename
    """
    COM_BINLOG_DUMP =  0x12
    buffer =  struct.pack('<i', len(log_file)  +  11) \
              +  struct.pack('<B', COM_BINLOG_DUMP)
    
    buffer +=  struct.pack('<I', log_pos)
    
    flags =  0
    buffer +=  struct.pack('<H', flags)
    buffer +=  struct.pack('<I', server_id)
    buffer += log_file.encode()
    
    return buffer
    
if __name__ ==  "__main__":
    
    conn = get_conn("192.168.1.100",  3306,  "repl",  "repl1234")
    
    # 請跟據(jù)自己的環(huán)境進(jìn)行修改
    log_file =  "mysql-bin.000012"
    log_pos =  4
    dump  = get_dump_pos(log_file, log_pos,  3306100)
    conn.send(dump)
    
    print("=== Dump Binlog Event ===")
    while  True:
        _header = conn.recv(5)
        _length =  struct.unpack("<I",  (_header[0:3]  + b"\x00"))[0]
        _sequenceId =  struct.unpack("<B", _header[3:4])[0]
        _packetType =  struct.unpack("<B", _header[4:])[0]
    
        if _packetType ==  0xfe:  # EOF
            break
    
        _payload = conn.recv(_length -  1)
        dump_packet(_header + _payload, f"read packet [{_sequenceId}]")

一個簡單Binlog dump進(jìn)程就寫好了，為了簡單化，它省略了所有的非必要動作，通過MySQL Server的驗(yàn)證并得到連接后，就直接向服務(wù)器發(fā)送Binlob dump指令，看一下輸出：

===  Dump  Binlog  Event  ===
read packet [1]
00000000  2C  00  00  01  00  00  00  00  00  04  74  72  32  00  2B  00  ,.......  ..tr2.+.
00000010  00  00  00  00  00  00  20  00  04  00  00  00  00  00  00  00  ......  .  ........
00000020  6D  79  73  71  6C  2D  62  69  6E  2E  30  30  30  30  31  35 mysql-bi n.000015
    
read packet [2]
00000000  78  00  00  02  00  76  4C  C9  5D  0F  74  72  32  00  77  00 x....vLé  ].tr2.w.
00000010  00  00  7B  00  00  00  00  00  04  00  35  2E  37  2E  32  30  ..{.....  ..5.7.20
00000020  2D  6C  6F  67  00  00  00  00  00  00  00  00  00  00  00  00  -log....  ........
00000030  00  00  00  00  00  00  00  00  00  00  00  00  00  00  00  00  ........  ........
00000040  00  00  00  00  00  00  00  00  00  00  00  00  76  4C  C9  5D  ........  ....vLé]
00000050  13  38  0D  00  08  00  12  00  04  04  04  04  12  00  00  5F  .8......  ......._
00000060  00  04  1A  08  00  00  00  08  08  08  02  00  00  00  0A  0A  ........  ........
00000070  0A  2A  2A  00  12  34  00  00  3B  CE  F1  09  .**..4..  ;??.
   
read packet [3]
00000000  6C  00  00  03  00  77  4C  C9  5D  23  74  72  32  00  6B  00 l....wLé  ]#tr2.k.
00000010  00  00  E6  00  00  00  80  00  02  00  00  00  00  00  00  00  ..?...  ........
00000020  F0  EA  18  E0  3C  FF  11  E9  94  88  08  00  27  5A  E9  E7 eê.à<..é  ??..'Zé?
00000030  01  00  00  00  00  00  00  00  01  00  00  00  00  00  00  00   ........ ........
00000040  1B  00  00  00  00  00  00  00  F6  70  08  50  F3  FD  11  E9   ........ ?p.Póy.é
00000050  B2  C1  08  00  27  5A  E9  E7  01  00  00  00  00  00  00   2á..'Zé?  ........
00000060  01  00  00  00  00  00  00  00  0F  00  00  00  00  00  00  00  ........  ........
    
read packet [4]
00000000  3E  00  00  04  00  FF  4C  C9  5D  21  74  72  32  00  3D  00  >.....Lé  ]!tr2.=.
00000010  00  00  23  01  00  00  00  00  00  F0  EA  18  E0  3C  FF  11  ..#.....  .eê.à<..
00000020  E9  94  88  08  00  27  5A  E9  E7  1B  00  00  00  00  00  00  é??..'Zé ?.......
00000030  00  02  00  00  00  00  00  00  00  00  01  00  00  00  00  00   ........ ........
00000040  00  00                                             ..
    
read packet [5]
00000000  44 00 00 05 00 FF 4C C9  5D 02 74 72 32 00 43 00   D.....Lé ].tr2.C.
00000010  00 00 66 01 00 00 08 00  05 00 00 00 00 00 00 00   ..f..... ........
00000020  03 00 00 1A 00 00 00 00  00 00 01 00 00 20 40 00   ........ ..... @.
00000030  00 00 00 06 03 73 74 64  04 E0 00 E0 00 E0 00 64   .....std .à.à.à.d
00000040  62 33 00 42 45 47 49 4E                            b3.BEGIN

接下來我們再"優(yōu)雅"一下我們的碼，并把Package中header去掉，只要真正的Binlog Event內(nèi)容（把它存到文件中，就是我們Binlog File了）：

# learn_packet4_dump2.py
    
def fetch_events(conn):
    while  True:
        _header = conn.recv(5)
        _length =  struct.unpack("<I",  (_header[0:3]  + b"\x00"))[0]
        _sequenceId =  struct.unpack("<B", _header[3:4])[0]
        _packetT   pe =  struct.unpack("<B", _header[4:])[0]
 
        if _packetType ==  0xfe:  # EOF
            break
        _payload = conn.recv(_length -  1)
        yield _payload
    
if __name__ ==  "__main__":
    
    conn = get_conn("192.168.1.100",  3306,  "repl",  "repl1234")
    
    log_file =  "mysql-bin.000015"
    log_pos =  4
    dump  = get_dump_pos(log_file, log_pos,  3306100)
    conn.send(dump)
    
    print("=== Dump Binlog Event ===")
    for  event  in fetch_events(conn):
         timestamp, event_type, server_id, event_size, log_pos, flags =  struct.unpack('<IBIIIH',  event[:19])
         print("Binlog Event[%s]: [%s] %s %s"  %  (timestamp,
                                                  event_type,
                                                  event_map.get(event_type), log_pos))
         dump_packet(event, f"Read event packet:")

使用Python yield生成器來處理event的獲取動作，就可以使用很優(yōu)雅的方式來迭代event。來看一下輸出：

===  Dump  Binlog  Event  ===
Binlog  Event[0]:  [4] ROTATE_EVENT 0
Read  event packet:
00000000  00  00  00  00  04  74  72  32  00  2B  00  00  00  00  00  00  .....tr2 .+......
00000010  00  20  00  04  00  00  00  00  00  00  00  6D  79  73  71  6C  .  ......  ...mysql
00000020  2D  62  69  6E  2E  30  30  30  30  31  35  -bin.000  015
    
Binlog  Event[1573473398]:  [15] FORMAT_DESCRIPTION_EVENT 123
Read  event packet:
00000000  76  4C  C9  5D  0F  74  72  32  00  77  00  00  00  7B  00  00 vLé].tr2 .w...{..
00000010  00  00  00  04  00  35  2E  37  2E  32  30  2D  6C  6F  67  00  .....5.7  .20-log.
00000020  00  00  00  00  00  00  00  00  00  00  00  00  00  00  00  00  ........  ........
00000030  00  00  00  00  00  00  00  00  00  00  00  00  00  00  00  00  ........  ........
00000040  00  00  00  00  00  00  00  76  4C  C9  5D  13  38  0D  00  08  .......v Lé].8...
00000050  00  12  00  04  04  04  04  12  00  00  5F  00  04  1A  08  00  ........  .._.....
00000060  00  00  08  08  08  02  00  00  00  0A  0A  0A  2A  2A  00  12  ........  ....**..
00000070  34  00  00  3B  CE  F1  09  4..;??.
    
Binlog  Event[1573473399]:  [35] PREVIOUS_GTIDS_LOG_EVENT 230
Read  event packet:
00000000  77  4C  C9  5D  23  74  72  32  00  6B  00  00  00  E6  00  00 wLé]#tr2 .k...?..
00000010  00  80  00  02  00  00  00  00  00  00  00  F0  EA  18  E0  3C  .?......  ...eê.à<
00000020  FF  11  E9  94  88  08  00  27  5A  E9  E7  01  00  00  00  00  ..é??..' Zé?.....
00000030  00  00  00  01  00  00  00  00  00  00  00  1B  00  00  00  00   ........ ........
00000040  00  00  00  F6  70  08  50  F3  FD  11  E9  B2  C1  08  00  27   ...?p.Pó y.é2á..
00000050  5A  E9  E7  01  00  00  00  00  00  00  00  01  00  00  00  00 Zé?.....  ........
00000060  00  00  00  0F  00  00  00  00  00  00  00  ........  ...
    
Binlog  Event[1573473535]:  [33] GTID_LOG_EVENT 291
Read  event packet:
00000000  FF  4C  C9  5D  21  74  72  32  00  3D  00  00  00  23  01  00  .Lé]!tr2 .=...#..
00000010  00  00  00  00  F0  EA  18  E0  3C  FF  11  E9  94  88  08  00  ....eê.à  <..é??..
00000020  27  5A  E9  E7  1B  00  00  00  00  00  00  00  02  00  00  00  'Zé?.... ........
00000030  00  00  00  00  00  01  00  00  00  00  00  00  00            ........ .....
    
Binlog Event[1573473535]: [2] QUERY_EVENT 358
Read event packet:
00000000  FF 4C C9 5D 02 74 72 32  00 43 00 00 00 66 01 00   .Lé].tr2 .C...f..
00000010  00 08 00 05 00 00 00 00  00 00 00 03 00 00 1A 00   ........ ........
00000020  00 00 00 00 00 01 00 00  20 40 00 00 00 00 06 03   ........  @......
00000030  73 74 64 04 E0 00 E0 00  E0 00 64 62 33 00 42 45   std.à.à. à.db3.BE
00000040  47 49 4E                                          GIN
    
Binlog Event[1573473535]: [19] TABLE_MAP_EVENT 401
Read event packet:
00000000  FF 4C C9 5D 13 74 72 32  00 2B 00 00 00 91 01 00   .Lé].tr2 .+...?..
00000010  00 00 00 70 00 00 00 00  00 01 00 03 64 62 33 00   ...p.... ....db3.
00000020  02 74 33 00 02 03 0F 02  28 00 02                  .t3..... (..
    
Binlog Event[1573473535]: [30] WRITE_ROWS_EVENT 441
Read event packet:
00000000  FF 4C C9 5D 1E 74 72 32  00 28 00 00 00 B9 01 00   .Lé].tr2 .(...1..
00000010  00 00 00 70 00 00 00 00  00 01 00 02 00 02 FF FC   ...p.... .......ü
00000020  06 00 00 00 03 32 32 32                            .....222
    
Binlog Event[1573473535]: [16] XID_EVENT 468
Read event packet:
00000000  FF 4C C9 5D 10 74 72 32  00 1B 00 00 00 D4 01 00   .Lé].tr2 .....?..
00000010  00 00 00 12 00 00 00 00  00 00 00                  ........ ...

五、半同步Binlog dump協(xié)議

在普通的Dump基礎(chǔ)上，我們只需要在會話級別設(shè)置SET @rplsemisync_slave=1就可以讓Master使用半同步復(fù)制協(xié)議進(jìn)行傳送Binlog Event,同時Master開始半同步協(xié)議后，Packet中會多兩個字節(jié)， 需要額外處理。

另外，需要注意的是發(fā)送ACK的時機(jī)，format=ROW時，在每個事務(wù)的最后一個XID——EVENT發(fā)送Semi ack給Master, 當(dāng)事務(wù)中DDL或format=Statement時，在QUERY_EVENT之后發(fā)送Semi ack給Master：

def get_semi_ack(log_file, log_pos):
    # 1 0xef kPacketMagicNum
    # 8 log_pos
    # n binlog_filename
    
    buff = b'\xef'
    buff +=  struct.pack("<Q", log_pos)
    buff += log_file.encode("utf8")
    
    return  struct.pack("<I", len(buff))  + buff
    
if __name__ ==  "__main__":
    conn = get_conn("192.168.1.100",  3306,  "repl",  "repl1234")
    query(conn,  "select @@version_comment")
    # 啟用增強(qiáng)半同步
    query(conn,  "SET @rpl_semi_sync_slave=1")
    
    log_file =  "mysql-bin.000016"
    log_pos =  4
    
    dump  = get_dump_pos(log_file, log_pos,  3306100)
    dump_packet(dump,  "Dump bin log:")
    conn.send(dump)
    
    for  event  in fetch_events(conn):
    
         timestamp, event_type, server_id, event_size, log_pos, flags =  struct.unpack('<IBIIIH',  event[2:21])
         print("Binlog Event[%s]: [%s] %s %s"  %  (timestamp,
                                                  event_type,
                                                  event_map.get(event_type), log_pos))
         dump_packet(event, f"Read event packet:")
    
         if event_type in  (XID_EVENT, QUERY_EVENT):
             # TODO  從ROTATE_EVENT中解析當(dāng)前的binlog文件名
             semi_ack = get_semi_ack(log_file, log_pos)
             dump_packet(semi_ack,  "Send semi ack:")
             conn.sendall(semi_ack)

輸出結(jié)果：

Dump  Binlog  Event:
00000000  1B  00  00  00  12  04  00  00  00  00  00  74  72  32  00  6D  ........  ...tr2.m
00000010  79  73  71  6C  2D  62  69  6E  2E  30  30  30  30  31  36 ysql-bin .000016
    
Binlog  Event[0]:  [4] ROTATE_EVENT 0
Read  event packet:
00000000  00  00  00  00  04  74  72  32  00  2B  00  00  00  00  00  00  .....tr2 .+......
00000010  00  20  00  04  00  00  00  00  00  00  00  6D  79  73  71  6C  .  ......  ...mysql
00000020  2D  62  69  6E  2E  30  30  30  30  31  36  -bin.000  016
    
Binlog  Event[1573474715]:  [15] FORMAT_DESCRIPTION_EVENT 123
Read  event packet:
00000000  9B  51  C9  5D  0F  74  72  32  00  77  00  00  00  7B  00  00  ?Qé].tr2 .w...{..
00000010  00  00  00  04  00  35  2E  37  2E  32  30  2D  6C  6F  67  00  .....5.7  .20-log.
00000020  00  00  00  00  00  00  00  00  00  00  00  00  00  00  00  00  ........  ........
00000030  00  00  00  00  00  00  00  00  00  00  00  00  00  00  00  00  ........  ........
00000040  00  00  00  00  00  00  00  00  00  00  00  13  38  0D  00  08  ........  ....8...
00000050  00  12  00  04  04  04  04  12  00  00  5F  00  04  1A  08  00  ........  .._.....
00000060  00  00  08  08  08  02  00  00  00  0A  0A  0A  2A  2A  00  12  ........  ....**..
00000070  34  00  00  B0  58  2A  BE  4..°X*?
    
Binlog  Event[1573474715]:  [35] PREVIOUS_GTIDS_LOG_EVENT 230
Read  event packet:
00000000  9B  51  C9  5D  23  74  72  32  00  6B  00  00  00  E6  00  00  ?Qé]#tr2 .k...?..
00000010  00  80  00  02  00  00  00  00  00  00  00  F0  EA  18  E0  3C  .?......  ...eê.à<
00000020  FF  11  E9  94  88  08  00  27  5A  E9  E7  01  00  00  00  00  ..é??..' Zé?.....
00000030  00  00  00  01  00  00  00  00  00  00  00  1F  00  00  00  00   ........ ........
00000040  00  00  00  F6  70  08  50  F3  FD  11  E9  B2  C1  08  00  27   ...?p.Pó y.é2á..'
00000050  5A  E9  E7  01  00  00  00  00  00  00  00  01  00  00  00  00 Zé?.....  ........
00000060  00  00  00  0F  00  00  00  00  00  00  00  ........  ...
    
Binlog  Event[1573474750]:  [33] GTID_LOG_EVENT 291
Read  event packet:
00000000  BE  51  C9  5D  21  74  72  32  00  3D  00  00  00  23  01  00  ?Qé]!tr2 .=...#..
00000010  00  00  00  00  F0  EA  18  E0  3C  FF  11  E9  94  88  08  00  ....eê.à  <..é??..
00000020  27  5A  E9  E7  1F  00  00  00  00  00  00  00  02  00  00  00  'Zé?.... ........
00000030  00  00  00  00  00  01  00  00  00  00  00  00  00            ........ .....
    
Binlog Event[1573474750]: [2] QUERY_EVENT 358
Read event packet:
00000000  BE 51 C9 5D 02 74 72 32  00 43 00 00 00 66 01 00   ?Qé].tr2 .C...f..
00000010  00 08 00 05 00 00 00 00  00 00 00 03 00 00 1A 00   ........ ........
00000020  00 00 00 00 00 01 00 00  20 40 00 00 00 00 06 03   ........  @......
00000030  73 74 64 04 E0 00 E0 00  E0 00 64 62 33 00 42 45   std.à.à. à.db3.BE
00000040  47 49 4E                                          GIN
    
Send semi ack:
00000000  19 00 00 00 EF 66 01 00  00 00 00 00 00 6D 79 73   ....?f.. .....mys
00000010  71 6C 2D 62 69 6E 2E 30  30 30 30 31 36            ql-bin.0 00016

我們的程序是從第1個Event開始Dump, 包含了Master上已經(jīng)提交的事務(wù)（只要Binlog File沒有被Purge掉之前，都可以Dump下來），當(dāng)Semi Ack的位置追上Master后，Mater的每個事務(wù)提交就會等待Slave進(jìn)程發(fā)送ACK包，再開啟下一下事務(wù)的提交流程。我們可以通過在 Master上查詢status確認(rèn)半同步是否生效：

mysql> show global status like '%semi%';
+--------------------------------------------+-------+
|  Variable_name  |  Value  |
+--------------------------------------------+-------+
|  Rpl_semi_sync_master_clients  |  1  |
|  Rpl_semi_sync_master_net_avg_wait_time  |  0  |
|  Rpl_semi_sync_master_net_wait_time  |  0  |
|  Rpl_semi_sync_master_net_waits  |  29  |
|  Rpl_semi_sync_master_no_times  |  1  |
|  Rpl_semi_sync_master_no_tx  |  8  |
|  Rpl_semi_sync_master_status  | ON |  <==  重點(diǎn)
|  Rpl_semi_sync_master_timefunc_failures  |  0  |
|  Rpl_semi_sync_master_tx_avg_wait_time  |  1654  |
|  Rpl_semi_sync_master_tx_wait_time  |  8271  |
|  Rpl_semi_sync_master_tx_waits  |  5  |
|  Rpl_semi_sync_master_wait_pos_backtraverse  |  0  |
|  Rpl_semi_sync_master_wait_sessions  |  0  |
|  Rpl_semi_sync_master_yes_tx  |  5  |  <==  重點(diǎn)
|  Rpl_semi_sync_slave_status  | OFF |
+--------------------------------------------+-------+
15 rows in  set  (0.00 sec)
    
mysql> insert into t3 select  null,222;
Query OK,  1 row affected (0.00 sec)
Records:  1  Duplicates:  0  Warnings:  0
    
mysql> show global status like '%semi%';
+--------------------------------------------+-------+
|  Variable_name  |  Value  |
+--------------------------------------------+-------+
|  Rpl_semi_sync_master_clients  |  1  |
|  Rpl_semi_sync_master_net_avg_wait_time  |  0  |
|  Rpl_semi_sync_master_net_wait_time  |  0  |
|  Rpl_semi_sync_master_net_waits  |  30  |
|  Rpl_semi_sync_master_no_times  |  1  |
|  Rpl_semi_sync_master_no_tx  |  8  |
|  Rpl_semi_sync_master_status  | ON |
|  Rpl_semi_sync_master_timefunc_failures  |  0  |
|  Rpl_semi_sync_master_tx_avg_wait_time  |  1721  |
|  Rpl_semi_sync_master_tx_wait_time  |  10328  |
|  Rpl_semi_sync_master_tx_waits  |  6  |
|  Rpl_semi_sync_master_wait_pos_backtraverse  |  0  |
|  Rpl_semi_sync_master_wait_sessions  |  0  |
|  Rpl_semi_sync_master_yes_tx  |  6  |  <==  重點(diǎn)
|  Rpl_semi_sync_slave_status  | OFF |
+--------------------------------------------+-------+
15 rows in  set  (0.00 sec)

可以看出通semi_sync提交的事務(wù)在增加了，說明我們的半同步Binlog Dump已經(jīng)大功告成。

六、如何實(shí)現(xiàn)Master協(xié)議

server.py是在socketserver的基礎(chǔ)上實(shí)現(xiàn)了一些必要的MySQL Server協(xié)議（簡單例子：learnsocket4servermulitthread.py），如認(rèn)證，執(zhí)行查詢，發(fā)送Binlog Event等等，為了簡化代簡，一些查詢被用"緩存包"的形式來實(shí)際的，這些緩存包可以通過proxy.py來獲取，同時使用proxy.py也可以非常方便的拿來觀察MySQL的通信包， 也為開發(fā)MySQL中間件提供了基礎(chǔ)， 更多的功能等待大家來實(shí)現(xiàn)。

小結(jié)

到目前為此，我們通過循序漸進(jìn)的方式，從MySQL的第一個Greeting包，到最后的半同步下Binlog Event包全部搞定了，接下來我們只需要再增加Binlog文件的存儲功能、Dump斷點(diǎn)續(xù)傳功能等，就可以實(shí)現(xiàn)一個完整的Binlog Sever功能了， 當(dāng)然這些功能都已經(jīng)含在項(xiàng)目，通過這一系列的教程， 相信你已經(jīng)有能力去讀懂py-mysql-binlogserver的大部分代碼了，更多的"制造工藝"等你去發(fā)現(xiàn)。

相關(guān)閱讀：
用Python開發(fā)MySQL增強(qiáng)半同步BinlogServer（T1基礎(chǔ)篇）
用Python開發(fā)MySQL增強(qiáng)半同步BinlogServer（T2通信篇）

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容