MySQL Proxy

When I start programming, the first thing I do is… starting MySQL Proxy.

This is a very small program that runs between the web server and database server. It’s behaviour can be configured with a LUA script.

I will not give a complete overview of everything that the MySQL Proxy can do. If you want this, go to the homepage of the proxy.

How does it work?

You start the MySQL Proxy in command line, and give some parameters with it. The most important ones are:

  • the LUA script to run
  • the database server host and port
mysql-proxy.exe --proxy-lua-script=script.lua
                --proxy-backend-addresses=<db_server>:<port>

In your local application code (PHP in my case), instead of configuring your database to the <db_server> and <port>, you write “localhost” for the host and “4040” for the port. Now the local web server connects to the MySQL Proxy, and the proxy sends the queries to the database server.

What does it do?

In my case, I use it to monitor all the queries that my web server sends to the database. When I start my application in the browser, I see the queries passing in the command line window. Here is an example:

-MySQL Query- SET NAMES 'utf8'
-REQtime- 0ms
-QueryNr- 1
-Conn.Nr- 1
-MySQL Query- SELECT
              u.id AS user_id,
              u.name AS name,
              u.address AS address,
              p.postal_code AS postal_code,
              p.city AS city
              FROM user u
              LEFT JOIN postal_code p
              ON (u.post_id = p.post_id)
              WHERE u.name LIKE 'Joh%'
              ORDER BY name ASC
              LIMIT 0,25
-REQtime- 15.625ms
-QueryNr- 2
-Conn.Nr- 1

Advantages

  • Is my query generated correctly? Where is the error in my query? The standard error messages from MySQL are most of the time not very clear, and don’t give enough information to find the bug in the query. With MySQL Proxy you see the generated query, and this makes it easier to discover the problem
  • How long does my query take?
  • Did the transaction commit or roll back?
  • Is the “set names” send to the database or not?
  • Why does my application generate 200 queries when I load it?

These are questions that can be answered more easily with the MySQL Proxy, and it makes debugging a lot easier!