MySQL协议分析（结合PyMySQL）

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

    }