概况
mysql客户端登陆到mysql服务端需要一个交互的过程，这里先看服务端给客户端发送的初始握手包。如下，client通过socket连接到server指定的端口后，server将往client发送初始握手包。服务端会根据不同的服务版本和不同的配置返回不同的初始化握手包。
client server |------connect---- >| | | |<----handshake-----| | | | | | |
mysql通信报文结构
| 类型 | 名字 | 描述 |
|---|---|---|
| int | payload长度 | 按照the least significant byte first存储，3个字节的payload和1个字节的序列号组合成报文头 |
| int | 序列号 | |
| string | payload | 报文体，长度即为前面指定的payload长度 |
初始握手包
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
}
}生成初始握手包
定义版
/** * * @author seaboat * @date 2016-09-25 * @version 1.0 *email: 849586227@qq.com*blog: http://www.php.cn/;/pre> *proxy's version.
*/public interface Versions { byte PROTOCOL_VERSION = 10; byte[] SERVER_VERSION = "5.6.0-snapshot".getBytes(); }
随机数工具
/** * * @author seaboat * @date 2016-09-25 * @version 1.0 *email: 849586227@qq.com*blog: http://www.php.cn/;/pre> *a random util .
*/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; } }
mysql包基类
/** * * @author seaboat * @date 2016-09-25 * @version 1.0 *email: 849586227@qq.com*blog: http://www.php.cn/;/pre> *MySQLPacket is mysql's basic packet.
*/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 *email: 849586227@qq.com*blog: http://www.php.cn/;/pre> *AuthPacket means mysql initial handshake packet .
*/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 *email: 849586227@qq.com*blog: http://www.php.cn/;/pre> *server capabilities .
*/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 *email: 849586227@qq.com*blog: http://www.php.cn/;/pre> *test handshake packet.
*/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; } }
