Overview
Mysql client logging in to the mysql server requires an interactive process. Here we first look at the initial handshake packet sent by the server to the client. As shown below, after the client connects to the port specified by the server through the socket, the server will send an initial handshake packet to the client. The server will return different initialization handshake packets according to different service versions and different configurations.
client server |------connect---- >| | | |<----handshake-----| | | | | | |
| Type | Name | Description |
|---|---|---|
| int<3> | payload length | Stored according to the least significant byte first, 3-byte payload and 1-byte sequence number Combined into a message header |
| int<1> | sequence number | |
| payload | Message body, the length is the previously specified payload length |
HandshakeV10 protocol is as follows
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>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();
}/** * * @author seaboat * @date 2016-09-25 * @version 1.0 * <pre class="brush:php;toolbar:false"><b>email: </b>849586227@qq.com*
<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;
}
}/** * * @author seaboat * @date 2016-09-25 * @version 1.0 * <pre class="brush:php;toolbar:false"><b>email: </b>849586227@qq.com*
<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();
}
}/** * * @author seaboat * @date 2016-09-25 * @version 1.0 * <pre class="brush:php;toolbar:false"><b>email: </b>849586227@qq.com*
<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";
}
}/** * * @author seaboat * @date 2016-09-25 * @version 1.0 * <pre class="brush:php;toolbar:false"><b>email: </b>849586227@qq.com*
<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;
}/** * * @author seaboat * @date 2016-09-25 * @version 1.0 * <pre class="brush:php;toolbar:false"><b>email: </b>849586227@qq.com*
<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;
}
}The above is the detailed content of Detailed introduction to the server handshake package of the mysql protocol and its analysis. For more information, please follow other related articles on the PHP Chinese website!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
