• [知识整理] Mysql Driver & 协议解析


    曾经在负责Mysql时,那会觉得IO就应该是异步的,但是C# 版本Mysql Driver竟然只支持同步调用方式,简直不可接受哇。

    然后就研究Mysql Driver,并改了一版异步的实现,并且做了一次小组分享: PPT抓包分析 (2011/11/3)。

    接下来为应对故障排查,也做了一版将Mysql 的Pcap抓包解析成为明文文本的工具,就是下文了。想想那会儿真是比较闲了,还能折腾点儿事,最近两年陷入业务不可自拔,荒废了。。。

    Mysql的客户服服务器协议还是比较简单的,官方有详细的协议说明:http://dev.mysql.com/doc/internals/en/client-server-protocol.html

    曾经想写协议解析工具,是为了mysql相关的问题排查和mysql请求统计分析,后来觉得重要性不高,没时间做、只完成request部分解析。

    代码写了一部分,不过也能正常工作了,记录一下:

       1:  class MysqlQueryParser : MysqlParser
       2:      {
       3:          private enum ParsingState { PacketHead, Body }
       4:          private StreamBuffer _buffer;
       5:          private ParsingState _state;
       6:          private MysqlQueryPacket _packet;
       7:          public MysqlQueryParser()
       8:          {
       9:              SipcStack.Initialize();
      10:              _buffer = new StreamBuffer();
      11:              _state = ParsingState.PacketHead;
      12:              _packet = new MysqlQueryPacket();
      13:          }
      14:   
      15:          public override void Parse(byte[] data)
      16:          {
      17:              _buffer.Write(data);
      18:              TryParse();
      19:          }
      20:   
      21:          private void TryParse()
      22:          {
      23:              if (_state == ParsingState.PacketHead)
      24:              {
      25:                  if (_buffer.Size >= 4)
      26:                  {
      27:                      byte[] head = new byte[4];
      28:                      _buffer.Read(head, 0, 4);
      29:                      ReadPacketHeader(head, out _packet.PacketLength, out _packet.PacketNum);
      30:                      _state = ParsingState.Body;
      31:                  }
      32:                  else
      33:                      return;
      34:              }
      35:   
      36:              if (_state == ParsingState.Body)
      37:              {
      38:                  if (_buffer.Size >= _packet.PacketLength)
      39:                  {
      40:                      byte[] body = new byte[_packet.PacketLength];
      41:                      _buffer.Read(body, 0, _packet.PacketLength);
      42:                      _packet.Body = body;
      43:                      _packet.Command = (DBCmd)body[0];
      44:                      HandleParseOk();
      45:                  }
      46:              }
      47:          }
      48:   
      49:          private void HandleParseError(string msg)
      50:          {
      51:              _buffer.Close();
      52:              _state = ParsingState.PacketHead;
      53:              _packet = new MysqlQueryPacket();
      54:   
      55:              if (OnParsedError != null)
      56:                  OnParsedError();
      57:          }
      58:   
      59:          private void HandleParseOk()
      60:          {
      61:              if (OnParsedPacket != null)
      62:              {
      63:                  _packet.Time = LastTime;
      64:                  _packet.SrcEP = LastSrcEP;
      65:                  _packet.DstEP = LastDstEP;
      66:                  OnParsedPacket(_packet);
      67:              }
      68:              _state = ParsingState.PacketHead;
      69:              _packet = new MysqlQueryPacket();
      70:          }
      71:      }
       1:    enum ResultSetType
       2:      {
       3:          OK,
       4:          Error,
       5:          ResultSet,
       6:      }
       7:   
       8:      class MysqlResultSetPacket : MysqlPacket
       9:      {
      10:          public MysqlResultSetPacket()
      11:          {
      12:              PacketType = PacketType.ResultSet;
      13:          }
      14:   
      15:          public ResultSetType ResultSetType { get; set; }
      16:      }
      17:   
      18:      class MysqlOKResultSet : MysqlResultSetPacket
      19:      {
      20:          public MysqlOKResultSet()
      21:          {
      22:              ResultSetType = ResultSetType.OK;
      23:          }
      24:          public int PacketLength;
      25:          public int PacketNum;
      26:          public int AffectRow;
      27:          public int ServerState;
      28:          public string Message;
      29:      }
      30:   
      31:      class MysqlOKResultSet : MysqlResultSetPacket
      32:      {
      33:          public MysqlOKResultSet()
      34:          {
      35:              ResultSetType = ResultSetType.Error;
      36:          }
      37:          public int PacketLength;
      38:          public int PacketNum;
      39:          public int ErrorNum;
      40:          public int SqlState;
      41:          public string Message;
      42:      }
      43:   
      44:      class MysqlDataResultSet : MysqlResultSetPacket
      45:      {
      46:          public ResultSetHeadPacket HeadPacket;
      47:          public List<ResultSetFieldPacket> FieldPacket;
      48:          public List<ResultSetRowPacket> RowPacket;
      49:   
      50:          public MysqlDataResultSet()
      51:          {
      52:              ResultSetType = ResultSetType.ResultSet;
      53:          }
      54:      }
      55:   
      56:      public class ResultSetHeadPacket
      57:      {
      58:          public int PacketLength;
      59:          public int PacketNum;
      60:          public int FieldNum;
      61:      }
      62:   
      63:      public class ResultSetFieldPacket
      64:      {
      65:          public int PacketLength;
      66:          public int PacketNum;
      67:      }
      68:   
      69:      public class ResultSetRowPacket
      70:      {
      71:          public int PacketLength;
      72:          public int PacketNum;
      73:      }
       1:  abstract class MysqlParser
       2:      {
       3:          public Action<MysqlPacket> OnParsedPacket;
       4:          public  Action OnParsedError;
       5:   
       6:          public DateTime LastTime { get; set; }
       7:          public IPEndPoint LastSrcEP { get; set; }
       8:          public IPEndPoint LastDstEP { get; set; }
       9:   
      10:          public abstract void Parse(byte[] data);
      11:   
      12:          //The Packet Header
      13:          //Bytes                 Name
      14:          // -----                 ----
      15:          // 3                     Packet Length
      16:          // 1                     Packet Number
      17:   
      18:          // Packet Length: The length, in bytes, of the packet
      19:          //                that follows the Packet Header. There
      20:          //                may be some special values in the most
      21:          //                significant byte. The maximum packet 
      22:          //                length is (2**24 -1),about 16MB.
      23:   
      24:          // Packet Number: A serial number which can be used to
      25:          //                ensure that all packets are present
      26:          //                and in order. The first packet of a
      27:          //                client query will have Packet Number = 0
      28:          //                Thus, when a new SQL statement starts, 
      29:          //                the packet number is re-initialised.
      30:   
      31:          protected void ReadPacketHeader(byte[] header,out int packetLength,out int packetNum)
      32:          {
      33:              if (header == null || header.Length != 4)
      34:                  throw new ArgumentException();
      35:   
      36:              packetLength = (int)header[0] + (((int)header[1]) << 8) + (((int)header[3]) << 16);
      37:              packetNum = header[3];
      38:          }
      39:      }
       1:  class MysqlQueryPacket : MysqlPacket
       2:      {
       3:          public MysqlQueryPacket()
       4:          {
       5:              this.PacketType = PacketType.Query;
       6:          }
       7:          public int PacketLength;
       8:          public int PacketNum;
       9:          public byte[] Body;
      10:          public DBCmd Command;
      11:   
      12:          private string _commandArgs;
      13:          public string CommandArgs
      14:          {
      15:              get
      16:              {
      17:                  if (Command == DBCmd.QUERY && Body != null && Body.Length > 0)
      18:                  {
      19:                      if (_commandArgs == null)
      20:                          _commandArgs = Encoding.UTF8.GetString(Body,1,Body.Length-1);
      21:                  }
      22:   
      23:                  return _commandArgs;
      24:              }
      25:          }
      26:   
      27:          public MysqlCommandArgs ResolveCommand()
      28:          {
      29:              MysqlCommandArgs args = new MysqlCommandArgs();
      30:              args.CmdType = CommandType.Text;
      31:              if (CommandArgs != null)
      32:              {
      33:                  if (CommandArgs.Substring(0, 4) == "CALL")
      34:                  {
      35:                      args.CmdType = CommandType.StoredProcedure;
      36:   
      37:                      int start = CommandArgs.IndexOf("(");
      38:                      if (start > 3)
      39:                          args.ResolveQuery = CommandArgs.Substring(3, start - 3);
      40:                  }
      41:                  else
      42:                  {
      43:                      //todo replace ='xx' to ={}
      44:                  }
      45:              }
      46:   
      47:              return args;
      48:          }
      49:      }
      50:   
      51:      class MysqlCommandArgs
      52:      {
      53:          public CommandType CmdType { get; set; }
      54:          public string ResolveQuery { get; set; }
      55:      }
       1:   enum PacketType
       2:      {
       3:          Query,
       4:          ResultSet,
       5:      }
       6:      class MysqlPacket
       7:      {
       8:          public DateTime Time { get; set; }
       9:          public IPEndPoint SrcEP { get; set; }
      10:          public IPEndPoint DstEP { get; set; }
      11:   
      12:          public PacketType PacketType { get; set; }
      13:      }
       1:  enum DBCmd : byte
       2:      {
       3:          SLEEP = 0,
       4:          QUIT = 1,
       5:          INIT_DB = 2,
       6:          QUERY = 3,
       7:          FIELD_LIST = 4,
       8:          CREATE_DB = 5,
       9:          DROP_DB = 6,
      10:          RELOAD = 7,
      11:          SHUTDOWN = 8,
      12:          STATISTICS = 9,
      13:          PROCESS_INFO = 10,
      14:          CONNECT = 11,
      15:          PROCESS_KILL = 12,
      16:          DEBUG = 13,
      17:          PING = 14,
      18:          TIME = 15,
      19:          DELAYED_INSERT = 16,
      20:          CHANGE_USER = 17,
      21:          BINLOG_DUMP = 18,
      22:          TABLE_DUMP = 19,
      23:          CONNECT_OUT = 20,
      24:          REGISTER_SLAVE = 21,
      25:          PREPARE = 22,
      26:          EXECUTE = 23,
      27:          LONG_DATA = 24,
      28:          CLOSE_STMT = 25,
      29:          RESET_STMT = 26,
      30:          SET_OPTION = 27,
      31:          FETCH = 28
      32:      }
  • 相关阅读:
    php数据库搜索用法
    实现留言板功能
    php对数据库的增删改
    签到 登录注册注意事项
    JavaScript复习 js登录简单实现 dom操作练习
    数据库表格老师学生教师表练习题
    数据控制语言
    数据操作语言
    数据库表的定义、视图与设计
    php变量
  • 原文地址:https://www.cnblogs.com/lulu/p/3130837.html
Copyright © 2020-2023  润新知