詳細介紹mysql協議的服務端握手包及對其解析

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

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
 }
 }

/**
 * 
 * @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();
}

/**
 * 
 * @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;
 }

}

/**
 * 
 * @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();
 }

}

/**
 * 
 * @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";
 }

}

/**
 * 
 * @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;

}

/**
 * 
 * @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;
 }

}