Mysql communication protocol and mysql communication protocol

Mysql communication protocol and mysql communication protocol

1. Mysql connection method

To understand the communication protocol of Mysql, you must first know the connection method used to connect to the Mysql server. The main connection methods of Mysql include Unix socket, Memory sharing, and named pipe, TCP/IP socket.

1.1Unix socket

In Linux and Unix environments, you can use Unix sockets to connect to the Mysql server. Unix sockets are not a network protocol and can only be used on the same computer as the client and Mysql server, the usage is also simple:

root@root ~]# mysql -uroot -prootmysql> show variables like 'socket';+---------------+---------------------------+| Variable_name | Value      |+---------------+---------------------------+| socket  | /var/lib/mysql/mysql.sock |+---------------+---------------------------+1 row in set (0.00 sec)

The preceding command is used to query the location of the Unix socket file;

1.2 Named Pipe and Memory Sharing

In the window system, the client and Mysql Server are on the same computer. You can use Named Pipes and shared memory,

-Shared-memory = on/off;

Enable shared memory:-enable-named-pipe = on/off;

1.3tcp/ip socket

The communication protocol described in this article is also based on this connection method, the following is a preliminary understanding of TCP/IP sockets through tcpdump:

Server:

[root@root ~]# tcpdump port 3306tcpdump: verbose output suppressed, use -v or -vv for full protocol decodelistening on venet0, link-type LINUX_SLL (Linux cooked), capture size 65535 bytes

The server listens to port 3306 (that is, the Mysql port );

Client:

C:\Users\hui.zhao>mysql -h64.xxx.xxx.xxx -uroot -prootmysql> exitBye

The client connects to the server and then disconnects. Observe the server listening result log at this time:

[root@root ~]# tcpdump port 3306tcpdump: verbose output suppressed, use -v or -vv for full protocol decodelistening on venet0, link-type LINUX_SLL (Linux cooked), capture size 65535 bytes02:06:25.442472 IP 153.3.251.202.33876 > root.mysql: Flags [S], seq 27289263, win 8192, options [mss 1460,nop,wscale 8,nop,nop,sackOK], length 002:06:25.442763 IP root.mysql > 153.3.251.202.33876: Flags [S.], seq 2014324548, ack 27289264, win 14600, options [mss 1460,nop,nop,sackOK,nop,wscale 7], length 002:06:25.617449 IP 153.3.251.202.33876 > root.mysql: Flags [.], ack 1, win 256, length 002:06:29.812946 IP root.mysql > 153.3.251.202.33876: Flags [P.], seq 1:57, ack 1, win 115, length 5602:06:29.992362 IP 153.3.251.202.33876 > root.mysql: Flags [P.], seq 1:63, ack 57, win 256, length 6202:06:29.992411 IP root.mysql > 153.3.251.202.33876: Flags [.], ack 63, win 115, length 002:06:29.992474 IP root.mysql > 153.3.251.202.33876: Flags [P.], seq 57:68, ack 63, win 115, length 1102:06:30.166992 IP 153.3.251.202.33876 > root.mysql: Flags [P.], seq 63:100, ack 68, win 256, length 3702:06:30.167109 IP root.mysql > 153.3.251.202.33876: Flags [P.], seq 68:158, ack 100, win 115, length 9002:06:30.536298 IP 153.3.251.202.33876 > root.mysql: Flags [.], ack 158, win 256, length 002:06:34.568611 IP 153.3.251.202.33876 > root.mysql: Flags [P.], seq 100:105, ack 158, win 256, length 502:06:34.568620 IP 153.3.251.202.33876 > root.mysql: Flags [F.], seq 105, ack 158, win 256, length 002:06:34.568751 IP root.mysql > 153.3.251.202.33876: Flags [F.], seq 158, ack 106, win 115, length 002:06:34.743815 IP 153.3.251.202.33876 > root.mysql: Flags [.], ack 159, win 256, length 0

[S]: indicates the SYN initiate connection flag. [P]: indicates the PUSH Transfer Data flag. [F]: indicates the FIN close connection flag. [.]: indicates the confirmation package;

The process is as follows: Establish a tcp connection, establish a connection between the client and the Mysql server, and close the tcp connection;

[S] [S.] [.] These packets represent three handshakes of tcp connections;

[F.] [F.] [.] These packets represent the four waves of the tcp connection;

Multiple [P.] [.] in the middle are actually protocol data packets sent by the client and the Mysql server.

2. Protocol Analysis

The Mysql protocol is used for communication between Mysql Clients and Mysql Server. The following scenarios are specific: connection between the client and the Server, Mysql proxy, and master-slave backup;

The interaction between the MySQL client and the server is divided into two phases: Connection Phase (Connection Phase or authentication Phase) and Command Phase (Command Phase );

Based on the output of tcpdump, the entire communication process between the client and the server is roughly as follows:

1. establish tcp connection three-way handshake; 2. establish a Connection with the Mysql server, that is, Connection Phase (Connection Phase or authentication Phase); s-> c: Send the Handshake initialization package (a Initial Handshake Packet) c-> s: authentication response () s-> c: server sends authentication result package 3. after the authentication is passed, the server accepts the client's Command package and sends the corresponding response package, that is, Command Phase (Command Phase); 4. disconnect and request the exit command; 5. four Waves of tcp disconnection;

2.1 Basic Types

The basic types in the entire Protocol: integer and string;

2.1.1 integer

There are two Types: Fixed-Length Integer Types and Length-Encoded Integer Type;

Fixed-Length Integer Types:

A fixed-length unsigned integer stores its values in a series of bytes. The number of fixed segments can be: 1, 2, 3, 4, 6, 8;

Length-Encoded Integer Type:

The number of bytes required for storage depends on the value size. For details, refer to the following:

1 byte: 0 <= X <251;

2 bytes: 251 <= X <2 ^ 16;

3 bytes: 2 ^ 16 <= X <2 ^ 24;

9 Bytes: 2 ^ 24 <= X <2 ^ 64;

2.1.2 string type

There are five types: FixedLengthString, NullTerminatedString, VariableLengthString, LengthEncodedString, and RestOfPacketString;

FixedLengthString: a fixed-length string has a known hard-coded length. For example, it is the SQL state of ERR_Packet, which is always 5 bytes long;
NullTerminatedString: string that ends with Null (00 in bytes;

VariableLengthString: a variable string. The length of a string is determined by another field or is calculated at runtime. For example, int + value, int Is the length, and value is the number of bytes of the specified length;

LengthEncodedString: A string prefixed with an integer that describes the length of the string. It is the int + value method specified by VariableLengthString;

RestOfPacketString: If a string is the last component of a data packet, its length can be calculated from the length of the entire data packet minus the current position;

2.2 Basic data packets

If the MySQL client or server wants to send data:

The size of each data packet cannot exceed 2 ^ 24 bytes (16 MB );

Add a data header before each data block;

The package format is as follows:

Int <3>: length of the specific package content; excluding int <3> + int <1> = 4-byte length; int <1>: sequence_id increases with each data packet, and may be surrounded. It starts from 0 and is reset to 0 when a new command starts in the Command phase; string <var>: the specific data content, also the length specified by int <3>;

For example:

The int value corresponding to 01 00 indicates that the length of the specific data content is 1 byte;

00 indicates sequence_id corresponding to int;

01 the data content specified before the string is 1 byte.

2.3 Message Type

It can be divided into three categories: login authentication message, client request message, and server return message, based on mysql5.1.73 (Versions later than mysql4.1)

2.3.1 logon authentication message

It is mainly in the interaction authentication phase. It can be known from the above that there are three phases: Handshake Packet, authentication response and result package. Here we mainly analyze the first two packages;

2.3.1.1 Handshake Packet

1 byte: Protocol version number NullTerminatedString: database version information 4 byte: connection to MySQL Server startup thread ID8 byte: Challenge random number, used for database Authentication 1 byte: Fill value (0x00) 2 bytes: Used to negotiate with the client. Communication Mode: 1 byte: Database encoding 2 byte: server status 13 byte: Reserved byte 12 byte: Challenge random number, used for database Authentication 1 byte: fill value (0x00)

Use tcpdump to listen and output the hexadecimal log as follows:

[root@root ~]# tcpdump port 3306 -X......03:20:34.299521 IP root.mysql > 153.3.251.202.44658: Flags [P.], seq 1:57, ack 1, win 115, length 56 0x0000: 4508 0060 09f1 4000 4006 c666 43da 9190 E..`..@.@..fC... 0x0010: 9903 fbca 0cea ae72 bb4e 25ba 21e7 27e3 .......r.N%.!.'. 0x0020: 5018 0073 b1e0 0000 3400 0000 0a35 2e31 P..s....4....5.1 0x0030: 2e37 3300 4024 0000 5157 4222 252f 5f6f .73.@$..QWB"%/_o 0x0040: 00ff f708 0200 0000 0000 0000 0000 0000 ................ 0x0050: 0000 0032 4a5d 7553 7e45 784f 627e 7400 ...2J]uS~ExOb~t.

The total length of the packet is 56, minus int <3> + int <1> 4 bytes = 52 bytes, And the hexadecimal value is 34; int <3> The hexadecimal value is 3400 00, which indicates the length of the Package content. int <1> The hexadecimal value is 00, which indicates sequence_id. The subsequent content includes 52 bytes of the Package content, 0 A corresponds to 10 in decimal format, and all protocol numbers are 10. Subsequent database version information ends at 00, and 35 2e31 2e37 33 corresponds to 5.1.73, which is the current database version; 4024 0000 corresponds to a decimal value of 6436; 08 represents the database encoding; 0200 represents the server status; 13 pairs of 00 are reserved bytes; the last 13 bytes are the challenge of random numbers and fill values.

2.3.1.2 Authentication Packet

4-byte: used for communication with the client. 4-byte: Maximum message length supported by the client when sending a request message. Value: 1 byte: The character encoding 23 bytes used during communication: reserved byte NullTerminatedString: username LengthEncodedString: encrypted password NullTerminatedString: Database Name (optional)

Use tcpdump to listen and output the hexadecimal log as follows:

03:20:34.587416 IP 153.3.251.202.44658 > root.mysql: Flags [P.], seq 1:63, ack 57, win 256, length 62 0x0000: 4500 0066 29ee 4000 7006 766b 9903 fbca E..f).@.p.vk.... 0x0010: 43da 9190 ae72 0cea 21e7 27e3 bb4e 25f2 C....r..!.'..N%. 0x0020: 5018 0100 d8d2 0000 3a00 0001 85a6 0f00 P.......:....... 0x0030: 0000 0001 2100 0000 0000 0000 0000 0000 ....!........... 0x0040: 0000 0000 0000 0000 0000 0000 726f 6f74 ............root 0x0050: 0014 ff58 4bd2 7946 91a0 a233 f2c1 28af ...XK.yF...3..(. 0x0060: d578 0762 c2e8       .x.b..

The total length of the package is 62, minus int <3> + int <1> 4 bytes = 58 bytes, And the hexadecimal format is 3a; int <3> The hexadecimal value of 3a00 indicates the length of the Package content; int <1> The hexadecimal value of 01 indicates sequence_id; and then f 6f74 00 indicates that the user name is decoded as root; the encrypted password type is

LengthEncodedString, 14 corresponds to 20 in decimal format, and the last 20 bytes are the encrypted password. The optional database name does not exist.

2.4 client Request Message

Int <1>: the command to be executed, such as switching the database string <var>: The corresponding parameter of the command.

Command list:

0x00 COM_SLEEP (internal thread status) 0x01 COM_QUIT close connection 0x02 COM_INIT_DB switch database 0x03 COM_QUERY SQL query request 0x04 COM_FIELD_LIST get data table Field Information 0x05 COM_CREATE_DB CREATE DATABASE 0x06 COM_DROP_DB delete database 0x07 COM_REFRESH clear cache 0x08 COM_SHUTDOWN stop server 0x09 COM_STATISTICS get server statistics 0x0A COM_PROCESS_INFO get current connection list 0x0B COM_CONNECT (internal thread status) 0x0C COM_PROCESS_KILL interrupt a connection 0x0D COM_DEBUG save server debugging information 0x0E COM_PING test connectivity 0x0F COM_TIME (internal thread status) 0x10 COM_DELAYED_INSERT (internal thread status) 0x11 COM_CHANGE_USER re-login (continuous connection) 0x12 COM_BINLOG_DUMP get binary log information 0x13 COM_TABLE_DUMP get data table structure information 0x14 COM_CONNECT_OUT (internal thread status) 0x15 COM_REGISTER_SLAVE register 0x16 COM_STMT_PREPARE preprocessing SQL statement 0x17 COM_STMT_EXECUTE execute preprocessing statement 0x18 then send BLOB Data 0x19 COM_STMT_CLOSE destroy preprocessing statement 0x1A COM_STMT_RESET clear preprocessing statement parameter cache 0x1B COM_SET_OPTION set statement option 0x1C COM_STMT_FETCH get the execution result of preprocessing statement

For example, use test; use tcpdump to listen, and output the hexadecimal log as follows:

22:04:29.379165 IP 153.3.251.202.33826 > root.mysql: Flags [P.], seq 122:131, ack 222, win 64019, length 9 0x0000: 4500 0031 3f19 4000 7006 6175 9903 fbca E..1?.@.p.au.... 0x0010: 43da 9190 8422 0cea 42e2 524b 7e18 25c1 C...."..B.RK~.%. 0x0020: 5018 fa13 a07b 0000 0500 0000 0274 6573 P....{.......tes 0x0030: 74

The total length of the packet is 9, minus int <3> + int <1> 4 bytes = 5 bytes, And the hexadecimal value is 05; int <3> The hexadecimal value of 0500 00 indicates the length of the Package content; int <1> The hexadecimal value of 00 indicates sequence_id; 02 corresponds to COM_INIT_DB, followed by the binary code of test;

2.5 Server Response Message

For most commands sent by the client to the server, the server returns one of the response data packets: OK _Packet, ERR_Packet, EOF_Packet, and Result Set;

2.5.1OK _ Packet

The command is successfully completed in the following format:

Int <1>: 0x00 or 0xFEOK Baotou int <lenenc>: Number of affected rows int <lenenc>: Last inserted index IDint <2>: Server Status int <2>: warning count Note: MySQL 4.1 and later versions only have string <lenenc>: Server Message (optional)

Use test; the server returns a packet that uses tcpdump for listening. The hexadecimal log is output as follows:

22:04:29.379308 IP root.mysql > 153.3.251.202.33826: Flags [P.], seq 222:233, ack 131, win 14600, length 11 0x0000: 4508 0033 4a0a 4000 4006 867a 43da 9190 E..3J.@.@..zC... 0x0010: 9903 fbca 0cea 8422 7e18 25c1 42e2 5254 ......."~.%.B.RT 0x0020: 5018 3908 3b61 0000 0700 0001 0000 0002 P.9.;a.......... 0x0030: 0000 00

The total length of the package is 11, minus int <3> + int <1> 4 bytes = 7 bytes, and the corresponding hexadecimal format is 07; int <3> hexadecimal 0700 00 indicates the length of the Package content; int <1> hexadecimal 01 indicates sequence_id; 00 indicates the Baotou; 00 indicates the number of affected rows; 00 indicates the last inserted index ID; 0200 indicates the server status;

2.5.2ERR _ Packet

Indicates An error occurred. The specific format is as follows:

Int <1>: 0xFF ERR Baotou int <2>: Error code string [1]: SQL status identifier Note: MySQL 4.1 and later versions only have string [5]: SQL status note: MySQL 4.1 and later versions only have string <EOF>: error message

2.5.3EOF _ Packet

To mark the end of the query execution result:

Int <1>: EOF value (0xFE) int <2>: Alarm count Note: MySQL 4.1 and later versions only have int <2>: Status flag bit note: mySQL 4.1 and later versions

2.5.4Result Set

After the client sends a query request, the server returns the Result Set to the client without errors. There are five parts in total:

Result Set Header returned Data column quantity Field returned Data column information (multiple) EOF Column End Row Data (multiple) EOF Data end

2.5.4.1Result Set Header

Length-Encoded Integer Field structure quantity Length-Encoded Integer Additional information

2.5.4.2Field

LengthEncodedString directory name LengthEncodedString database name LengthEncodedString data table Original Name LengthEncodedString column (field) original Name int <1> Fill value int <2> character encoding int <4> column (field) length int <1> column (field) type int <2> column (field) int <1> integer value precision int <2> Fill value (0x00) LengthEncodedString Default Value

2.5.4.3EOF

See 2.5.3EOF _ Packet

2.5.4.4Row Data

LengthEncodedString field value ...... Multiple Field Values

Instance analysis. The table information is as follows:

CREATE TABLE `btest` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `age` int(11) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8

Select * from btest; the server returns the following data:

mysql> select * from btest;+----+------+---------+| id | age | name |+----+------+---------+| 1 | 10 | zhaohui || 2 | 11 | zhaohui |+----+------+---------+

The server uses tcpdump to listen on the returned packets. The hexadecimal logs are output as follows:

01:54:21.522660 IP root.mysql > 153.3.251.202.58587: Flags [P.], seq 1:196, ack 24, win 115, length 195 0x0000: 4508 00eb 8839 4000 4006 4793 43da 9190 E....9@.@.G.C... 0x0010: 9903 fbca 0cea e4db 9dd8 0216 eda6 f730 ...............0 0x0020: 5018 0073 ca34 0000 0100 0001 0328 0000 P..s.4.......(.. 0x0030: 0203 6465 6604 7465 7374 0562 7465 7374 ..def.test.btest 0x0040: 0562 7465 7374 0269 6402 6964 0c3f 0014 .btest.id.id.?.. 0x0050: 0000 0008 0342 0000 002a 0000 0303 6465 .....B...*....de 0x0060: 6604 7465 7374 0562 7465 7374 0562 7465 f.test.btest.bte 0x0070: 7374 0361 6765 0361 6765 0c3f 000b 0000 st.age.age.?.... 0x0080: 0003 0000 0000 002c 0000 0403 6465 6604 .......,....def. 0x0090: 7465 7374 0562 7465 7374 0562 7465 7374 test.btest.btest 0x00a0: 046e 616d 6504 6e61 6d65 0c21 00fd 0200 .name.name.!.... 0x00b0: 00fd 0000 0000 0005 0000 05fe 0000 2200 ..............". 0x00c0: 0d00 0006 0131 0231 3007 7a68 616f 6875 .....1.10.zhaohu 0x00d0: 690d 0000 0701 3202 3131 077a 6861 6f68 i.....2.11.zhaoh 0x00e0: 7569 0500 0008 fe00 0022 00    ui.......".

0328 0000 02 corresponds to the Result Set Header, 03 represents three fields; 03 6465 66 corresponds to the default def of the directory name, 03 represents 3 bytes; 04 7465 7374

It corresponds to the database name test; 0562 7465 7374 corresponds to the data table name btest; 0562 7465 7374 corresponds to the original data table name btest; 0269 64 corresponds to the field name id; 02 6964 corresponds to the original name id of the column (field); 0c3f 00 corresponds to the filling value and character encoding; 14 0000 00 corresponds to a decimal value of 20 indicating the length of the column (field; 0342 00 represents the column (field) type, identifier, and integer value precision respectively. 00002 bytes are filled values; 00 is the default value, which indicates null values;

The subsequent age and name fields are the same as the preceding ones. They are not repeated;

The character 1 corresponding to the 0131 type LengthEncodedString is the id value; the character 10 corresponding to the 0231 30 type LengthEncodedString is the age value; 07 7a68 616f 6875 the character zhaohui corresponding to LengthEncodedString of 69 type is the value of name;

The above is all the content related to the MYSQL communication protocol that we have prepared for you. If you have any questions about the MYSQL communication protocol, please leave a message to us. Thank you for your support for the help house.