小议MySQL登录过程

建立mysql连接

mysql -uroot  -h172.16.2.39 -p

image.png

138、140、142这三个数据包是常规操作:TCP协议的三次握手，这个无须赘述。重点看一下MySQL协议的数据包
先看第一个143可以看到TCP三次握手完成后，
在Server Greeting中，我们可以看到我们比较关注的几个报文字段：

1. 协议版本 int<1>
   ：对应值为10
2. 服务器版本 string<NUL>
   ：对应值为5.6.21-70.1-log
3. 线程id int<4>
   : 对应值为：3138329
4. 20个随机字符中的前8个字符 string[8]
   :对应值为*t0-0I`(
5. 服务器状态
   ) int<2>
   ：对应值为：0x00000002
6. 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