mysql登录过程_小议MySQL登录过程

建立mysql连接

mysql -uroot -h172.16.2.39 -p

image.png

138、140、142这三个数据包是常规操作:TCP协议的三次握手，这个无须赘述。重点看一下MySQL协议的数据包

先看第一个143可以看到TCP三次握手完成后，

在Server Greeting中，我们可以看到我们比较关注的几个报文字段：

协议版本 int<1>

：对应值为10

服务器版本 string

：对应值为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" 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协议，还有需要更多需要理解的地方。欢迎各位拍砖。

参考

附上报文详情：

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