Archive for November, 2009

Redirect MySQL Traffic on FreeBSD with PF and SSH

Friday, November 20th, 2009

So this week at work we were going to be taking a machine and splitting some server services off and onto smaller easier to manage virtual machines. One of the services this server was goign to stop serving is MySQL. As you may or may not know MySQL runs (by default) on a non-privileged port (3306). This is important to know later. This server was pretty old. Its was running FreeBSD 6.0 This server has been on the same IP running the same services for more years than anyone in IT can remember. This means there are going to be TONS of scripts all over the network that over the years have been long forgotten about, so when we move MySQL off and onto the new stand alone system we are going to have to go all over trying to find broken scripts and point them to the new DB server… Or are we?

I was thinking I should solve this problem before it fills our ticket queue 🙂
I decided to use PF since this system already had it. This server only had 1 NIC and we have more we could add but we cant have down time so I needed to figure out a way to do it with only 1 NIC. The solution I came up with was pretty simple and used only things that are available on a default install of FreeBSD
First I used ssh to do a simple port forward so that connections on the old servers localhost port 4040 would forward over an ssh tunnel to the new servers port 3306

ssh -L 4040:localhost:3306 dbproxy@newserver

Next I added a rule to my pf.conf

rdr pass log on $int_if proto { tcp, udp } from any to any port 3306 -> 127.0.0.1 port 4040

This rule redirects traffic headed to port 3306 on the old server (any interface l0 or em0) to port 4040 on the loop back interface, where we did our non privileged port forward with ssh.

Then a simple reload of my pf.conf and now Im all setup and dont have to worry about those scripts around the network, and the fact we log this rule we can now make a simple parser for our pflog to find out which hosts are using it and then go find the scripts and fix them with out having to have a ticket to do it first.

I would also like to mention that MySQL offers a solution to do this as well called the MySQL-proxy. We could not install that on this machine though.

You can see the thread on the FreeBSD forums where I originally posted the problem, and solution: http://forums.freebsd.org/showthread.php?t=8447