详细介绍mysql协议的服务端握手包及对其解析

概况

mysql客户端登陆到mysql服务端需要一个交互的过程，这里先看服务端给客户端发送的初始握手包。如下，client通过socket连接到server指定的端口后，server将往client发送初始握手包。服务端会根据不同的服务版本和不同的配置返回不同的初始化握手包。

client 
server 
|------connect---- >|
 | |
 |<----handshake-----|
 | |
 | |
 | |

mysql通信报文结构

初始握手包

HandshakeV10协议如下

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 {
 if version >= (5.5.7 and < 5.5.10) or (>= 5.6.0 and < 5.6.2) {
string[EOF] auth-plugin name
 } elseif version >= 5.5.10 or >= 5.6.2 {
string[NUL] auth-plugin name
 }
 }

生成初始握手包

1. 定义版

/**
 * 
 * @author seaboat
 * @date 2016-09-25
 * @version 1.0
 * <pre><b>email: </b>849586227@qq.com</pre>
 * <pre><b>blog: </b>http://www.gxlcms.com/;/pre>
 * <p>proxy's version.</p>
 */public interface Versions {

 byte PROTOCOL_VERSION = 10; byte[] SERVER_VERSION = "5.6.0-snapshot".getBytes();
}

1. 随机数工具

/**
 * 
 * @author seaboat
 * @date 2016-09-25
 * @version 1.0
 * <pre><b>email: </b>849586227@qq.com</pre>
 * <pre><b>blog: </b>http://www.gxlcms.com/;/pre>
 * <p>a random util .</p>
 */public class RandomUtil {
 private static final byte[] bytes = { '1', '2', '3', '4', '5', '6', '7', 
 '8', '9', '0', 'q', 'w', 'e', 'r', 't', 'y', 'u', 'i', 'o', 'p', 
 'a', 's', 'd', 'f', 'g', 'h', 'j', 'k', 'l', 'z', 'x', 'c', 'v', 
 'b', 'n', 'm', 'Q', 'W', 'E', 'R', 'T', 'Y', 'U', 'I', 'O', 'P', 
 'A', 'S', 'D', 'F', 'G', 'H', 'J', 'K', 'L', 'Z', 'X', 'C', 'V', 
 'B', 'N', 'M' }; 
 private static final long multiplier = 0x5DEECE66DL; 
 private static final long addend = 0xBL; 
 private static final long mask = (1L << 48) - 1; 
 private static final long integerMask = (1L << 33) - 1; 
 private static final long seedUniquifier = 8682522807148012L; 
 private static long seed; 
 static { 
 long s = seedUniquifier + System.nanoTime();
 s = (s ^ multiplier) & mask;
 seed = s;
 } public static final byte[] randomBytes(int size) { 
 byte[] bb = bytes; 
 byte[] ab = new byte[size]; 
 for (int i = 0; i < size; i++) {
 ab[i] = randomByte(bb);
 } return ab;
 } private static byte randomByte(byte[] b) { 
 int ran = (int) ((next() & integerMask) >>> 16); 
 return b[ran % b.length];
 } private static long next() { 
 long oldSeed = seed; 
 long nextSeed = 0L;
 do {
 nextSeed = (oldSeed * multiplier + addend) & mask;
 } 
 while (oldSeed == nextSeed);
 seed = nextSeed; 
 return nextSeed;
 }

}

1. mysql包基类

/**
 * 
 * @author seaboat
 * @date 2016-09-25
 * @version 1.0
 * <pre><b>email: </b>849586227@qq.com</pre>
 * <pre><b>blog: </b>http://www.gxlcms.com/;/pre>
 * <p>MySQLPacket is mysql's basic packet.</p>
 */public abstract class MySQLPacket {
 /**
 * none, this is an internal thread state
 */
 public static final byte COM_SLEEP = 0; /**
 * mysql_close
 */
 public static final byte COM_QUIT = 1; /**
 * mysql_select_db
 */
 public static final byte COM_INIT_DB = 2; /**
 * mysql_real_query
 */
 public static final byte COM_QUERY = 3; /**
 * mysql_list_fields
 */
 public static final byte COM_FIELD_LIST = 4; /**
 * mysql_create_db (deprecated)
 */
 public static final byte COM_CREATE_DB = 5; /**
 * mysql_drop_db (deprecated)
 */
 public static final byte COM_DROP_DB = 6; /**
 * mysql_refresh
 */
 public static final byte COM_REFRESH = 7; /**
 * mysql_shutdown
 */
 public static final byte COM_SHUTDOWN = 8; /**
 * mysql_stat
 */
 public static final byte COM_STATISTICS = 9; /**
 * mysql_list_processes
 */
 public static final byte COM_PROCESS_INFO = 10; /**
 * none, this is an internal thread state
 */
 public static final byte COM_CONNECT = 11; /**
 * mysql_kill
 */
 public static final byte COM_PROCESS_KILL = 12; /**
 * mysql_dump_debug_info
 */
 public static final byte COM_DEBUG = 13; /**
 * mysql_ping
 */
 public static final byte COM_PING = 14; /**
 * none, this is an internal thread state
 */
 public static final byte COM_TIME = 15; /**
 * none, this is an internal thread state
 */
 public static final byte COM_DELAYED_INSERT = 16; /**
 * mysql_change_user
 */
 public static final byte COM_CHANGE_USER = 17; /**
 * used by slave server mysqlbinlog
 */
 public static final byte COM_BINLOG_DUMP = 18; /**
 * used by slave server to get master table
 */
 public static final byte COM_TABLE_DUMP = 19; /**
 * used by slave to log connection to master
 */
 public static final byte COM_CONNECT_OUT = 20; /**
 * used by slave to register to master
 */
 public static final byte COM_REGISTER_SLAVE = 21; /**
 * mysql_stmt_prepare
 */
 public static final byte COM_STMT_PREPARE = 22; /**
 * mysql_stmt_execute
 */
 public static final byte COM_STMT_EXECUTE = 23; /**
 * mysql_stmt_send_long_data
 */
 public static final byte COM_STMT_SEND_LONG_DATA = 24; /**
 * mysql_stmt_close
 */
 public static final byte COM_STMT_CLOSE = 25; /**
 * mysql_stmt_reset
 */
 public static final byte COM_STMT_RESET = 26; /**
 * mysql_set_server_option
 */
 public static final byte COM_SET_OPTION = 27; /**
 * mysql_stmt_fetch
 */
 public static final byte COM_STMT_FETCH = 28; /**
 * mysql packet length
 */
 public int packetLength; /**
 * mysql packet id
 */
 public byte packetId; /**
 * calculate mysql packet length
 */
 public abstract int calcPacketSize(); /**
 * mysql packet info
 */
 protected abstract String getPacketInfo(); @Override
 public String toString() { return new StringBuilder().append(getPacketInfo()).append("{length=")
 .append(packetLength).append(",id=").append(packetId)
 .append('}').toString();
 }

}

1. 握手包类

/**
 * 
 * @author seaboat
 * @date 2016-09-25
 * @version 1.0
 * <pre><b>email: </b>849586227@qq.com</pre>
 * <pre><b>blog: </b>http://www.gxlcms.com/;/pre>
 * <p>AuthPacket means mysql initial handshake packet .</p>
 */public class HandshakePacket extends MySQLPacket {
 private static final byte[] FILLER_13 = new byte[] { 0, 0, 0, 0, 0, 0, 0, 
 0, 0, 0, 0, 0, 0 }; 
 public byte protocolVersion; 
 public byte[] serverVersion; 
 public long threadId; 
 public byte[] seed; 
 public int serverCapabilities; 
 public byte serverCharsetIndex; 
 public int serverStatus; 
 public byte[] restOfScrambleBuff; 
 public void read(byte[] data) {
 MySQLMessage mm = new MySQLMessage(data);
 packetLength = mm.readUB3();
 packetId = mm.read();
 protocolVersion = mm.read();
 serverVersion = mm.readBytesWithNull();
 threadId = mm.readUB4();
 seed = mm.readBytesWithNull();
 serverCapabilities = mm.readUB2();
 serverCharsetIndex = mm.read();
 serverStatus = mm.readUB2();
 mm.move(13);
 restOfScrambleBuff = mm.readBytesWithNull();
 } @Override
 public int calcPacketSize() { int size = 1;
 size += serverVersion.length;// n
 size += 5;// 1+4
 size += seed.length;// 8
 size += 19;// 1+2+1+2+13
 size += restOfScrambleBuff.length;// 12
 size += 1;// 1
 return size;
 } public void write(ByteBuffer buffer) {
 BufferUtil.writeUB3(buffer, calcPacketSize());
 buffer.put(packetId);
 buffer.put(protocolVersion);
 BufferUtil.writeWithNull(buffer, serverVersion);
 BufferUtil.writeUB4(buffer, threadId);
 BufferUtil.writeWithNull(buffer, seed);
 BufferUtil.writeUB2(buffer, serverCapabilities);
 buffer.put(serverCharsetIndex);
 BufferUtil.writeUB2(buffer, serverStatus);
 buffer.put(FILLER_13);
 BufferUtil.writeWithNull(buffer, restOfScrambleBuff);
 } @Override
 protected String getPacketInfo() { 
 return "MySQL Handshake Packet";
 }

}

1. 服务端能力类

/**
 * 
 * @author seaboat
 * @date 2016-09-25
 * @version 1.0
 * <pre><b>email: </b>849586227@qq.com</pre>
 * <pre><b>blog: </b>http://www.gxlcms.com/;/pre>
 * <p>server capabilities .</p>
 */public interface Capabilities {

 // new more secure passwords
 int CLIENT_LONG_PASSWORD = 1; // Found instead of affected rows
 int CLIENT_FOUND_ROWS = 2; // Get all column flags
 int CLIENT_LONG_FLAG = 4; // One can specify db on connect
 int CLIENT_CONNECT_WITH_DB = 8; // Don't allow database.table.column
 int CLIENT_NO_SCHEMA = 16; // Can use compression protocol
 int CLIENT_COMPRESS = 32; // Odbc client
 int CLIENT_ODBC = 64; // Can use LOAD DATA LOCAL
 int CLIENT_LOCAL_FILES = 128; // Ignore spaces before '('
 int CLIENT_IGNORE_SPACE = 256; // New 4.1 protocol This is an interactive client
 int CLIENT_PROTOCOL_41 = 512; // This is an interactive client
 int CLIENT_INTERACTIVE = 1024; // Switch to SSL after handshake
 int CLIENT_SSL = 2048; // IGNORE sigpipes
 int CLIENT_IGNORE_SIGPIPE = 4096; // Client knows about transactions
 int CLIENT_TRANSACTIONS = 8192; // Old flag for 4.1 protocol
 int CLIENT_RESERVED = 16384; // New 4.1 authentication
 int CLIENT_SECURE_CONNECTION = 32768; // Enable/disable multi-stmt support
 int CLIENT_MULTI_STATEMENTS = 65536; // Enable/disable multi-results
 int CLIENT_MULTI_RESULTS = 131072;

}

1. 测试类

/**
 * 
 * @author seaboat
 * @date 2016-09-25
 * @version 1.0
 * <pre><b>email: </b>849586227@qq.com</pre>
 * <pre><b>blog: </b>http://www.gxlcms.com/;/pre>
 * <p>test handshake packet.</p>
 */public class HandshakePacketTest {
 private final static byte[] hex = "0123456789ABCDEF".getBytes(); @Test
 public void produce() { 
 byte[] rand1 = RandomUtil.randomBytes(8); 
 byte[] rand2 = RandomUtil.randomBytes(12); 
 byte[] seed = new byte[rand1.length + rand2.length];
 System.arraycopy(rand1, 0, seed, 0, rand1.length);
 System.arraycopy(rand2, 0, seed, rand1.length, rand2.length);
 HandshakePacket hs = new HandshakePacket();
 hs.packetId = 0;
 hs.protocolVersion = Versions.PROTOCOL_VERSION;
 hs.serverVersion = Versions.SERVER_VERSION;
 hs.threadId = 1000;
 hs.seed = rand1;
 hs.serverCapabilities = getServerCapabilities();
 hs.serverCharsetIndex = (byte) (CharsetUtil.getIndex("utf8") & 0xff);
 hs.serverStatus = 2;
 hs.restOfScrambleBuff = rand2;

 ByteBuffer buffer = ByteBuffer.allocate(256);
 hs.write(buffer);
 buffer.flip(); 
 byte[] bytes = new byte[buffer.remaining()];
 buffer.get(bytes, 0, bytes.length);
 String result = Bytes2HexString(bytes);
 assertTrue(Integer.valueOf(result.substring(0,2),16)==result.length()/2-4);
 } public static String Bytes2HexString(byte[] b) { 
 byte[] buff = new byte[2 * b.length]; 
 for (int i = 0; i < b.length; i++) {
 buff[2 * i] = hex[(b[i] >> 4) & 0x0f];
 buff[2 * i + 1] = hex[b[i] & 0x0f];
 } return new String(buff);
 } public static String str2HexStr(String str) { 
 char[] chars = "0123456789ABCDEF".toCharArray();
 StringBuilder sb = new StringBuilder(""); 
 byte[] bs = str.getBytes(); 
 int bit; 
 for (int i = 0; i < bs.length; i++) {
 bit = (bs[i] & 0x0f0) >> 4;
 sb.append(chars[bit]);
 bit = bs[i] & 0x0f;
 sb.append(chars[bit]);
 } return sb.toString();
 } protected int getServerCapabilities() { 
 int flag = 0;
 flag |= Capabilities.CLIENT_LONG_PASSWORD;
 flag |= Capabilities.CLIENT_FOUND_ROWS;
 flag |= Capabilities.CLIENT_LONG_FLAG;
 flag |= Capabilities.CLIENT_CONNECT_WITH_DB;
 flag |= Capabilities.CLIENT_ODBC;
 flag |= Capabilities.CLIENT_IGNORE_SPACE;
 flag |= Capabilities.CLIENT_PROTOCOL_41;
 flag |= Capabilities.CLIENT_INTERACTIVE;
 flag |= Capabilities.CLIENT_IGNORE_SIGPIPE;
 flag |= Capabilities.CLIENT_TRANSACTIONS;
 flag |= Capabilities.CLIENT_SECURE_CONNECTION; 
 return flag;
 }

}