mycat mysql协议

一、握手阶段
1.client跟server之间进行tcp三次握手
2.server发送handshake
3.client读取handshake，构建发送auth packet, 其中加密后的pass = encrypt(明文pass, seed, restOfScrambleBuff) ，seed和restOfScrambleBuff由handshake返回
4.server认证auth packet，如果认证成功则发送okpacket，否则返回errorpacket
至此握手完成, client端就可以发送真正的sql命令，进入执行阶段

二、执行阶段
以select查询为例，如sql = "select * from t1 limit 0,10;"
1.client发送command packet (select语句相应的command=COM_QUERY)
2.server执行完毕之后，并返回结果(5个packet)
3.client先读取metadata数据包(前3个packet)
ResultSetHeadPacket(有多少个column) + FieldPacket[columns] + EOFPacket
4.client接着读取真正的数据(后2个packet) RowDataPacket[rows] + EOFPacket

三、握手阶段demo

/**
     * Handshake握手阶段
     */
    public boolean handshakePhase(BufferedOutputStream out, BufferedInputStream in) throws IOException {
        //STEP-1:读取handshake握手包
        byte[] buffer = new byte[1024];
        int reads = in.read(buffer);
        byte[] data = Arrays.copyOf(buffer, reads);
        HandshakePacket handshakePacket = new HandshakePacket();
        handshakePacket.read(data);
        System.out.println(String.format("一、读取handshake数据包， packetId = %d, seed = %s, restOfScrambleBuff = %s\n", handshakePacket.packetId, new String(handshakePacket.seed), new String(handshakePacket.restOfScrambleBuff)));

        //STEP-2:发送auth认证包
        AuthPacket authPacket = new AuthPacket();
        authPacket.packetId = 1;
        authPacket.clientFlags = getClientFlags();
        authPacket.maxPacketSize = 1024 * 1024 * 16;
        authPacket.charsetIndex = handshakePacket.serverCharsetIndex & 0xff;
        authPacket.user = USERNAME;
        authPacket.password = passwd(PASSWORD, handshakePacket.seed, handshakePacket.restOfScrambleBuff);
        authPacket.database = DB;
        authPacket.write(out);
        out.flush();
        System.out.println(String.format("二、发送AuthPacket数据包， username = %s, database = %s.\n", authPacket.user, authPacket.database));

        //STEP-3:读取服务器auth的认证响应包，成功为OkPacket,失败为ErrorPacket
        buffer = new byte[1024];
        reads = in.read(buffer);
        data = Arrays.copyOf(buffer, reads);

        if (data[4] == OkPacket.FIELD_COUNT) {
            OkPacket okPacket = new OkPacket();
            okPacket.read(data);
            System.out.println("三、auth认证成功. " + okPacket + "\n");
            return true;
        }
        else if (data[4] == ErrorPacket.FIELD_COUNT) {
            ErrorPacket errorPacket = new ErrorPacket();
            errorPacket.read(data);
            System.out.println("三、auth认证失败. " + errorPacket.errno + "  " + new String(errorPacket.message) + "\n");
        }
        return false;
    }

四、执行阶段demo

/**
     * 真正执行SQL命令阶段
     * 1个ResultSet Head Packet (返回有多少个column)
     * n个Field Packet (n = column number)
     * 1个EOF Packet
     * m个Row Data Packet (m = row number)
     * 1个EOF Packet
     */
    public void executePhase(BufferedOutputStream out, BufferedInputStream in) throws IOException {
        //发送SQL命令请求数据包
        CommandPacket commandPacket = new CommandPacket();
        commandPacket.packetId = 0;
        commandPacket.command = CommandPacket.COM_QUERY;
        commandPacket.arg = SQL.getBytes();
        commandPacket.write(out);
        out.flush();
        System.out.println("四、发送SQL命令. " + SQL + "\n");

        byte[] buffer = new byte[10240];
        int reads = in.read(buffer);
        ByteBuffer bb = ByteBuffer.wrap(buffer, 0, reads);

        //读取ResultSetHeaderPacket
        ResultSetHeaderPacket headerPacket = new ResultSetHeaderPacket();
        headerPacket.read(bb.array());
        int packetLength = headerPacket.packetLength;
        int packetId = headerPacket.packetId;
        int fieldCount = headerPacket.fieldCount;
        System.out.println(String.format("五、读取ResultSetHeaderPacket. packetLength = %d, packetId = %d, fieldCount = %d\n", packetLength, packetId, fieldCount));
        bb = clearCurrentPacket(bb, 4 + headerPacket.packetLength);

        //读取多个FieldPacket
        System.out.println("六、读取FieldPacket信息.......");
        System.out.println("----------------------------------------------------------------------------------------");
        for (int i = 0; i < fieldCount; i++) {
            FieldPacket fieldPacket = new FieldPacket();
            fieldPacket.read(bb.array());
            //field.type信息可参考io.mycat.config.Fields中定义的常量
            System.out.println(new String(fieldPacket.name) + ", " + fieldPacket.type + ", " + fieldPacket.length + " ");

            bb = clearCurrentPacket(bb, 4 + fieldPacket.packetLength);
        }
        System.out.println("----------------------------------------------------------------------------------------\n");

        //读取EOFPacket
        EOFPacket eofPacket = new EOFPacket();
        eofPacket.read(bb.array());
        packetLength = eofPacket.packetLength;
        packetId = eofPacket.packetId;
        int warningCount = eofPacket.warningCount;
        int status = eofPacket.status;
        System.out.println("七、读取EOFPackage. packetId = " + packetId + " " + "\n");
        bb = clearCurrentPacket(bb, 4 + packetLength);

        //读取多个RowDataPacket
        System.out.println("八、读取RowDataPacket.......");
        System.out.println("----------------------------------------------------------------------------------------");
        EOFPacket lastEofPacket = null; //最后一个EOFPacket
        while(bb.hasRemaining()) {
            byte[] array = bb.array();
            //读取最后一个EOFPacket
            if (array[4] == EOFPacket.FIELD_COUNT) {
                lastEofPacket = new EOFPacket();
                lastEofPacket.read(array);
                break;
            }
            RowDataPacket rowDataPacket = new RowDataPacket(fieldCount);
            rowDataPacket.read(bb.array());
            //System.out.println("RowDataPackage.  packetId = " + rowDataPacket.packetId + "  fieldValues size = " + rowDataPacket.fieldValues.size());
            for (int i = 0; i < rowDataPacket.fieldValues.size(); i++) {
                byte[] row = rowDataPacket.fieldValues.get(i);
                if (row == null) {
                    System.out.print("null, ");
                }
                else {
                    System.out.print(new String(row) + ", ");
                }
            }
            System.out.println();
            bb = clearCurrentPacket(bb, 4 + rowDataPacket.packetLength);
        }
        System.out.println("----------------------------------------------------------------------------------------\n");

        //输出最后一个EOFPacket
        if (lastEofPacket != null) {
            System.out.println("九、读取最后一个EOFPacket. packetId = " + lastEofPacket.packetId);
        }

    }