Re: Git as a backup system?

From: Ævar Arnfjörð Bjarmason <avarab@gmail.com>
Date: Nov 8, 2010, 20:25 UTC
Message-ID: <AANLkTikK+owARQoNxpF=CzcY_s-OChdkCgj6WbD=_eNW@mail.gmail.com>
In-Reply-To: <AANLkTikcBvN+5hkcc9+xt291B4Gm+Yhe53R3qY0PNt97@mail.gmail.com>

On Mon, Nov 8, 2010 at 19:01, Eric Frederich <eric.frederich@gmail.com> wrote:

Show 21 quoted lines

> I maintain a corporate MediaWiki installation.
> Currently I have a cron job that runs daily and tar's up the contents
> of the installation directory and runs a mysqldump.
> I keep backups of the past 45 days.
> Each backup is about 200M, so all in all I always have about 9.0G of backups.
> Most of the changes are in the database, so the mysqldump file is
> changed every day.
> Other than that, there can be new files uploaded but they never
> change, just get added.
> All configuration files stay the same.
>
> I wrote a script that untar'd the contents each backup, gunziped the
> mysql dump, and made a git commit.
> The resulting .git directory wound up being 837M, but after running a
> long (8 minute) "git gc" command, it went down to 204M.
>
> == Questions ==
> What mysqldump options would be good to use for storage in git?
> Right now I'm not passing any parameters to mysqldump and its doing
> all inserts for each table on a single huge line.
> Would git handle it better if each insert was on its own line?

I use git to back up all my data. It works great. But how big the dumps get depends very much on the database.

Here's a graph of the size of my mysql backup directories: http://munin.nix.is/nix.is/v.nix.is/dirs_var_backup_mysql.html

I use this little wrapper script: https://github.com/avar/linode-etc/blob/master/bin/cron/mysqldump-to-git-all

Which calls this: https://github.com/avar/linode-etc/blob/master/bin/cron/sqldump-to-git

I find with MySQL --skip-extended-insert and --compact work really well.

Then I use this to repack + gc the repos: https://github.com/avar/linode-etc/blob/master/bin/cron/git-repack-and-gc-dir