How to convert IPv4 to IPv6 in MySQL

If you original IPv4 address is in an unsigned long called “IPv4“, then…

select concat("0:0:0:0:0:0:", LPAD(CONV(substring_index(inet_ntoa(IPv4), '.', 1), 10, 16), 2, "0"), LPAD(CONV(SUBSTRING_INDEX(SUBSTRING_INDEX( inet_ntoa(IPv4) , '.', 2 ),'.',-1), 10, 16), 2, "0"), ":", LPAD(CONV(SUBSTRING_INDEX(SUBSTRING_INDEX( inet_ntoa(IPv4) , '.', 3 ),'.',-1), 10, 16), 2, "0"), LPAD(CONV(SUBSTRING_INDEX(SUBSTRING_INDEX( inet_ntoa(IPv4) , '.', 4 ),'.',-1), 10, 16), 2, "0")) as IPv6 from Log3NF.Access limit 1000;

Log3NF – IPv6 support coming real soon

A few years ago, I converted my earlier idea of logging Apache request to 3rd normal form into a fully fledged Mod Perl 2.0 Log Handler – embedding this into Apache. Its essentially a very simple Handler of less than 60 lines, and a stored procedure inside MySQL that normalises the data. Its been running on my personal server since February 2009, and in that time its collected around

  • 1.4 million hits
  • 27,000 unique useragents
  • 57,000 unique paths
  • 9 basic authentiation users
  • 13 HTTP methods
  • 50,000 unique referrer URLs
  • 13 HTTP Status
  • Recorded the transfer of 192,185.9637 Megabytes of (body) data; the average body response is 144 KB.

Wow. The log data on disk is 574 MB, or around 410 bytes per request – including the indexes (this is the size of the MySQL directory containing the data).

All well and good. Now time to get it fit for IPv6, and then improve the reporting. The reporting has two phases:

  • Live reporting from the 3rd normal form for data covering the last few seconds/minutes/hours/days.
  • Summary reporting per day or per month, per statistic, pre-calculated

Anyway, we’re about to pull in IPv6, again storing this as efficiently as possible, and then improve the currently very basi reporting interface…. stay tuned… and see the SVN repository for code…

Now with IPv6 goodness

So, with about 10 mins of reading and 1 min of work, this site is now available with both IPv4 and IPv6. Thanks Bytemark for making it so easy, and of course to all the software stack that just works. If you want to test and ensure you’re using just IPv6, you can browse to ipv6.james.rcpt.to, which I have only published a AAAA address for.

Next up; update my Log3NF Apache module to understand IPv6 addresses as well as the IPv4 if currently does.

Agent forwarding – with Putty

Note to self – when setting up putty, change the “Default Settings” profile to turn on Agent Forwarding: Connection -> SSH -> Auth -> “Allow agent forwarding”. Also, on servers you will be forwarding through, edit /etc/ssh/ssh_config, and turn on “ForwardAgent yes” for either all or set hosts needed.