Detailed explanation of Mysql communication protocol
|
1.Mysql connection method To understand the MySQL communication protocol, you first need to know which connection method is used to connect to the MySQL server; the main connection methods of MySQL include: Unix sockets, memory sharing, named pipes, TCP/IP sockets, etc. 1.1Unix Sockets In Linux and Unix environments, you can use Unix sockets to connect to the MySQL server; Unix sockets are not actually a network protocol and can only be used when the client and MySQL server are on the same computer. The usage is also very simple: root@root ~]# mysql -uroot -proot mysql> 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 the window system, the client and the MySQL server are on the same computer, and named pipes and shared memory can be used. Named pipe enabled: –shared-memory=on/off; Shared memory enabled: –enable-named-pipe=on/off; 1.3 TCP/IP Sockets This method can be used in any system and is also the most commonly used connection method. The communication protocol to be introduced in this article is also based on this connection method. The following is a preliminary understanding of TCP/IP sockets through tcpdump: Server side: [root@root ~]# tcpdump port 3306 tcpdump: verbose output suppressed, use -v or -vv for full protocol decode listening 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 -proot mysql> exit Bye The client connects to the server and then disconnects. At this time, observe the server's monitoring result log: [root@root ~]# tcpdump port 3306 tcpdump: verbose output suppressed, use -v or -vv for full protocol decode listening on venet0, link-type LINUX_SLL (Linux cooked), capture size 65535 bytes 02: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 0 02: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 0 02:06:25.617449 IP 153.3.251.202.33876 > root.mysql: Flags [.], ack 1, win 256, length 0 02:06:29.812946 IP root.mysql > 153.3.251.202.33876: Flags [P.], seq 1:57, ack 1, win 115, length 56 02:06:29.992362 IP 153.3.251.202.33876 > root.mysql: Flags [P.], seq 1:63, ack 57, win 256, length 62 02:06:29.992411 IP root.mysql > 153.3.251.202.33876: Flags [.], ack 63, win 115, length 0 02:06:29.992474 IP root.mysql > 153.3.251.202.33876: Flags [P.], seq 57:68, ack 63, win 115, length 11 02:06:30.166992 IP 153.3.251.202.33876 > root.mysql: Flags [P.], seq 63:100, ack 68, win 256, length 37 02:06:30.167109 IP root.mysql > 153.3.251.202.33876: Flags [P.], seq 68:158, ack 100, win 115, length 90 02:06:30.536298 IP 153.3.251.202.33876 > root.mysql: Flags [.], ack 158, win 256, length 0 02:06:34.568611 IP 153.3.251.202.33876 > root.mysql: Flags [P.], seq 100:105, ack 158, win 256, length 5 02:06:34.568620 IP 153.3.251.202.33876 > root.mysql: Flags [F.], seq 105, ack 158, win 256, length 0 02:06:34.568751 IP root.mysql > 153.3.251.202.33876: Flags [F.], seq 158, ack 106, win 115, length 0 02:06:34.743815 IP 153.3.251.202.33876 > root.mysql: Flags [.], ack 159, win 256, length 0 [S]:SYN initiates a connection, [P]:PUSH transmits data, [F]:FIN closes the connection, [.]: indicates a confirmation packet; The process can be roughly seen: establish a TCP connection, the client and the Mysql server establish a connection communication, and close the TCP connection; [S][S.][.] These packets represent the three-way handshake of the TCP connection; [F.][F.][.] These packets represent four handshakes of TCP connection; The multiple [P.][.] in the middle are actually the protocol data packets sent by the client and the MySQL server to establish a connection. 2. Protocol Analysis The MySQL protocol is used when MySQL Clients and MySQL Servers communicate. There are several specific scenarios: client and server connection, MySQL proxy, and master-slave backup; The interaction between the MySQL client and the server is mainly divided into two phases: Connection Phase (or authentication phase) and Command Phase; Combined with the output of tcpdump, the entire process of communication between the client and the server is as follows: 1. Establish a three-way handshake for a TCP connection; 2. Establish a connection with the MySQL server, which is the Connection Phase (connection phase or authentication phase); s->c: Send an Initial Handshake Packet c->s: send authentication response s->c: The server sends the authentication result packet 3. After the authentication is passed, the server accepts the command packet from the client and sends the corresponding response packet, which is the Command Phase; 4. Disconnect and request the exit command; 5. Wave TCP four times to disconnect; 2.1 Basic Types Basic types in the entire protocol: integer and string; 2.1.1 Integer Type There are two types: Fixed-Length Integer Types and Length-Encoded Integer Type; Fixed-Length Integer Types: A fixed-length unsigned integer stores its value in a series of bytes. The specific fixed number of 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 value, as shown below: 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 5 types including FixedLengthString, NullTerminatedString, VariableLengthString, LengthEncodedString and RestOfPacketString; FixedLengthString: A fixed-length string has a known hard-coded length, an example is the SQL status of ERR_Packet, which is always 5 bytes long. VariableLengthString: a variable string whose length is determined by another field or calculated at runtime, such as int+value, where int is the length and value is the number of bytes of the specified length; LengthEncodedString: A string prefixed with a length-encoded integer describing the length of the string, in the int+value format specified by VariableLengthString; RestOfPacketString: If a string is the last component of a packet, its length can be calculated by subtracting the current position from the entire packet length; 2.2 Basic Data Packet If a MySQL client or server wants to send data, it: The size of each data packet cannot exceed 2^24 bytes (16MB); Add a data packet header in front of each data block; The package format is as follows: int<3>: length of the specific packet content; excluding int<3>+int<1>=4 bytes; int<1>: sequence_id increments with each packet and may wrap around. It starts at 0 and is reset to 0 when a new command is started in the command phase; string<var>: specific data content, also the length specified by int<3>; For example: 01 00 00 corresponds to int, indicating that the length of the specific data content is 1 byte; 00 corresponds to int representing sequence_id; 01 corresponds to 1 byte of data specified in front of string. 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 (mysql4.1 and later versions) 2.3.1 Login authentication message Mainly in the interactive authentication phase, as we can see from the above, it is divided into three phases: Handshake Packet, authentication response and result packet. Here we mainly analyze the first two packets; 2.3.1.1 Handshake Packet 1 byte: protocol version number NullTerminatedString: database version information 4 bytes: thread ID started by connecting to MySQL Server 8 bytes: Challenge random number, used for database authentication 1 byte: Fill value (0x00) 2 bytes: used to negotiate the communication mode with the client 1 byte: database code 2 bytes: server status 13 bytes: reserved bytes 12 bytes: challenge random number, used for database authentication 1 byte: padding value (0x00) Use tcpdump to monitor 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..`..@[email protected]... 0x0010: 9903 fbca 0cea ae72 bb4e 25ba 21e7 27e3 .......rN%.!.'. 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, the corresponding hexadecimal is 34; int<3> hexadecimal is 3400 00 represents the packet content length, int<1> hexadecimal is 00 represents sequence_id; the subsequent content is the packet body content, a total of 52 bytes, 0a corresponds to decimal 10, all protocol version numbers are 10; the subsequent database version information ends with 00, 35 2e31 2e37 33 corresponds to 5.1.73, which is the currently used database version; 4024 0000 corresponds to decimal 6436; 08 represents the database code; 0200 represents the server status; the subsequent 13 pairs of 00 are reserved bytes; the last 13 bytes are the challenge random number and padding value. 2.3.1.2 Authentication Packet 4 bytes: used to negotiate the communication mode with the client 4 bytes: the maximum message length value supported by the client when sending a request message 1 byte: identifies the character encoding used in the communication process 23 bytes: reserved bytes NullTerminatedString: user name LengthEncodedString: encrypted password NullTerminatedString: database name (optional) Use tcpdump to monitor 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)[email protected].... 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 .xb. The total length of the packet is 62, minus int<3>+int<1>4 bytes = 58 bytes, the corresponding hexadecimal is 3a; int<3> hexadecimal is 3a00 00 represents the packet content length; int<1> hexadecimal is 01 represents sequence_id; 726f 6f74 00 is the username, which is root after decoding; the following is the encrypted password type LengthEncodedString, the decimal value of 14 is 20, and the following 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 databases string<var>: the corresponding parameters of the command Commands List: 0x00 COM_SLEEP (internal thread state) 0x01 COM_QUIT Close the 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 a database 0x06 COM_DROP_DB Delete a database 0x07 COM_REFRESH Clear the cache 0x08 COM_SHUTDOWN Stop the server 0x09 COM_STATISTICS Get server statistics 0x0A COM_PROCESS_INFO Get a list of current connections 0x0B COM_CONNECT (internal thread status) 0x0C COM_PROCESS_KILL Terminate 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 state) 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 the slave server with the master server 0x16 COM_STMT_PREPARE Prepare SQL statements 0x17 COM_STMT_EXECUTE Execute prepared statements 0x18 COM_STMT_SEND_LONG_DATA Send BLOB data 0x19 COM_STMT_CLOSE Destroy prepared statements 0x1A COM_STMT_RESET Clear prepared statement parameter cache 0x1B COM_SET_OPTION Set statement options 0x1C COM_STMT_FETCH Get the execution result of prepared statements For example: use test; use tcpdump to monitor, and the hexadecimal log output is 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 [email protected].... 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, the corresponding hexadecimal is 05; int<3> hexadecimal is 0500 00 represents the packet content length; int<1> hexadecimal is 00 represents 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 packets: OK_Packet, ERR_Packet and EOF_Packet, Result Set; 2.5.1OK_Packet Indicates that a command has been successfully completed. The specific format is as follows: int<1>: 0x00 or 0xFEOK header int<lenenc>: number of affected rows int<lenenc>: last inserted index ID int<2>: server status int<2>: alarm count Note: only available in MySQL 4.1 and later versions string<lenenc>: server message (optional) use test; The server returns a packet, and uses tcpdump to monitor it. The hexadecimal log output is 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.@[email protected]... 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 packet is 11, minus int<3>+int<1>4 bytes = 7 bytes, the corresponding hexadecimal is 07; int<3> is 0700 in hexadecimal. 00 indicates the packet content length; int<1> is 01 in hexadecimal, indicating sequence_id; 00 indicates the packet header; 00 indicates the number of affected rows; 00 indicates the last inserted index ID; 0200 indicates the server status; 2.5.2ERR_Packet Indicates that an error has occurred. The specific format is as follows: int<1>: 0xFF ERR header int<2>: error code string[1]: SQL status identifier Note: Only available in MySQL 4.1 and later versions string[5]: SQL status Note: Only available in MySQL 4.1 and later versions string<EOF>: error message 2.5.3EOF_Packet To mark the end of the query execution results: int<1>: EOF value (0xFE) int<2>: Alarm count Note: Available only in MySQL 4.1 and later versions int<2>: Status flag Note: Available only in MySQL 4.1 and later versions 2.5.4Result Set When the client sends a query request, if there is no error, the server will return a result set to the client. There are five parts in total: Result Set Header The number of columns of returned data Field The column information of returned data (multiple) EOF Column End Row Data Row Data (multiple) EOF End of data 2.5.4.1Result Set Header Length-Encoded Integer Number of Field structures Length-Encoded Integer Additional information 2.5.4.2Field LengthEncodedString Directory nameLengthEncodedString Database nameLengthEncodedString Table nameLengthEncodedString Table original nameLengthEncodedString Column (field) nameLengthEncodedString Column (field) original nameint<1> Fill valueint<2> Character encodingint<4> Column (field) lengthint<1> Column (field) typeint<2> Column (field) flagint<1> Integer value precisionint<2> Fill value (0x00) LengthEncodedString default value 2.5.4.3EOF Refer to 2.5.3EOF_Packet 2.5.4.4Row Data LengthEncodedString field value... multiple field values Example 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 data returned by the server is as follows: mysql> select * from btest; +----+------+---------+ | id | age | name | +----+------+---------+ | 1 | 10 | zhaohui | | 2 | 11 | zhaohui | +----+------+---------+ The packets returned by the server are monitored using tcpdump, and the hexadecimal log output is 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@[email protected].. 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 indicates 3 fields; 03 6465 66 corresponds to the default value def of the directory name, 03 indicates the number of bytes following is 3; 04 7465 7374 The corresponding value is the database name test; 0562 7465 7374 corresponds to the data table name btest; 0562 7465 7374 corresponds to the original name of the data table btest; 0269 64 corresponds to the field name id; 02 6964 corresponds to the original name of the column (field) id; 0c3f 00 corresponds to the fill value and character encoding; 14 0000 00 corresponds to 20 in decimal, indicating the length of the column (field); 08 0342 00 indicates the column (field) type, identifier, and integer value precision respectively; 00002 bytes are the fill value; 00 is the default value, indicating empty; The subsequent age and name fields are the same as above and will not be repeated; The character 1 corresponding to the 0131 type LengthEncodedString is the value of id; the character 10 corresponding to the 0231 30 type LengthEncodedString is the value of age; the character zhaohui corresponding to the 07 7a68 616f 6875 69 type LengthEncodedString is the value of name; The above is all the content related to MYSQL communication protocol that we have compiled for you. If you have any questions when learning, you can leave us a message. Thank you for your support of 123WORDPRESS.COM. |
<<: How to make your browser talk with JavaScript
>>: Install Python 3.6 on Linux and avoid pitfalls
Recommend
Summary of MySQL lock related knowledge
Locks in MySQL Locks are a means to resolve resou...
How to create a stylish web page design (graphic tutorial)
"Grand" are probably the two words that ...
GDB debugging MySQL actual combat source code compilation and installation
Download source code git clone https://github.com...
Display ellipsis effect when table cell content exceeds (implementation code)
illustrate In front-end development, you often en...
Graphical explanation of the solutions for front-end processing of small icons
Preface Before starting this article, let’s do a ...
What does href=# mean in a link?
Links to the current page. －－－－－－－－－－－－－－－－－－－ Com...
How to fix some content in a fixed position when scrolling HTML page
This article mainly introduces how some content i...
This article will help you get started and understand the basic operations of Jquery
Table of contents 1. Steps to use Jquery: (1) Imp...
IIS7 IIS8 http automatically jumps to HTTPS (port 80 jumps to port 443)
IIS7 needs to confirm whether the "URL REWRI...
Sample code for implementing 3D rotation effect using pure CSS
Mainly use the preserve-3d and perspective proper...
Encapsulate a simplest ErrorBoundary component to handle react exceptions
Preface Starting from React 16, the concept of Er...
MySQL ID starts to increase from 1 to quickly solve the problem of discontinuous ID
mysql id starts from 1 and increases automaticall...
Write a formal blog using XHTML CSS
The full name of Blog should be Web log, which mea...
Example code for implementing dynamic skinning with vue+element
Sometimes the theme of a project cannot satisfy e...
React mouse multi-selection function configuration method
Generally, lists have selection functions, and si...