Wrote a MySQL agent

Time:2021-9-2

GitHub addressgithub.com/moodrain/mysql-proxy

background

Because I mainly wrote PHP before, knowing that PHP needs to reconnect once every request will affect the performance of MySQL. Some proxy libraries such as smproxy and kingshard are also found later. These libraries are very perfect and can be used in the production environment without recycling wheels. But for the purpose of learning golang, let’s try writing MySQL agent.

sketch

It mainly uses the convenience of golang collaboration to create multiple collaboration processes and establish connections with MySQL. When PHP finishes processing the request to release the connection, golang maintains the connection and reuses the connection the next time PHP opens the connection.

The main codes are as follows

for i := 0; i < connCount; i++ {

    go func(proxy lib.ProxyConn) {

        //It is blocked here. You need to wait for a new client request when connecting for the first time
        proxy.NewClientConn(server)
        proxy.NewMysqlConn(mysqlUrl)

        //The first connection requires a complete MySQL and client handshake process
        err := proxy.Handshake()
        if err != nil {
            proxy.Close()
        }

        go proxy.PipeMysql2Client()
        go proxy.PipeClient2Mysql()

        for {
            if !proxy.IsClientClose() {
                continue
            }
            //When the client closes the connection, it is blocked and the agent waits for a new client connection
            proxy.NewClientConn(server)
            //Because the proxy maintains a connection with MySQL, the proxy only needs a fake handshake with the client
            err := proxy.FakeHandshake()
            if err != nil {
                proxy.CloseClient()
            }
            go proxy.PipeClient2Mysql()
        }

    }(connList[i])

}

reference resources

MySQL packet structure

Official documents

  1. The first three bytes in a packet represent the length of the payload (so the maximum length is ffffff 16777215, that is, the 24th power of 2 – 1)
  2. The fourth byte represents the serial number. The serial number increases from 0 until it is reset back to 0 after the command is completed
  3. The rest belongs to payload
MySQL protocol authentication process

Official documents

  1. After the client connects, MySQL sends an initial handshake packet
  2. The client sends a handshake response packet after processing
  3. After MySQL authentication is passed, return OK_ Packet, and then the client starts sending commands such as query
Ignore disconnect command

Official documents

After processing the request, the PHP program will send it to MySQL0x01 COM_QUITCommand, which will cause Mysql to close the connection. The agent ignores this command in order to maintain and reuse the connection

remarks

  1. This example is only used to learn and verify golang’s MySQL agent. Because I am a beginner of golang, I can’t guarantee the correctness and efficiency of the code (I tested PHP to connect to golang agent mysql, but it is less efficient). If you find any problems, please comment
  2. This example only describes the general of MySQL protocol and is not completely accurate. Please refer to the official document for the complete usage

This work adoptsCC agreement, reprint must indicate the author and the link to this article

Recommended Today

STM32 IIC details

1. IIC definition IIC is inter integrated circuit bus. This bus type is a simple, bidirectional, two-wire and synchronous serial bus designed by Philips semiconductor company (later acquired by NXP) in the early 1980s. It is mainly used to connect the overall circuit (ICS) , IIC is a multi-directional control bus, that is, multiple chips […]