Archive for the ‘freebsd’ Category

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 -> 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:

The end of week 1

Friday, January 19th, 2007

Im still alive, and I still have a job. This job is really sweet. We use a lot of FreeBSD at work. We do use a little bit of linux, and I managed to get though a whole week using Fedora for my desktop. Im looking forward to a project I have been handed. I have to complete a pretty large router setup. It will all be run on some form of FreeBSD. Its a very challenging project that will test my abilities very well.I setup a wiki at work using media wiki. Its going to be for internal use only but will be used to document the router I am building, as well as most other aspects of my job and any one elses job who cares to document how to preform their job. I really like the people I work with, they are very sharp and I will be able to learn a whole lot from them.

This week has been really cold here in San Antonio. We had a hard freeze a couple days in a row and lots of places lost power (thankfully not me). I have found an H-E-B on just about every street corner. From my apartment there is like 2 in what I consider to be walking distance… I have looked around while I have been out driving around lost for other stores that we had in Odessa but I have not found anything other than WalMart. I guess H-E-B has run everyone else out of town… I really like the people I have met so far, although I havent met anyone out side of work… I pretty much stay home and play nintendo wii most of the time anyway. I joined a gym called anytime fitness. They are a 24hr gym. I like this because sometimes when I cant sleep the thing that will make me tired is tossing around some heavy weights or running a mile or so on the cross trainer. Im missing my little girl April very much and I wish she were able to be here…

ahhhh spring break

Monday, March 14th, 2005

Its sure nice to be on spring break this week. I have needed the break from school. You might have noticed the site being down for a week or more and wondered why. Well I have been busy setting up a FreeBSD 5.3 web server and I will start setting up the email service on it soon using QMail Rocks.

We have been busy trying to clean up our shop in the back yard. We have a 20×40 shop out back that makes a great workshop but we started filling it with stuff that we wanted to sell in a yard sale almost 5 years ago and have never had the sale. It sat out there so long it got ruined and so we have been cleaning it out. I get to haul off a trailer load of broken recliners and other trash off to the dump in the morning, it should be loads of fun; we just have to get out there before it heats up….if you know what I mean.