详细介绍mysql 协议的服务端握手包及对其解析-mysql教程-PHP中文网

首页

数据库

mysql教程

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

黄舟

May 12, 2018 pm 04:41 PM

概况

mysql客户端登陆到mysql服务端需要一个交互的过程，这里先看服务端给客户端发送的初始握手包。如下，client通过socket连接到server指定的端口后，server将往client发送初始握手包。服务端会根据不同的服务版本和不同的配置返回不同的初始化握手包。

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

mysql通信报文结构

类型	名字	描述
int5bdf4c78156c7953567bb5a0aef2fc53	payload长度	按照the least significant byte first存储，3个字节的payload和1个字节的序列号组合成报文头
intf35d6e602fd7d0f0edfa6f7d103c1b57	序列号
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
 * <pre class="brush:php;toolbar:false"><b>email: </b>849586227@qq.com

<b>blog: </b>http://www.php.cn/;/pre>
 * <p>proxy&#39;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 = { &#39;1&#39;, &#39;2&#39;, &#39;3&#39;, &#39;4&#39;, &#39;5&#39;, &#39;6&#39;, &#39;7&#39;,            
    &#39;8&#39;, &#39;9&#39;, &#39;0&#39;, &#39;q&#39;, &#39;w&#39;, &#39;e&#39;, &#39;r&#39;, &#39;t&#39;, &#39;y&#39;, &#39;u&#39;, &#39;i&#39;, &#39;o&#39;, &#39;p&#39;,            
    &#39;a&#39;, &#39;s&#39;, &#39;d&#39;, &#39;f&#39;, &#39;g&#39;, &#39;h&#39;, &#39;j&#39;, &#39;k&#39;, &#39;l&#39;, &#39;z&#39;, &#39;x&#39;, &#39;c&#39;, &#39;v&#39;,            
    &#39;b&#39;, &#39;n&#39;, &#39;m&#39;, &#39;Q&#39;, &#39;W&#39;, &#39;E&#39;, &#39;R&#39;, &#39;T&#39;, &#39;Y&#39;, &#39;U&#39;, &#39;I&#39;, &#39;O&#39;, &#39;P&#39;,            
    &#39;A&#39;, &#39;S&#39;, &#39;D&#39;, &#39;F&#39;, &#39;G&#39;, &#39;H&#39;, &#39;J&#39;, &#39;K&#39;, &#39;L&#39;, &#39;Z&#39;, &#39;X&#39;, &#39;C&#39;, &#39;V&#39;,            
    &#39;B&#39;, &#39;N&#39;, &#39;M&#39; };    
    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
 * <pre class="brush:php;toolbar:false"><b>email: </b>849586227@qq.com

<b>blog: </b>http://www.php.cn/;/pre>
 * <p>MySQLPacket is mysql&#39;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(&#39;}&#39;).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&#39;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 &#39;(&#39;
    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;
    }

}

以上是详细介绍mysql 协议的服务端握手包及对其解析的详细内容。更多信息请关注PHP中文网其他相关文章！

声明

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn

MySQL与Sqlite有何不同？Apr 24, 2025 am 12:12 AM

MySQL和SQLite的主要区别在于设计理念和使用场景：1.MySQL适用于大型应用和企业级解决方案，支持高性能和高并发；2.SQLite适合移动应用和桌面软件，轻量级且易于嵌入。

MySQL中的索引是什么？它们如何提高性能？Apr 24, 2025 am 12:09 AM

MySQL中的索引是数据库表中一列或多列的有序结构，用于加速数据检索。1）索引通过减少扫描数据量提升查询速度。2）B-Tree索引利用平衡树结构，适合范围查询和排序。3）创建索引使用CREATEINDEX语句，如CREATEINDEXidx_customer_idONorders(customer_id)。4）复合索引可优化多列查询，如CREATEINDEXidx_customer_orderONorders(customer_id,order_date)。5）使用EXPLAIN分析查询计划，避

说明如何使用MySQL中的交易来确保数据一致性。Apr 24, 2025 am 12:09 AM

在MySQL中使用事务可以确保数据一致性。1)通过STARTTRANSACTION开始事务，执行SQL操作后用COMMIT提交或ROLLBACK回滚。2)使用SAVEPOINT可以设置保存点，允许部分回滚。3)性能优化建议包括缩短事务时间、避免大规模查询和合理使用隔离级别。

在哪些情况下，您可以选择PostgreSQL而不是MySQL？Apr 24, 2025 am 12:07 AM

选择PostgreSQL而非MySQL的场景包括：1)需要复杂查询和高级SQL功能，2)要求严格的数据完整性和ACID遵从性，3)需要高级空间功能，4)处理大数据集时需要高性能。PostgreSQL在这些方面表现出色，适合需要复杂数据处理和高数据完整性的项目。

如何保护MySQL数据库？Apr 24, 2025 am 12:04 AM

MySQL数据库的安全可以通过以下措施实现：1.用户权限管理：通过CREATEUSER和GRANT命令严格控制访问权限。2.加密传输：配置SSL/TLS确保数据传输安全。3.数据库备份和恢复：使用mysqldump或mysqlpump定期备份数据。4.高级安全策略：使用防火墙限制访问，并启用审计日志记录操作。5.性能优化与最佳实践：通过索引和查询优化以及定期维护兼顾安全和性能。

您可以使用哪些工具来监视MySQL性能？Apr 23, 2025 am 12:21 AM

如何有效监控MySQL性能？使用mysqladmin、SHOWGLOBALSTATUS、PerconaMonitoringandManagement(PMM)和MySQLEnterpriseMonitor等工具。1.使用mysqladmin查看连接数。2.用SHOWGLOBALSTATUS查看查询数。3.PMM提供详细性能数据和图形化界面。4.MySQLEnterpriseMonitor提供丰富的监控功能和报警机制。

MySQL与SQL Server有何不同？Apr 23, 2025 am 12:20 AM

MySQL和SQLServer的区别在于：1)MySQL是开源的，适用于Web和嵌入式系统，2)SQLServer是微软的商业产品，适用于企业级应用。两者在存储引擎、性能优化和应用场景上有显着差异，选择时需考虑项目规模和未来扩展性。

在哪些情况下，您可以选择SQL Server而不是MySQL？Apr 23, 2025 am 12:20 AM

在需要高可用性、高级安全性和良好集成性的企业级应用场景下，应选择SQLServer而不是MySQL。1)SQLServer提供企业级功能，如高可用性和高级安全性。2)它与微软生态系统如VisualStudio和PowerBI紧密集成。3)SQLServer在性能优化方面表现出色，支持内存优化表和列存储索引。

See all articles