index.md (2526B)
1 --- 2 title: "Backup your Database in Git" 3 date: 2009-05-08T00:00:00+00:00 4 draft: false 5 canonical_url: https://www.viget.com/articles/backup-your-database-in-git/ 6 --- 7 8 **Short version**: dump your production database into a git repository 9 for an instant backup solution. 10 11 **Long version**: keeping backups of production data is fundamental for 12 a well-run web application, but it's tricky to maintain history while 13 keeping disk usage at a reasonable level. You could continually 14 overwrite the backup with the latest data, but you risk automatically 15 replacing good data with bad. You could save each version in a separate, 16 timestamped file, but since most of the data is static, you would end up 17 wasting a lot of disk space. 18 19 When you think about it, a database dump is just SQL code, so why not 20 manage it the same way you manage the rest of your code --- in a source 21 code manager? Setting such a scheme up is dead simple. On your 22 production server, with git installed: 23 24 ```sh 25 mkdir -p /path/to/backup 26 cd /path/to/backup 27 mysqldump -u [user] -p[pass] --skip-extended-insert [database] > [database].sql 28 git init 29 git add [database].sql 30 git commit -m "Initial commit" 31 ``` 32 33 The `--skip-extended-insert` option tells mysqldump to give each table 34 row its own `insert` statement. This creates a larger initial commit 35 than the default bulk insert, but makes future commits much easier to 36 read and (I suspect) keeps the overall repository size smaller, since 37 each patch only includes the individual records added/updated/deleted. 38 39 From here, all we have to do is set up a cronjob to update the backup: 40 41 ``` 42 0 * * * * cd /path/to/backup && \ 43 mysqldump -u [user] -p[pass] --skip-extended-insert [database] > [database].sql && \ 44 git commit -am "Updating DB backup" 45 ``` 46 47 You may want to add another entry to run 48 [`git gc`](http://www.kernel.org/pub/software/scm/git/docs/git-gc.html) 49 every day or so in order to keep disk space down and performance up. 50 51 Now that you have all of your data in a git repo, you've got a lot of 52 options. Easily view activity on your site with `git whatchanged -p`. 53 Update your staging server to the latest data with 54 `git clone ssh://[hostname]/path/to/backup`. Add a remote on 55 [Github](https://github.com/) and get offsite backups with a simple 56 `git push`. 57 58 This technique might fall down if your app approaches 59 [Craigslist](http://craigslist.org/)-level traffic, but it's working 60 flawlessly for us on [SpeakerRate](http://speakerrate.com), and should 61 work well for your small- to medium-sized web application.