Archive for the ‘mysql’ 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 -> 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

using php mail() with postfix

Monday, April 16th, 2007

First, let me give a short history… I had to make a script at work that would get fired off by our hylafax server when ever an incoming fax was recieved. This script needed to check the caller id info and see what number was dialed, then based on that either move the file into a dir for the department it was intended for, or if it was ment for a person then to convert the tiff file into a pdf then email them the fax. Sounds simple enough… So for what ever reason my boss is hung up on using php for everything so I start working on this thing in php. We are using asterisk at work with IAXmodem to make asterisk and hylafax work happy like.. We have given all 90+ users we have their own DID as well as their own FaxDID. What I do with this script is lookup the number that was passed with callerid info in a mysql database to see whos umber it is, I get their username and their email as well as some other info, then mail the user the fax. I decided to use Pear for this because I like to reuse code when ever possible. I made a simple script to get the job done and damn to hell if it wouldnt work. I would get an email with the headers all screwed up. I knew they were not getting messed up in the php because I could print the email (headers and all) to the command line and it was perfect. I tried this same script on another box (that happened to have sendmail) and it woked perfect. I spent all damn day trying to figure out why my emails were not being sent correctly today. Then along came my good buddy Caleb. The first words from his mouth were ” I bet the working servers are sendmail and the nonworking are postfix” Sure enough that was the case. For what ever reason postfix is adding 2 new lines to my stuff. The fix was really extremely trivial.

$body = $mime->get();
$body = str_replace(“\r”,”,$body);
$headers = $mime->headers($headers);
$headers = str_replace(“\r”,”,$headers);

The full script can be found here. This script is not the best work ever, and it could be improved for sure. But this is what I am using. I hope someone can find it useful someday.