MySQL协议分析

Packet name	Server Greeting Pocket
	Name	Length(Bytes)			Description
	Packet length	3			Packet body length stored with the low byte first.
	Packet number	1			Packet sequence number. The sequence numbers are reset with each new command.
	Protocol	1			Protocol version number.
	Version	End with ‘/0’			Zero-terminated server version string.
	Thread id	4			Internal MySQL ID of the thread that is handling this connection. Low byte first.
	Salt	9			The first 8 bytes of the 20-byte random seed string. At the end is a terminating zero.
	Server capabilities	2			Server capabilities bit mask with the low byte first.
	Character set	1			Default character set code.
	Server status	2			The server status bit mask with the low byte first.
	Unused	13			Reserved for future use. Currently zeroed out.
	Salt	13			The rest of the random seed string terminated with a zero byte.
	Examples
	0000   41 00 00 00 0a 35 2e 30 2e 32 32 2d 63 6f 6d 6d  A....5.0.22-comm 0010   75 6e 69 74 79 2d 6e 74 00 01 00 00 00 7e 37 33  unity-nt.....~73 0020   3a 69 25 78 4f 00 2c a2 08 02 00 00 00 00 00 00  :i%xO.,......... 0030   00 00 00 00 00 00 00 00 61 7c 69 78 42 7b 6a 21  ........a|ixB{j! 0040   61 49 29 2d 00                                   aI)-.
	Packet name			Login Packet
	Name			Length(Bytes)	Description
	Packet length			3	Packet body length stored with the low byte first.
	Packet number			1	Packet sequence number. The sequence numbers are reset with each new command.
	Client Capabilities			2	Protocol capabilities bit mask of the client, low-byte first.
	Extended Client Capabilities			2
	Max packet			4	Maximum packet length that the client is willing to send or receive.
	Character set			1	Default character set code.
	Unknown			23	Reserved space, currently zeroed out.
	Username			End with ‘/0’	Zero-terminated username.
	SHA1			1	Then the length of the SHA1 encrypted password (decimal 20).
	Password			20	The SHA1 encrypted password value (20 bytes),
	Initial database			End with ‘/0’	Zeroterminated initial database name, optionally .
	Examples
	0000   3a 00 00 01 85 a6 03 00 00 00 00 01 08 00 00 00  :............... 0010   00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  ................ 0020   00 00 00 00 72 6f 6f 74 00 14 a5 4a b4 f1 35 b8  ....root...J..5. 0030   2a 0c a5 c8 2a 80 65 60 e1 39 e8 56 5e 10        *...*.e`.9.V^.   0000   3e 00 00 01 85 a6 03 00 00 00 00 01 08 00 00 00  >............... 0010   00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  ................ 0020   00 00 00 00 55 53 45 52 4e 41 4d 45 00 14 d0 6d  ....USERNAME...m 0030   46 48 de fc 0f c4 9a d4 8b 75 5f 05 9b af a1 62  FH.......u_....b 0040   af b3                                            ..
	Packet name		Commands Packet
	Name		Length(Bytes)		Description
	Packet length		3		Packet body length stored with the low byte first.
	Packet number		1		Packet sequence number. The sequence numbers are reset with each new command.
	Command		1		Command code.
	Parameter		Packet length -1		The argument of the command, if present.
	Examples
	0000   0f 00 00 00 03 73 68 6f 77 20 64 61 74 61 62 61  .....show databa 0010   73 65 73                                          ses   0000   05 00 00 00 02 6d 79 64 62                       .....mydb   0000   16 00 00 00 03 73 65 6c 65 63 74 20 2a 20 66 72  .....select * fr 0010   6f 6d 20 73 74 75 64 65 6e 74                    om student   0000   00 50 56 c0 00 08 00 0c 29 f7 81 fd 08 00 45 00  .PV.....).....E. 0010   01 46 07 3c 40 00 80 06 d0 a3 c0 a8 50 80 c0 a8  .F.<@.......P... 0020   50 01 04 13 0c ea 80 a2 4a 18 24 80 7a be 50 18  P.......J.$.z.P. 0030   f 8 0c b4 87 00 00 1a 01 00 00 03 73 65 6c 65 63  ...........selec 0040   74 20 31 32 33 34 35 36 37 38 39 30 31 32 33 34  t 12345678901234 0050   35 36 31 32 33 34 35 36 37 38 39 30 31 32 33 34  5612345678901234 0060   35 36 31 32 33 34 35 36 37 38 39 30 31 32 33 34  5612345678901234 0070   35 36 31 32 33 34 35 36 37 38 39 30 31 32 33 34  5612345678901234 0080   35 36 31 32 33 34 35 36 37 38 39 30 31 32 33 34  5612345678901234 0090   35 36 31 32 33 34 35 36 37 38 39 30 31 32 33 34  5612345678901234 00a 0   35 36 31 32 33 34 35 36 37 38 39 30 31 32 33 34   5612345678901234 00b0   35 36 31 32 33 34 35 36 37 38 39 30 31 32 33 34  5612345678901234 00c 0   35 36 31 32 33 34 35 36 37 38 39 30 31 32 33 34  5612345678901234 00d0   35 36 31 32 33 34 35 36 37 38 39 30 31 32 33 34  5612345678901234 00e0   35 36 31 32 33 34 35 36 37 38 39 30 31 32 33 34  5612345678901234 00f 0   35 36 31 32 33 34 35 36 37 38 39 30 31 32 33 34  5612345678901234 0100   35 36 31 32 33 34 35 36 37 38 39 30 31 32 33 34  5612345678901234 0110   35 36 31 32 33 34 35 36 37 38 39 30 31 32 33 34  5612345678901234 0120   35 36 31 32 33 34 35 36 37 38 39 30 31 32 33 34  5612345678901234 0130   35 36 31 32 33 34 35 36 37 38 39 30 31 32 33 34  5612345678901234 0140   35 36 31 32 33 34 35 36 37 38 39 30 31 32 33 34  5612345678901234 0150   35 36 2b 31                                       56+1   0000   01 00 00 00 01                                   .....   0000   01 00 00 00 0e                                   .....
	Packet name	OK Packet
	Name	Length(Bytes)			Description
	Packet length	3			Packet body length stored with the low byte first.
	Packet number	1			Packet sequence number. The sequence numbers are reset with each new command.
	Marker	1			A byte with the value of 0.
	Affected rows	field length format			The number of records that the query has changed in the field length Format .
	Id	field length format			The value of the generated auto-increment ID for the primary key.
	Server status	2			Server status bit mask, low byte first.
	Warnings	2			The number of warnings the last command has generated.
	Status message	data field format			An optional field for the status message if one is present in the standard data field format
	Examples
	0000   30 00 00 01 00 04 00 22 00 00 00 28 52 6f 77 73  0......"...(Rows 0010   20 6d 61 74 63 68 65 64 3a 20 34 20 20 43 68 61   matched: 4  Cha 0020   6e 67 65 64 3a 20 34 20 20 57 61 72 6e 69 6e 67  nged: 4  Warning 0030   73 3a 20 30                                      s: 0   0000   07 00 00 01 00 00 00 02 00 00 00                 ...........   0000   07 00 00 02 00 00 00 02 00 00 00                  ...........
	Packet name		Error Packet
	Name		Length(Bytes)		Description
	Packet length		3		Packet body length stored with the low byte first.
	Packet number		1		Packet sequence number. The sequence numbers are reset with each new command.
	Marker		1		A byte containing 255.
	Error code		2		The error code. Low byte first.
	#		1		Character ‘ # ’ .
	ODBC/JDBC SQL state		5		The value of the ODBC/JDBC SQL state.
	Error massage		End with ‘/0’		Zero-terminated text of the error message.
	Examples
	0000   21 00 00 01 ff 19 04 23 34 32 30 30 30 55 6e 6b  !......#42000Unk 0010   6e 6f 77 6e 20 64 61 74 61 62 61 73 65 20 27 6d  nown database 'm 0020   79 64 62 32 27                                   ydb2'   0000   b9 00 00 01 ff 28 04 23 34 32 30 30 30 59 6f 75  .....(.#42000You 0010   20 68 61 76 65 20 61 6e 20 65 72 72 6f 72 20 69   have an error i 0020   6e 20 79 6f 75 72 20 53 51 4c 20 73 79 6e 74 61  n your SQL synta 0030   78 3b 20 63 68 65 63 6b 20 74 68 65 20 6d 61 6e  x; check the man 0040   75 61 6c 20 74 68 61 74 20 63 6f 72 72 65 73 70  ual that corresp 0050   6f 6e 64 73 20 74 6f 20 79 6f 75 72 20 4d 79 53  onds to your MyS 0060   51 4c 20 73 65 72 76 65 72 20 76 65 72 73 69 6f  QL server versio 0070   6e 20 66 6f 72 20 74 68 65 20 72 69 67 68 74 20  n for the right 0080   73 79 6e 74 61 78 20 74 6f 20 75 73 65 20 6e 65  syntax to use ne 0090   61 72 20 27 75 70 64 61 74 61 20 73 74 75 64 65  ar 'updata stude 00a 0   6e 74 20 73 65 74 20 73 61 67 65 3d 73 61 67 65  nt set sage=sage 00b0   2d 31 27 20 61 74 20 6c 69 6e 65 20 31           -1' at line 1
	Packet name		EOF Packet
	Name		Length(Bytes)		Description
	Packet length		3		Packet body length stored with the low byte first.
	Packet number		1		Packet sequence number. The sequence numbers are reset with each new command.
Marker			1		Byte with the decimal 254 .
	Warming		2		Number of warnings .
	Server status		2		Server status bit mask .
	Examples
	0000   05 00 00 07 fe 00 00 22 00                       .......".   0000   05 00 00 03 fe 01 00 02 00                       .........
	Packet name		Head Packet
	Name		Length(Bytes)		Description
	Packet length		3		Packet body length stored with the low byte first.
	Packet number		1		Packet sequence number. The sequence numbers are reset with each new command.
	Number of fields		field length format		It indicates the number of fields in the result set.
	Examples
	0000   01 00 00 01 05                                   .....   0000   01 00 00 01 01                                   .....
	Packet name		Field Description Packet
	Name		Length(Bytes)		Description
	Packet length		3		Packet body length stored with the low byte first.
	Packet number		1		Packet sequence number. The sequence numbers are reset with each new command.
	Catalog		4		Data field (see the section “Data Field,” earlier in this chapter) containing the ASCII string def .
	Database name		data field format		Database name of the field in the data field format.
	Table name		data field format		Table name of the field in the data field format.
	Original table name		data field format		Original table name of the field in the data field format.
	Column name		data field format		Column name of the field in the data field format.
	Original column name		data field format		Original column name of the field in the data field format.
	‘0c’		1		Byte containing decimal 12, meaning that 12 bytes of data follow.
	Character set		2		Character set code of the field (low byte first).
	Field length		4		Field length (low byte first).
	Type code of the field		1		Type code of the field
	Flags		2		Bit mask of field option flags (low byte first).
	Precision of field values		1		Decimal-point precision of field values.
	Unused		2		Reserved
	Optional element		data field format		Optional element. If present, contains the default value of the field in the standard field data format.
	Examples
	0000   2e 00 00 02 03 64 65 66 04 6d 79 64 62 07 73 74  .....def.mydb.st 0010   75 64 65 6e 74 07 73 74 75 64 65 6e 74 03 53 6e  udent.student.Sn 0020   6f 03 53 6e 6f 0c 08 00 09 00 00 00 fe 03 50 00  o.Sno.........P. 0030   00 00                                             ..   0000   20 00 00 02 03 64 65 66 00 00 00 0a 44 41 54 41   ....def....DATA 0010   42 41 53 45 28 29 00 0c 08 00 22 00 00 00 fd 00  BASE()...."..... 0020   00 1f 00 00                                      ....   0000   31 00 00 02 03 64 65 66 00 08 53 43 48 45 4d 41  1....def..SCHEMA 0010   54 41 00 08 44 61 74 61 62 61 73 65 0b 53 43 48  TA..Database.SCH 0020   45 4d 41 5f 4e 41 4d 45 0c 08 00 40 00 00 00 fd  EMA_NAME...@.... 0030   01 00 00 00 00                                   .....
	Packet name		Row Data Packet
	Name		Length(Bytes)		Description
	Packet length		3		Packet body length stored with the low byte first.
	Packet number		1		Packet sequence number. The sequence numbers are reset with each new command.
	Row data		field number * data field format		A row data contains n data fields, n equals to field number show in the head packet. Each data fields are in data field format.
	Examples
	0000   13 00 00 04 12 69 6e 66 6f 72 6d 61 74 69 6f 6e  .....information 0010   5f 73 63 68 65 6d 61                              _schema   0000   0d 00 00 08 01 31 02 63 64 02 c5 ae 01 34 02 43  .....1.cd....4.C 0010   53                                               S   0000   15 00 00 09 06 31 32 33 34 35 36 04 b3 c2 b3 c2  .....123456..... 0010   02 c4 d0 02 31 38 02 49 53                       ....18.IS

 

The session between a client and a server begins with an authenticating handshake. Before it can begin, the server checks whether the host that the client is connecting from is even allowed to connect to this server. If it is not, an error message packet is sent to the client notifying it that the host is not allowed to connect. In the case of successful host verification, the server sends a greeting packet . The client responds with a credentials packet (login packet ). Once the server receives the credentials packet, it verifies the information. From this point, it can respond in three different ways:

? If the check succeeds, the standard OK response packet is sent

? If the credentials did not meet the expectations of the server, the standard error message response is sent.

? The third possibility comes from the need to support the transition from 4.0 to 4.1. In that event, the server sends a special packet with the 1-byte-long body containing decimal 254, which means: “ please send the authentication credentials in the old format. ” The client responds with a packet whose body contains a zeroterminated encrypted password string. The server responds with either OK or a standard error message.

At this point the handshake is complete, and the client begins to issue commands.

Once the authentication is complete, the client begins sending commands to the server using command packets .

Once the server receives a command, it processes it and sends one or more response packets.

Server response packets are divided into four categories: data packets , EOF packets , success report packets (OK packets ), and error packets .

A data packet contains 5 parts: a head packet ; a group of field description packets , one for each field in the field order of the result set; an EOF packet; a group of row data packets , one packet per row; a terminating EOF packet.

 

Data fields (in data field format ) are critical components in many of the server response packets. A data field consists of a length specifier sequence (in field length format ) followed by the actual data value. The length specifier sequence is introduced as follow:

If the value of length does not exceed 251 (i.e., if it can fit into 1 byte without a conflict with the reserved values), the code just stores it in a byte. If it is 251 and higher but fits into 2   bytes, the code prefixes it with the value of 252 and then writes it out in the following 2 bytes. If 2 bytes is not enough, but 4 would do, the code uses 253 for the code, and then occupies the next 4 bytes with the length value. If 4 bytes is not enough, the code uses 254 for the code, and stores it in 8 bytes. It must be noted that all length values following the code are stored with the low byte first. The code 251 has a special meaning. It indicates that there is no length value or data following the code, and the value of the field is the SQL NULL.

 

 

[1]. OReilly. Understanding MySQL Internals. Apr 2007.

 

http://41620935.blog.163.com/blog/static/4982064200926105119642/