MySQL协议分析（结合PyMySQL）

MySQL Packets

当MySQL客户端或者服务端发送数据时，它会首先把数据分割成(2^24-1)bytes的包，然后给每个包加上packet header。

类型 名称 描述 int<3> payload_length 负载长度，除了header的4字节 int<1> sequence_id 序列ID string payload 实际负载

* 当数据大于16M时，payload_length设为 2^24−1 (ff ff ff)
* 序列ID在0~255循环，在新命令开始的时候重置为0

PyMySQL相关代码：

        buff = b''        while True:            packet_header = self._read_bytes(4)            if DEBUG: dump_packet(packet_header)            btrl, btrh, packet_number = struct.unpack('<HBB', packet_header)            bytes_to_read = btrl + (btrh << 16)            if packet_number != self._next_seq_id:                raise err.InternalError("Packet sequence number wrong - got %d expected %d" %                    (packet_number, self._next_seq_id))            self._next_seq_id = (self._next_seq_id + 1) % 256            recv_data = self._read_bytes(bytes_to_read)            if DEBUG: dump_packet(recv_data)            buff += recv_data            # https://dev.mysql.com/doc/internals/en/sending-more-than-16mbyte.html            if bytes_to_read == 0xffffff:                continue            if bytes_to_read < MAX_PACKET_LEN:                break

参考： [Python struct格式] (https://docs.python.org/2/library/struct.html#format-characters)

Replication协议

• Binlog网络流
• 客户端首先发送一个COM_BINLOG_DUMP类型的包，如成功，Server响应返回Binlog网络流。

包负载如下表：

长度 字段 描述 1 COM_BINLOG_DUMP(0x12) Binlog DUMP请求 4 binog-pos binlog文件中的位置 2 flags BINLOG_DUMP_NON_BLOCK(0x01) 4 server-id slave的server id string[EOF] binlog-filename master上的binlog文件名

python-mysql-replication的相关代码：(BinLogStreamReader.__connect_to_stream)

        if not self.auto_position:            # only when log_file and log_pos both provided, the position info is            # valid, if not, get the current position from master            if self.log_file is None or self.log_pos is None:                cur = self._stream_connection.cursor()                cur.execute("SHOW MASTER STATUS")                self.log_file, self.log_pos = cur.fetchone()[:2]                cur.close()            prelude = struct.pack('<i', len(self.log_file) + 11) \                + int2byte(COM_BINLOG_DUMP)            if self.__resume_stream:                prelude += struct.pack('<I', self.log_pos)            else:                prelude += struct.pack('<I', 4)            if self.__blocking:                prelude += struct.pack('<h', 0)            else:                prelude += struct.pack('<h', 1)            prelude += struct.pack('<I', self.__server_id)            prelude += self.log_file.encode()

• 如果binlog-filename为空，服务端会自动定位，返回第一个已知的binlog，这时候客户端发送的包为COM_BINLOG_DUMP_GTID。

包负载如下图：

长度 字段 1 COM_BINLOG_DUMP_GTID 2 flags 4 server-id 4 binlog-filename-len string[len] binlog-filename 8 binlog-pos 4 data-size string[len] data

python-mysql-replication的相关代码：(BinLogStreamReader.__connect_to_stream)

        else:            # Format for mysql packet master_auto_position            #            # All fields are little endian            # All fields are unsigned            # Packet length   uint   4bytes            # Packet type     byte   1byte   == 0x1e            # Binlog flags    ushort 2bytes  == 0 (for retrocompatibilty)            # Server id       uint   4bytes            # binlognamesize  uint   4bytes            # binlogname      str    Nbytes  N = binlognamesize            #                                Zeroified            # binlog position uint   4bytes  == 4            # payload_size    uint   4bytes            # What come next, is the payload, where the slave gtid_executed            # is sent to the master            # n_sid           ulong  8bytes  == which size is the gtid_set            # | sid           uuid   16bytes UUID as a binary            # | n_intervals   ulong  8bytes  == how many intervals are sent for this gtid            # | | start       ulong  8bytes  Start position of this interval            # | | stop        ulong  8bytes  Stop position of this interval            # A gtid set looks like:            #   19d69c1e-ae97-4b8c-a1ef-9e12ba966457:1-3:8-10,            #   1c2aad49-ae92-409a-b4df-d05a03e4702e:42-47:80-100:130-140            #            # In this particular gtid set, 19d69c1e-ae97-4b8c-a1ef-9e12ba966457:1-3:8-10            # is the first member of the set, it is called a gtid.            # In this gtid, 19d69c1e-ae97-4b8c-a1ef-9e12ba966457 is the sid            # and have two intervals, 1-3 and 8-10, 1 is the start position of the first interval            # 3 is the stop position of the first interval.            gtid_set = GtidSet(self.auto_position)            encoded_data_size = gtid_set.encoded_length            header_size = (2 +  # binlog_flags                           4 +  # server_id                           4 +  # binlog_name_info_size                           4 +  # empty binlog name                           8 +  # binlog_pos_info_size                           4)  # encoded_data_size            prelude = b'' + struct.pack('<i', header_size + encoded_data_size) \                + int2byte(COM_BINLOG_DUMP_GTID)            # binlog_flags = 0 (2 bytes)            prelude += struct.pack('<H', 0)            # server_id (4 bytes)            prelude += struct.pack('<I', self.__server_id)            # binlog_name_info_size (4 bytes)            prelude += struct.pack('<I', 3)            # empty_binlog_name (4 bytes)            prelude += b'\0\0\0'            # binlog_pos_info (8 bytes)            prelude += struct.pack('<Q', 4)            # encoded_data_size (4 bytes)            prelude += struct.pack('<I', gtid_set.encoded_length)            # encoded_data            prelude += gtid_set.encoded()

Binlog Event

• Binlog Event Header

Binlog事件的包头长度为13或19字节，根据binlog版本的不同。

长度 字段 描述 4 timestamp unix纪年起的秒数 1 event_type Binlog事件类型 4 server-id 服务端server id 4 event-size 事件大小（包含header） 4 log_pos 下一事件的位置 2 flags Binlog事件flag

python-mysql-replication的相关代码：(BinLogPacketWrapper.__init__)

        # OK value        # timestamp        # event_type        # server_id        # log_pos        # flags        unpack = struct.unpack('<cIcIIIH', self.packet.read(20))        # Header        self.timestamp = unpack[1]        self.event_type = byte2int(unpack[2])        self.server_id = unpack[3]        self.event_size = unpack[4]        # position of the next event        self.log_pos = unpack[5]        self.flags = unpack[6]        # MySQL 5.6 and more if binlog-checksum = CRC32        if use_checksum:            event_size_without_header = self.event_size - 23        else:            event_size_without_header = self.event_size - 19

• Binlog事件类型

python-mysql-replication的相关代码：(BinLogPacketWrapper.__init__)

__event_map = {        # event        constants.QUERY_EVENT: event.QueryEvent,        constants.ROTATE_EVENT: event.RotateEvent,        constants.FORMAT_DESCRIPTION_EVENT: event.FormatDescriptionEvent,        constants.XID_EVENT: event.XidEvent,        constants.INTVAR_EVENT: event.IntvarEvent,        constants.GTID_LOG_EVENT: event.GtidEvent,        constants.STOP_EVENT: event.StopEvent,        constants.BEGIN_LOAD_QUERY_EVENT: event.BeginLoadQueryEvent,        constants.EXECUTE_LOAD_QUERY_EVENT: event.ExecuteLoadQueryEvent,        # row_event        constants.UPDATE_ROWS_EVENT_V1: row_event.UpdateRowsEvent,        constants.WRITE_ROWS_EVENT_V1: row_event.WriteRowsEvent,        constants.DELETE_ROWS_EVENT_V1: row_event.DeleteRowsEvent,        constants.UPDATE_ROWS_EVENT_V2: row_event.UpdateRowsEvent,        constants.WRITE_ROWS_EVENT_V2: row_event.WriteRowsEvent,        constants.DELETE_ROWS_EVENT_V2: row_event.DeleteRowsEvent,        constants.TABLE_MAP_EVENT: row_event.TableMapEvent,        #5.6 GTID enabled replication events        constants.ANONYMOUS_GTID_LOG_EVENT: event.NotImplementedEvent,        constants.PREVIOUS_GTIDS_LOG_EVENT: event.NotImplementedEvent    }