Shulou(Shulou.com)05/31 Report--

Editor to share with you an example of Mysql communication protocol analysis, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to understand it!

The connection mode of 1.Mysql

To understand the communication protocol of Mysql, you first need to know which connection mode is used to connect to the Mysql server; the main connection methods of Mysql include: Unix socket, memory sharing, named pipe, TCP/IP socket and so on.

1.1Unix socket

In Linux and Unix environments, you can use Unix sockets to connect to Mysql servers. Unix sockets are not a network protocol and can only be used on the same computer as the client and Mysql server. The way to use them is 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 above command queries the location of the Unix socket file

1.2 named pipes and memory sharing

In window system, the client and the Mysql server are on the same computer, and named pipes and shared memory can be used.

Named pipe is enabled:-shared-memory=on/off

Shared memory enabled:-enable-named-pipe=on/off

1.3TCP/IP socket

It can be used in any system, and it is also the most frequently used connection method. The communication protocol introduced in this article is also based on this connection method. Here is a preliminary understanding of TCP/IP sockets through tcpdump:

Server side:

[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 on port 3306 (that is, the port of Mysql)

Client:

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

The client connects to the server, then disconnects, and then observes the monitoring result log of the 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 bytes02:06:25.442472 IP 153.3.251.202.33876 > root.mysql: Flags [S], seq 27289263, win 8192, options [mss 1460 paraphrase], length 002VOV06VR 25.442763 IP root.mysql > 153.251.202.33876: Flags [S.], seq 2014324548, ack 27289264, win 14600 Options [mss 1460, noprecoversackOK7], length 002 IP 153.251.202.33876 > root.mysql: Flags [.], ack 1, win 256, length 002 length 06 IP 29.812946 IP root.mysql > 153.251.202.33876: Flags [P.], seq 1:57, ack 1, win 11515, length 5602 seq 1:63, ack 57, win 256 root.mysql: Flags [P.] Length 6202 IP root.mysql > 153.3.251.202.33876: Flags [.], ack 63, win 115, length 002 ack > 153.251.202.33876 IP root.mysql > 153.3.251.202.33876: Flags [P.], seq 57:68, ack 63, win 115, length 1102 6lav 30.166992 IP 153.251.202.33876 > root.mysql: Flags [P.], seq 6315 100, ack 68, win 256 Length 3702 IP root.mysql > 153.3.251.202.33876: Flags [P.], seq 68 Flags [P.], ack 100, win 115, length 9002, 06V 30.536298 IP 153.251.202.33876 > root.mysql: Flags, ack 158, win 256, length 002 Flags 34.568611 IP 153.251.202.33876 > root.mysql: Flags [P.], seq 100u105, ack 158, win 256 Length 502 root.mysql root.mysql: Flags [F.], seq 105,202.33876 > IP root.mysql > 153.251.202.33876 IP root.mysql > 153.251.202.33876: Flags [F.], seq 158, ack 106, win 115, length 002 Fran 06R 34.743815 IP 153.251.202.33876 > root.mysql: Flags [.], ack 159, win 256, length 0

[s]: SYN initiate connection flag, [P]: PUSH transfer data flag, [F]: FIN close connection flag, [.]: confirm packet

You can roughly see the flow: establish a tcp connection, establish a connection communication between the client and the Mysql server, and close the tcp connection.

[S] [S.] [.] These packets represent a three-way handshake for a tcp connection

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

Multiple in the middle [P.] [.] In fact, it is the protocol packet sent by the client and the Mysql server when they establish a connection.

two。 Protocol analysis

When Mysql protocol is used to communicate between Mysql Clients and Mysql Server, there are several specific scenarios: client and server connection, Mysql proxy and master-slave backup.

The interaction between MySQL client and server is mainly divided into two phases: Connection Phase (connection phase or authentication phase) and Command Phase (command phase).

Combined with the output of tcpdump, the whole process of communication between client and server is roughly as follows:

1. Establish a three-way handshake for tcp connection; 2. Establish a connection with the Mysql server, that is, Connection Phase (connection phase or authentication phase); s-> c: send handshake initialization package (an Initial Handshake Packet) c-> s: send authentication package (authentication response) s-> c: server sends authentication result package 3. After the authentication is passed, the server accepts the command packet from the client and sends the corresponding response packet, namely Command Phase (command phase); 4. Disconnect request exit command; 5. Disconnect the tcp with four waves

2.1 basic types

Basic types in the entire protocol: integer type and string type

2.1.1 Integer type

There are two types of Fixed-Length Integer Types and Length-Encoded Integer Type

Fixed-Length Integer Types:

An unsigned integer of a fixed length stores its value in a series of bytes. The specific number of fixed bytes can be: 1, 2, 3, 4, 6, 8

Length-Encoded Integer Type:

The number of bytes required for storage depends on the size of the number, as shown below:

1 byte: 0 root.mysql: Flags [P.], seq 122 win 131, ack 222, win 64019, length 9 0x0000: 4500 0031 3f19 4000 7006 6175 9903 fbca E.. 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+int4 bytes = 5 bytes, the corresponding hexadecimal is 05; int hexadecimal 0500 indicates the packet content length; int hexadecimal 00 indicates that sequence_id;02 corresponds to COM_INIT_DB, followed by test binary coding

2.5 Server response message

For most commands sent by the client to the server, the server returns packets for one of the responses: OK_Packet,ERR_Packet and EOF_Packet,Result Set

2.5.1OK_Packet

Indicates the successful completion of a command in the following format:

Int:0x00 or 0xFEOK header int: affected rows int: last inserted index IDint: server status int: alarm count Note: MySQL 4.1 or later string: server messages (optional)

The packet returned by the use test; server, which is monitored by tcpdump, outputs the following hexadecimal log:

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

The total length of the packet is 11, minus int+int4 bytes = 7 bytes, and the corresponding hexadecimal is 07; an int hexadecimal of 0700 indicates the packet content length; an int hexadecimal of 01 indicates the packet header; 00 indicates the number of affected rows; and 00 indicates the server status of the last inserted index ID;0200

2.5.2ERR_Packet

Indicates that an error has occurred in the following format:

Int:0xFF ERR header int: error code string [1]: Sql status identification Note: string is available only for MySQL 4.1and later [5]: Sql status Note: MySQL 4.1and later string: error message

2.5.3EOF_Packet

To mark the end of the query execution result:

Int: int value (0xFE) int: alarm count Note: MySQL 4.1and later version int: status flag bit note: MySQL 4.1and later version

2.5.4Result Set

When the client sends a query request, the server returns the result set (Result Set) to the client without error, which has five parts:

Number of columns returned by Result Set Header Field returns column information of data (multiple) EOF column ends Row Data row data (multiple) EOF data ends

2.5.4.1Result Set Header

Number of Length-Encoded Integer Field structures Length-Encoded Integer additional information

2.5.4.2Field

LengthEncodedString directory name LengthEncodedString database name LengthEncodedString data table name LengthEncodedString data table original name LengthEncodedString column (field) name LengthEncodedString column (field) original name int fill value int character encoding int column (field) length int column (field) type int column (field) flag int integer value precision int fill value (0x00) LengthEncodedString default value

2.5.4.3EOF

Reference 2.5.3EOF_Packet

2.5.4.4Row Data

LengthEncodedString field value. Multiple field valu

For example analysis, the information in the table 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

The data returned by select * from btest; server is as follows:

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

The packet returned by the server is monitored using tcpdump, and the hexadecimal log is output as follows:

01ack 54 eda6 21.522660 IP root.mysql > 153.3.251.202.58587: Flags [P.], seq 1VO196, ack 24, win 115,195 0x0000: 4508 00eb 8839 4000 4006 4793 43da 9190 E.9bike. G.C... 0x0010: 9903 fbca 0cea e4db 9dd8 0216 eda6 f730. 0 0x0020: 5018 0073 ca34 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 03616765 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 00000701 3202 3131 077a 6861 6f68 i.2.11.zhaoh 0x00e0: 756905000008 fe00 002200 ui.

0328 0000 02 corresponds to Result Set Header,03 for 3 fields; 03 6465 66 corresponds to the default value for directory name def,03 indicates that the number of bytes following it is 310 04 7465 7374

The database name test;0562 7465 7374 corresponds to the data table name btest;0562 7465 7374, the data table original name btest;0269 64 corresponds to the field name id;02 6964 corresponding to the column (field) the original name id;0c3f 00 corresponds to the padding value and character encoding; the decimal system corresponding to 14 0000 00 represents the column (field) length; 08 0342 00 represents the column (field) type, identification, and integer value precision, respectively. 00002 bytes are padding values; 00 is the default value for empty

The subsequent age and name fields are the same as above and are not repeated.

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

The above is all the contents of this article "sample Analysis of Mysql Communication Protocol". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.