mysql binlog解析概要

標籤：

1,dump協議：

根據資料庫的ip+port建立socket,如果建立成功，說明連結建立成功，接下來是使用dump協議訂閱binlog

連結建立成功之後,服務端會主動向用戶端發送如下問候資訊greeting(可以理解為經java轉換後，是一個java對象)，

在下面的代碼中可以看到greeting中的資訊：

this.context.setServerStatus(greeting.getServerStatus());//
this.context.setServerVersion(greeting.getServerVersion().toString());
this.context.setServerCollation(greeting.getServerCollation());
this.context.setServerCapabilities(greeting.getServerCapabilities());
this.context.setThreadId(greeting.getThreadId());
this.context.setProtocolVersion(greeting.getProtocolVersion());
this.context.setScramble(greeting.getScramble1().toString() + greeting.getScramble2().toString());

然後如下：
將ctx=this.context;

final XSerializer s = new XSerializer(64);
s.writeInt(buildClientCapabilities(), 4);
s.writeInt(this.maximumPacketLength, 4);
s.writeInt(this.clientCollation > 0 ? this.clientCollation : ctx.getServerCollation(), 1);
s.writeBytes((byte)0, 23); // Fixed, all 0
s.writeNullTerminatedString(StringColumn.valueOf(this.user.getBytes(this.encoding)));
s.writeInt(20, 1); // the length of the SHA1 encrypted password
s.writeBytes(MySQLUtils.password41OrLater(this.password.getBytes(this.encoding), ctx.getScramble().getBytes(this.encoding)));
if(this.initialSchema != null) s.writeNullTerminatedString(StringColumn.valueOf(this.initialSchema.getBytes(this.encoding)));

//transport可以理解為是一個socket的封裝後的東西
final RawPacket request = new RawPacket();
request.setSequence(1);
request.setPacketBody(s.toByteArray());
request.setLength(request.getPacketBody().length);
transport.getOutputStream().writePacket(request);
transport.getOutputStream().flush();

然後可以在下面的代碼中得到mysql的相應：

final Packet response = transport.getInputStream().readPacket();
if(response.getPacketBody()[0] == ErrorPacket.PACKET_MARKER) {
   final ErrorPacket error = ErrorPacket.valueOf(response);
   LOGGER.info("login failed, user: {}, error: {}", this.user, error);
   throw new TransportException(error);
} else if(response.getPacketBody()[0] == OKPacket.PACKET_MARKER) {
   final OKPacket ok = OKPacket.valueOf(response);
   LOGGER.info("login successfully, user: {}, detail: {}", this.user, ok);
} else {
   LOGGER.warn("login failed, unknown packet: ", response);
   throw new RuntimeException("assertion failed, invalid packet: " + response);
}

2,dump報文格式：

下方表格來自mysql官網連結：http://dev.mysql.com/doc/internals/en/mysql-packet.html

Type	Name	Description
int<3>	payload_length	Length of the payload. The number of bytes in the packet beyond the initial 4 bytes that make up the packet header.
int<1>	sequence_id	Sequence ID
string<var>	payload	[len=payload_length] payload of the packet

報文對超大packet的支援方式如下：

當一個packet過大 (超過1<<24-1byte ~= 16 MB) 時, 傳輸需要對packet進行切割, 參看這裡

注意, 在A上產生binlog時, 是可以容納大於16MB的packet的, 也就是原binlog裡存在超大的event, 需要在傳輸時加以限制

切割packet沒什麼特別之處, 僅需要注意包格式, 一個20MB的event的傳輸packet格式舉例為 (此處用16MB便於描述, 應為1<<24-1byte):

    packet 1        4位元組 packet header        1位元組 值為[00], 是binlog event的特徵標誌        16MB-1位元組 為第一段資料            packet 2        4位元組 packet header        20MB-16MB+1位元組 為第二段資料

需要注意的是之後的packet時不帶有[00]特徵位的. 而包的大小計算範圍為除去前4位元組的全部位元組

上面的講解對應的代碼為：

// Parse packet
final int packetLength = is.readInt(3);
final int packetSequence = is.readInt(1);//超大資料包切割後的順序
is.setReadLimit(packetLength); // Ensure the packet boundary

//
final int packetMarker = is.readInt(1);//特徵位
if(packetMarker != OKPacket.PACKET_MARKER) { // 0x00
   if((byte)packetMarker == ErrorPacket.PACKET_MARKER) {
      final ErrorPacket packet = ErrorPacket.valueOf(packetLength, packetSequence, packetMarker, is);
      throw new RuntimeException(packet.toString());
   } else if((byte)packetMarker == EOFPacket.PACKET_MARKER) {
      final EOFPacket packet = EOFPacket.valueOf(packetLength, packetSequence, packetMarker, is);
      throw new RuntimeException(packet.toString());
   } else {
      throw new RuntimeException("assertion failed, invalid packet marker: " + packetMarker);
   }
}

 接下來直接解析mysql binlog即可，header擷取如下：

// Parse the event header
final BinlogEventV4HeaderImpl header = new BinlogEventV4HeaderImpl();
header.setTimestamp(is.readLong(4) * 1000L);//timestamp
header.setEventType(is.readInt(1));//type_code
header.setServerId(is.readLong(4));//server_id
header.setEventLength(is.readInt(4));//event_length
header.setNextPosition(is.readLong(4));//next_position
header.setFlags(is.readInt(2));//flags
header.setBinlogFileName(this.binlogFileName);
header.setTimestampOfReceipt(System.currentTimeMillis());

body的解析不同事件格式不一樣，類似header那樣取值



註：代碼來自開源項目open replicator

mysql binlog解析概要