MySQL 是怎样通讯的？

我们平常使用数据库的场景一般是程序里面代码直接连接使用，然后进行 CRUD 操作。或者使用有 GUI 界面的数据库软件来手动操作数据库，这类软件有 DataGrip、Navicat 等等。平常很少关心它们的底层数据交互是怎么样的，相信你看了这篇文章一定能有大概的了解。本篇文章的代码使用 Go 语言来实现 MySQL 的协议。

协议简介

MySQL 协议一般分为两个阶段，一个是连接阶段，一个是命令阶段。

连接阶段主要是客户端和服务端进行相互认证的阶段，就像我们平常登陆某个网站的一个操作。命令阶段主要是客户端向服务端进行的一些指令的发送，然后服务端处理指令并返回结果的一个过程。

在客户端和服务端发送的数据包中，前 3 个字节表示这个数据包的大小，所以这里就有一个问题，就是它有一个大小的限制，数据包大小不能超过 16777215（2²⁴-1）bytes，也就是 16M 大小（16 进制表示：ff ff ff，刚刚 3 个字节）。这就会有三种情况出现：

• 小于 16M：发送一个数据包就可以了
• 等于 16M：发送两个数据包，第二个包为空包
• 大于 16M：发送多个数据包，每个数据包大小最大为 16M，当最后一个数据包等于 16M 时，再多发送一个空数据包

每个数据包中的第 4 个字节表示这个数据包的序号 ID，这个 ID 在不同阶段会递增，比如在连接阶段，这个 ID 会随着包的数量而递增，当连接阶段完成后进入命令阶段，这个 ID 又会从 0 开始递增，直到这个命令的生命周期结束。

初始握手包

当客户端进行尝试使用 TCP 连接 MySQL 服务端时，服务端就会响应一个初始的握手包，这个握手包有 V9、V10 两个版本。不过现在一般用的都是 V10 版本，如果 MySQL 的版本在 3.21.0 之前，那么服务端响应的是 V9 版本的初始握手包。本篇文章就讲讲现在常用的 V10 版本的初始握手包。

我们可以使用以下代码来尝试连接我们本地的 MySQL 服务:

package main

import "net"

func main()  {
	conn, err := net.Dial("tcp","127.0.0.1:3306")
	if err != nil {
		return
	}
	defer func(conn net.Conn) {
		err := conn.Close()
		if err != nil {}
	}(conn)
}

运行程序后，服务端就会响应一个初始握手包给我们。可以用 Wireshark 来查看 MySQL 服务端返回的数据包：

可以看到前 4 个字节为 16 进制的数据：4e 00 00 00，表示了这个数据包大小为 78 字节，序号 ID 为 0。具体的字段字节大小和描述如下表：

字段名	字节数据长度(byte)	描述
Protocol	1	初始握手包协议版本
Version	直到遇到字节数据为 0 的时候停止	MySQL 服务端版本描述字符串
Thread ID	4	连接 ID
Slat（第一段）	8	用于处理后续客户端的密码加密
filler	1	填充一个字节，默认为 0
Service Capability（Low）	2	服务端能力标志低 2 位字节
Server Language	1	服务端字符编码
Server Status	2	服务端状态
Service Capability（Upper）	2	服务端能力标志高 2 位字节
Authentication Plugin Length	1	身份验证插件长度
Unused	10	预留的 10 个字节数据，默认全部为 0
Slat（第二段）	MAX(13, 身份验证插件长度 - 8)	用于处理后续客户端的密码加密
Authentication Plugin	直到遇到字节数据为 0 的时候停止	身份验证插件

能力标志

上面服务端响应的初始握手包中包含了一个能力标志，这个能力标志一共有 4 个字节来表示，4 个字节一共有 32 个 bit，其中除了最高的 7 个 bit，另外的每一个 bit 都代表着一个能力标志的状态（0 为不支持，1 为支持）：

Server Capabilities: 0xffff
.... .... .... ...1 = Long Password: Set
.... .... .... ..1. = Found Rows: Set
.... .... .... .1.. = Long Column Flags: Set
.... .... .... 1... = Connect With Database: Set
...

官方给出了各个能力的值：

序号	Capability Flags	值（16进制）
1	CLIENT_LONG_PASSWORD	0x1
2	CLIENT_FOUND_ROWS	0x2
10	CLIENT_PROTOCOL_41	0x200
16	CLIENT_SECURE_CONNECTION	0x8000
17	CLIENT_MULTI_STATEMENTS	0x10000
20	CLIENT_PLUGIN_AUTH	0x80000
25	CLIENT_DEPRECATE_EOF	0x1000000

当我们要发送客户端支持的能力标志时，只要把所有支持的能力标志的值相加，然后转换为 4 字节大小的数据。

注意：上面转换的字节数据为小端数据。

字符编码

初始握手包还有一个字节表示了支持的字符编码，后续我们响应的 HandshakeResponse 数据包中也要发送客户端支持的字符编码，相对应的字符编码对应的 ID 如下（部分）：

+--------------------+---------------------+-----+
| CHARACTER_SET_NAME | COLLATION_NAME      | ID  |
+--------------------+---------------------+-----+
| utf8               | utf8_general_ci     |  33 |
| binary             | binary              |  63 |
| utf8mb4            | utf8mb4_0900_ai_ci  | 255 |
+--------------------+---------------------+-----+

客户端握手响应包（HandshakeResponse）

客户端和 MySQL 服务端进行数据交互时，有明文数据交互和 SSL 加密数据交互。不管是明文连接和加密连接，客户端都必须返回 HandshakeResponse 这个数据包给服务端。

这里贴一张 MySQL 官网给出的连接流程图：

这个数据包也有两个版本，一个是 HandshakeResponse41，另一个是 HandshakeResponse320。现在一般都是用 HandshakeResponse41 这个版本的数据包。服务端通过解析客户端发来的 Capability Flags 数据中是否支持 CLIENT_PROTOCOL_41 这个能力，来判断客户端握手响应包的版本。

HandshakeResponse41

4              capability flags, CLIENT_PROTOCOL_41 always set
4              max-packet size
1              character set
string[23]     reserved (all [0])
string[NUL]    username

if capabilities & CLIENT_PLUGIN_AUTH_LENENC_CLIENT_DATA {
  lenenc-int     length of auth-response
  string[n]      auth-response
} else if capabilities & CLIENT_SECURE_CONNECTION {
  1              length of auth-response
  string[n]      auth-response
} else {
  string[NUL]    auth-response
}

if capabilities & CLIENT_CONNECT_WITH_DB {
  string[NUL]    database
}

if capabilities & CLIENT_PLUGIN_AUTH {
  string[NUL]    auth plugin name
}

密码加密方式

名称	Auth Plugin Name	能力标志
旧密码认证	mysql_old_password	不能使用
安全密码认证	mysql_native_password	CLIENT_SECURE_CONNECTION
明文认证	mysql_clear_password	CLIENT_PLUGIN_AUTH
SHA256	sha256_password	CLIENT_PLUGIN_AUTH

现在一般常用的是安全密码认证（mysql_native_password），加密方式如下：

SHA1( password ) XOR SHA1( "20-bytes random data from server" <concat> SHA1( SHA1( password ) ) )

响应数据包

当我们发送响应握手包 HandshakeResponse 后，服务端就会返回一个通用的响应包给我们，这个响应包可以是以下其中一个：

• OK_Packet：第一个字节数据为 0x00
• ERR_Packet：第一个字节数据为 0xff
• EOF_Packet：第一个字节数据为 0xfe

从 MySQL 5.7.5 开始，OK_Packet 包也用于指示 EOF_Packet，并且不推荐使用 EOF_Packet 包。

OK_Packet 格式

int<1>          header              [00] or [fe]
int<lenenc>     affected_rows       受影响行数
int<lenenc>     last_insert_id      最后插入 ID

if capabilities & CLIENT_PROTOCOL_41 {
    int<2>      status_flags        状态标志
    int<2>      warnings            警告数
}

ERR_Packet 格式

int<1>      header          [ff]
int<2>      error_code      错误代码

if capabilities & CLIENT_PROTOCOL_41 {
  string<1>   sql_state_marker
  string<5>   sql_state
}

string<EOF>   error_message

数据包数据类型介绍

数据类型	字节长度
int<1>	1 字节
int<2>	2 字节
int<4>	4 字节
int<lenenc>	见下文
string<lenenc>	见下文
string<NUL>	以 [00] 字节结尾的字符串
string<EOF>	长度从整个数据包长度减去当前位置来计算

int<lenenc>

当要解析这个长度的数据时，它一般开头的第一个字节有 4 种表现形式：

• 第一个字节的值小于 0xfb：代表这个数据就是这一个字节长度，并且第一个字节的值就是对应字段的值
• 第一个字节的值等于 0xfc：代表这个字节往后的两个字节就是这个字段的数据
• 第一个字节的值等于 0xfd：字段数据字节一共占用 4 个字节，其中后 3 个字节表示这个字段的数据
• 第一个字节的值等于 0xfe：字段数据字节一共占用 9 个字节，其中后 8 个字节表示这个字段的数据

发送命令

当我们连接成功后，就可以向服务端发送命令了，常用命令：

HEX 值	NAME
01	COM_QUIT
02	COM_INIT_DB
03	COM_QUERY
0e	COM_PING

一般我们用的最多的就是 COM_QUERY 这个命令，像 CRUD 都可以通过这个命令来发送。例如发送一个查询当前数据库的命令：

0f 00 00 00   03 73 68 6f 77 20 64 61 74 61 62 61 73 65 73

其中前 4 个字节代表这个包的大小和序号 ID，03 代表这个命令是 COM_QUERY，后面所有的字节数据都是 show databases 转换 byte 后的字节数据。

结果集

当你发送的 COM_QUERY 命令时，它返回三种数据包的其中一种：

• 当第一个字节数据等于 0x00：返回的是 OK_Packet
• 当第一个字节的数据等于 0xff：返回的是 ERR_Packet
• 当第一个字节的数据不是以上两个值时：返回一个结果集，并且第一个字节的值代表返回结果集中列（columns）的总数

结果集分 3 个部分来读取：

1. 第一个数据包表示返回结果集中列（columns）的总数
2. 然后通过第一个数据包获取的列总数来读取相关列的所有数据包，一列有一个数据包
3. 读完列的所有数据包后，紧接着就是每行数据的数据包了，一个数据包代表一行数据，每个数据包中有所有列的字段值。其中，如果值长度的值为 0xfe 时，则代表这行中这列的数据为 NULL。行数据直到读取到 OK_Packet/EOF_Packet 包出现为止

列数据包格式（ColumnDefinition41）

string<lenenc>      catalog         目录 (固定为 "def")
string<lenenc>      schema          数据库
string<lenenc>      table           虚拟表
string<lenenc>      org_table       源表
string<lenenc>      name            虚拟名称
string<lenenc>      org_name        源名称
2                   character set   字符集
4                   column length   字段的最大长度
1                   type            列类型
2                   flags           标志
1                   decimals        显示的小数位数

行数据包格式

行数据包里面包含了所有列的字段数据，每个列的字段的数据可以通过 string<lenenc> 数据类型的计算方式获得。如果字段长度描述字节的数据等于 0xfe 时，代表这行中这列的数据为 NULL。

代码实现

通过以上的介绍，现在我们可以用代码来实现不用第三方驱动的情况下手动连接 MySQL 服务器，然后发送一条查询 databases 的命令。

完整代码：https://gist.github.com/greycodee/22f98464fece7792a83433a1fba58e2a

连接 MySQL 服务器

type MySQLClient struct {
	conn     net.Conn
	addr     string
	username string
	password string
}

func (m *MySQLClient) init() {
	handshake := m.startConn()
	m.sendHandshakeResponse41(handshake)
}

func (m *MySQLClient) startConn() *HandshakeV10 {
	m.conn, _ = net.Dial("tcp", m.addr)
	initResp := make([]byte, 1024)
	readLen, _ := m.conn.Read(initResp)
	return ReadHandShakeV10(initResp[:readLen])
}

解析初始握手数据包

func ReadHandShakeV10(data []byte) *HandshakeV10 {
	index := 0
	var h = &HandshakeV10{}
	index += 4
	h.ProtocolVersion = int32(data[index])
	index++
	var serverVersion []byte
	for data[index] != 0 {
		serverVersion = append(serverVersion, data[index])
		index++
	}
	h.ServerVersion = string(serverVersion)
	index++
	// ... 省略部分解析代码
	return h
}

发送初始响应数据包

func (m *MySQLClient) sendHandshakeResponse41(serverResp *HandshakeV10) {
	resp := make([]byte, 0)
	resp = append(resp, Int32ToBytesOfLittle(19833351)...)
	resp = append(resp, Int32ToBytesOfLittle(16777215)...)
	resp = append(resp, 33)
	reserved := make([]byte, 23)
	resp = append(resp, reserved...)
	resp = append(resp, []byte(m.username)...)
	resp = append(resp, 0)
	resp = append(resp, 20)
	resp = append(resp, CalcPassword([]byte(serverResp.AuthPluginDataPart_1+serverResp.AuthPluginDataPart_2)[:20], []byte(m.password))...)
	resp = append(resp, []byte("mysql_native_password")...)
	resp = append(resp, 0)

	_, _ = m.conn.Write(Pack(resp, 1))
	flag := m.handleResponse()
	if flag == 0xff {
		panic("连接失败")
	}
}

发送命令

func (m *MySQLClient) CommandQuery(sql string) {
	resp := make([]byte, 0)
	resp = append(resp, 3)
	resp = append(resp, []byte(sql)...)
	_, _ = m.conn.Write(Pack(resp, 0))
	m.handleResponse()
}

控制台结果输出

执行上面的代码后，控制台就会输出所有的数据库名字：

+--------------------+
|      Database      |
+--------------------+
| information_schema |
|      greycode      |
|       mysql        |
| performance_schema |
|        sys         |
+--------------------+

参考资料

https://dev.mysql.com/doc/internals/en/client-server-protocol.html