小议MySQL登录过程
2018-11-24 本文已影响23人
wu_sphinx
建立mysql连接
mysql -uroot -h172.16.2.39 -p
image.png
138、140、142这三个数据包是常规操作:TCP协议的三次握手，这个无须赘述。重点看一下MySQL协议的数据包
先看第一个143可以看到TCP三次握手完成后，
在Server Greeting中，我们可以看到我们比较关注的几个报文字段：
- 协议版本 int<1>
：对应值为10 - 服务器版本 string<NUL>
：对应值为5.6.21-70.1-log - 线程id int<4>
: 对应值为：3138329 - 20个随机字符中的前8个字符 string[8]
:对应值为*t0-0I`( -
服务器状态
) int<2>
：对应值为：0x00000002 - 20 个随机字符串中的后12个字符：
BTahfIw?r@)L
……（还有其它字段，暂时不用关心）
image.png
红框标识处是两个Salt: *t0-0I`(和 BTahfIw?r@)L 合起来就是:*t0-0I`(BTahfIw?r@)L，正好是20个字符，根据以下公式
SHA1( password ) XOR SHA1( "20-bytes random data from server" <concat> SHA1( SHA1( password ) ) )
计算所得值应该与客户端发起Login Request中的Password值相对应
然后服务端回一个
Response OK, 至此，登录验证算是完成。
后面客户端向服务端发起查询请求：
select @@version_comment limit 1
我们可以先手动执行看下结果：
MySQL [(none)]> select @@version_comment limit 1;
+--------------------------------------------------+
| @@version_comment |
+--------------------------------------------------+
| Percona Server (GPL), Release 70.1, Revision 698 |
+--------------------------------------------------+
image.png
然后再来看服务端的响应
image.png
是能够跟我们手动查询的结果对应上的。
交互步骤总结如下:
image.png
只是简单的登录连接，过程看起来却并不简单，感觉脑细胞已经不够用的，wireshark是个好东西，让我能够初窥门径，只能算是小议了，简单的观察了一下整个过程，关于MySQL协议，还有需要更多需要理解的地方。欢迎各位拍砖。
参考
- MySQL协议
- https://dev.mysql.com/doc/dev/mysql-server/8.0.11/page_protocol_connection_phase_packets_protocol_handshake_v10.html
附上报文详情：
No. Time Source Destination Protocol Length Info
138 2018-11-24 10:30:48.965079 172.22.111.235 172.16.2.39 TCP 78 62336 → 3306 [SYN] Seq=0 Win=65535 Len=0 MSS=1460 WS=64 TSval=901768710 TSecr=0 SACK_PERM=1
Frame 138: 78 bytes on wire (624 bits), 78 bytes captured (624 bits) on interface 0
Ethernet II, Src: Apple_a5:e5:cb (78:4f:43:a5:e5:cb), Dst: HuaweiTe_85:fc:e1 (64:3e:8c:85:fc:e1)
Internet Protocol Version 4, Src: 172.22.111.235, Dst: 172.16.2.39
Transmission Control Protocol, Src Port: 62336, Dst Port: 3306, Seq: 0, Len: 0
No. Time Source Destination Protocol Length Info
140 2018-11-24 10:30:49.028763 172.16.2.39 172.22.111.235 TCP 74 3306 → 62336 [SYN, ACK] Seq=0 Ack=1 Win=14480 Len=0 MSS=1380 SACK_PERM=1 TSval=4063048666 TSecr=901768710 WS=128
Frame 140: 74 bytes on wire (592 bits), 74 bytes captured (592 bits) on interface 0
Ethernet II, Src: HuaweiTe_85:fc:e1 (64:3e:8c:85:fc:e1), Dst: Apple_a5:e5:cb (78:4f:43:a5:e5:cb)
Internet Protocol Version 4, Src: 172.16.2.39, Dst: 172.22.111.235
Transmission Control Protocol, Src Port: 3306, Dst Port: 62336, Seq: 0, Ack: 1, Len: 0
No. Time Source Destination Protocol Length Info
142 2018-11-24 10:30:49.028845 172.22.111.235 172.16.2.39 TCP 66 62336 → 3306 [ACK] Seq=1 Ack=1 Win=131328 Len=0 TSval=901768773 TSecr=4063048666
Frame 142: 66 bytes on wire (528 bits), 66 bytes captured (528 bits) on interface 0
Ethernet II, Src: Apple_a5:e5:cb (78:4f:43:a5:e5:cb), Dst: HuaweiTe_85:fc:e1 (64:3e:8c:85:fc:e1)
Internet Protocol Version 4, Src: 172.22.111.235, Dst: 172.16.2.39
Transmission Control Protocol, Src Port: 62336, Dst Port: 3306, Seq: 1, Ack: 1, Len: 0
No. Time Source Destination Protocol Length Info
143 2018-11-24 10:30:49.092737 172.16.2.39 172.22.111.235 MySQL 153 Server Greeting proto=10 version=5.6.21-70.1-log
Frame 143: 153 bytes on wire (1224 bits), 153 bytes captured (1224 bits) on interface 0
Ethernet II, Src: HuaweiTe_85:fc:e1 (64:3e:8c:85:fc:e1), Dst: Apple_a5:e5:cb (78:4f:43:a5:e5:cb)
Internet Protocol Version 4, Src: 172.16.2.39, Dst: 172.22.111.235
Transmission Control Protocol, Src Port: 3306, Dst Port: 62336, Seq: 1, Ack: 1, Len: 87
MySQL Protocol
Packet Length: 83
Packet Number: 0
Server Greeting
Protocol: 10
Version: 5.6.21-70.1-log
Thread ID: 3138329
Salt: *t0-0I`(
Server Capabilities: 0xf7ff
Server Language: utf8 COLLATE utf8_general_ci (33)
Server Status: 0x0002
Extended Server Capabilities: 0x807f
Authentication Plugin Length: 21
Unused: 00000000000000000000
Salt: BTahfIw?r@)L
Authentication Plugin: mysql_native_password
No. Time Source Destination Protocol Length Info
144 2018-11-24 10:30:49.092792 172.22.111.235 172.16.2.39 TCP 66 62336 → 3306 [ACK] Seq=1 Ack=88 Win=131200 Len=0 TSval=901768836 TSecr=4063048728
Frame 144: 66 bytes on wire (528 bits), 66 bytes captured (528 bits) on interface 0
Ethernet II, Src: Apple_a5:e5:cb (78:4f:43:a5:e5:cb), Dst: HuaweiTe_85:fc:e1 (64:3e:8c:85:fc:e1)
Internet Protocol Version 4, Src: 172.22.111.235, Dst: 172.16.2.39
Transmission Control Protocol, Src Port: 62336, Dst Port: 3306, Seq: 1, Ack: 88, Len: 0
No. Time Source Destination Protocol Length Info
145 2018-11-24 10:30:49.093411 172.22.111.235 172.16.2.39 MySQL 280 Login Request user=root
Frame 145: 280 bytes on wire (2240 bits), 280 bytes captured (2240 bits) on interface 0
Ethernet II, Src: Apple_a5:e5:cb (78:4f:43:a5:e5:cb), Dst: HuaweiTe_85:fc:e1 (64:3e:8c:85:fc:e1)
Internet Protocol Version 4, Src: 172.22.111.235, Dst: 172.16.2.39
Transmission Control Protocol, Src Port: 62336, Dst Port: 3306, Seq: 1, Ack: 88, Len: 214
MySQL Protocol
Packet Length: 210
Packet Number: 1
Login Request
Client Capabilities: 0xa685
Extended Client Capabilities: 0x209f
MAX Packet: 16777216
Charset: utf8 COLLATE utf8_general_ci (33)
Username: root
Password: 2e3efed207fa08e78f3bc253939369b1c758fc3f
Client Auth Plugin: mysql_native_password
Connection Attributes
Connection Attributes length: 129
Connection Attribute - _os: Darwin
Connection Attribute Name Length: 3
Connection Attribute Name: _os
Connection Attribute Name Length: 6
Connection Attribute Value: Darwin
Connection Attribute - _client_name: libmariadb
Connection Attribute Name Length: 12
Connection Attribute Name: _client_name
Connection Attribute Name Length: 10
Connection Attribute Value: libmariadb
Connection Attribute - _pid: 67370
Connection Attribute Name Length: 4
Connection Attribute Name: _pid
Connection Attribute Name Length: 5
Connection Attribute Value: 67370
Connection Attribute - _client_version: 3.0.6
Connection Attribute Name Length: 15
Connection Attribute Name: _client_version
Connection Attribute Name Length: 5
Connection Attribute Value: 3.0.6
Connection Attribute - _platform: x86_64
Connection Attribute Name Length: 9
Connection Attribute Name: _platform
Connection Attribute Name Length: 6
Connection Attribute Value: x86_64
Connection Attribute - program_name: mysql
Connection Attribute Name Length: 12
Connection Attribute Name: program_name
Connection Attribute Name Length: 5
Connection Attribute Value: mysql
Connection Attribute - _server_host: 172.16.2.39
Connection Attribute Name Length: 12
Connection Attribute Name: _server_host
Connection Attribute Name Length: 11
Connection Attribute Value: 172.16.2.39
No. Time Source Destination Protocol Length Info
146 2018-11-24 10:30:49.181597 172.16.2.39 172.22.111.235 TCP 66 3306 → 62336 [ACK] Seq=88 Ack=215 Win=15616 Len=0 TSval=4063048818 TSecr=901768836
Frame 146: 66 bytes on wire (528 bits), 66 bytes captured (528 bits) on interface 0
Ethernet II, Src: HuaweiTe_85:fc:e1 (64:3e:8c:85:fc:e1), Dst: Apple_a5:e5:cb (78:4f:43:a5:e5:cb)
Internet Protocol Version 4, Src: 172.16.2.39, Dst: 172.22.111.235
Transmission Control Protocol, Src Port: 3306, Dst Port: 62336, Seq: 88, Ack: 215, Len: 0
No. Time Source Destination Protocol Length Info
147 2018-11-24 10:30:49.181601 172.16.2.39 172.22.111.235 MySQL 77 Response OK
Frame 147: 77 bytes on wire (616 bits), 77 bytes captured (616 bits) on interface 0
Ethernet II, Src: HuaweiTe_85:fc:e1 (64:3e:8c:85:fc:e1), Dst: Apple_a5:e5:cb (78:4f:43:a5:e5:cb)
Internet Protocol Version 4, Src: 172.16.2.39, Dst: 172.22.111.235
Transmission Control Protocol, Src Port: 3306, Dst Port: 62336, Seq: 88, Ack: 215, Len: 11
MySQL Protocol
Packet Length: 7
Packet Number: 2
Affected Rows: 0
Server Status: 0x0002
Warnings: 0
No. Time Source Destination Protocol Length Info
148 2018-11-24 10:30:49.181673 172.22.111.235 172.16.2.39 TCP 66 62336 → 3306 [ACK] Seq=215 Ack=99 Win=131200 Len=0 TSval=901768924 TSecr=4063048818
Frame 148: 66 bytes on wire (528 bits), 66 bytes captured (528 bits) on interface 0
Ethernet II, Src: Apple_a5:e5:cb (78:4f:43:a5:e5:cb), Dst: HuaweiTe_85:fc:e1 (64:3e:8c:85:fc:e1)
Internet Protocol Version 4, Src: 172.22.111.235, Dst: 172.16.2.39
Transmission Control Protocol, Src Port: 62336, Dst Port: 3306, Seq: 215, Ack: 99, Len: 0
No. Time Source Destination Protocol Length Info
149 2018-11-24 10:30:49.190773 172.22.111.235 172.16.2.39 MySQL 103 Request Query
Frame 149: 103 bytes on wire (824 bits), 103 bytes captured (824 bits) on interface 0
Ethernet II, Src: Apple_a5:e5:cb (78:4f:43:a5:e5:cb), Dst: HuaweiTe_85:fc:e1 (64:3e:8c:85:fc:e1)
Internet Protocol Version 4, Src: 172.22.111.235, Dst: 172.16.2.39
Transmission Control Protocol, Src Port: 62336, Dst Port: 3306, Seq: 215, Ack: 99, Len: 37
MySQL Protocol
Packet Length: 33
Packet Number: 0
Request Command Query
Command: Query (3)
Statement: select @@version_comment limit 1
No. Time Source Destination Protocol Length Info
150 2018-11-24 10:30:49.250008 172.16.2.39 172.22.111.235 MySQL 185 Response
Frame 150: 185 bytes on wire (1480 bits), 185 bytes captured (1480 bits) on interface 0
Ethernet II, Src: HuaweiTe_85:fc:e1 (64:3e:8c:85:fc:e1), Dst: Apple_a5:e5:cb (78:4f:43:a5:e5:cb)
Internet Protocol Version 4, Src: 172.16.2.39, Dst: 172.22.111.235
Transmission Control Protocol, Src Port: 3306, Dst Port: 62336, Seq: 99, Ack: 252, Len: 119
MySQL Protocol
Packet Length: 1
Packet Number: 1
Number of fields: 1
MySQL Protocol
Packet Length: 39
Packet Number: 2
Catalog: def
Database:
Table:
Original table:
Name: @@version_comment
Original name:
Charset number: utf8 COLLATE utf8_general_ci (33)
Length: 144
Type: FIELD_TYPE_VAR_STRING (253)
Flags: 0x0000
Decimals: 31
MySQL Protocol
Packet Length: 5
Packet Number: 3
EOF marker: 254
Warnings: 0
Server Status: 0x0002
MySQL Protocol
Packet Length: 49
Packet Number: 4
text: Percona Server (GPL), Release 70.1, Revision 698
MySQL Protocol
Packet Length: 5
Packet Number: 5
EOF marker: 254
Warnings: 0
Server Status: 0x0002
No. Time Source Destination Protocol Length Info
151 2018-11-24 10:30:49.250079 172.22.111.235 172.16.2.39 TCP 66 62336 → 3306 [ACK] Seq=252 Ack=218 Win=131072 Len=0 TSval=901768992 TSecr=4063048887
Frame 151: 66 bytes on wire (528 bits), 66 bytes captured (528 bits) on interface 0
Ethernet II, Src: Apple_a5:e5:cb (78:4f:43:a5:e5:cb), Dst: HuaweiTe_85:fc:e1 (64:3e:8c:85:fc:e1)
Internet Protocol Version 4, Src: 172.22.111.235, Dst: 172.16.2.39
Transmission Control Protocol, Src Port: 62336, Dst Port: 3306, Seq: 252, Ack: 218, Len: 0
