by R.I. Pienaar | Aug 9, 2006 | Code
MySQL has an option to log all queries to a log file, stupidly though you need to restart the server to start/stop this log, its a bit lame, it also adds a lot of overhead to your box. I had the idea some time ago to use libpcap to figure out what gets sent to the server and started the code, I’ve mostly finished this now.
The MySQL protocol is undocumented but there is a reverse engineered protocol doc here. It is a tad outdated and I’ve found some queries that does not get parsed perfectly using the info there, I’ll look into those some more.
Here are some sample outputs from my code:
# ./mysqldump.pl -i mysql.cap
17:08:44: SET autocommit=1
17:08:44: SELECT last_insert_id()
17:08:44: rollback
It also has a more verbose option:
# ./mysqldump.pl -i mysql.cap -v
MySQL Packet:
Length : 24
Packet Number : 0
Packet Command : 3
SQL Command : SELECT last_insert_id()
Packet Header:
Source : 192.168.1.1
Dest : 192.168.1.2
S.Port : 47241
D.Port : 3306
Length : 77
Timestamp: 1150128523.750337
I could make the script do its own dumping using libpcap it’s pretty easy but I prefer to only let it read tcpdump files, we get a LOT of queries (> 600 per second) and I don’t want slow perl code to affect the servers, tcpdump is pretty fast and efficient.
I use Net::Pcap and NetPacket::TCP to extract the packets from the file, decoding the MySQL commands is pretty easy then:
$plen = ord(substr($pkt, 66, 3));
$pnum = ord(substr($pkt, 69, 1));
$pcmd = ord(substr($pkt, 70, 1));
$cmd = substr($pkt, 71);
The offsets etc is defined in the documentation linked too above, $cmd will now hold the actual query ran against the server, but only where $pcmd == 3.
I’ll upload the code for this in the next few days just letting a few people do some beta testing for me first.
UPDATE: You can now get the code here.
by R.I. Pienaar | Jul 19, 2006 | Code
My previous homebrew backup system had a number of drawbacks, one of the biggest was that its daily emails were massive, listing all the files that was backed up.
With a lot of machines being backed up these mails can come to several MB per day but also general Human Nature means I just didn’t pay them enough attention. For instance, I would need to somehow notice if on a given day the tar died half way through by manual inspection, this was pretty useless.
Bacula provides good one-page job status emails on a daily basis but still I tend to not look at them as I will get about 20 of them a day, the ideal situation is to have it only mail you on errors and it does support this. There is one problem with this though, if anything prevents the mail from getting to you, or in-fact if the whole Director process dies and no backups get run at all you just wont know about it.
I’ve written a per-job monitoring solution that uses Bacula’s ability to run a script on the client after a successful backup has been run, it writes a small status file with a timestamp, this I pull into Net-SNMP and query over the network using Nagios.
Now if any of my jobs fail or if the whole backup system collapses Nagios will notify me via my already existing notification systems, email and SMS in my case. I will still get the Error mails from Bacula but I totally do not rely on them, they are merely there for information purposes so I can use them to quickly investigate a error once Nagios has alerted me.
I’ve documented this and put up the short scripts I use to achieve this, you can see this document in my wiki
by R.I. Pienaar | Jul 19, 2006 | Code
I’ve been using the Bacula client/server backup system for my backups. Bacula is on par with commercial client/server backup systems, central control, supports auto changers etc. It is a opensource system and available for Linux, FreeBSD, OS X, Windows etc.
The configuration is pretty complex though and while right now I have mine working I do not have guides written up about it really. I wanted to SSL enable my installation though to give me secure transfer between the various clients and the storage server but found the Bacula documentation on the subject woefully lacking.
I’ve written up a bit of info on my Wiki about getting SSL going on a Bacula installation, you need to first have a fully working Bacula setup before attempting this, as starting from a known working system and then systematically SSL enabling it will ease in debugging and possibly increase your understanding of what is going on.
The full Wiki document can be found here.
by R.I. Pienaar | Jul 11, 2006 | Code
I have a client who needs to host graphic content closer to their audience, partly to speed things up for their audience but also to allow them to save some money on bandwidth bills by spreading it out to cheaper hosts.
The obvious answer is to just use Akamai, but unless you have a huge amount of bandwidth it does not really make financial sense. So I proposed we get a few UML / Virtual Machines at ISPs and host the images there. Typically we’d just round-robin the A record but this doesn’t give us any geo-awareness.
I googled a bit and came across a guy who made a patch to bind 9.2.4. He bascially hooks the Maxmind GeoIP C Library into ISC Bind. As it turns out thats exactly the version RedHat uses in their Enterprise distribution so it seemed like a winner.
The basic idea is to setup a view that matches countries rather than just IP Blocks as is the default supported scenario. A sample view:
view "us" {
match-clients { country_US; };
zone "geotest.devco.net" {
type master;
file "data/us-geotest.devco.net";
};
};
view "other" {
match-clients { any; };
zone "geotest.devco.net" {
type master;
file "data/other-geotest.devco.net";
};
};
The above will serve us-geotest.devco.net to visitors from the United States and everyone else will get other-geotest.devco.net. These 2 zone files are stock standard bind zone files, my sample just set a different A record in each.
To see this in action, first a query from my UK hosted machine:
% host geotest.devco.net
geotest.devco.net has address 193.201.200.135
and now a query from the states:
% host geotest.devco.net
geotest.devco.net has address 72.21.58.28
So to make a long story short, I’ve patched the stock RedHat Enterprise 4 bind RPM with the GeoDNS patch and am making the files available here, first I have the source files up if you want to look through them to ensure I didn’t put any funny stuff in them:
The actual patch from GeoDNS – I had to modify the file names in the patch since the one from the source site is made to patch from outside the bind source dir while RPM will change directory into the source directory before running the patch.
Changes made to the RPM Spec file
Sample config – this gets placed in the standard documentation directory
I’ve build a full RPMs of this on a CentOS 4.3 machine, the files are:
bind-9.2.4-2geodns.i386.rpm
bind-libs-9.2.4-2geodns.i386.rpm
bind-utils-9.2.4-2geodns.i386.rpm
bind-devel-9.2.4-2geodns.i386.rpm
bind-chroot-9.2.4-2geodns.i386.rpm
and finally a Source RPM that you can use to build this all on your own machine:
bind-9.2.4-2geodns.src.rpm
Before you can install these you’ll need to get GeoIP on your machine, CentOS has an extras Yum repository that you can enable in /etc/yum.repos.d/CentOS-Base.repo. Once enabled you can install it with yum:
# yum install GeoIP GeoIP-data
<snip>
Running Transaction
Installing: GeoIP ######################### [1/2]
Installing: GeoIP-data ######################### [2/2]
Installed: GeoIP.i386 0:1.3.14-2.c4 GeoIP-data.i386 0:20060501-2.c4
RedHat Enterprise has an extras CD, you can find GeoIP on there and install it using RPM.
The bind RPMs above should simply install on your standard CentOS/RedHat Enterprise box, be sure to remove all the old bind stuff especially bind-libs before installing these.
by R.I. Pienaar | Jun 19, 2006 | Code
There is a new version of GMapEZ that brings with it some new features, I’ve implimented some of these into GMaps PHP.
As you can see from the screenshot above the map now supports varying sizes of markers, the small ones are good for maps with lots of points but obviously they do not support any kind of text on the marker.
To activate mini icons for a specific type of point you need to edit your config file:
[types]
visit = orange mini
transit = blue
lived = green
The above configuration snipped will make any points of type visit to be small markers.
The other change in this version is simply a little mouse-over window that will show the title of any point when you hover over it, activating this feature does not require any config changes.
These features will only be available to you if your config file points to the latest version 2 of GMapEZ:
[misc]
gmapez=http://bluweb.com/chouser/gmapez/gmapez-2.js
There is one more feature in GMapEZ 2.2 that I did not include in this release, it supports tabbed popup windows, if anyone need these please drop me a line and I’ll look at including them.
GMapEZ also supports drawing lines, I may include support for these if there are demand for it, personally I don’t need them but if someone does, leave a comment here and I’ll keep it in mind for the next release.
Upgrading from 1.4 to 1.5 is really simple, simply replace the gmap.inc.php from your site with the one in the tarball below. Then make the optional config change above if you’d like mini icons.
As always you can get the latest version at http://www.devco.net/code/gmapsphp-current.tgz and full documentation can be found on my Wiki
by R.I. Pienaar | May 20, 2006 | Code
The author of GMapEZ released a new version of his javascript that supports the new version of Google Maps.
I’ve had some mails asking me to support the new version but as it was in Beta till this morning I delayed doing a full release of the new code till now.
Upgrading from 1.3 to 1.4 is really simple, simply replace the gmap.inc.php from your site with the one in the tarball below. You also need a small change in your config file for your map, the url to the GMapEZ has changed:
[misc]
gmapez=http://bluweb.com/chouser/gmapez/gmapez-2.js
With this in place, if you open a map it should still look roughly the same, but if you scroll over France – or in fact all of Europe – you’ll see street level maps now, in future some of the newer Google features are likely to be supported.
Get the code at http://www.devco.net/code/gmapsphp-current.tgz, full documentation can be found here
