曾经在负责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: }