{"thread":{"id":"25677","subject":"Git as a backup system?","startedAt":"2010-11-08T18:01:29Z","lastAt":"2010-11-09T00:17:37Z","messageCount":6,"participants":["Eric Frederich","Jonathan Nieder","Konstantin Khomoutov","Dirk Süsserott","Ævar Arnfjörð Bjarmason","Patrick Rouleau"],"isPatch":false,"patchVersion":null,"patchTotal":null},"messages":[{"id":"155398","messageId":"AANLkTikcBvN+5hkcc9+xt291B4Gm+Yhe53R3qY0PNt97@mail.gmail.com","threadId":"25677","inReplyTo":null,"subject":"Git as a backup system?","fromName":"Eric Frederich","fromEmail":"eric.frederich@gmail.com","sentAt":"2010-11-08T18:01:29Z","receivedAt":"2010-11-08T18:01:29Z","isPatch":false,"sender":{"key":"eric.frederich@gmail.com","avatar":"https://gravatar.com/avatar/a047bc6d3404a87c923025574cdee9abf57ae9efc1d96c7bbedf30faf47ee12c?d=mp&s=160"},"body":"I maintain a corporate MediaWiki installation.\nCurrently I have a cron job that runs daily and tar's up the contents\nof the installation directory and runs a mysqldump.\nI keep backups of the past 45 days.\nEach backup is about 200M, so all in all I always have about 9.0G of backups.\nMost of the changes are in the database, so the mysqldump file is\nchanged every day.\nOther than that, there can be new files uploaded but they never\nchange, just get added.\nAll configuration files stay the same.\n\nI wrote a script that untar'd the contents each backup, gunziped the\nmysql dump, and made a git commit.\nThe resulting .git directory wound up being 837M, but after running a\nlong (8 minute) \"git gc\" command, it went down to 204M.\n\n== Questions ==\nWhat mysqldump options would be good to use for storage in git?\nRight now I'm not passing any parameters to mysqldump and its doing\nall inserts for each table on a single huge line.\nWould git handle it better if each insert was on its own line?\n\nLets say that the repo gets too big and I want to throw away history.\nI'd have a linear history with a single commit every day.\nIs there a way to take just the last 30 commits and throw away everything else?\n\nAm I insane? Are there other tools more suited toward this?\nI just thought of using Git since I looked at my 9G worth of data out\nthere in my backup directory that is almost exactly the same and said\n\"git could handle this well\".\n\nAre any of you using git for a backup system? Have any tips, words of wisdom?\n\nThanks,\n~Eric\n"},{"id":"155402","messageId":"20101108180427.GA4005@burratino","threadId":"25677","inReplyTo":"AANLkTikcBvN+5hkcc9+xt291B4Gm+Yhe53R3qY0PNt97@mail.gmail.com","subject":"Re: Git as a backup system?","fromName":"Jonathan Nieder","fromEmail":"jrnieder@gmail.com","sentAt":"2010-11-08T18:04:27Z","receivedAt":"2010-11-08T18:04:27Z","isPatch":false,"sender":{"key":"jrnieder@gmail.com","avatar":"https://avatars.githubusercontent.com/u/281595?v=4"},"body":"Eric Frederich wrote:\n\n> Am I insane? Are there other tools more suited toward this?\n> I just thought of using Git since I looked at my 9G worth of data out\n> there in my backup directory that is almost exactly the same and said\n> \"git could handle this well\".\n\nYou might like bup: https://github.com/apenwarr/bup#readme\n"},{"id":"155407","messageId":"20101108212612.bb4bfeb8.kostix@domain007.com","threadId":"25677","inReplyTo":"AANLkTikcBvN+5hkcc9+xt291B4Gm+Yhe53R3qY0PNt97@mail.gmail.com","subject":"Re: Git as a backup system?","fromName":"Konstantin Khomoutov","fromEmail":"flatworm@users.sourceforge.net","sentAt":"2010-11-08T18:26:12Z","receivedAt":"2010-11-08T18:26:12Z","isPatch":false,"sender":{"key":"flatworm@users.sourceforge.net","avatar":null},"body":"On Mon, 8 Nov 2010 13:01:29 -0500\nEric Frederich wrote:\n\n> I maintain a corporate MediaWiki installation.\n> Currently I have a cron job that runs daily and tar's up the contents\n> of the installation directory and runs a mysqldump.\n> I keep backups of the past 45 days.\n> Each backup is about 200M, so all in all I always have about 9.0G of\n> backups. Most of the changes are in the database, so the mysqldump\n> file is changed every day.\n> Other than that, there can be new files uploaded but they never\n> change, just get added.\n> All configuration files stay the same.\n[...]\n> Am I insane? Are there other tools more suited toward this?\n> I just thought of using Git since I looked at my 9G worth of data out\n> there in my backup directory that is almost exactly the same and said\n> \"git could handle this well\".\n> \n> Are any of you using git for a backup system? Have any tips, words\n> of wisdom?\nI suspect the rdiff-backup tool [1] was invented precisely for the setup\nlike yours: it is able to sync one directory to another + create diffs\nbetween them thus providing for incremental backups.\n\n1. http://www.nongnu.org/rdiff-backup/\n"},{"id":"155424","messageId":"4CD8585B.8000802@dirk.my1.cc","threadId":"25677","inReplyTo":"AANLkTikcBvN+5hkcc9+xt291B4Gm+Yhe53R3qY0PNt97@mail.gmail.com","subject":"Re: Git as a backup system?","fromName":"Dirk Süsserott","fromEmail":"newsletter@dirk.my1.cc","sentAt":"2010-11-08T20:06:51Z","receivedAt":"2010-11-08T20:06:51Z","isPatch":false,"sender":{"key":"newsletter@dirk.my1.cc","avatar":null},"body":"Am 08.11.2010 19:01 schrieb Eric Frederich:\n> I maintain a corporate MediaWiki installation.\n> Currently I have a cron job that runs daily and tar's up the contents\n> of the installation directory and runs a mysqldump.\n>\n> I wrote a script that untar'd the contents each backup, gunziped the\n> mysql dump, and made a git commit.\n> The resulting .git directory wound up being 837M, but after running a\n> long (8 minute) \"git gc\" command, it went down to 204M.\n>\n> == Questions ==\n> What mysqldump options would be good to use for storage in git?\n> Right now I'm not passing any parameters to mysqldump and its doing\n> all inserts for each table on a single huge line.\n> Would git handle it better if each insert was on its own line?\n>\n> Are any of you using git for a backup system? Have any tips, words of wisdom?\n>\n> Thanks,\n> ~Eric\n\nHi Eric,\n\nI also use mysqldump and Git to make backups of my databases. Indeed, it \nperforms much better when each change (insert statement) is on a \nseparate line. mysqldump has an option for that which I don't recommend, \nbecause it dramatically slows down the dump and the restore. It would \nthen create separate \"insert into ...\" statement for each changed line.\n\nFor me the attached script worked very well: I pipe the output of \nmysqldump through the script and it simply inserts a linefeed after each \nrecord.\n\n----------------------------------\n#!/usr/bin/perl -p\n\nuse strict;\nuse warnings;\n\n# Before:\n# INSERT INTO `schliess_grund` VALUES \n(1,'Explizit'),(2,'Neuanmeldung'),(4,'Sperrung'),(3,'Timeout');\n#\n# After:\n# INSERT INTO `schliess_grund` VALUES\n# (1,'Explizit'),\n# (2,'Neuanmeldung'),\n# (4,'Sperrung'),\n# (3,'Timeout');\nif (/^(INSERT INTO .*? VALUES) (.*);$/)\n{\n $_ = \"$1\\n $2\\n ;\\n\";\n s/\\),\\(/\\)\\n ,\\(/g;\n}\n----------------------------------\n\nThe changeset will be much smaller. Let's call the script \"wrap.pl\". \nThen run the following:\n\n----------------------------------\nmysqldump --opt --routines [...] -r \n./wrap.pl > ; rm \ngit add \nif ! git diff-index --quiet HEAD --; then\n git commit -m \"Backup of ...\"\nfi\n----------------------------------\n\nTry it out!\n\nCheers,\n Dirk\n"},{"id":"155427","messageId":"AANLkTikK+owARQoNxpF=CzcY_s-OChdkCgj6WbD=_eNW@mail.gmail.com","threadId":"25677","inReplyTo":"AANLkTikcBvN+5hkcc9+xt291B4Gm+Yhe53R3qY0PNt97@mail.gmail.com","subject":"Re: Git as a backup system?","fromName":"Ævar Arnfjörð Bjarmason","fromEmail":"avarab@gmail.com","sentAt":"2010-11-08T20:25:43Z","receivedAt":"2010-11-08T20:25:43Z","isPatch":false,"sender":{"key":"avarab@gmail.com","avatar":"https://avatars.githubusercontent.com/u/45301?v=4"},"body":"On Mon, Nov 8, 2010 at 19:01, Eric Frederich wrote:\n> I maintain a corporate MediaWiki installation.\n> Currently I have a cron job that runs daily and tar's up the contents\n> of the installation directory and runs a mysqldump.\n> I keep backups of the past 45 days.\n> Each backup is about 200M, so all in all I always have about 9.0G of backups.\n> Most of the changes are in the database, so the mysqldump file is\n> changed every day.\n> Other than that, there can be new files uploaded but they never\n> change, just get added.\n> All configuration files stay the same.\n>\n> I wrote a script that untar'd the contents each backup, gunziped the\n> mysql dump, and made a git commit.\n> The resulting .git directory wound up being 837M, but after running a\n> long (8 minute) \"git gc\" command, it went down to 204M.\n>\n> == Questions ==\n> What mysqldump options would be good to use for storage in git?\n> Right now I'm not passing any parameters to mysqldump and its doing\n> all inserts for each table on a single huge line.\n> Would git handle it better if each insert was on its own line?\n\nI use git to back up all my data. It works great. But how big the\ndumps get depends very much on the database.\n\nHere's a graph of the size of my mysql backup directories:\nhttp://munin.nix.is/nix.is/v.nix.is/dirs_var_backup_mysql.html\n\nI use this little wrapper script:\nhttps://github.com/avar/linode-etc/blob/master/bin/cron/mysqldump-to-git-all\n\nWhich calls this:\nhttps://github.com/avar/linode-etc/blob/master/bin/cron/sqldump-to-git\n\nI find with MySQL --skip-extended-insert and --compact work really well.\n\nThen I use this to repack + gc the repos:\nhttps://github.com/avar/linode-etc/blob/master/bin/cron/git-repack-and-gc-dir\n"},{"id":"155449","messageId":"loom.20101109T010709-599@post.gmane.org","threadId":"25677","inReplyTo":"AANLkTikcBvN+5hkcc9+xt291B4Gm+Yhe53R3qY0PNt97@mail.gmail.com","subject":"Re: Git as a backup system?","fromName":"Patrick Rouleau","fromEmail":"pfrouleau@gmail.com","sentAt":"2010-11-09T00:17:37Z","receivedAt":"2010-11-09T00:17:37Z","isPatch":false,"sender":{"key":"pfrouleau@gmail.com","avatar":"https://gravatar.com/avatar/e5e6b2a31d0eca8858656019a059ed6a5917966544e38bdc2d1e591ee5482115?d=mp&s=160"},"body":"Eric Frederich gmail.com> writes:\n\n> Are any of you using git for a backup system? Have any tips, words of wisdom?\n\nI had the same idea to backup a MySQL database with git.\n\nTo be able to easily drop old \"backups\", I have choose to go with monthly \nbranches: each month, the previous month's branch is checked out and a new \nbranch is created; each year, the previous year is checked out...\n\nThis is running only for 5 weeks, but so far it works pretty well.\n"}]}