Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I am trying to improve the backup situation for my application. I have a Django application and MySQL database. I read an article suggesting backing up the database in Git.

On the one hand I like it, as it will keep a copy of the data and the code in sync.

But Git is designed for code, not for data. As such it will be doing a lot of extra work diffing the MySQL dump every commit, which is not really necessary. If I compress the file before storing it, will git still diff the files?

(The dump file is currently 100MB uncompressed, 5.7MB when bzipped.)

Edit: the code and database schema definitions are already in Git, it is really the data I am concerned about backing up now.

share|improve this question
11  
If your company has an IT (ops) department, they should be handling this. –  Michael Hampton May 26 at 14:09
1  
is the data part of the application, or what is created through the application? –  Winston Ewert May 26 at 18:25
1  
Git will attempt to diff all files when you run git gc (or it's underlying git repack; git will, by configurable default, occasionally run it automatically). It will also always deflate them, so it might be actually better to store them uncompressed. –  Jan Hudec May 26 at 21:46
1  
What kind of database is it: is it production or development database? –  el.pescado May 27 at 8:47
4  
viget.com/extend/backup-your-database-in-git, he is a "senior developer". –  wobbily_col May 27 at 13:18

4 Answers 4

up vote 64 down vote accepted

Before you lose any data, let me try to introduce a sysadmin perspective to this question.

There is only one reason we create backups: to make it possible to restore when something goes wrong, as it invariably will. As such, a proper backup system has requirements that go far beyond what git can reasonably handle.

Here are some of the issues I can foresee with trying to backup your database in git:

  • The repository will grow dramatically with every "backup". Since git stores entire objects (albeit compressed) and then diffs them later (e.g. when you run git gc), and keeps history forever, you will have a very large amount of data stored that you don't actually need or even want. You might need to limit the amount or retention period of backups you do to save disk space or for legal reasons, but it's difficult to remove old revisions from a git repo without a lot of collateral damage.
  • Restoring is limited to points in time that you have stored in the repository, and since the data is so large, going back more than a trivial amount of time may be slow. A backup system designed for the purpose limits the amount of data stored while potentially providing more granularity, and provides faster restores, reducing downtime in the event of a disaster. Database-aware backup solutions (example) can also provide continuous backup, ensuring that not a single transaction is lost.
  • Commits are likely to be slow as well, and get slower as the database grows. Remember that git is essentially a key-value data store mapped onto a filesystem, and thus is subject to the performance characteristics of the underlying filesystem. It is possible for this length of time to eventually exceed the backup interval, and at that point you can no longer meet your SLA. Proper backup systems also take longer to backup as the data grows, but not nearly so dramatically, since they will automatically manage their own size based on the retention policy you will have configured.

Despite the fact that there are apparently several interesting things you can do with a database dump if you put it into git, overall I can't recommend it for the purpose of keeping backups. Especially since backup systems are widely available (and many are even open source) and work much better at keeping your data safe and making it possible to recover as quickly as possible.

share|improve this answer
    
This is the best answer as Michael has covered consistency issues. Depending on the size and usage of the database a snapshot can't reliably reproduce the data at given point in time and you're likely to run into constraint issues. Replication may be something you want to look into - dev.mysql.com/doc/refman/5.0/en/replication.html –  Aaron Newton May 27 at 5:16
3  
This isn't just the best answer, it's the only answer. As a general rule you're a developer so backups aren't your business; somebody else is (or should be) already looking after them, and if you start getting involved you may be interfering with a system that already works. These boxes should already be being backed up, so then you'll have a backup, your own backup, and a backup of your own backup, all with ever-increasing size. That's just nuts. Plus: you're a developer: why are you (probably) going near production boxes anyway? –  Darth Satan May 27 at 12:50
2  
@JimmyShelter There's a school of thought that DevOps means not that Dev and Ops work closely together, but that Dev actually does Ops. It usually doesn't work well, but that doesn't stop people trying it. –  Michael Hampton May 27 at 12:55
    
This should be the accepted answer. It clearly explains the requirements for and purpose of a backup system, then shows how git does not fit. Extra bonus points for discussion of consistency and performance. –  Gabriel Bauman May 27 at 18:55
    
Let me remark that I posted my answer assuming that the OP does not have any Operations team that can handle this issue for him. I agree with you that this kind of task is best left to those who are actually operating the system, and know their way around it. But there are situations where you have to put on a hat that is not exactly yours, and I believe in that situation it is better to try to learn some best practices than just come up with your own contrived solution. I have to say I have also found your answer very instructive! –  logc May 28 at 13:06

My two cents: I do not think it is a good idea. GIT does something like "storing snapshots of a set of files at different points in time", so you can perfectly use GIT for something like that, but that doesn't mean you should. GIT is designed to store source code, so you would be missing most of its functionality, and you would be trading a lot of performance for just a little bit of convenience.

Let me assume that the main reason why you are thinking about this is to "keep a copy of the data and the code in synch", and that this means you are worried that version 2.0 of your code needs a different database schema than version 1.0. A simpler solution would be to store the database schema, as a set of SQL scripts with CREATE statements, along the source code in your Git repository. Then, a part of your installation procedure would be to execute those scripts on a previously installed database server.

The actual contents of those just CREATE-d tables have nothing to do with the version of your source code. Imagine you install your software, version 1.0, on server A and on server B, which are used in different companies by different teams. After some weeks, the contents of the tables will be very different, even though the schemas are exactly the same.

Since you want to back up the contents of the database, I would suggest to you that you use a backup script that tags the backup dump with the current version of the software that the dump belongs to. The script should be in the GIT repository (so that it has access to the source code version string), but the dumps themselves do not belong into a version control system.

EDIT:

After reading the original post that motivated the question, I find this an even more dubious idea. The key point is that the mysqldump command transforms the current state of a DB into a series of SQL INSERT statements, and GIT can diff them to get only the updated table rows.

The mysqldump part is sound, since this is one of the backup methods listed in MySQL's documentation. The GIT part is where the author fails to notice that database servers keep a transaction log in order to recover from crashes, including MySQL. It is using this log, not GIT, that you should create incremental backups for your database. This has, first and foremost, the advantage that you can rotate or flush the logs after recovery, instead of bloating a GIT repository into infinity and beyond ...

share|improve this answer
2  
I am not sure I see any point in storing the database schema without the data in version control. The data is the most important thing, and that is what I want to back up. I like the idea of tagging the database backup with the current software version however. I'll try to implement something like that. –  wobbily_col May 26 at 9:36
9  
The point of storing the schema without the data is that, right after installation, your software should be "ready to be used". If it is a wiki, then it should be ready to start creating wiki pages and writing something into them. If you install the schema and the contents, then your wiki is already filled with X wiki pages after installation... That is not exactly "installing a wiki system to write our content", but "copying a wiki from somewhere to read it". –  logc May 26 at 9:40
3  
It may be a good idea to modify your question with the actual situation you are in. Even if you can't post all details, it would be important to state that you need a lot of the data to appear unmodified in each installation, or there is a single installation ... –  logc May 26 at 9:51
2  
@wobbily_col A non-text, binary based format has limited value in the context of source control. You can't diff it, you can't branch / merge it, etc. So, while you certainly CAN use git to store the DB, most people prefer to script the DB structure as well as the necessary data. It's a compromise between having a bit more work, but providing the above list of features. You'll have to weigh up whether or not this is a good idea for your solution. Otherwise, you can probably get GIT to store the DB directly, it's just not exactly the best fit for the task. –  Daniel B May 26 at 11:16
3  
@RaduMurzea: I think this is a question of principles. A version control system is designed to manage source code, and not binaries, that's all. It's not a question of size. No, database dumps should not be checked in into the repository, just like training videos should not be checked in either. But no one is stopping you from doing so. :) –  logc May 26 at 12:38

Personally, I don't think it's a good idea to use a source control version system to store the backup files, because the GIT version control is designed for data files, not for binaries or dump files like a MySQL backup dump file. The fact that you can do it doesn't mean automatically that you should do it. Moreover, your repository, considering a new database backup for each new commit, will dramatically grow, using a lot of hard disk space and the performance of GIT will get affected, resulting in a slow source control system. For me it's fine to execute a backup strategy and have always ready a backup file when you need to restore the database when something in your code goes wrong, but source control tools aren't made to store binary data.

For these reasons, I don't see any utility in storing the backup files for day 1 and for day 2, and then seeing the differences between the two backup files. It will require a lot of extra and useless work. Instead of using GIT to store database backups when you commit new code, store the database backups in a different path, separated by date and time, and insert in your code some reference to the new database backups created for each version, using the tags, as someone already suggested.

My final note about the database backups and GIT: A database administrator, when he needs to restore a database because some data has been lost, doesn't need to check the differences between the backup file for day 1 and the backup file for day 2, he needs just to know which is the last backup file that will allow him to restore the database, without any error and data loss, reducing downtime. Indeed, the task of a database administrator is to make the data available for recovery as soon as possible, when the system, for some reasons, fails. If you store the database backups in GIT, linked to your commits, you don't allow the database administrator to restore the data quickly, because your backups are limited to points in time that you stored in the GIT repository, and to reduce the downtime of the system, because the performance of your GIT repository will be dramatically reduced having a lot of data to store.

Then, I don't recommend to store the backups using GIT, use instead a good backup software solution (there are some of them here), that will provide more granularity and will allow you to keep your data safe and secure, and making your data recovery simple and fast in case of disasters.

share|improve this answer
    
Maybe the downvoter will explain why he/she downvoted.. –  Alberto Solano May 26 at 9:25
1  
Not the downvoter, but I think this approach introduces an ever present merge conflict which is not particularly conducive to the branch-often, merge-often workflow that most git users prefer. –  Daniel B May 26 at 11:25
    
@DanielB I propose to not use the version control system to store the database backup files. I think the database backup problem could be easily solved without using any version control system. The version control systems (GIT, TFS, SVN and so on..) are designed for software, not dump files or database backups or for just storing data (there're are a lot of solutions for that). –  Alberto Solano May 26 at 13:20
    
I think most users read first few sentences and downvote, as it seems you will be saying it's ok to use. –  edvinas.me May 26 at 20:28
1  
@AlbertoSolano I see; but reading the question ("can I backup my DB in in GIT?") and then your first statement ("it's fine to store the backup file..."), it seems like you're saying the opposite. The rest of the answer seems to be saying that it's neither here nor there, while I suspect most people think it's a train-wreck waiting to happen. –  Daniel B May 27 at 6:13

You should not store binary data in Git - especially database.
Code changes and database DML changes are totally different things.

MySQL and Oracle can write archive logs for the purpose of being restored to any point in time. Just backup those logs to somewhere safe and you will be okay.

To use Git to back up these "archive logs" does not make sense. Archive logs in production environments are rather heavy and should be removed after making regular full backups. Also it is useless to put them in git - those are already a repository in some sense.

share|improve this answer
1  
why wouldn't one use Git to back up these "archive logs" created by MySQL? –  gnat May 26 at 14:39
1  
Just because it does not make sense. Archive logs in production environments are rather heavy and should be removed after making regular full backups. Also it is useless to put them in git - those are already a repository in some sense. Michael Hampton gives a pretty good answer on this issue (on this page). –  Jehy May 26 at 14:44
1  
Why bother rotating logs, if you are going to keep a copy of everything in git? Might as well just keep one monster log file. –  wobbily_col May 27 at 11:46

protected by Yannis Rizos May 28 at 9:34

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.