概況
mysql客戶端登陸到mysql服務端需要一個互動的流程，這裡先看服務端傳送給客戶端的初始握手包。如下，client透過socket連接到server指定的連接埠後，server會傳送初始握手包給client。服務端會根據不同的服務版本和不同的配置傳回不同的初始化握手包。
client server |------connect---- >| | | |<----handshake-----| | | | | | |
| 類型 | 名字 | 描述 |
|---|---|---|
| int<3> | payload長度 | #依照the least significant byte first存儲，3個字節的payload和1個字節的序號組合成封包頭 |
| int<1> | #序號 | |
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 class="brush:php;toolbar:false"><b>email: </b>849586227@qq.com*
<b>blog: </b>//m.sbmmt.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 class="brush:php;toolbar:false"><b>email: </b>849586227@qq.com*
<b>blog: </b>//m.sbmmt.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 class="brush:php;toolbar:false"><b>email: </b>849586227@qq.com*
<b>blog: </b>//m.sbmmt.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 class="brush:php;toolbar:false"><b>email: </b>849586227@qq.com*
<b>blog: </b>//m.sbmmt.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 class="brush:php;toolbar:false"><b>email: </b>849586227@qq.com*
<b>blog: </b>//m.sbmmt.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 class="brush:php;toolbar:false"><b>email: </b>849586227@qq.com*
<b>blog: </b>//m.sbmmt.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;
}
}以上是詳細介紹mysql 協定的服務端握手包及對其解析的詳細內容。更多資訊請關注PHP中文網其他相關文章！
