Log in

No account? Create an account

Mercurial, Clever or just a weird way to do Backups?

« previous entry | next entry »
Apr. 22nd, 2008 | 10:55 am

All of my sites run a piece of software called Everything. Mine is a fork from the original Everydevel Corp's release. It shares some common traits with Slash but it has a more rapid development cycle that I like.

One of the things it shares with Slash, is that the "site" is the database. AKA everything that makes a site, a site, is the database. This means that a backup of the site can be tossed into an everything container and be up and running in seconds (I use this with virtual machines so that I can move stuff around on a whim).

I hate backups. I don't want to take snapshots with LVM, since I know I just need the content, and typically with dumps I end up with a huge set of these files just sitting around.

  • I cannot search them.
  • I cannot diff them between dates.
  • I cannot partially restore them.
  • They take up a lot of space

    So it dawns on me at dinner last night. What if I just used Mercurial?

    So I add a crontab like so:

    0 3 * * * mysqldump -f --single-transaction -T /var/backup/sitename sitename; (cd /var/backup/sitename; hg commit -m "auto"; hg push)

    I push the backups to a centralized server.

    Instant daily backups, and I can partially restore thanks to the tab format. I can pull diffs that I can directly apply and reinsert into the database.

    Perfect? Nope.

    Mercurial is storing deltas so the space difference does not seem to be to bad (and I am only shipping what changed over the wire). This will not work for my Archive tables, they are just too damn big (4 billion rows and growing). Those tables are just logs and I have a different long term plan for them.

    But the data that makes up the site? Works well.

    Since I use Innodb for sites, I can use the --single-transaction trick to take an online backup. If you are using any other storage engine, the above crontab will lock up your site while the dump is being made.

    This will probably work for most revision systems (if you take one of the nifty ones that can chop history you could even automate the tossing of deltas after a week or so). Mercurial has the strong benefit of being http centric and it is incredibly easy to install as a server (and it has never eaten my data like some other open source systems have).

    I welcome feedback :)
  • Link | Leave a comment |

    Comments {16}


    (no subject)

    from: awfief
    date: Apr. 22nd, 2008 06:36 pm (UTC)

    If it works for you, then that's great!

    One company I worked for used their version control system for keeping the schema changes (using mysqldump --no-data).

    If this technique is used for sensitive data then of course the repository should be secured, as should access to it.

    I usually recommend a mysqldump --skip-extended-insert on each table individually as an alternative backup methodology to be able to easily diff backups (table by table). This of course means 2 backups -- one that's consistent, for disaster recovery, and another that isn't, for diff-ability.

    Mostly I really like this solution; it shows you have thought about what you really *want* in a backup, and have adjusted your backup plan accordingly. Most folks think a consistent backup is enough, and while it's great for making a new slave, it stinks for incrementals and diffs and whatnot.

    I think that in general, using a source control program for backups will work, if what one wants is what the source control program provides. :)

    Reply | Thread


    (no subject)

    from: awfief
    date: Apr. 22nd, 2008 06:42 pm (UTC)

    Another thought I just had -- this would make data auditing much easier, at least for the "when did this change?" aspect. (that's why a company I worked for used it for schema changes....)

    Reply | Parent | Thread

    Brian "Krow" Aker

    (no subject)

    from: krow
    date: Apr. 22nd, 2008 06:59 pm (UTC)

    I use tabs for the above. This will keep the size of each individual file smaller (and it makes it much easier to do selected restore on a per table basis).

    Tab formats also diff really well :)

    Reply | Parent | Thread