For me, getting to many of the activities I enjoy, involves crossing a river.

Normally, this is about 35-40 minutes on bike, but if the crossing is flooded, I have to take a ~20 minute detour. What’s more, it’s dark, and this often involves me suddenly getting very wet feet.

Thankfully, it’s the 21st century, and I can do something about this!

Flood sensors

The UK Environment Agency, thanks in a large part to the wonderful work being done by the Data.gov.uk, has made the data for their network of flood sensors available on the internet.

I can tell, by visiting a webpage, more or less whether I need to take the short or long way round.

This wasn’t enough for me, because I’m lazy, and I want to be able to either ask the question “Is sandford flooded?” in a console window, or have my computer send me an alert if I need to allow extra time to get in. Unfortunately, there’s no feed of this data immediately available, but this was nothing a bit of Script-Fu wasn’t able to sort out!

The first step was getting the current levels. Looking at the markup, you can see that the current level is a nice two digit number between two <strong> tags, so this was pretty easy to regex for.

The second was to get a value for what the environment agency considers “flooding” for this sensor. This was a little bit more tricky, but it’s marked on the graph, and it would seem that the source for this is a JSON blob in the html, so again, relatively straightforward.

Putting this together, I was able to build a nice little script that, when given a sensor’s webpage, compares these two values and gives you a simple yes or no as to whether it’s flooded. For bonus points, and because the surrounding low areas may flood before the sensor does, I took a 90% value for a given sensor and use it to return an “Almost” if reached… kindof a “proceed with caution”.

This was pretty much to scratch my own itch, but since the flood sensor network is nation-wide, I figured it might be useful to someone else out there!

» Visit the project on Github...

mysql-logo3 So, the other week, I migrated all my sites over to a new server. This was accomplished with minimum fuss, using lots of rsync magic and juggling of DNS ttls.

The part of the migration I imagined would be the most complicated, moving several tens of MySQL databases running on the old server to the new, turned out to be pretty straightforward, and essentially completed with one command. I thought others might find it handy to know how…

First, I brought down Apache on both servers, so I could be sure that nobody was going to try and write to the database. This may not really be required, since mysqldump can handle dumping live databases consistently, for MyISAM at least, but in never hurts to be paranoid.

Next, I needed to move all the databases, together with their access permissions and users (so the full MySQL schema) to the other server. One way to do that is to copy the whole /var/lib/mysql directory over. However, I had a lot of cruft in there (old bin-logs etc), plus there were a number of articles suggesting that the straight binary copy had a number of issues, especially for mixed storage engine environments. So, I opted for the mysqldump method.

Traditionally, this takes a lot of SCPing. Here’s how to do it with one command, using the magic of Unix pipes:

mysqldump -u root -pPASSWORD --all-databases | ssh USER@NEW.HOST.COM 'cat - | mysql -u root -pPASSWORD'

Boom. This ran surprisingly quickly for me, and you can of course just as easily use this method to transfer a single database.

Three gotchas:

  • If the link dies, you need to start again, so don’t do this over a flakey connection, and I suggest you run the command in a screen if the first server isn’t localhost.
  • You need to restart the database server on the target machine for the new users and privileges to come into effect.
  • On Debian, you may see an error along the lines of:

    Got error: 1045: Access denied for user ‘debian-sys-maint’@’localhost’ (using password: YES) when trying to connect

    Fix this by executing the command:

    GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'THEPASSWORD' WITH GRANT OPTION;

    Where THEPASSWORD is the password found in /etc/mysql/debian.cnf

One final note: this will only work if both servers are the same major version number. I was moving between two Debian 6.0 installs, so YMMV.

Happy Easter!