1、mysql 通信协议使用小端序列进行传输。
大端序列与小端序列：
- 小端法(Little-Endian)就是低位字节排放在内存的低地址端即该值的起始地址，高位字节排放在内存的高地址端。
- 大端法(Big-Endian)就是高位字节排放在内存的低地址端即该值的起始地址，低位字节排放在内存的高地址端。
通俗的讲，小端法，接收方先接收到整数的低位部分。大端法，接收方先接收到正式的高位部分。
比如我们通过网络发送 0x12345678 这个整数，在80X86平台中，它是以小端法存放的，在发送前需要使用系统提供的htonl将其转换成大端法存放，如图2所示。
小端序列存储如下：
大端序列存储如下：
注意：大端序列、小端序列是针对数值型来说的，比如short、int、long等类型。
2、Mysql 握手验证协议
客户端首先发起 TCP 连接，连接服务端，TCP 经过三次握手协议之后，建立可靠的传输通道。
- 成功建立TCP之后，首先由Mysql服务器发送一个握手包，包括协议版本号、服务器版本号，服务器授权认证信息、服务器权能标识等等。
- 客户端收到握手包后向服务端发送登录验证报文，主要包括用户名，数据库名，密码（密文）
- 服务器向客户端发送认证结果报文（OK Package或 Error Package）或其他响应结果。
2.1 报文结构
mysql通信报文分为 消息头与消息体两部分。
其中消息头固定4个字节，前个字节为消息体的长度，1个处理序列号。
2.2 Mysql 基本类型
2.2.1 整数值
Mysql报文中整数值分别有 1、2、3、4、8字节长度，使用小端序列传输。（接收方先接收到 整数的低位部分）。
2.2.2 字符串(以Null结尾 0x00)(Null-Terminated String)
字符串长度不固定，当遇到'NULL'(0x00) 字符时结束。
2.2.3 二进制数据(长度编码)(Length Coded Binary)
数据长度不固定，字节数由第一个字节决定。
第一个字节值 | 后续字节数 | 长度值说明 |
0-250 | 0 | 第一个字节值即为数据的真实长度 |
251 | 0 | 空数据，数据的真实长度为零 |
252 | 2 | 后续额外2个字节标识了数据的真实长度 |
253 | 3 | 后续额外3个字节标识了数据的真实长度 |
254 | 8 | 后续额外8个字节标识了数据的真实长度 |
2.2.4 字符串（长度编码）(Length Coded String)
字符串长度不固定，无'NULL'(0x00)的介绍符，编码方式与上面的Length Code Binary。
2.3 协议描述mysql通信协议描述
Type | Description |
int<1> | 1 byte Protocol::FixedLengthInteger |
int<2> | 2 byte Protocol::FixedLengthInteger |
int<3> | 3 byte Protocol::FixedLengthInteger |
int<4> | 4 byte Protocol::FixedLengthInteger |
int<6> | 6 byte Protocol::FixedLengthInteger |
int<8> | 8 byte Protocol::FixedLengthInteger |
int<lenenc> | |
string<lenenc> | |
string<fix> | |
string<var> | |
string<EOF> | |
string<NUL> |
3、模拟Mysql Client 实现Mysql握手认证交互过程
参考资料：
- mysql协议分析中文版
- mysql官方文档：https://dev.mysql.com/doc/internals/en/client-server-protocol.html
Mysql握手认证协议抓包：
握手包
认证包
响应包
源码地址： 包路径：persistent.prestige.console.mysql
源码核心类如下：
3.1 握手包Handshake10Packet
package persistent.prestige.console.mysql.protocol;
/*
* 服务器--》客户端 握手数据包
* mysql4.1之后
*
* mysql报文头格式固定为4字节:3字节数据报文长度，1字节序号
*
* 报文体
* 1 协议版本号
* string[NUL] 服务器版本信息，(null-Termimated String)
* 4 服务器线程ID
* string[8] 随机挑战数(auth-plugin-data-part-1)
* 1 填充值0x00 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] filler
}
* 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
}
*
*/
@SuppressWarnings("serial")
public class Handshake10Packet extends Packet {
// 协议版本号
private byte protocolVersion;
private byte[] serverVersion;
private int connectionId;
private byte[] authPluginDataPart1;
private int serverCapability;
private byte characterSet;
private int serverStatus;
private byte[] authPluginDataPart2;
private byte[] authPluginName;
private Handshake10Packet() {}
/**
* 根据msg解析出该包
* @param msg
* @return
*/
public static final Handshake10Packet newInstance(MysqlMessage msg) {
int recvBufferCapacity = msg.remaining();
int dataLen = 0;
if(recvBufferCapacity > 3) {
dataLen = msg.getPacketLength();
}
if(recvBufferCapacity < HEAD_LENGTH + dataLen) { //不是一个完整的包名
return null;
}
Handshake10Packet packet = new Handshake10Packet();
msg.skipReadBytes(4);
packet.setProtocolVersion(msg.get());
packet.setServerVersion(msg.readNullTerminatedString());
packet.setConnectionId(msg.getInt());
byte[] authPluginDataPart1 = new byte[8];
msg.get(authPluginDataPart1);
packet.setAuthPluginDataPart1(authPluginDataPart1);
msg.skipReadBytes(1);
//两个字节的 capability flags
packet.setServerCapability(msg.getUB2());//低位两字节的服务器权能标识符
if(!msg.hasRemaining()) { //还有可读字节，继续解析
return packet;
}
packet.setCharacterSet(msg.get()); //服务器编码
packet.setServerStatus(msg.getUB2());
int high = msg.getUB2();//服务器权能标志，高16位置
int serverCapability = packet.getServerCapability() | ( high << 16 );
packet.setServerCapability(serverCapability);
int authPluginDataLen = 0;
if( (serverCapability & CLIENT_PLUGIN_AUTH) != 0) {
authPluginDataLen = msg.get();
} else {
msg.skipReadBytes(1);
}
msg.skipReadBytes(10);//10个填充字符
if((serverCapability & CLIENT_SECURE_CONNECTION) != 0) {
authPluginDataLen = Math.max( 13 , authPluginDataLen - 8);
byte[] authPluginDataPart2 = new byte[authPluginDataLen];
msg.get(authPluginDataPart2);
packet.setAuthPluginDataPart2(authPluginDataPart2);
}
if( (serverCapability & CLIENT_PLUGIN_AUTH) != 0) {
packet.setAuthPluginName( msg.readNullTerminatedString() );
}
return packet;
}
@Override
public int hashCode() {
// TODO Auto-generated method stub
return super.hashCode();
}
@Override
public String toString() {
StringBuilder str = new StringBuilder(100);
str.append("协议版本号:").append(protocolVersion).append("\n")
.append("服务器版本信息:").append(new String(serverVersion)).append("\n")
.append("服务器连接线程ID:").append(connectionId).append("\n")
.append("capabilityFlag:").append( Integer.toHexString(serverCapability)).append("\n")
.append("serverCharact:").append(characterSet).append("\n")
.append("statusFlags:").append(Integer.toHexString(serverStatus)).append("\n");
if(authPluginDataPart1 != null && authPluginDataPart1.length > 0 ) {
str.append("authPluginDataPart1:").append(new String(authPluginDataPart1)).append("\n");
}
if(authPluginDataPart2 != null && authPluginDataPart2.length > 0 ) {
str.append("authPluginDataPart2:").append(new String(authPluginDataPart2)).append("\n");
}
if(authPluginName != null && authPluginName.length > 0 ) {
str.append("authPluginName:").append(new String(authPluginName)).append("\n");
}
// .append("authPluginName:").append(new String(authPluginName));
return str.toString();
}
public byte getProtocolVersion() {
return protocolVersion;
}
public void setProtocolVersion(byte protocolVersion) {
this.protocolVersion = protocolVersion;
}
public byte[] getServerVersion() {
return serverVersion;
}
public void setServerVersion(byte[] serverVersion) {
this.serverVersion = serverVersion;
}
public int getConnectionId() {
return connectionId;
}
public void setConnectionId(int connectionId) {
this.connectionId = connectionId;
}
public byte[] getAuthPluginDataPart1() {
return authPluginDataPart1;
}
public void setAuthPluginDataPart1(byte[] authPluginDataPart1) {
this.authPluginDataPart1 = authPluginDataPart1;
}
public int getServerCapability() {
return serverCapability;
}
public void setServerCapability(int serverCapability) {
this.serverCapability = serverCapability;
}
public byte getCharacterSet() {
return characterSet;
}
public void setCharacterSet(byte characterSet) {
this.characterSet = characterSet;
}
public int getServerStatus() {
return serverStatus;
}
public void setServerStatus(int serverStatus) {
this.serverStatus = serverStatus;
}
public byte[] getAuthPluginDataPart2() {
return authPluginDataPart2;
}
public void setAuthPluginDataPart2(byte[] authPluginDataPart2) {
this.authPluginDataPart2 = authPluginDataPart2;
}
public byte[] getAuthPluginName() {
return authPluginName;
}
public void setAuthPluginName(byte[] authPluginName) {
this.authPluginName = authPluginName;
}
}4.2 认证包
package persistent.prestige.console.mysql.protocol;
import java.io.IOException;
import java.nio.ByteBuffer;
import java.nio.channels.SelectableChannel;
import java.nio.channels.SocketChannel;
import persistent.prestige.console.mysql.MysqlClient;
import persistent.prestige.console.mysql.utils.SecurityUtil;
import persistent.prestige.console.mysql.utils.SeqUtils;
/**
* 验证数据包
* 客户端在收到服务端的握手协议后，需要向服务器验证权限（用户名进行登录）
* @author dingwei2
*
* Protocol::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
}
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
}
*
*/
public class Auth41Packet extends Packet {
private Handshake10Packet handshake10Packet;
private int capabilityFlags;
private int maxPacketSize = 1 << 31 - 1;
private byte characterSet;
private final static byte[] reserved = new byte[23];
private byte[] username;
private byte authResponseLen;
private byte[] authResponse;
private byte[] database = MysqlClient.DB.getBytes();
private byte[] authPluginName;
static {
for (int i = 0; i < 23; i++) {
reserved[i] = FILLER;
}
}
private Auth41Packet(Handshake10Packet handshake10Packet) {
this.handshake10Packet = handshake10Packet;
}
public static final Auth41Packet newInstance(Handshake10Packet handshake10Packet) {
Auth41Packet packet = new Auth41Packet(handshake10Packet);
packet.setCapabilityFlags(getCapabilities());
// packet.setCapabilityFlags(handshake10Packet.getServerCapability());
packet.setCharacterSet(handshake10Packet.getCharacterSet());
packet.setUsername(MysqlClient.USERNAME.getBytes());
try {
if (handshake10Packet.getAuthPluginDataPart2() == null) {
packet.setAuthResponse(SecurityUtil.scramble411_2(MysqlClient.PWD.getBytes("UTF-8"),
handshake10Packet.getAuthPluginDataPart1()));
} else {
final byte[] auth1 = handshake10Packet.getAuthPluginDataPart1();
final byte[] auth2 = handshake10Packet.getAuthPluginDataPart2();
byte[] seed = new byte[auth1.length + auth2.length - 1];
System.arraycopy(auth1, 0, seed, 0, auth1.length);
System.arraycopy(auth2, 0, seed, auth1.length, auth2.length - 1);
// 关于seed 为什么只取auth2的 长度-1，是因为
// Due to Bug#59453 the auth-plugin-name is missing the
// terminating NUL-char
// in versions prior to 5.5.10 and 5.6.2.;
// 由于本示例代码的目的是为了学习mysql通信协议，所以这里就不做版本方面的兼容了。直接取auth2
// 0-length-1个字节参与密码的加密
byte[] authResponse = SecurityUtil.scramble411_2(MysqlClient.PWD.getBytes(), seed);
packet.setAuthResponse(authResponse);
packet.setAuthResponseLen((byte) authResponse.length);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
packet.setAuthPluginName(handshake10Packet.getAuthPluginName());
return packet;
}
public int getPacketLength() {
// 32 +
int len = 32;
if (username != null) {
len += username.length + 1;// 1字节填充
}
if (authResponseLen > 0) {
len += 1 + authResponseLen; // 1字节填充
}
if (database != null) {
len += database.length + 1; // 1字节填充
}
if (authPluginName != null) {
len += authPluginName.length + 1; // 1字节填充
}
return len;
}
/**
*
* @param channel
* @return
*/
public int write(SelectableChannel channel) throws IOException {
int packetLen = this.getPacketLength() + HEAD_LENGTH;
byte seq = SeqUtils.getSeq(channel);
ByteBuffer buf = ByteBuffer.allocate(packetLen);
MysqlMessage msg = new MysqlMessage(buf);
msg.putUB3(packetLen - HEAD_LENGTH);
msg.put(seq); // 包头 3字节长度 + 1 字节序列号
msg.putInt(this.getCapabilityFlags());
msg.putInt(this.maxPacketSize);
msg.put(this.getCharacterSet());
msg.putBytes(reserved);
msg.putBytes(this.username);
msg.put(FILLER);
msg.putBytes(this.authResponse);
msg.put(FILLER);
msg.putBytes(this.database);
msg.put(FILLER);
msg.putBytes(authPluginName);
msg.put(FILLER);
msg.flip();
SocketChannel c = (SocketChannel) channel;
return c.write(msg.nioBuffer());
}
public static final int getCapabilities() {
return CLIENT_LONG_PASSWORD | CLIENT_FOUND_ROWS | CLIENT_CONNECT_WITH_DB |
// CLIENT_COMPRESS ,压缩协议，为了简单，暂不开启
CLIENT_LOCAL_FILES | CLIENT_IGNORE_SPACE | CLIENT_PROTOCOL_41 | CLIENT_INTERACTIVE
| CLIENT_IGNORE_SIGPIPE | CLIENT_TRANSACTIONS |
// CLIENT_SECURE_CONNECTION |
// CLIENT_MULTI_STATEMENTS |
CLIENT_MULTI_RESULTS | CLIENT_PS_MULTI_RESULTS | CLIENT_PLUGIN_AUTH;
// CLIENT_CONNECT_ATTRS |
// CLIENT_CAN_HANDLE_EXPIRED_PASSWORDS |
// CLIENT_SESSION_TRACK |
// CLIENT_DEPRECATE_EOF;
}
public Handshake10Packet getHandshake10Packet() {
return handshake10Packet;
}
public void setHandshake10Packet(Handshake10Packet handshake10Packet) {
this.handshake10Packet = handshake10Packet;
}
public int getCapabilityFlags() {
return capabilityFlags;
}
public void setCapabilityFlags(int capabilityFlags) {
this.capabilityFlags = capabilityFlags;
}
public int getMaxPacketSize() {
return maxPacketSize;
}
public void setMaxPacketSize(int maxPacketSize) {
this.maxPacketSize = maxPacketSize;
}
public byte getCharacterSet() {
return characterSet;
}
public void setCharacterSet(byte characterSet) {
this.characterSet = characterSet;
}
public byte[] getUsername() {
return username;
}
public void setUsername(byte[] username) {
this.username = username;
}
public static byte[] getReserved() {
return reserved;
}
public byte getAuthResponseLen() {
return authResponseLen;
}
public void setAuthResponseLen(byte authResponseLen) {
this.authResponseLen = authResponseLen;
}
public byte[] getAuthResponse() {
return authResponse;
}
public void setAuthResponse(byte[] authResponse) {
this.authResponse = authResponse;
}
public byte[] getDatabase() {
return database;
}
public void setDatabase(byte[] database) {
this.database = database;
}
public byte[] getAuthPluginName() {
return authPluginName;
}
public void setAuthPluginName(byte[] authPluginName) {
this.authPluginName = authPluginName;
}
}4.3 mysql client
package persistent.prestige.console.mysql;
import java.io.IOException;
import java.net.InetSocketAddress;
import java.nio.ByteBuffer;
import java.nio.channels.SelectionKey;
import java.nio.channels.Selector;
import java.nio.channels.SocketChannel;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
import persistent.prestige.console.mysql.connection.Connection;
import persistent.prestige.console.mysql.connection.ConnectionFactory;
import persistent.prestige.console.mysql.protocol.Auth41Packet;
import persistent.prestige.console.mysql.protocol.ErrorPacket;
import persistent.prestige.console.mysql.protocol.Handshake10Packet;
import persistent.prestige.console.mysql.protocol.MysqlMessage;
import persistent.prestige.console.mysql.protocol.OkPacket;
public class MysqlClient {
public static final String MYSQL_HOST = "10.2.35.23";
public static final int MYSQL_PORT = 3306;
public static final String DB = "demo";
public static final String USERNAME = "peter";
public static final String PWD = "peter";
private Map<SocketChannel, Connection> connMap = new HashMap<SocketChannel, Connection>();
public static void main(String[] args) {
MysqlClient client = new MysqlClient();
(new Thread(client.new Client())).start();
}
private class Client implements Runnable {
@Override
public void run() {
// TODO Auto-generated method stub
Selector selector = null;
SocketChannel scc = null;
try {
selector = Selector.open();
scc = SocketChannel.open();
scc.configureBlocking(false);
scc.register(selector, SelectionKey.OP_CONNECT);
scc.connect(new InetSocketAddress(MYSQL_HOST, MYSQL_PORT));
Set<SelectionKey> selOps = null;
loop:
while(true) {
int n = selector.select();
selOps = selector.selectedKeys();
if(selOps == null || selOps.isEmpty()) {
continue;
}
try {
for(Iterator<SelectionKey> it = selOps.iterator(); it.hasNext(); ) {
SelectionKey key = it.next();
if(!key.isValid()) {
key.cancel();
}
if(key.isReadable()) { //可读
System.out.println("读事件触发");
SocketChannel sc = (SocketChannel)key.channel();
ByteBuffer recvBuffer = ByteBuffer.allocate(1024); // 固定1024字节用来接收数据
int r = 0;
int remaining = recvBuffer.remaining();
int localRead = 0;
while( (r = sc.read(recvBuffer) ) > 0 ) { //一次性读完通道数据
remaining -= r;
localRead += r;
if(r > 0 && remaining == 0 ) { //接收缓存区不足，扩容一倍
ByteBuffer tempBuf = ByteBuffer.allocate( recvBuffer.capacity() << 1 );
recvBuffer.flip();//变成读模式
tempBuf.put(recvBuffer);
remaining = recvBuffer.remaining();
recvBuffer = tempBuf;
}
}
System.out.println("可读数据:" + localRead);
if(r == -1 && localRead < 1) { //链路关闭了
System.out.println("收到字节为-1，服务端关闭连接");
break loop;
}
Connection conn = getConn(sc);
if(!conn.isHandshake()) { //未验证，发送握手协议包
//开始解析服务端发送过来的握手协议
recvBuffer.flip();//变成可读模式
Handshake10Packet handshkakePacket = Handshake10Packet.newInstance( new MysqlMessage(recvBuffer));
System.out.println(handshkakePacket);
if(handshkakePacket != null && !recvBuffer.hasRemaining()) { //如果解析出完整的包，并且recvBuffer
//取消读事件
clearOpRead(key); //这里不考虑只接收到一半的数据包，继续下一次包解析，本示例主要关注的点mysql通信协议
}
//注册写事件
key.attach(handshkakePacket);
} else if (!conn.isAuth()) { // 未成功授权，尝试解析服务端包
//开始解析服务器授权响应报文
recvBuffer.flip();//变成可读模式
MysqlMessage msg = new MysqlMessage(recvBuffer);
int packetLen = msg.getPacketLength();
byte packetSeq = msg.getPacketSeq();
short pType = msg.getPTypeByFrom1Byte();
System.out.println("数据包类型:" + pType);
if(pType == 0) { //OK数据包 //此处不考虑其他情况
OkPacket ok = OkPacket.newInstance(msg, packetSeq, packetLen);
System.err.println(ok);
conn.setAuth(true);
System.out.println("成功通过验证");
//接下来，取消读事件，开始发送命令给服务端，-----测试mysql的请求命令。
clearOpRead(key);
//目前暂时退出客户端
break loop;
} else if(pType == 0xFF) { // error 包
System.out.println("错误包");
ErrorPacket errorPacket = ErrorPacket.newInstance(msg, packetSeq, packetLen);
System.out.println(errorPacket);
//然后退出 客户端
break loop;
} else {
System.out.println("收到暂不支持的包，将退出");
break loop;
}
} else { //其他响应包
}
addOpWrite(key);
} else if(key.isWritable()) { // 可写
System.out.println("写事件触发");
SocketChannel sc = (SocketChannel)key.channel();
Connection conn = getConn(sc);
Object attachment = key.attachment();
if( attachment instanceof Handshake10Packet ) {
Handshake10Packet handshkakePacket = (Handshake10Packet) attachment;
Auth41Packet handshakeResponse = Auth41Packet.newInstance(handshkakePacket);
int wc = handshakeResponse.write(key.channel());
System.out.println("写入通道数据：" + wc + "字节");
clearOpWrite(key);
conn.setHandshake(true);
}
addOpRead(key);
} else if(key.isConnectable()) {
if(scc.isConnectionPending()) {
scc.finishConnect();
System.out.println("完成tcp连接");
}
scc.register(selector, SelectionKey.OP_READ);
}
it.remove();
}
} catch (Throwable e) {
e.printStackTrace();
}
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if(scc != null) {
try {
scc.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(selector != null) {
try {
selector.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
System.out.println("链路关闭");
}
}
}
public Connection getConn(SocketChannel sc) {
if(connMap.containsKey(sc)) {
return connMap.get(sc);
}
Connection conn = ConnectionFactory.getConnection(sc);
connMap.put(sc, conn);
return conn;
}
public static void addOpRead(SelectionKey key) {
key.interestOps( key.interestOps() | SelectionKey.OP_READ );
}
public static void clearOpRead(SelectionKey key) {
key.interestOps( key.interestOps() & ~SelectionKey.OP_READ );
}
public static void clearOpWrite(SelectionKey key) {
key.interestOps( key.interestOps() & ~SelectionKey.OP_WRITE );
}
public static void addOpWrite(SelectionKey key) {
key.interestOps( key.interestOps() | SelectionKey.OP_WRITE );
}
}遇到的坑
1、服务端返回的握手包的认证信息中，如果服务器版本是5.5.10-5.6.2，如果服务版本是上述版本的话，在认证包对密码加密的时候，只需要获取字段中（1-12个字节，第13个字节忽略），不然总是提示用户名密码错误。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
