Debugging mysql on the wire

Earlier today i was struggling with a mysql replication that didn’t work and while banging my head at that problem i realized that maybe i would help to see what the master actually sends to the slave. Well i decided to redo my whole replication setup and see if that would help and it did.

But the idea of actually debugging what was sent on the wire still lurked around in my head so i decided to at least write some thing about it here. just in case someone else would like to do the same thing.

And it’s really easy to do. If you don’t have wireshark, install wireshark like this:

# cd /usr/ports/net/wireshark

# make install

So now when we have the tool here is how to use it for debugging mysql by listening to what actually get transmitted to the server:

# tshark -T text -V -f ‘dst port 3306′ | grep -i Statement: | cut -b25-

Now just lean back and watch the output looking like this:

select address from pools where citypool = ‘sth.globalns.openpeers.net’ \n
select address from pools where citypool = ‘sth.globalns.openpeers.net’\n
select address from pools where citypool = ‘lon.globalns.openpeers.net’ \n
select address from pools where citypool = ‘lon.globalns.openpeers.net’\n
select address from pools where citypool = ‘none.globalns.openpeers.net’ \n
select enabled from site where name = ‘www’ and site = ‘arnold’ and domain =’globalns.openpeers.net’ and enabled = 1\n
select pool from which where nexthop = ’83.142.224.2′ and enabled = ’1′\n

But hey! Whats this it looks like i’m sending identical mysql queries!

Thats right while writing this entry i noticed a flaw in my program! That was very easy to code in a better way. So writing this post  helped me to fix a flaw that i didn’t even no existed.

Btw. by using port replication in your switch you can divert all traffic for the sql server to another box analyzing the traffic. Save load on the client or server, or enables you  to debug a multi client setup.