mysql网络协议分析

我们从客户端本地登陆一个mysql的用户以及使用mysql命令获得想要的消息.  需要去请求mysql的服务器，这个时候就需要用到

mysql的网络通信协议.  当你打算编写数据库的代理服务器，数据库的中间件，这些直接对数据包进行直接操作的组件时，你必

须了解到mysql网络通信协议底层的原理.

服务器会使用TCP监听本地socket端口或者本地socket连接. 当一个客户端的连接请求到达.  就会执行握手和权限验证. 如果验证

成功,会话开始. 客户端发送消息，服务器会以一个适合该发送命令的数据类型的数据集或一条消息进行回去. 当客户端发送完成后

会发送一个特殊命令，告诉服务器已发送，然后会话结束. 通信的基本单位是应用程序包. 多个指令则成一个包. 答复可以包含几

个包.

mysql网络通信协议有自己的数据类型：

在整个协议中的基本类型 : 整数型和字符串型

整数型：

分为两种类型Fixed-Length Integer Types和Length-Encoded Integer Type；

Fixed-Length Integer Types :

一个固定长度的无符号整数将其值存储在一系列字节中，具体固定字节数可以是： 1,2,3,4,5,6,8；

Length-Encoded Integer Type(INT<lenenc>) :

存储需要的字节数取决于第一个字节数值的大小，具体可以参照如下：

>如果该值 < 251，则将其存储为一个字节的整数.

>如果该值 >= 251  则将其存储为 fc + 2字节整数.

>如果该值 >= 2^16 且 < 2^24，则将其存储为fd + 3字节整数.

>如果该值 >= 2^24 且 < 2^64，则它将存储为fe + 8字节整数.

要将长度编码的整数转换为其数值，请检查第一个字节：

>如果它是 <0xfb，则将其视为一个1字节的整数.

>如果它是0xfc, 则它后面跟着两个字节的整数.

>如果它是0xfd，则后面跟着3个字节的整数

>如果它是0xfe，它后面跟着一个8字节的整数.

这里有一个问题：

如果数据包的第一个字节是长度编码的整数并且其字节值为0xfe，则必须检查数据包的长度以验证它是否有足够的空间存储8个

字节的整数，如果不是，他可能是一个EOF_Packet代替.

根据上下文的不同，第一个字节也可能具有其他含义：

>如果它是0xfb，它代表一个NULL  ProtocolText::ResultsetRow

>如果它是0xff，并且是第一个字节  ERR_Packet

字符串类型：

分为5种类型包括，FixedLengthString，NullTerminatedString，VariableLengthString，LengthEncodedString和RestOfPacketString；

FixedLengthString：固定长度的字符串具有已知的硬编码长度，一个例子是ERR_Packet的SQL状态，它总是5个字节长；

NullTerminatedString：以遇到Null(字节为00)结束的字符串；

VariableLengthString：可变字符串，字符串的长度由另一个字段决定在运行时计算，比如int+value，分别为长度和指定长度的字节数；

LengthEncodedString：以描述字符串长度的长度编码的整数作为前缀的字符串，是VariableLengthString指定的int+value方式；

RestOfPacketString：如果一个字符串是数据包的最后一个组件，它的长度可以从整个数据包长度减去当前位置来计算；

现在我们的客户端需要与服务器交互数据，首先需要连接服务器，这里会发生三次握手，再由服务器主动发送一个认证的握手数据包，

接下来客户端接收到握手数据包之后，会将用户的信息(用户名，密码，数据库等等信息)打包成握手回包发送回mysql服务器，如果

服务器通过了验证，那发送一个Ok_pack包给你，如果没有验证通过那么发送一个error_pack给你.   如果客户端收到一个Ok_pack，

那接下来你就可以给服务器发送mysql命令操作数据库，这里的用到的命令数据包格式.  比如说insert,delect，update成功的话，这

些命令服务器会发送一个ok_pack，反之为error_pack. 但是select命令收到的就是ResultSetPacket 数据包. 在客户端服务器连接断

开之前你可以一直发送命令与服务器通信，当你想要断开连接的时候，发送一个quit数据包给服务器，接下来就进行四次挥手结束连接.  

如下图所示：

交互过程

mysql客户端与服务器的交互主要分为两个阶段：握手认证阶段和命令执行阶段.

握手认证阶段为客户端与服务器建立连接后进行的，交互过程如下：

>服务器->客户端 ： 握手初始化消息

>客户端->服务器 ：登录认证信息

>服务器->客户端：认证结果消息

客户端认证成功以后，会进入命令执行阶段，交互过程如如下：

>客户端->服务器：执行命令消息

>服务器->客户端：命令执行结果

报文结构

每一个mysql数据报的报文结构都是如下格式的，报文分为消息头和消息体两部分，其中消息头占用固定四个字节，消息体长度由消息头中

的长度字段决定，报文结构如下：

1>用于标记当前请求消息的时间数据长度值，以字节为单位，占用三个字节，最大值为0XFFFFFF，既接近16MB大小(16mb-1)

2>在一次完整的请求响应交互过程中，用于保证消息顺序的正确，每次客户端发起请求时，序号值会从0开始计算.

3>消息体用于存放请求的内容及其相应的数据，长度由消息头中的长度值决定.

接下来我们来一起揭开这些握手包，命令包，结束包的网络协议结构！ 从握手包开刀！

首先第一个包就是服务器跟你完成三次握手之后！ 服务器给你发送一个握手初始化消息包(服务器->客户端)

接下来了解这其中的字段信息:

>服务协议版本号：该值由PROTOCOL_VERSION宏定义决定(参照mysql官方文档)

>服务版本信息：该值为字符串，由MYSQL_SERVER_VERSION宏定义决定(还是需要参照官方文档)

>服务器线程ID：服务器为当前连接所创建的线程ID

>挑战随机数：mysql数据库用户认证采用的是挑战/应答的方式，服务器生产该挑战书并发送给客户端，客户端需要使用加密算法对数据

进行处理返回相应结果，然后服务器检查是否与预期的结果相同，从而完成用户认证的过程.

>服务器权能标志：用于与客户端协商通讯方式，各标志位如下：

>字符编码：标识服务器所使用的字符集

>服务器状态：状态中定义如下：

mysql文档规定的服务器初始化握手包协议字段：

1              [0a] protocol version
string[NUL]    server version
4              connection id
string[8]      auth-plugin-data-part-1
1              [00] filler
2              capability flags (lower 2 bytes)
  if more data in the packet:
1              character set
2              status flags
2              capability flags (upper 2 bytes)
  if capabilities & CLIENT_PLUGIN_AUTH {
1              length of auth-plugin-data
  } else {
1              [00]
  }
string[10]     reserved (all [00])
  if capabilities & CLIENT_SECURE_CONNECTION {
string[$len]   auth-plugin-data-part-2 ($len=MAX(13, length of auth-plugin-data - 8))
  if capabilities & CLIENT_PLUGIN_AUTH {
string[NUL]    auth-plugin name
  }

客户端接收到了服务器的握手初始化消息数据报包之后，需要回复一个登录认证报文(客户端->服务器)

现在基本使用的都是Mysql4.1及之后的版本，所以直接介绍mysql4.1以后版本的登录认证数据报格式：

>客户端权能标志：用于与客户端协商通讯方式，标志位含义与握手初始化报文中的相同. 客户端收到服务器发过来的初始化报文之后，会

对服务器发送的权能标志进行修改，保留自身所支持的功能，然后将权能标志返回给服务器，从而保证服务器与客户端通讯的兼容性.

>最大消息长度：客户端发送请求报文时所支持的最大消息长度值.

>字符编码：标识通讯过程中使用的字符编码，与服务器在认证初始化报文中发送的相同.

>用户名：客户端登录用户的用户名称

>挑战认证数据：客户端用户密码使用服务器发送的挑战随机数进行加密以后，生产挑战认证数据，然后返回给服务器，用于身份认证.

>数据库名称：当客户端的权能标志位CLIENT_CONNECT_WITH_DB被置位时，该字段必须出现.

mysql客户端回复包的规定协议字段：

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
  }
  if capabilities & CLIENT_CONNECT_ATTRS {
lenenc-int     length of all key-values
lenenc-str     key
lenenc-str     value
   if-more data in 'length of all key-values', more keys and value pairs
  }

这个时候等客户端再收到一个ok_pack之后，那么客户端与服务器之间就算彻底链接成功了，接下来就是命令的交互了：

客户端命令请求报文(客户端->服务器)

>命令：用于标识当前请求消息的类型，例如切换数据库(0x02)，查询命令(0x03)等，命令值的取值范围及说明下表

>参数：内容是用户在mysql客户端输入的命令(不包括每行命令结尾的";"分号)，另外这个字段的字符串不是以NULL字符结尾

，而是通过消息头中的长度值计算而来. 比如你想执行一个select命令，那么你的参数就是0x03 然后后面跟上你具体的命令

就可以了. 读取的截止会根据你的消息头提供的长度，所以不用担心.

1              [03] COM_QUERY
string[EOF]    the query the server shall execute

接下来你发送命令请求成功之后，如果你需要执行insert,delect,updata这些命令，如果执行成功会返回一个ok_pack，如果执

行失败会返回error_pack. 如果是select命令就会返回一个result_pack.

关于ok_pack/error_pack/eof_pack mysql使用文档已经介绍的非常清楚了. 今天着重介绍一下result_pack. 

ok_pack介绍链接：->>>>>>>>>传送门

error_pack介绍链接：->>>>>>>>>>>飞雷神

eof_pack介绍链接：->>>>>>>>>>>>>>传送门

其实这几个包具体网络协议字段都比较简单清楚，比如ok_pack里面会有受影响的行数，最后结束行这些东西. 需要熟悉一下.

接下来就是比较重要的boss了,Result结果包.其实应该叫他result结果集(result set)

严格来说ResultSet是由多个独立的报文以协议的形式组织起来，现直接放出ResultSet的具体结构步骤：

从上图来看，客户端发送一个select的com_query包之后，DB会按照下列步骤返回：

>1.返回一个 Protocol::LengthEncodedInteger ，其中数据为column_count.（column_count就是表中字段的个数）

>2.接下来会跟column_count个Protocol::ColumnDefinition 包(都是独立的包). 

Protocol::ColumnDefinition协议包格式：

lenenc_str     catalog   //目录
lenenc_str     schema    //模式
lenenc_str     table     //虚拟表名
lenenc_str     org_table //物理表名
lenenc_str     name      //虚拟列字段名
lenenc_str     org_name  //物理列字段名
lenenc_int     length of fixed-length fields [0c]   //以下字段长度
2              character set    //列字符集
4              column length    //字段最大长度
1              type             //字段类型
2              flags            //标志
1              decimals         // ??? 
2              filler [00] [00]
  if command was COM_FIELD_LIST {
lenenc_int     length of default-values
string[$len]   default values
 }

这里你的表中有多少个字段，就会有多少个Protocol::ColumnDefinition 包，每个包中的org_name就是你的字段名称，这里你需要记录

下来，下面读取ROW包的时候会有作用.

>3.再读取一个eof包表示ColumnDefinition包流结束. 

>4.接下来一直读Row包，直到读到last eof位置，Row包的格式如下：

这里需要了解到ResultSet是由很多行的(ROW)组成，每一个ROW包就表示一条记录(也就是表中一行的数据). 所以每个ROW包里面会有

column_count个LengthEncodedString(文章上面有介绍到)数据也就是上图的(length,value). 所以你获取的row包中所有的数据，把

这些value按(步骤2)读取出来的字段顺序拼凑起来就是表中一行数据的值. 

>5.如果读到任何一个error包之后，从此读取结束，抛出错误.

>6.或者你读取到了第二个eof包，按照正常顺序这里就会结束了. 但是：

如果eof包中的status & SERVER_MORE_RESULT_EXISTS不为0，表明还有ResultSet,则返回到步骤1，开始读取下一个ResultSet.

>7.至此，整个Result读取完毕.

所以！ 这就是一个mysql客户端和服务器交互的过程！ 所以小伙伴们有没有收到什么启发？？   mysql库给我提供了几个原生的函数

但是这几个函数是同步的，也就是说在网络I/O的时候计算机就卡在哪里等mysql服务器响应. 单进程模式这样非常影响响应时间. 多

线程版本的又非常浪费机器的资源. 所以各位有没有什么想法？？  我们如果可以使用协程来进行与mysql服务器进行交互，那我们就

可以同时解决上面两个问题！ 所以这就是我为什么要了解mysql网络通信协议的原因，我要实现一个基于协程的mysql异步代理，可以

让机器抗住更高的mysql并发量，并且拥有更快的响应速度.  如果有兴趣可以看我接下来要介绍的我的项目：协程版本的mysql异步代理.