一、概述
MySQL协议是有状态的协议,主要用于MySQL客户端与服务器之间的通信。实现协议的地方有:
- 客户端连接器(Connector/C、Connector/J等)
- MySQL Proxy
- 主从之间复制
协议特性有:支持SSL、压缩、认证等。
1.1 基础类型
MySQL通信协议中定义了一些基础类型。
1.1.1 整型
定长整型
![cfea611a2db0df95f1af35cd95f8ba3b.png](https://i-blog.csdnimg.cn/blog_migrate/7fb07f08d95d2b497b3e2d6b39ee1ea0.png)
变长整型
用int<lenenc>表示,编码方式如下表。
![7bf8de916ba6a773e1f9c434cd17061f.png](https://i-blog.csdnimg.cn/blog_migrate/56013e6937efef75ccffd23847d10f29.png)
1.1.2 字符串
![20629f906ba4a2721041eabdf611db39.png](https://i-blog.csdnimg.cn/blog_migrate/3e374dafbacd417f40faa0ab657d06cf.jpeg)
1.1.3 服务器状态
![5d00f9cb25663ec2e6eef2665abfd9d2.png](https://i-blog.csdnimg.cn/blog_migrate/d0d3035c35c6c829044ca643e2c2881f.jpeg)
1.2 MySQL数据包
这是MySQL最基础的数据包,所有协议都是基于这个数据包格式下扩展的。
![dad201afa83cc8874854f73a97d06b0d.png](https://i-blog.csdnimg.cn/blog_migrate/ff36b92a8baa476877e9526c9e102f82.jpeg)
最大16MB,大于会被拆包,当payload_length < 0xffffff时表示最后一个包。
例如,客户端退出协议(COM_QUIT):
![2a3c42c66357020a5ef126244d24e7a0.png](https://i-blog.csdnimg.cn/blog_migrate/8790d97ec2a1bdcb8b04a364a2334d6a.png)
1.3 通用响应包
1.3.1 OK响应包
![b9d9925d99d5a9063958399890b06699.png](https://i-blog.csdnimg.cn/blog_migrate/628a1b1240dc49c9d090c6e9f6abfa13.jpeg)
例如,执行“USE test”的响应包:
![af447e401d2937747123d8508da0f918.png](https://i-blog.csdnimg.cn/blog_migrate/f1b5748f2b2e6b8bdfc9a6ae75cc607f.png)
源码实现:net_send_ok()
1.3.2 ERR响应包
![ef40e913443fb26a6902bc2b3f08c7e2.png](https://i-blog.csdnimg.cn/blog_migrate/9b3108733dd1b0e6a008c847e4f5e527.jpeg)
例如:
![e8b5c6860c991da2823a025606505000.png](https://i-blog.csdnimg.cn/blog_migrate/706da0a1e5a8d80a92bf7440231a2385.png)
源码实现:net_send_error_packet()
1.3.3 EOF响应包
消息体字段:
![8da8c463dc6f2d1c921f700cc793f788.png](https://i-blog.csdnimg.cn/blog_migrate/d5359ad0616281fff7e22b88016a2009.png)
例如,EOF响应包:0 warnings, AUTOCOMMIT enabled.
![67767e3eee4d2e18ccc7ffe5b6aa0033.png](https://i-blog.csdnimg.cn/blog_migrate/08f57bd3ec6f89f9adca954a39cd7faf.png)
在MySQL 5.7.5及之后版本已弃用。
1.4 字符编码
查看支付的字符编码:
SELECT
常用字符编码:
![73880298bd2e2abfbf25cd8f3d16025a.png](https://i-blog.csdnimg.cn/blog_migrate/01e5e76abccd0c2158545068448d2a03.png)
1.5 连接周期
MySQL连接周期包括两个阶段:
- 连接阶段:建立连接和认证。
- 命令阶段:处理命令、SQL执行、复制等。
![9bcfd789dc438a1b63ec5b6963f40682.png](https://i-blog.csdnimg.cn/blog_migrate/89c73b5628d8a0d8d9eca4d5cc6fd62e.png)
二、连接阶段
连接阶段的状态图:
![e664a53f594caafedce6ef72624d258a.png](https://i-blog.csdnimg.cn/blog_migrate/3fe5cdea0c196e00a9216667e9ef03f8.png)
2.1 交互过程
普通握手(不加密):
![8dbce76ddfd85669efd19910de978904.png](https://i-blog.csdnimg.cn/blog_migrate/4af58cd46bb368bbeb666a2d1102bd88.png)
SSL握手:
![e6e14c911c4f95da1cc617dbef705f6d.png](https://i-blog.csdnimg.cn/blog_migrate/b783a4f2e98574dc983682dced0f14b5.png)
2.2 协议格式
2.2.1 握手初始化请求
![aac17872d3fee351a13d49f568ce72b6.png](https://i-blog.csdnimg.cn/blog_migrate/81a86a4bc4df7e9577f06b2c7741d22b.jpeg)
权能标记(Protocol::CapabilityFlags),是一个功能标记,由4个字节组成,每个比特位代表一种功能,用于服务器或客户端之前协商需要支持或使用到的功能。比如,512表示使用4.1版本的协议、2048表示使用SSL安全链接等等。
2.2.2 握手响应
HandshakeV9和HandshakeResponse320的版本较老,这里不做介绍。下面介绍现在mysql客户端 v4.1+使用的CLIENT_PROTOCOL_41。
![ee4dafb990ad94ef2e263b596fff7716.png](https://i-blog.csdnimg.cn/blog_migrate/c847b1240e5f01753077d7fda0fe741e.jpeg)
源码实现:send_server_handshake_packet()
2.2.3 SSL请求
如果服务器支持CLIENT_SSL权能,那么客户端可以通过这个请求包建立SSL连接,且必须带上CLIENT_SSL权能标记。
![2d4e96cab2fe9c24b6af37a9926b57e0.png](https://i-blog.csdnimg.cn/blog_migrate/846ca686f1c50d0cb283a6a6b12d979e.jpeg)
2.2.4 认证方法切换请求
服务器发送给客户端的请求包,客户端对应回复认证方法切换响应包。
![3a4b36c3d7e9f2780f4b9e2f8822d8f9.png](https://i-blog.csdnimg.cn/blog_migrate/4b6b34c2c10ccca796d47f466a944c74.png)
2.2.5 认证方法切换响应
客户端收回服务器的认证方法切换请求时的响应包。
![4e76df8f22f5d2ac786b435084dabb6b.png](https://i-blog.csdnimg.cn/blog_migrate/17348a402d1b403bbafc40a8ba622717.png)
2.2.6 更多认证数据请求
服务器收到客户端的认证方法切换响应包时,通常回复OK包或ERR包,(没太明白什么情况下需要这个?)。
![2b620d1021bad607b3d8706688c13f79.png](https://i-blog.csdnimg.cn/blog_migrate/eec871e30ffa54036f6b61017f015b66.png)
2.3 认证方法
// 后续补充:https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_connection_phase_authentication_methods.html
三、命令阶段
在命令阶段,客户端发送一个序号为0的命令请求包。不同的命令,数据格式不同,但消息体的第一个字节都是命令类型,格式结构如下:
![5b6d1cb79ec0b5fad5b98f7b7516a7ea.png](https://i-blog.csdnimg.cn/blog_migrate/9ee8cf8d9e32359859e0af94518fa770.png)
命令类型定义:
![f3d53fd74019f9c0cfb5e468950d34c1.png](https://i-blog.csdnimg.cn/blog_migrate/f37b97cb93beb437176fb188911e17c5.jpeg)
3.1 文本协议
目前只有COM_QUERY命令是文本协议。
响应包有四种:
- ERR响应包
- OK响应包
- LOCAL INFILE 请求(后面会介绍)
- 结果集
流程图:
![55772274ae2410dafa1a6de81202d461.png](https://i-blog.csdnimg.cn/blog_migrate/3c64610c3c857b3775087e1b8218268b.jpeg)
3.1.1 请求包格式
![4529f2038bccd7dfcfff9e4592153852.png](https://i-blog.csdnimg.cn/blog_migrate/c302d45baf27bd89b74f1ae914994a91.png)
3.1.2 文本协议结果集
由两部分组成:
- 列定义,也称元数据
- 行记录
![6135d0591f5e0e0e145357a83b8c5759.png](https://i-blog.csdnimg.cn/blog_migrate/75d1636fef5034783563d5dbc136e0e5.jpeg)
3.1.3 列定义
这里只介绍4.1版本的列定义。
![8cbf09477ec7b57784dd6f8f8c67942e.png](https://i-blog.csdnimg.cn/blog_migrate/5fdcf95b169ff21242f75f92cca1a381.jpeg)
发送列定义的实现函数:Protocol::send_result_set_metadata()
3.1.3 文本协议的行记录
- NULL的列都按0xFB发送
- 其它非NULL列都转换为string,并按string<lenenc>发送。
发送行记录的实现函数:Protocol::send_result_set_row()
3.2 二进制协议
二进制协议应该只是有时在服务器给客户端回包时用到(待确认),预处理的COM_STMT_EXECUTE命令会用到。
3.2.1 二进制协议结果集
二进制协议结果集与文本协议结果集相似,格式如下:
![d561bc372a06f355e2ae0b120fc5637d.png](https://i-blog.csdnimg.cn/blog_migrate/b2bd6d0c25972c6794d5bcca0f596cbf.png)
注意,如果客户端设置了CLIENT_DEPRECATE_EOF的权能标记位,则发送OK包,否则才发送EOF包。
3.2.2 二进制协议的行记录
![e564ec63ab98bb535dbc86d3caa9eeea.png](https://i-blog.csdnimg.cn/blog_migrate/d07fce4a280e2c9e3aae50bda78ab515.png)
其中binary<var>是二进制协议的值,后面会详细介绍。
NULL bitmap通过每个比特位来表示一个NULL列的方式,相比文本协议的行记录用一个字节表示NULL列要有效果得多。长度计算中,7为了向上取整,(2是怎么来的?)。
3.2.3 二进制协议的值(Binary Protocol Value)
二进制协议的值,用binary<var>表示。
![f8b92d60321ec87e7a10c6e1ea33bd19.png](https://i-blog.csdnimg.cn/blog_migrate/a439b4106403fa655efc0869d778550e.jpeg)
MYSQL_TYPE_DATE, MYSQL_TYPE_DATETIME, MYSQL_TYPE_TIMESTAMP类型:
![b4632d62c2a70fcdf199c8fa2c35ae3b.png](https://i-blog.csdnimg.cn/blog_migrate/c4d29e1e88159857a13e9d3d7399b6bb.jpeg)
MYSQL_TYPE_TIME类型:
![e26e1a0c1c5f007d9573237812077ea8.png](https://i-blog.csdnimg.cn/blog_migrate/fa5dca9185f974c8507a73f70c8346ae.jpeg)
3.3 通用命令(Utility Commands)
通用命令有:COM_QUIT、COM_INIT_DB、COM_FIELD_LIST、COM_REFRESH、COM_STATISTICS、COM_PROCESS_INFO、COM_PROCESS_KILL、COM_DEBUG、COM_PING、COM_CHANGE_USER、COM_RESET_CONNECTION、COM_SET_OPTION。
方法都一样,不一一分析,只分析若干个常见的。
3.3.1 COM_QUIT
客户端请求关闭与服务器的连接。
![1ce24c47a93856884a8e7049fab41cf5.png](https://i-blog.csdnimg.cn/blog_migrate/58705042f0ba9f8820514e553cd4de39.png)
服务器关闭连接或返回ERR包。
3.3.2 COM_INIT_DB
更改当前连接默认的模式(数据库)名,服务器返回OK包或ERR包。
![ffcd4f1da83c1fd5541ccdbef661841f.png](https://i-blog.csdnimg.cn/blog_migrate/2c8282a2ed4a8a9c143da25af7f880f4.png)
3.3.3 COM_PING
心跳检测,服务器返回OK包。
![b570a007b7569ffe2896210314ba5d2b.png](https://i-blog.csdnimg.cn/blog_migrate/c1febb5d31efcb4bbf55b3282f3d3032.png)
源码实现:mysql_ping(),dispatch_command()。
3.3.4 COM_FIELD_LIST
获取表的列定义,返回列定义,出错时返回ERR包。
![96cc3a64c5beb3f1a774833561ea08b9.png](https://i-blog.csdnimg.cn/blog_migrate/cdba52352559b5ab9b4693bebd9d7298.png)
源码实现:mysql_list_fields(), mysqld_list_fields()
3.4 预处理语句
3.4.1 COM_STMT_PREPARE
![c02666edb41f4a36f7a9315838d1829e.png](https://i-blog.csdnimg.cn/blog_migrate/da942bebad4432c849a96518e4628445.png)
成功返回COM_STMT_PREPARE_OK包,失败返回ERR包。
源码实现:mysqld_stmt_prepare()。
COM_STMT_PREPARE_OK包格式:
![8ca605b1da25a3ea969aebd938f98859.png](https://i-blog.csdnimg.cn/blog_migrate/4831eb1799a03a2163f0f7e284fdf35b.jpeg)
如果num_params>0且未设置CLIENT_OPTIONAL_RESULTSET_METADATA权能,或者如果medatdata_follows=1,那么紧接着会发送num_params个参数定义(格式同列定义)数据包。然后如果没有设置CLIENT_DEPRECATE_EOF权能标记的话,不会发送一个EOF包。
如果num_columns>0且未设置CLIENT_OPTIONAL_RESULTSET_METADATA权能,或者如果medatdata_follows=1,那么紧接着会发送num_columns个列定义数据包。然后如果没有设置CLIENT_DEPRECATE_EOF权能标记的话,不会发送一个EOF包。
源码实现有:cli_read_prepare_result(), mysql_stmt_prepare(), send_statement(), THD::send_result_metadata()。
3.4.2 COM_STMT_EXECUTE
请求服务器执行预处理语句,返回OK包、ERR包、二进制结果集中之一。
![5efb6e5c7469dbde533f55d0363f6c45.png](https://i-blog.csdnimg.cn/blog_migrate/68a880ebc7d3dac359cf7cc402d2aabd.jpeg)
源码实现有:mysql_stmt_execute(), cli_stmt_execute(), mysql_stmt_precheck(), mysqld_stmt_execute()。
3.4.3 COM_STMT_FETCH
获取由COM_STMT_EXECUTE请求生成的结果中的行数据,返回ERR包或多结果集(Multi-Resultset),多结果集的格式后面会介绍。
![ec10ae3da58801301e1dcf18d3ee0c07.png](https://i-blog.csdnimg.cn/blog_migrate/bbe083a11cc227233e3c01f02120fab3.png)
源码实现有:mysqld_stmt_fetch(), mysql_stmt_fetch()。
另外还有COM_STMT_CLOSE、COM_STMT_RESET和COM_STMT_SEND_LONG_DATA,协议简单,不再赘述。
3.4 存储过程(Stored Programs)
3.4.1 多语句(Multi-Statement)
命令COM_QUERY可以同时给服务器发送多条SQL语句,以“;”分号隔开。客户端需要支持多语句的话,可以通过设置CLIENT_MULTI_STATEMENTS权能标记,或者通过命令COM_SET_OPTION开启。
3.4.1 多结果集(Multi-Resultset)
存储过程或其它多语句可能会产生多个结果集。对应的协议格式也是“文本协议结果集”,如果第一个结果集中标识行记录结束的OK包或ERR包中的status设置了SERVER_MORE_RESULTS_EXISTS标记位,表明是多结果集,其它结果集紧随其后。输出参数集(OUT Parameter Set)
存储过程的预处理语句可以绑定输出参数,返回的形式是多结果集里的一个结果集。如果OK包或ERR包设置了SERVER_PS_OUT_PARAMS标记位,表示是输出参数的结果集。
四、复制协议(Replication Protocol)
复制是把数据库的变更通过binlog的方式从主服务器传送到从服务器,可以写入binlog文件和通过binlog网络流发送。
Binlog版本:
![7dc8913cfdf5b8ef54132d1720179ed3.png](https://i-blog.csdnimg.cn/blog_migrate/dbda81a274ca63c1655d5f4848e303d6.jpeg)
通过第一个事件判断binlog版本的方法:
- FORMAT_DESCRIPTION_EVENT事件: version = 4
- START_EVENT_V3事件:
- if event-size == 13 + 56: version = 1
- if event-size == 19 + 56: version = 3
- 否则:无效
4.1 Binlog文件
binlog文件的组成:
![d3da1b5d7a113a9d6f38989fd43543dc.png](https://i-blog.csdnimg.cn/blog_migrate/641ba4f419ef5dd5bb0ec85d6e0b124b.png)
4.2 Binlog事件
4.2.1 Binlog事件头
![2e55e5fd761fa40083d1f4b721000d00.png](https://i-blog.csdnimg.cn/blog_migrate/4f478b461188ace125846952438316cc.jpeg)
binlog事件标记位定义:
![26bb1258eb1d1a2b4aab3b5376bb4a60.png](https://i-blog.csdnimg.cn/blog_migrate/3bd39144dbdbf9b6dbdfbb2e5807fa90.jpeg)
4.2.2 Binlog管理
Binlog文件的第一个事件是START_EVENT_V3或FORMAT_DESCRIPTION_EVENT,最后一个事件是STOP_EVENT或ROTATE_EVENT。START_EVENT_V3事件
![578cba07c79325e9b8b914a904155273.png](https://i-blog.csdnimg.cn/blog_migrate/c7b96ae2c60bf537bff5cecfbd4301a1.png)
FORMAT_DESCRIPTION_EVENT事件
格式描述事件是v4 binlog的每一个事件,描述其它事件如何分布。
![28c251879771d030f968a77b03a6d385.png](https://i-blog.csdnimg.cn/blog_migrate/a0ae8ccb2dc585a2e3d09d0c913c59e6.jpeg)
STOP_EVENT事件
该事件没有消息体,也没有post头。ROTATE_EVENT事件
该事件作为最后一个事件添加到binlog,用以说明下一个binlog的名称。
![0954c7b94fa0b262f68e8ef05ffa9f11.png](https://i-blog.csdnimg.cn/blog_migrate/acd389e349d14ff908825bbba0a4e78d.png)
HEARTBEAT_EVENT事件
心跳事件,不写入relay日志。该事件没有消息体,也没有post头。
4.2.3 基于语句的复制事件
基于语句的复制将客户端发送给主服务器的SQL语句发送给从服务器,它需要额外的事件来模拟客户端连接在从服务器上的状态。QUERY_EVENT事件
该事件是用于发送文本的SQL语句。
post头:
![007ad3d8fa8da2a0c694c843b007830a.png](https://i-blog.csdnimg.cn/blog_migrate/a49ad040adb439f362d790704ebf51c3.jpeg)
消息体:
![5c38cd6bae0b445067844829e8dc3f42.png](https://i-blog.csdnimg.cn/blog_migrate/8351be7b82645f1b9e8d094136d6fa75.jpeg)
其它还有INTVAR_EVENT、RAND_EVENT、USER_VAR_EVENT、XID_EVENT事件,不再一一介绍。
4.2.4 基于行的复制事件
基于行复制是把修改的行发送给从服务器,这样消除了副作用,而且更可靠。TABLE_MAP_EVENT事件
该事件是是基于行复制的第一个事件,说明了表的修改情况。
post头:
![e7794ab4a17f4c759aed46bcb9d3611c.png](https://i-blog.csdnimg.cn/blog_migrate/ccb12db17fd52b8e8d71a4629750abfb.png)
消息体:
![e1711f2d3bc24be2d1fb0aebb71ecba2.png](https://i-blog.csdnimg.cn/blog_migrate/b61e3a5f7d74cd1842e1fb254f369ac3.jpeg)
ROWS_EVENT事件
行事件有如下三类:
![3aefca0897f2abbe7f1fb31a744267d0.png](https://i-blog.csdnimg.cn/blog_migrate/e9d94f3867961ccfad014aee7a3b96c9.jpeg)
不同版本对应的事件:
![b0b61688ff8e47d72ce43045e47ed558.png](https://i-blog.csdnimg.cn/blog_migrate/56f8f4731b29ffad8c1d774d7d056483.jpeg)
上述所有事件的格式都极其相似。
post头:
![34669a98d40f5f1b179e4cb3ec04c339.png](https://i-blog.csdnimg.cn/blog_migrate/c7f309611a547210820578b4f9072c56.jpeg)
消息体
![c5d45236f870921e6f45734ccc9a7230.png](https://i-blog.csdnimg.cn/blog_migrate/56740f2521107aa9de6fa6dfbbaac05f.jpeg)
行:
![97984289195887575e25ceb866fcb01e.png](https://i-blog.csdnimg.cn/blog_migrate/ba6656235c401ac287ad0a63524b5335.jpeg)
4.2.5 数据导入复制(LOAD INFILE Replication)
LOAD DATA|XML INFILE 是特殊的SQL语句,用于从binlog文件把数据导入到数据库。本文不再具体介绍。
相关事件有:
- LOAD_EVENT
- CREATE_FILE_EVENT
- APPEND_BLOCK_EVENT
- EXEC_LOAD_EVENT
- DELETE_FILE_EVENT
- NEW_LOAD_EVENT
- BEGIN_LOAD_QUERY_EVENT
- EXECUTE_LOAD_QUERY_EVENT
4.3 COM_BINLOG_DUMP命令
从服务器请求一个binlog网络流,返回binlog网络流或ERR包。用于从服务器之间的数据复制。
![de8ea93861e4f77d3ed86cf8c80ce300.png](https://i-blog.csdnimg.cn/blog_migrate/3f49ef55a396948c97e40c2043bcc279.jpeg)
源码实现:com_binlog_dump()
Binlog网络流用于请求COM_BINLOG_DUMP命令,以及给每个binlog事件前追加“00 OK”。(这里追加的内容是分别是'0' 'O' 'K'三个字符?)
还有如下相关命令,不再一一介绍。
- COM_BINLOG_DUMP_GTID
- COM_TABLE_DUMP
- COM_CONNECT_OUT
- COM_REGISTER_SLAVE
4.4 半同步复制
半同步复制,当主服务器提交事务时,至少有一个从服务器在一定时间内接受了(不需要执行,写成功relay日志即可)事务,就认为成功。如果超时,则禁用半同步复制。
4.4.1 半同步复制事件
Binlog网络流的“00 OK”和常规binlog事件之间有2个字节来标记半同步。
![834303469634ae9334846f14c17f40ea.png](https://i-blog.csdnimg.cn/blog_migrate/bfe15b1ad60ca813a3cbcd13470c3166.jpeg)
4.4.2 半同步确认包
主服务器收到半同步确认包后,还需要回复OK包或ERR包。
![1358476c098f3178df50949dec00700d.png](https://i-blog.csdnimg.cn/blog_migrate/feb79678cc70bab7d81321cc8b5f03bd.png)
五、参考
- https://dev.mysql.com/doc/dev/mysql-server/latest/PAGE_PROTOCOL.html
- https://dev.mysql.com/doc/internals/en/client-server-protocol.html