MySQL Protocol analysis 2

MySQL protocol analysis

议程协议头协议类型网络协议相关函数NET缓冲VIO缓冲MySQL API

Protocol Header

The data becomes the data transmitted over the network, requiring an additional 4 bytes of header to be added to the header. Packet Length (3 bytes) of the package body. Packet Number (1 bytes), incrementing SQL starting at 0 " Select 1"What is the network protocol?

Protocol Header

packet length Three bytes means that MySQL packet maximum 16M greater than 16M is sub-package (Net_write_command, My_net_write) Packet number sub-package starting from 0, Increment sequentially. Packet_number 0 (SQL/net_serv.c:net_clear) for each execution of SQL

Protocol type

handshake auth OK| Error resultset     0header     0field     0eof     0row command Packet

Interaction at the time of connection

Protocol description

The field in the agreement is divided into three forms 0 fixed length (include/my_global.h)UINT*Korr Unpackingint*Store Envelope 0length coded binary (SQL-common/pack.c) net_field_length unpacking net_store_length Pack 0NULL-terminatedstringlength coded binary0 avoid binaryunsafe string, the length of the string is saved in front of the string<251 1 bytelength< the^2 3 byte(the first byte is 252) length< the^34byte(the first byte is 253)Else9byte(The first byte is 254)

Handshake packet

the protocol is sent by the server client in parentheses as a number of bytes, the number of bytes is n is null string n with uppercase in bytes means length code binary. Salt is scramble. Divided into two parts to be compatible with version 4.1 sql_connect.cc:check_connection sql_client.c: Mysql_real_connect

Auth Packet

The protocol is sent to the server by using scramble encryption from the client for the password, where databasename is optional. The salt is the encrypted password. Sql_client.c:mysql_real_connect sql_connect.c : check_connection

OK packet

OK package, command and Insert,update,delete return results The first byte of the package body is 0. insert_id, Affect_rows is also a concurrent come over. SRC/PROTOCOL.CC:NET_SEND_OK

Error packet

The wrong command, the illegal SQL return packet body first byte is 255. error code is cr_***,include/errmsg.h sqlstate marker is # SQLState is the error state, Include/sql_state.h message is the wrong information for SQL/protocol.cc:net_send_error_packet

ResultSet packet

a result set of packets, composed of multiple packet, such as querying a structure set, in the following order:     0header     0field1....fieldn     0eof     0row1...rown     0 EOF SQL/"select * from D" Query Result set example, result set is 6 rows, 3 field     0 formula: Suppose the result set has n rows, M fields. The number of packets is, header (1) + field (M) + EOF (1) + row (N) + EOF (1)     0 So the number of MySQL packet for this example is 12

ResultSet Packet-header

Field packet number determines the next field packet. A query statement that returns 6 rows of records, 3 fields

ResultSet Packet-field

a field in the result set Packet. Tables_alias is the alias of the table in the SQL statement, and Org_table is the real name of the table. SQL/protocol.cc:protocol::send _fields SQL/client.c:cli_read_query_result

ResultSet packet-eof

The EOF package is used to split the first byte of the field packet and row packet. packets to 254 SQL/protocol.cc:net_send_eof

ResultSet Packet-row

row packet is the real packet. A row of data each field in a Packet. row is the number of length coded binary fields in header packet SQL/client.c:cli_ Read_rows

Command packet

command packages, including our SQL statements, have some common commands. The first letter of the package indicates the type of command (include/mysql_com.h), and most of the commands are com_query.

Network protocol key functions

Net_write_command (sql/net_serv.cc) All SQL eventually calls this command to send out. My_net_write (SQL/net_serv.cc) connection phase socket The write operation calls this function. My_net_read reads the packet, it will determine the packet size, whether it is a sub-my_real_read parsing the MySQL packet, the first read 4 bytes, according to packet length and then read the remainder of the time Cli_safe The _read client unpacking function contains the My_net_read

NET buffering

Each time the socket operation writes the data, reads to the net->buff, which is a buffer that reduces the number of calls to the system call. When the data written and the buff are over the buff size, a write operation is issued. Then insert the number in the buff to be written, and the write will not cause the buff area to expand. (SQL/net_serv.cc:net_write_buff). NET->buff size initial net->max_packet, reading results in realloc maximum net-that can cause buff >max_packet_size The end of a SQL command will call Net_flush and write the data in the buff into the socket.

Vio buffering

it can be seen from my_read_read that every packet read is read on demand, in order to reduce system calls, the VIO layer adds a read_buffer. to determinethe length of the data required for vio->read_buffer before each read Not enough. If present, copy directly. If not enough, trigger a socket read read 2048 words (Vio/viosocket.c:vio_read_buff)

MySQL API

data from Mysql_send_query sent to the server, the actual call is Net_write_command. Cli_read_query_result parsing header packet, field packet, received The number of Field_count Mysql_store_result parse row packet, and stored in result->data myql_fetch_row actually traverse result- >data

________________________________

PACKET number

In the time to do a proxy in the confusion here, turned several times the code to understand, the details are as follows: The client service side of the Net->pkt_nr are starting from 0. When the package is accepted, compare packet number and NET->PKT_NR are equal, otherwise the report packet Number disorderly sequence, connection error, equal PKT_NR self-increment. Send Net->pkt_nr as packet number when sending the package, and then synchronize the NET->PKT_NR with the self-increment and the peer.

Receive Package

sql/net_serv.c:my_real_read     if3]! = (UCHAR) net->pkt_nr)

Send Package

sql/net_serv.c:my_net_write   int3store (buff,len);   buff[3]= (UCHAR) net->pkt_nr++;

Let's take a few specific scenes of packet number, NET->PKT_NR change

Connection

0  <--01  handshake c-–1 1    <-–20  OK

Starting with both sides at 0, the server sends handshake packet (pkt=0) after the self-increment to 1, and then waits for the packet sent over pkt=1 to the end

Inquire

The service client will clear the NET->PKT_NR for each query

include/#define net_new_transaction (NET) ((net)->pkt_nr=0)SQL/sql_parse.cc:do_ Command   net_new_transaction (net); SQL/client.c:cli_advanced_command   net_clear (&mysql- >net, (Command! = com_quit));

Beginning both sides NET->PKT_NR is 0, the command sends after the client side is 1, the service side begins to send the subcontracting, the subcontract pkt_nr increments, the client's net->pkt_nr also increases.

c--00  <--12  <--23   ResultSet

Unpacking details

My_net_read is responsible for unpacking, first reading 4 bytes, determine whether packet number equals NET->PKT_NR and then read the Packet_number length of the package again.

The pseudo code is as follows:

remain=4for02; i++) {    // data is finished reading while     (remain>0)  {        = Read (FD, net->buff, remain)        = remain- length    }    //  First    if (i=0)        {= Uint3korr (net->buff+net->where_b);}    } 

Network layer Optimization

From the PPT can be seen, a resultset packet consists of multiple packages, if each read and write package causes system calls that is certainly unreasonable, general optimization method: Write large packet plus pre-read

Net->buff

Each packet sent to the network or read from the network packet will be in the packet Net->buff, until the Net->buff full or a command end will be issued to the peer through the socket. Net->buff has an initial size (net->max_ Packet), will expand with the increase in read data.

Vio->read_buffer

Every time a packet is read from the network, it is not read by the size of the packet, but instead reads as much as 2048 bytes, so that a resultset packet reads no more calls to the system. Header packet after reading, the next field,eof, row Apcket reads only the data that is required to copy the specified bytes from the Vio-read_buffer.

MYSQL API Description

Both the API and the MySQL client use the sql/client.c file, and the process of unpacking is using Sql/client.c:cli_read_query_result.

Mysql_store_result to parse the row packet and store the data in Res->data, when all the data is in memory.

Mysql_fetch_row only uses internal cursors to traverse the data in the Result->data

     if (!res->data_cursor)     {       Dbug_print ("info", ("end of data");       Dbug_return (res->current_row=(mysql_row) NULL);     }      = res->data_cursor->data;     Res->data_cursor = res->data_cursor->Next;     Dbug_return (res->current_row=tmp);

Mysql_free_result is to release the row data specified by Result->data.

MySQL Protocol analysis 2