Writing MySQL Proxy in GO for self-learning: Part 1 — TCP Proxy

Weekend. Quarantine. If you bored like me, and want to learn something new, you can join my journey of writing yet another MySQL Proxy Server.

In the past, I had a crazy idea of writing MySQL Proxy Server. The first reason was to understand the MySQL Communication Protocol to let me write more efficient programs. The second one was to learn more about low-level network programming concepts in general. Finally, I have time at least to start… and you can join me!

The real usage of MySQL Proxy can vary:

  • Testing and benchmarking of the infrastructure
  • Testing your applications for dealing with MySQL network failures and delays
  • Wrap the MySQL usage with own business layer
  • Load balancing
  • Multiplexing

Prelude

  • HTTP Web Server
  • HTTP Client
  • TCP Socket, TCP Connection
  • Port
  • MySQL
  • Any TCP/HTTP Proxy (NGINX, ELB)

If you ever did an HTTP request, I believe you are mature enough to start this journey and google for the missing knowledge gaps.

Also, I assume you are fluent in Golang, to be able to understand the code and build the binary.

If you know what is epoll/kqueue I think you can skip this article ;)

Disclaimer

Also, the code will be far away from being production-ready. I’ll not focus on things like memory usage, efficient resource utilization, connection pools, and low-level things like epoll. At least not at the start. The main purpose is a self-learning process of MySQL Protocol, and enjoying GO programming in general :)

Requirements

  • Golang environment installed
  • macOS or Linux
  • IDE

Let’s start

Our TCP Proxy will be implemented in a very naive approach:

  • Create a TCP Server that will listen for incoming TCP connection
  • Accept the connection, and run a handler as a go-routine
  • Create TCP connection to MySQL
  • Start proxying byte stream from the client to the MySQL Server, and back by using pipes

To create our TCP Proxy, we will use the standard library and its famous net package.

Let’s define the main Proxy Interface. As a start, we will provide only destination host and port, and the port the proxy itself will be discoverable for connections. Simple enough.

The Proxy struct will receive a new connection, and handle it by invoking its Handle() method of the “Connection” object inside a go-routine.

proxy.go

And finally, the connection will be responsible for transferring the data between the client and the MySQL Server.

We’ll use net.Deal() method to open a TCP connection to MySQL Server. as we need to transfer the data in both directions, it must be done in different go-routines. To physically transfer the data, we’ll use io.Copy() function.

io.Copy copies the byte stream from src to dst until either EOF is reached on src or an error occurs. When it finished, it returns the number of bytes copied and the first error encountered while copying, if any.

connection.go

That all we need to implement the simplest TCP Proxy in go. Yeah, it lacks retries, proper error handling and not so efficient. But again, I don’t care about it at the current stage of our journey.

Let’s try it. I’ll do it using free MySQL Workbench GUI. I have already prepared two connections to my local MySQL Server instance running in docker. You can use any other MySQL Client.

When I clicked on proxy connection configuration, this is what I saw.

Oh… I can see that MySQL Workbench opens 2 connections to MySQL. Also, I tried to run a select query, and it worked as expected.

Then I did close the tab with the connection, and as you can see, the connections are gone (I’ll improve the logs next time, promise ;))

That’s all. Of course, I wouldn’t use this in production, but it’s good enough for us to start our journey :)

Summary

go run .

from its directory.

Repo: https://github.com/spaiz/go-mysql-proxy

What’s next?

  • We will introduce abstractions so our TCP Proxy will be more modular and maintainable
  • We’ll do refactor and get rid of io.Copy() function, cause we need access to a raw byte stream
  • We’ll learn a general MySQL Protocol concepts
  • We’ll implement decoding of MySQL Connection Phase
  • We’ll meet go-sql-driver/mysql, and use it in tests
  • At the and, we’ll have working partially implemented MySQL Proxy (Connection Phase)

P.S.

If you never did a network programming or/and you are new at programming in general, there is a bunch of concepts you’ll need to learn from this a very short program. Just google for: threads vs go-routines, blocking operations (like io.Copy), TCP Sockets, TCP Full-duplex, TCP Socket vs Port, Proxy Server.

P.S.2

P.S.3

In GO we trust. Software Engineer.