mysql协议

Category: MySQLDevelopment MySQL Internals ClientServer Protocol ← Back to MySQL Internals overview page Contents [hide] 1 MySQL Client/Server Protocol o 1.1 Organization o 1.2 Elements o 1.3 The Packet Header o 1.4 Packet Types o 1.5 Handshake Initialization Packet o 1.6 Client Authentication Packet o 1.7 Pass word word文档格式规范word作业纸小票打印word模板word简历模板免费word简历 functions  1.7.1 4.0 and before  1.7.2 4.1 and later o 1.8 Command Packet (Overview) o 1.9 Command Packet (detailed description)  1.9.1 COM_QUIT  1.9.2 COM_INIT_DB  1.9.3 COM_QUERY  1.9.4 COM_FIELD_LIST  1.9.5 COM_CREATE_DB  1.9.6 COM_DROP_DB  1.9.7 COM_REFRESH  1.9.8 COM_SHUTDOWN  1.9.9 COM_STATISTICS  1.9.10 COM_PROCESS_INFO  1.9.11 COM_PROCESS_KILL  1.9.12 COM_DEBUG  1.9.13 COM_PING  1.9.14 COM_CHANGE_USER  1.9.15 COM_BINLOG_DUMP  1.9.16 COM_TABLE_DUMP  1.9.17 COM_REGISTER_SLAVE  1.9.18 COM_PREPARE  1.9.19 COM_EXECUTE  1.9.20 COM_LONG_DATA  1.9.21 COM_CLOSE_STMT  1.9.22 COM_RESET_STMT  1.9.23 COM_SET_OPTION  1.9.24 COM_FETCH_STMT Page 1/36 2012-01-19 o 1.10 Types Of Result Packets o 1.11 OK Packet o 1.12 Error Packet o 1.13 Result Set Header Packet o 1.14 Field Packet o 1.15 EOF Packet o 1.16 Row Data Packet o 1.17 Row Data Packet: Binary (Tentative Description) o 1.18 OK for Prepared Statement Initialization Packet o 1.19 Parameter Packet (Tentative Description) o 1.20 Long Data Packet (Tentative Description) o 1.21 Execute Packet (Tentative Description) o 1.22 Compression o 1.23 Encryption [edit] MySQL Client/Server Protocol [edit] Organization The topic is: the contents of logical packets in MySQL version 5.0 client/server communication. The description is of logical packets. There will be only passing mention of non-logical considerations, such as physical packets, transport, buffering, and compression. If you are interested in those topics, you may wish to consult another document: "MySQL Client - Server Protocol Documentation" in the file net_doc.txt in the internals directory of the mysqldoc MySQL documentation repository. The description is of the version-5.0 protocol at the time of writing. Most of the examples show version-4.1 tests, which is okay because the changes from version-4.1 to version-5.0 were small. A typical description of a packet will include: "Bytes and Names". This is intended as a quick summary of the lengths and identifiers for every field in the packet, in order of appearance. The "Bytes" column contains the length in bytes. The Names column contains names which are taken from the MySQL source code whenever possible. If the version-4.0 and version-4.1 formats differ significantly, we will show both formats. Descriptions for each field. This contains text notes about the usage and possible contents. (If necessary) notes about alternative terms. Naming in this document is not authoritative and you will often see different words used for the same things, in other documents. Page 2/36 2012-01-19 (If necessary) references to program or header files in the MySQL source code. An example of such a reference is: sql/protocol.cc net_store_length() which means "in the sql subdirectory, in the protocol.cc file, the function named net_store_length". An Example. All examples have three columns: -- the field name -- a hexadecimal dump -- an ascii dump, if the field has character data All spaces and carriage returns in the hexadecimal dump are there for formatting purposes only. In the later sections, related to prepared statements, the notes should be considered unreliable and there are no examples. [edit] Elements Null-Terminated String: used for some variable-length character strings. The value '\0' (sometimes written 0x00) denotes the end of the string. Length Coded Binary: a variable-length number. To compute the value of a Length Coded Binary, one must examine the value of its first byte. Value Of # Of Bytes Description First Byte Following ---------- ----------- ----------- 0-250 0 = value of first byte 251 0 column value = NULL only appropriate in a Row Data Packet 252 2 = value of following 16-bit word 253 3 = value of following 24-bit word 254 8 = value of following 64-bit word Thus the length of a Length Coded Binary, including the first byte, will vary from 1 to 9 bytes. The relevant MySQL source program is sql/protocol.cc net_store_length(). All numbers are stored with the least significant byte first. All numbers are unsigned. Length Coded String: a variable-length string. Used instead of Null-Terminated String, especially for character strings which might contain '\0' or might be very long. The first part of a Length Coded String is a Length Coded Binary number (the length); the second part of a Length Coded String is the actual data. An example of Page 3/36 2012-01-19 a short Length Coded String is these three hexadecimal bytes: 02 61 62, which means "length = 2, contents = 'ab'". [edit] The Packet Header Bytes Name ----- ---- 3 Packet Length 1 Packet Number Packet Length: The length, in bytes, of the packet that follows the Packet Header. There may be some special values in the most significant byte. The maximum packet length is (2**24 -1),about 16MB. Packet Number: A serial number which can be used to ensure that all packets are present and in order. The first packet of a client query will have Packet Number = 0 Thus, when a new SQL statement starts, the packet number is re-initialised. The Packet Header will not be shown in the descriptions of packets that follow this section. Think of it as always there. But logically, it "precedes the packet" rather than "is included in the packet". Indeed,if the packet length is equal or greater than (2**24 -1) Bytes, this packet must be split into two or more packets. Alternative terms: Packet Length is also called "packetsize". Packet Number is also called "Packet no". Relevant MySQL Source Code: include/my_global.h int3store() sql/net_serv.cc my_net_write(), net_flush(), net_write_command(), my_net_read() [edit] Packet Types This is what happens in a typical session: The Handshake (when client connects): Server Sends To Client: Handshake Initialisation Packet Page 4/36 2012-01-19 Client Sends To Server: Client Authentication Packet Server Sends To Client: OK Packet, or Error Packet The Commands (for every action the client wants the server to do): Client Sends To Server: Command Packet Server Sends To Client: OK Packet, or Error Packet, or Result Set Packet In the rest of this chapter, you will find a description for each packet type, in separate sections. Alternative terms: The Handshake is also called "client login" or "login procedure" or "connecting". [edit] Handshake Initialization Packet From server to client during initial handshake. The follow is taken from sql/sql_acl.cc: Bytes Name ----- ---- 1 protocol_version n (Null-Terminated String) server_version 4 thread_id 8 scramble_buff 1 (filler) always 0x00 2 server_capabilities 1 server_language 2 server_status 2 server capabilities (two upper bytes) 1 length of the scramble 10 (filler) always 0 n rest of the plugin provided data (at least 12 bytes) 1 \0 byte, terminating the second part of a scramble protocol_version: The server takes this from PROTOCOL_VERSION in /include/mysql_version.h. Example value = 10. Page 5/36 2012-01-19 server_version: The server takes this from MYSQL_SERVER_VERSION in /include/mysql_version.h. Example value = "4.1.1-alpha". thread_number: ID of the server thread for this connection. scramble_buff: The password mechanism uses this. The second part are the last 13 bytes. (See "Password functions" section elsewhere in this document.) server_capabilities: CLIENT_XXX options. The possible flag values at time of writing (taken from include/mysql_com.h): CLIENT_LONG_PASSWORD 1 /* new more secure passwords */ CLIENT_FOUND_ROWS 2 /* Found instead of affected rows */ CLIENT_LONG_FLAG 4 /* Get all column flags */ CLIENT_CONNECT_WITH_DB 8 /* One can specify db on connect */ CLIENT_NO_SCHEMA 16 /* Don't allow database.table.column */ CLIENT_COMPRESS 32 /* Can use compression protocol */ CLIENT_ODBC 64 /* Odbc client */ CLIENT_LOCAL_FILES 128 /* Can use LOAD DATA LOCAL */ CLIENT_IGNORE_SPACE 256 /* Ignore spaces before '(' */ CLIENT_PROTOCOL_41 512 /* New 4.1 protocol */ CLIENT_INTERACTIVE 1024 /* This is an interactive client */ CLIENT_SSL 2048 /* Switch to SSL after handshake */ CLIENT_IGNORE_SIGPIPE 4096 /* IGNORE sigpipes */ CLIENT_TRANSACTIONS 8192 /* Client knows about transactions */ Page 6/36 2012-01-19 CLIENT_RESERVED 16384 /* Old flag for 4.1 protocol */ CLIENT_SECURE_CONNECTION 32768 /* New 4.1 authentication */ CLIENT_MULTI_STATEMENTS 65536 /* Enable/disable multi-stmt support */ CLIENT_MULTI_RESULTS 131072 /* Enable/disable multi-results */ server_language: current server character set number server_status: SERVER_STATUS_xxx flags: e.g. SERVER_STATUS_AUTOCOMMIT Alternative terms: Handshake Initialization Packet is also called "greeting packet". Protocol version is also called "Prot. version". server_version is also called "Server Version String". thread_number is also called "Thread Number". current server charset number is also called "charset_no". scramble_buff is also called "crypt seed". server_status is also called "SERVER_STATUS_xxx flags" or "Server status variables". Example Handshake Initialization Packet Hexadecimal ASCII ----------- ----- protocol_version 0a . server_version 34 2e 31 2e 31 2d 71 6c 4.1.1-al 70 68 61 2d 64 65 62 75 pha-debu 67 00 g. thread_number 01 00 00 00 .... scramble_buff 3a 23 3d 4b 43 4a 2e 43 ........ (filler) 00 . server_capabilities 2c 82 .. server_language 08 . server_status 02 00 .. (filler) 00 00 00 00 00 00 00 00 ........ 00 00 00 00 00 In the example, the server is telling the client that its server_capabilities include CLIENT_MULTI_RESULTS, CLIENT_SSL, CLIENT_COMPRESS, CLIENT_CONNECT_WITH_DB, CLIENT_FOUND_ROWS. The "server_language" (or "charset") corresponds to the character_set_server Page 7/36 2012-01-19 variable in the MySQL server. This number also contains the collation used. Technically this number determines the collation and the character set is implicit for the collation. You can use the following SQL statement to get the cleartext information: mysql> SELECT CHARACTER_SET_NAME, COLLATION_NAME -> FROM INFORMATION_SCHEMA.COLLATIONS -> WHERE ID=8; +--------------------+-------------------+ | CHARACTER_SET_NAME | COLLATION_NAME | +--------------------+-------------------+ | latin1 | latin1_swedish_ci | +--------------------+-------------------+ 1 row in set (0,00 sec) [edit] Client Authentication Packet From client to server during initial handshake. VERSION 4.0 Bytes Name ----- ---- 2 client_flags 3 max_packet_size n (Null-Terminated String) user 8 scramble_buff 1 (filler) always 0x00 VERSION 4.1 Bytes Name ----- ---- 4 client_flags 4 max_packet_size 1 charset_number 23 (filler) always 0x00... n (Null-Terminated String) user n (Length Coded Binary) scramble_buff (1 + x bytes) n (Null-Terminated String) databasename (optional) client_flags: CLIENT_xxx options. The list of possible flag values is in the description of the Handshake Initialisation Packet, for server_capabilities. Page 8/36 2012-01-19 For some of the bits, the server passed "what it's capable of". The client leaves some of the bits on, adds others, and passes back to the server. One important flag is: whether compression is desired. Another interesting one is: CLIENT_CONNECT_WITH_DB, which shows the presence of the optional databasename. max_packet_size: the maximum number of bytes in a packet for the client charset_number: in the same domain as the server_language field that the server passes in the Handshake Initialization packet. user: identification scramble_buff: the password, after encrypting using the scramble_buff contents passed by the server (see "Password functions" section elsewhere in this document) if length is zero, no password was given databasename: name of schema to use initially The scramble_buff and databasename fields are optional. The length-coding byte for the scramble_buff will always be given, even if it's zero. Alternative terms: "Client authentication packet" is sometimes called "client auth response" or "client auth packet" or "login packet". "Scramble_buff" is sometimes called "crypted password". Relevant MySQL Source Code: - On the client side: libmysql/libmysql.c::mysql_real_connect(). Page 9/36 2012-01-19 - On the server side: sql/sql_parse.cc::check_connections() Example Client Authentication Packet Hexadecimal ASCII ----------- ----- client_flags 85 a6 03 00 .... max_packet_size 00 00 00 01 .... charset_number 08 . (filler) 00 00 00 00 00 00 00 00 ........ 00 00 00 00 00 00 00 00 ........ 00 00 00 00 00 00 00 ....... user 70 67 75 6c 75 74 7a 61 pgulutza 6e 00 n. [edit] Password functions The Server Initialization Packet and the Client Authentication Packet both have an 8-byte field, scramble_buff. The value in this field is used for password authentication. Relevant MySQL Source Code: libmysql/password.c, see also comments at start of file. It works thus: [edit] 4.0 and before  The server sends a random string to the client, in scramble_buff.  The client encrypts the scramble_buff value using the hash of a password that the user has entered. This happens in sql/password.c:scramble() function.  The client sends the encrypted scramble_buff value to the server.  The server encrypts the original random string using a value in the mysql database, mysql.user.Password.  The server compares its encrypted random string to what the client sent in scramble_buff.  If they are the same, the password is okay. In this protocol, snooping on the wire doesn't reveal the password. But note the problem - if the client doesn't know the password, but knows a hash of it (as stored in mysql.user.Password) it can connect to the server. In other words, the hash of a password is the real password; if one can get the value of mysql.user.Password - he can connect to the server. [edit] 4.1 and later Page 10/36 2012-01-19 Remember that mysql.user.Password stores SHA1(SHA1(password))  The server sends a random string (scramble) to the client  the client calculates: o stage1_hash = SHA1(password), using the password that the user has entered. o token = SHA1(scramble + SHA1(stage1_hash)) XOR stage1_hash  the client sends the token to the server  the server calculates o stage1_hash' = token XOR SHA1(scramble + mysql.user.Password)  the server compares SHA1(stage1_hash') and mysql.user.Password  If they are the same, the password is okay. (Note SHA1(A+B) is the SHA1 of the concatenation of A with B.) This protocol fixes the flaw of the old one, neither snooping on the wire nor mysql.user.Password are sufficient for a successful connection. But when one has both mysql.user.Password and the intercepted data on the wire, he has enough information to connect. [edit] Command Packet (Overview) From client to server whenever the client wants the server to do something. Bytes Name ----- ---- 1 command n arg command: The most common value is 03 COM_QUERY, because INSERT UPDATE DELETE SELECT etc. have this code. The possible values at time of writing (taken from /include/mysql_com.h for enum_server_command) are: # Name Associated client function - ---- -------------------------- Page 11/36 2012-01-19 0x00 COM_SLEEP (none, this is an internal thread state) 0x01 COM_QUIT mysql_close 0x02 COM_INIT_DB mysql_select_db 0x03 COM_QUERY mysql_real_query 0x04 COM_FIELD_LIST mysql_list_fields 0x05 COM_CREATE_DB mysql_create_db (deprecated) 0x06 COM_DROP_DB mysql_drop_db (deprecated) 0x07 COM_REFRESH mysql_refresh 0x08 COM_SHUTDOWN mysql_shutdown 0x09 COM_STATISTICS mysql_stat 0x0a COM_PROCESS_INFO mysql_list_processes 0x0b COM_CONNECT (none, this is an internal thread state) 0x0c COM_PROCESS_KILL mysql_kill 0x0d COM_DEBUG mysql_dump_debug_info 0x0e COM_PING mysql_ping 0x0f COM_TIME (none, this is an internal thread state) 0x10 COM_DELAYED_INSERT (none, this is an internal thread state) 0x11 COM_CHANGE_USER mysql_change_user 0x12 COM_BINLOG_DUMP sent by the slave IO thread to request a binlog 0x13 COM_TABLE_DUMP LOAD TABLE ... FROM MASTER (deprecated) 0x14 COM_CONNECT_OUT (none, this is an i