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

client                server     |------connect---- &gt;|     |                   |     |<h2>mysql通信报文结构</h2>

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.6.0 and = 5.5.10 or >= 5.6.2 {  string[NUL]    auth-plugin name      }    }

<b>blog: </b>http://www.php.cn/;/pre>   * <p>proxy's version.</p>   */public Interface Versions {        byte PROTOCOL_VERSION = 10;    byte[] SERVER_VERSION = "5.6.0-snapshot".getBytes();  }

<b>blog: </b>http://www.php.cn/;/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;      }    }

<b>blog: </b>http://www.php.cn/;/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();      }    }

<b>blog: </b>http://www.php.cn/;/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";      }    }

<b>blog: </b>http://www.php.cn/;/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;    }

<b>blog: </b>http://www.php.cn/;/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;      }    }