- 15 MySQL Client/Server Protocol
- 15.1 Overview
- 15.2 Connection Phase
- 15.3 Authentication Method
- 15.4 Compression
- 15.5 SSL
- 15.6 Text Protocol
- 15.7 Prepared Statements
- 15.8 Stored Procedures
- 15.9 Replication Protocol
- 15.10 Row Based Replication
- 15.11 Semi-Sync Replication
- 15.12 Protocol Examples
- 15.13 Source Code Locations
All the examples here are captured with
$ ngrep -x -q -d lo0 '' 'port 3306'
Taking a look at the packet dump when a mysql-client logs in:
client -> server <connect>
The client initiates the communication by connecting to the server:
server -> client 36 00 00 00 0a 35 2e 35 2e 32 2d 6d 32 00 03 00 6....5.5.2-m2... 00 00 27 75 3e 6f 38 66 79 4e 00 ff f7 08 02 00 ..'u>o8fyN...... 00 00 00 00 00 00 00 00 00 00 00 00 00 57 4d 5d .............WM] 6a 7c 53 68 32 5c 59 2e 73 00 j|Sh2\Y.s.
which responds with a handshake packet which contains the version, some flags and a password challenge.:
client -> server 3a 00 00 01 05 a6 03 00 00 00 00 01 08 00 00 00 :............... 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................ 00 00 00 00 72 6f 6f 74 00 14 cb b5 ea 68 eb 6b ....root.....h.k 3b 03 cb ae fb 9b df 5a cb 0f 6d b5 de fd ;......Z..m...
The client answers with username, some flags and the response to the challenge.:
server -> client 07 00 00 02 00 00 00 02 00 00 00 ...........
As the client provided the right password and the flags are
fine, the server responds with a
OK_Packet.
That closes auth-phase and switches to the command-phase.:
client -> server 21 00 00 00 03 73 65 6c 65 63 74 20 40 40 76 65 !....select @@ve 72 73 69 6f 6e 5f 63 6f 6d 6d 65 6e 74 20 6c 69 rsion_comment li 6d 69 74 20 31 mit 1
The mysql client first checks the version string of the server
and sends a
COM_QUERY
packet.:
server -> client 01 00 00 01 01 27 00 00 02 03 64 65 66 00 00 00 .....'....def... 11 40 40 76 65 72 73 69 6f 6e 5f 63 6f 6d 6d 65 .@@version_comme 6e 74 00 0c 08 00 1c 00 00 00 fd 00 00 1f 00 00 nt.............. 05 00 00 03 fe 00 00 02 00 1d 00 00 04 1c 4d 79 ..............My 53 51 4c 20 43 6f 6d 6d 75 6e 69 74 79 20 53 65 SQL Community Se 72 76 65 72 20 28 47 50 4c 29 05 00 00 05 fe 00 rver (GPL)...... 00 02 00 ...
The server responds with a resultset containing the version-string.:
client -> server 0e 00 00 00 03 73 65 6c 65 63 74 20 55 53 45 52 .....select USER 28 29 ()
For the prompt (u ...) the mysql client also asks for the current username.:
server -> client 01 00 00 01 01 1c 00 00 02 03 64 65 66 00 00 00 ..........def... 06 55 53 45 52 28 29 00 0c 08 00 4d 00 00 00 fd .USER()....M.... 01 00 1f 00 00 05 00 00 03 fe 00 00 02 00 0f 00 ................ 00 04 0e 72 6f 6f 74 40 6c 6f 63 61 6c 68 6f 73 ...root@localhos 74 05 00 00 05 fe 00 00 02 00 t.........
which is 'root@localhost' in this example.
A query like SELECT
@@version_comment returns:
01 00 00 01 01|27 00 00 02 03 64 65 66 00 00 00 .....'....def... 11 40 40 76 65 72 73 69 6f 6e 5f 63 6f 6d 6d 65 .@@version_comme 6e 74 00 0c 08 00 1c 00 00 00 fd 00 00 1f 00 00| nt.............. 05 00 00 03 fe 00 00 02 00|1d 00 00 04 1c 4d 79 ..............My 53 51 4c 20 43 6f 6d 6d 75 6e 69 74 79 20 53 65 SQL Community Se 72 76 65 72 20 28 47 50 4c 29|05 00 00 05 fe 00 rver (GPL)...... 00 02 00 ...
-
length =
01 00 00, sequence_id =01column_count =
01(1)
-
length =
27 00 00, sequence_id =02catalog =
03 64 65 66("def")schema =
00("")table =
00("")org_table =
00("")name =
11 40 40 76 65 72 73 69 6f 6e 5f 63 6f 6d 6d 65 6e 74("@@version_comment")org_name =
00( "")filler_1 =
0ccharacter_set =
08 00(latin1_swedish_ci)column_length =
1c 00 00 00(28)column_type =
fd(Protocol::MYSQL_TYPE_VAR_STRING)flags =
00 00decimals =
1f(127)filler_2
00 00
-
length =
05 00 00, sequence_id =03fe(EOF indicator)warning_count =
00 00(0)status_flags =
02 00(Protocol::StatusFlags=SERVER_STATUS_AUTOCOMMIT)
-
length =
05 00 00, sequence_id =041c 4d 79 53 51 4c 20 43 6f 6d 6d 75 6e 69 74 79 20 53 65 72 76 65 72 20 28 47 50 4c 29(length = 28, string = "MySQL Community Server (GPL)")
-
length =
05 00 00, sequence_id =05fe(EOF indicator)warning_count =
00 00(0)status_flags =
02 00(Protocol::StatusFlags=SERVER_STATUS_AUTOCOMMIT)
EXPLAIN SELECT * FROM dual;
results in
01 00 00 01 0a 18 00 00 02 03 64 65 66 00 00 00 ..........def... 02 69 64 00 0c 3f 00 03 00 00 00 08 a1 00 00 00 .id..?.......... 00 21 00 00 03 03 64 65 66 00 00 00 0b 73 65 6c .!....def....sel 65 63 74 5f 74 79 70 65 00 0c 08 00 13 00 00 00 ect_type........ fd 01 00 1f 00 00 1b 00 00 04 03 64 65 66 00 00 ...........def.. 00 05 74 61 62 6c 65 00 0c 08 00 40 00 00 00 fd ..table....@.... 00 00 1f 00 00 1a 00 00 05 03 64 65 66 00 00 00 ..........def... 04 74 79 70 65 00 0c 08 00 0a 00 00 00 fd 00 00 .type........... 1f 00 00 23 00 00 06 03 64 65 66 00 00 00 0d 70 ...#....def....p 6f 73 73 69 62 6c 65 5f 6b 65 79 73 00 0c 08 00 ossible_keys.... 00 10 00 00 fd 00 00 1f 00 00 19 00 00 07 03 64 ...............d 65 66 00 00 00 03 6b 65 79 00 0c 08 00 40 00 00 ef....key....@.. 00 fd 00 00 1f 00 00 1d 00 00 08 03 64 65 66 00 ............def. 00 00 07 6b 65 79 5f 6c 65 6e 00 0c 08 00 00 10 ...key_len...... 00 00 fd 00 00 1f 00 00 19 00 00 09 03 64 65 66 .............def 00 00 00 03 72 65 66 00 0c 08 00 00 04 00 00 fd ....ref......... 00 00 1f 00 00 1a 00 00 0a 03 64 65 66 00 00 00 ..........def... 04 72 6f 77 73 00 0c 3f 00 0a 00 00 00 08 a0 00 .rows..?........ 00 00 00 1b 00 00 0b 03 64 65 66 00 00 00 05 45 ........def....E 78 74 72 61 00 0c 08 00 ff 00 00 00 fd 01 00 1f xtra............ 00 00 05 00 00 0c fe 00 00 02 00 17 00 00 0d ff ................ 48 04 23 48 59 30 30 30 4e 6f 20 74 61 62 6c 65 H.#HY000No table 73 20 75 73 65 64 s used
See how after the column definitions a
ERR_Packet
is sent:
17 00 00 0d ff ................ 48 04 23 48 59 30 30 30 4e 6f 20 74 61 62 6c 65 H.#HY000No table 73 20 75 73 65 64 s used
$ mysql --default-auth=mysql_native_password ## against a mysql_old_password user
50 00 00 00 0a 35 2e 36 2e 34 2d 6d 37 2d 6c 6f P....5.6.4-m7-lo
67 00 5d 0a 00 00 66 5c 7b 74 55 2d 7b 4b 00 ff g.]...f\{tU-{K..
ff 08 02 00 0f c0 15 00 00 00 00 00 00 00 00 00 ................
00 40 42 68 66 48 74 2f 2d 34 5e 5a 2c 00 6d 79 .@BhfHt/-4^Z,.my
73 71 6c 5f 6e 61 74 69 76 65 5f 70 61 73 73 77 sql_native_passw
6f 72 64 00 ord.
Handshake
Response Packet announces client wants user
old with
mysql_native_password:
4f 00 00 01 85 a6 0f 00 00 00 00 01 08 00 00 00 O............... 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................ 00 00 00 00 6f 6c 64 00 14 1c eb bb 3a 53 97 c6 ....old.....:S.. 0d 80 b0 62 75 ea 71 24 a2 6c cd 53 81 6d 79 73 ...bu.q$.l.S.mys 71 6c 5f 6e 61 74 69 76 65 5f 70 61 73 73 77 6f ql_native_passwo 72 64 00 rd.
server checks the account settings and sees mysql_old_password and asks the client to switch:
29 00 00 02 fe 6d 79 73 71 6c 5f 6f 6c 64 5f 70 )....mysql_old_p
61 73 73 77 6f 72 64 00 66 5c 7b 74 55 2d 7b 4b assword.f\{tU-{K
40 42 68 66 48 74 2f 2d 34 5e 5a 2c 00 @BhfHt/-4^Z,.
client reprocesses the password and sends it according to the mysql_old_password:
09 00 00 03 4e 55 4c 52 40 5e 46 54 00 ....NULR@^FT.
server is happy and sends a
OK_Packet:
07 00 00 04 00 00 00 02 00 00 00 ...........
$ mysql --default-auth=mysql_old_password ## against a mysql_native_password user
50 00 00 00 0a 35 2e 36 2e 34 2d 6d 37 2d 6c 6f P....5.6.4-m7-lo 67 00 5b 0a 00 00 6c 2e 3e 2f 6c 55 44 36 00 ff g.[...l.>/lUD6.. ff 08 02 00 0f c0 15 00 00 00 00 00 00 00 00 00 ................ 00 22 43 6b 74 24 52 53 29 71 7d 44 77 00 6d 79 ."Ckt$RS)q}Dw.my 73 71 6c 5f 6e 61 74 69 76 65 5f 70 61 73 73 77 sql_native_passw 6f 72 64 00 ord.
Handshake
Response Packet announces client wants user
root with
mysql_old_password:
39 00 00 01 85 a6 0f 00 00 00 00 01 08 00 00 00 9............... 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................ 00 00 00 00 72 6f 6f 74 00 00 6d 79 73 71 6c 5f ....root..mysql_ 6f 6c 64 5f 70 61 73 73 77 6f 72 64 00 old_password.
server checks the account settings and sees mysql_native_password and asks the client to switch:
2c 00 00 02 fe 6d 79 73 71 6c 5f 6e 61 74 69 76 ,....mysql_nativ 65 5f 70 61 73 73 77 6f 72 64 00 6c 2e 3e 2f 6c e_password.l.>/l 55 44 36 22 43 6b 74 24 52 53 29 71 7d 44 77 00 UD6"Ckt$RS)q}Dw.
client reprocesses the password and sends it according to the mysql_native_password:
14 00 00 03 f4 17 96 1f 79 f3 ac 10 0b da a6 b3 ........y....... b5 c2 0e ab 59 85 ff b8 ....Y...
server is happy and sends a
OK_Packet:
07 00 00 04 00 00 00 02 00 00 00 ...........
Examples for
Authentication::SHA256
-
set
--default-authentication-plugin=sha256_passwordfor the MySQL server:46 00 00 00 0a 35 2e 36 2e 37 2d 6c 6f 67 00 02 F....5.6.7-log.. 00 00 00 01 2d 4f 1d 32 56 16 4c 00 ff f7 08 02 ....-O.2V.L..... 00 3f 80 14 00 00 00 00 00 00 00 00 00 00 63 07 .?............c. 35 39 35 15 5c 5f 27 08 3a 27 73 68 61 32 35 36 595.\_'.:'sha256 5f 70 61 73 73 77 6f 72 64 00 _password.
-
set
--default-auth=sha256_passwordfor the MySQL client, provide it with the servers public-key with--server-public-key=...and login with a user whose account usessha256_password:1d 01 00 01 85 a6 3f 00 00 00 00 01 21 00 00 00 ......?.....!... 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................ 00 00 00 00 73 68 61 32 35 36 00 80 a2 77 34 40 ....sha256...w4@ 7e 15 37 ea 41 03 e9 d3 eb ac 66 1e cb 1a b1 79 ~.7.A.....f....y ed 16 49 e6 94 e7 77 c5 f8 9e 3b 94 4f b0 d4 4d ..I...w...;.O..M 3e 7d 63 4b 11 43 4e 12 9c 70 8d 4f 55 b0 f9 93 >}cK.CN..p.OU... 5f 19 70 36 ea ca ac 10 3d 95 43 95 b8 cb 4b 3e _.p6....=.C...K> 93 cb 46 2c 0f 38 1b 0e 2e ee cd 06 6b 34 0d 2f ..F,.8......k4./ 8a ef 98 1b 0a e1 58 2f bf 93 24 ea 1d ba 68 04 ......X/..$...h. cd a9 c0 01 12 21 f8 f9 4a e2 e4 62 2b c7 99 98 .....!..J..b+... 91 03 d3 f5 54 7a 27 97 9c 2e b7 37 73 68 61 32 ....Tz'....7sha2 35 36 5f 70 61 73 73 77 6f 72 64 00 64 03 5f 6f 56_password.d._o 73 05 4c 69 6e 75 78 0c 5f 63 6c 69 65 6e 74 5f s.Linux._client_ 6e 61 6d 65 08 6c 69 62 6d 79 73 71 6c 04 5f 70 name.libmysql._p 69 64 04 34 32 36 30 0f 5f 63 6c 69 65 6e 74 5f id.4260._client_ 76 65 72 73 69 6f 6e 05 35 2e 36 2e 37 09 5f 70 version.5.6.7._p 6c 61 74 66 6f 72 6d 06 78 38 36 5f 36 34 0c 70 latform.x86_64.p 72 6f 67 72 61 6d 5f 6e 61 6d 65 05 6d 79 73 71 rogram_name.mysq 6c l
-
get a
OK_Packetback:07 00 00 02 00 00 00 02 00 00 00 ...........
-
set
--default-authentication-plugin=sha256_passwordfor the MySQL server:46 00 00 00 0a 35 2e 36 2e 37 2d 6c 6f 67 00 04 F....5.6.7-log.. 00 00 00 60 4a 57 38 26 2d 03 72 00 ff f7 08 02 ...`JW8&-.r..... 00 3f 80 14 00 00 00 00 00 00 00 00 00 00 62 53 .?............bS 42 4f 3a 09 15 5c 7b 17 27 39{73 68 61 32 35 36 BO:..\{.'9sha256 5f 70 61 73 73 77 6f 72 64 00} _password. -
set
--default-auth=sha256_passwordfor the MySQL client, don't provide it with the servers public-key and login with a user whose account usessha256_password:9f 00 00 01 85 a6 3f 00 00 00 00 01 21 00 00 00 ......?.....!... 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................ 00 00 00 00 73 68 61 32 35 36 00{01 01 73 68 61 ....sha256...sha 32 35 36 5f 70 61 73 73 77 6f 72 64 00}65 03 5f 256_password.e._ 6f 73 05 4c 69 6e 75 78 0c 5f 63 6c 69 65 6e 74 os.Linux._client 5f 6e 61 6d 65 08 6c 69 62 6d 79 73 71 6c 04 5f _name.libmysql._ 70 69 64 05 31 36 30 38 30 0f 5f 63 6c 69 65 6e pid.16080._clien 74 5f 76 65 72 73 69 6f 6e 05 35 2e 36 2e 37 09 t_version.5.6.7. 5f 70 6c 61 74 66 6f 72 6d 06 78 38 36 5f 36 34 _platform.x86_64 0c 70 72 6f 67 72 61 6d 5f 6e 61 6d 65 05 6d 79 .program_name.my 73 71 6c sql -
server sends a
Protocol::AuthMoreDatathe public key:11 01 00 02 01 2d 2d 2d 2d 2d 42 45 47 49 4e 20 .....-----BEGIN 50 55 42 4c 49 43 20 4b 45 59 2d 2d 2d 2d 2d 0a PUBLIC KEY-----. 4d 49 47 66 4d 41 30 47 43 53 71 47 53 49 62 33 MIGfMA0GCSqGSIb3 44 51 45 42 41 51 55 41 41 34 47 4e 41 44 43 42 DQEBAQUAA4GNADCB 69 51 4b 42 67 51 43 33 65 57 55 56 70 71 48 38 iQKBgQC3eWUVpqH8 38 51 33 54 55 4a 34 48 44 41 48 64 37 65 65 7a 8Q3TUJ4HDAHd7eez 0a 65 6e 51 6d 32 64 57 36 76 44 4e 38 7a 5a 64 .enQm2dW6vDN8zZd 31 30 68 46 31 76 66 46 59 30 48 52 49 34 70 32 10hF1vfFY0HRI4p2 4b 32 68 53 65 36 70 35 4c 30 75 64 73 76 5a 46 K2hSe6p5L0udsvZF 65 35 68 34 4d 30 2f 4e 65 4f 36 41 54 5a 5a 56 e5h4M0/NeO6ATZZV 34 0a 57 72 59 7a 4e 4b 37 34 72 78 59 34 52 32 4.WrYzNK74rxY4R2 6b 46 39 47 7a 47 55 4c 67 74 50 69 54 71 75 67 kF9GzGULgtPiTqug 48 64 77 44 4d 56 57 6c 73 52 76 57 31 47 76 39 HdwDMVWlsRvW1Gv9 53 35 4e 6f 6c 77 48 42 69 59 6c 31 45 75 6d 62 S5NolwHBiYl1Eumb 2f 4f 0a 67 2f 74 4d 65 77 57 30 32 72 33 6b 58 /O.g/tMewW02r3kX 45 58 77 64 51 49 44 41 51 41 42 0a 2d 2d 2d 2d EXwdQIDAQAB.---- 2d 45 4e 44 20 50 55 42 4c 49 43 20 4b 45 59 2d -END PUBLIC KEY- 2d 2d 2d 2d 0a ----.
-
client sends back the encrypted key:
80 00 00 03 57 4a bf 0c 19 ee f9 83 31 b7 42 28 ....WJ......1.B( a7 ea 95 38 e6 79 94 ee 2c 87 7d c2 09 99 a9 47 ...8.y..,.}....G 3a 45 2d 75 74 97 42 8d 3b 2c 5b c4 9f 90 de 95 :E-ut.B.;,[..... e2 52 67 63 ec 4c fc f8 42 eb 9e b7 7e 8d d5 0c .Rgc.L..B...~... 6a a0 46 1a 2e db f9 ee 2c 65 45 ca 7e 8b 28 76 j.F.....,eE.~.(v 0c 2f 62 65 5d 53 d7 20 d0 df 21 6f 85 03 8e 69 ./be]S. ..!o...i 98 1c 27 e9 c0 70 5c 61 c8 84 3c e0 04 44 0f ac ..'..p\a..<..D.. 4a 8c 96 39 d1 48 d7 d8 78 75 7a 69 c8 e5 67 e4 J..9.H..xuzi..g. 91 70 8c 6c .p.l
-
server is happy and sends back the
OK_Packet:07 00 00 04 00 00 00 02 00 00 00 ...........
If the servers default auth method doesn't match the clients or accounts auth method a extra round is needed as usual. The client indicates this by sending a empty password.
-
server uses
mysql_native_passwordas default:4d 00 00 00 0a 35 2e 36 2e 37 2d 6c 6f 67 00 01 M....5.6.7-log.. 00 00 00 51 5f 5a 73 4c 21 3c 45 00 ff f7 08 02 ...Q_ZsL!<E..... 00 3f 80 15 00 00 00 00 00 00 00 00 00 00 6e 2c .?............n, 6d 26 4a 50 5f 43 71 33 3b 6d 00 6d 79 73 71 6c m&JP_Cq3;m.mysql 5f 6e 61 74 69 76 65 5f 70 61 73 73 77 6f 72 64 _native_password 00 .
-
client wants to login with
sha256_password, but doesn't have a matching scramble in the initial packet above and sends a empty password:9e 00 00 01 85 a6 3f 00 00 00 00 01 21 00 00 00 ......?.....!... 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................ 00 00 00 00 73 68 61 32 35 36 00{00}73 68 61 32 ....sha256..sha2 35 36 5f 70 61 73 73 77 6f 72 64 00 65 03 5f 6f 56_password.e._o 73 05 4c 69 6e 75 78 0c 5f 63 6c 69 65 6e 74 5f s.Linux._client_ 6e 61 6d 65 08 6c 69 62 6d 79 73 71 6c 04 5f 70 name.libmysql._p 69 64 05 32 34 34 39 37 0f 5f 63 6c 69 65 6e 74 id.24497._client 5f 76 65 72 73 69 6f 6e 05 35 2e 36 2e 37 09 5f _version.5.6.7._ 70 6c 61 74 66 6f 72 6d 06 78 38 36 5f 36 34 0c platform.x86_64. 70 72 6f 67 72 61 6d 5f 6e 61 6d 65 05 6d 79 73 program_name.mys 71 6c ql -
server sends
Protocol::AuthSwitchRequestwith the proper scramble forsha256_password25 00 00 02 fe 73 68 61 32 35 36 5f 70 61 73 73 %....sha256_pass 77 6f 72 64 00{4e 2c 65 5e 2f 13 3f 40 5f 25 26 word.N,e^/.?@_%& 44 65 27 01 13 05 45 45 04} De'...EE. -
client needs the public key and asks for it:
01 00 00 03{01} ..... -
server sends public key:
a7 00 00 04 01 2d 2d 2d 2d 2d 42 45 47 49 4e 20 .....-----BEGIN 50 55 42 4c 49 43 20 4b 45 59 2d 2d 2d 2d 2d 0a PUBLIC KEY-----. 4d 46 41 77 44 51 59 4a 4b 6f 5a 49 68 76 63 4e MFAwDQYJKoZIhvcN 41 51 45 42 42 51 41 44 50 77 41 77 50 41 49 31 AQEBBQADPwAwPAI1 41 57 74 75 73 79 2b 6b 38 33 50 33 6f 6f 79 65 AWtusy+k83P3ooye 42 59 6a 77 58 4b 55 66 57 47 42 72 4f 65 44 33 BYjwXKUfWGBrOeD3 0a 76 4c 5a 62 68 58 4b 46 33 6c 67 53 66 75 2f .vLZbhXKF3lgSfu/ 6b 34 2b 79 77 4c 43 6e 36 46 45 77 30 52 31 76 k4+ywLCn6FEw0R1v 34 64 74 70 6e 34 58 38 43 41 77 45 41 41 51 3d 4dtpn4X8CAwEAAQ= 3d 0a 2d 2d 2d 2d 2d 45 4e 44 20 50 55 42 4c 49 =.-----END PUBLI 43 20 4b 45 59 2d 2d 2d 2d 2d 0a C KEY-----.
-
client XORs and RSA encrypts its password and sends it back:
35 00 00 05 00 7e 54 3f ae ca 70 a3 b0 2f b0 c2 5....~T?..p../.. e7 8a 18 29 69 c1 86 8c f8 e1 79 c3 45 4d d6 95 ...)i.....y.EM.. fd 47 9c cb b3 f7 da 31 a3 c1 8d 22 fa e4 aa 7c .G.....1..."...| a0 1c e6 8f e6 9e 9e 1a 31 ........1
-
server sends back
OK_Packet:07 00 00 06 00 00 00 02 00 00 00 ...........
