mysql rsync backup doh!

Well, I moved a big live database from one server to another for a customer. I was planning on taking the site offline first and doing an sql dump/move/restore, but someone accidentally deleted the old server before they were meant too (oops) - leaving me with some rsync backups that I'd made of the whole server over the previous days, to guard against such an eventuality.

Turns out rsyncing a live mysql database is not the right way to do things - I ended up with a database that managed to get mysqld to actually crash when I tried to dump out the database contents. I managed to fix this by dropping the tables that it was crashing on: it conveniently told me which ones it was crashing on, and it let me drop them, just not dump them. Luckily they were all cache tables so there didn't seem to be harm in dropping them and recreating them empty.

It also turns out (obviously, post facto) that mysql databases don't play nicely with rsync's --link-dest option, which is supposed to reduce disk space on the target file system when making multiple snapshots. Because it only optimises away duplicate data for identical files, but mysql stores the entire database (to a first approximation) in a single file, hiding its internal structure from rsync, and so the whole db gets duplicated each time.

Taking an SQL dump should solve the first problem. Not entirely sure what the right way to do an incremental backup of the database is. Maybe I shouldn't even try.

batch checking DNS delegation

I'm working with someone who has ten or so domains. All the domains are registered in different places, and with slightly different DNS settings. As part of tidying that up to get everything consistent, I wrote the following bash+dig script to display the delegation of each zone from its parent.

That is importantly not the same as what the name servers for the zone return as NS records. The "authoritative" source of NS (nameserver) records for a zone is that zone itself. Using dig to query the NS records seems to be returning those, unsurprisingly.

However, in order to query a zone, there is a second place where name servers must be configured, separately from in the zone itself. That is in the parent zone. If those are wrong, then you can get awkward to diagnose problems: you can see from dig that the nameservers are right, yet lookups go to the wrong place.

Hence my script.

You can see even on my own domains there's a slight misconfiguration: barwen.ch claims to have s0.barwen.ch as a server, but the .ch registry isn't delegating to it. That won't cause bad DNS lookups but will cause s0.barwen.ch to not be used as a nameserver sometimes. Worse is when the delegation points to an old server that then returns a new servers DNS, giving the illusion that all is well, until you turn off the old server (which is the problem I have on other zones)

$ ./list-domains-NS.sh 
ZONE hawaga.org.uk
NAMESERVERS ACCORDING TO GOOGLE DNS
dildano.hawaga.org.uk.
paella.hawaga.org.uk.
NAMESERVERS ACCORDING TO org.uk
hawaga.org.uk.  172800 IN NS paella.hawaga.org.uk.
hawaga.org.uk.  172800 IN NS dildano.hawaga.org.uk.

ZONE barwen.ch
NAMESERVERS ACCORDING TO GOOGLE DNS
paella.hawaga.org.uk.
s0.barwen.ch.
dildano.hawaga.org.uk.
NAMESERVERS ACCORDING TO ch
barwen.ch.  3600 IN NS dildano.hawaga.org.uk.
barwen.ch.  3600 IN NS paella.hawaga.org.uk.

So here's the script:

#!/bin/bash
cat domainlist.txt | while read d ; do
  echo "ZONE $d"
  echo "NAMESERVERS ACCORDING TO GOOGLE DNS"
  dig @8.8.8.8 -t ns $d +short

  PARENT=$(echo $d | sed 's/^[^.]*\.//')
  echo "NAMESERVERS ACCORDING TO $PARENT"

  PARENTNS=$(dig +short -t NS ${PARENT}. | head -n 1)

  dig @$PARENTNS -t NS +noall +authority +norecurse $d

  echo
done

country selector

I've had a rant queued up on this blog but never completed about various forms of internationalisation/localisation.

One part of the rant is about web forms that ask for your country. A common way to do this is a drop down list of "every country" (deliberately in quotes). That's awful.

Its especially awful when you are from simultaneously from: Britain; United Kingdom; Great Britain; and England, all "countries" in someone's definition or another - not only do you have to scroll down to your country, you don't even know which of the four countries you are meant to scroll to until you get there and find that country missing.

I saw (on hacker news) this redesigned country selector: http://baymard.com/labs/country-selector which presents an autocompleting text box.

Type "eng" and it brings up "United Kingdom". woo!

It also converts "deut" into "Germany", but fails to convert ”日” into Japan.