I want to automate the backup of my databases and files with cron. Should I add the following lines to crontab ?

mysqldump -u root -pPASSWORD database_name | gzip > /home/backup/database_`date +\%m-\%d-\%Y`.sql.gz

svn commit -m "Committing the working copy containing the database dump"
  1. First of all, is this a good approach?

  2. It is not clear how to specify the repository and the working copy with svn?

  3. How can I run svn only when the mysqldump is done and not before ? Avoiding conflicts

  • 1
    Why in the world are you pushing database dumps to SVN?
    – blueben
    Jan 3 '11 at 11:54
  • @blueben Because I'm using the svn to store my backups (drupal websites consisting of files and mysql database)
    – aneuryzm
    Jan 3 '11 at 11:55
  • How large are these database dumps?
    – blueben
    Jan 3 '11 at 11:56
  • @blueben mhm why? It depends on the website.. but let's say around 5mb if they are gzipped
    – aneuryzm
    Jan 3 '11 at 11:57
  • 2
    Backing up databases to SVN is a unique method.
    – blueben
    Jan 3 '11 at 12:02

1) If you insist on storing backups in subversion, then there is nothing wrong with this approach. It is strange, though.

2) You should keep a checkout around, place the dump into the working directory, and run svn update and svn add as appropriate before committing.

3) If you run the commands as shown from a shell script, there should be no overlap.

  • 1) Saving a DB backup in a SVN is absolutely a bad approach. 2) if you copy/move the DB in a working dir and then you do an update you will overwrite the local copy with the one on the SVN server (am I wrong?)
    – tmow
    Jan 3 '11 at 13:20
  • @tmoww Why is a bad approach ? I'm using svn to create a backup of my Drupal files and somehow I have to store the database as well. 2) Yes, I need to create a dump and committ first, otherwise I overwrite it.
    – aneuryzm
    Jan 3 '11 at 13:37

Also note that compressing the mysql output will create vastly different binaries and cause your repository disk requirements to balloon. It may take more initial space with the uncompressed sql but the text diffs will be stored much more efficiently in the repository. Also there is no need to store each one as a separate file with the date in the name. Might as well be the same file since version control will let you turn back the clock.

I much prefer rolling backups with something like the last 7 days of zipped up sql then snapshots one week apart going back a month or two. I don't see the need to version control the database for all eternity.

  • Is there a way to get rid of the database file old versions in my svn repository ? Or is the repository just dropping the oldest versions when there is no space anymore ?
    – aneuryzm
    Jan 4 '11 at 8:46
  • I think there are ways to cherry pick revisions and delete them permanently. A new feature is going to be added called obliterate that will do this much easier. subversion.wandisco.com/component/content/article/1/36.html
    – vinnyjames
    Jan 6 '11 at 21:08

As I said saving the DB in a SVN repository is not a good practice.

Regarding the mysqldump, keep in mind that in this way you are also including these options (--opt is the default that is a shorthand of the below):

--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset

So, if you will use the full dump you created you will overwrite all the data you inserted after the last backup you performed.

Till your DB will be as small as you said, I advice you to do a backup more often.

This is a nice example on how you can proceed.

  • How do you suggest to backup the database then ? I've been told I can backup the files using svn and committing the changes with cron. I also need to backup the database.
    – aneuryzm
    Jan 3 '11 at 13:50
  • @Patrick You just save the dump you obtained somewhere safe. baked up on a tape, or on a CD/DVD, on a shared disk, on another backed up server. This really depends on your needs. Did you see the link I've provided you?
    – tmow
    Jan 3 '11 at 13:57
  • Yeah but the point is that I need to know which backup is associated with the last changes. Let's say a user add a new node to my Drupaò: new pictures are added to the files folder and new rows are added to the database. These changes should be tracked under 1 unique version of the code.
    – aneuryzm
    Jan 3 '11 at 14:29
  • However now I have another doubt. Let's say a new picture is added to the folder. Is the svn command "commit" automatically adding the new file ?
    – aneuryzm
    Jan 3 '11 at 14:30
  • it's more interesting if you make a new question I think. This way we don't mess up different subjects.
    – tmow
    Jan 3 '11 at 14:32

If your DB is large, storing hundreds of copies of a database will exceed your storage capacity, probably catching you unprepared and busy with something else. gzip compression will almost certainly hurt, since it impede's SVN's ability to compress between revisions, and I think SVN already uses a zip library internally. You might take take a few days worth of backups and try it both ways and see which one uses less disk. It'll probably also be helpful to order the dump in some way, like say with --order-by-primary; otherwise, SVN will have to waste disk representing frivolous reorderings of mysqldump, which you don't care about.

But eventually you have to simply discard data. One interesting approach I've seen went by the name of "logarithmic backups". The idea is that newer backups are more important than older backups, so you save more of them and expire most of them as they age. So you end up with

  • 7 daily backups from the past week
  • 12 monthly backups from the past year
  • 1 annual backup from years prior.

This is a similar approach to RRDtool, where data is consolidated into a representative object. You wind up with 20+ backups total, and the ability to recover short lived data from the recent past and long-lived data from the distant past.

Actually answering the question

Since your data is relatively small, and probably doesn't change much, SVN might not be a bad approach.

I have a similar process for putting websites of interest into SVN, which I've modified to suit your needs. Put this in your cron.daily or whereever, and it'll get you done. You will need to initialize the repo first, and tweak it to suit your needs, but this is a good start:

# check out to temp dir
DIR=`mktemp -d`
cd $DIR

# check out repository
svn co $1 .

# dump db
mysqldump --order-by-primary -u root -pPASSWORD database_name

# if changed, commit
svn commit -m 'Nightly backup'
cd ..
rm -rf $DIR
  • @jldugger Thanks for the answer. I'm actually planning to do a monthly backup for my website. The gzipped database is around 10MB.
    – aneuryzm
    Jan 3 '11 at 17:52
  • 10MB is kind of small in the relm of databases, so you face a tradeoff: Is the disk space worth losing 29 days worth of website data? If most of that 10MB doesn't change, you may well be able to the cost of backing the DB up to below two full mysqldumps. Plus, SVN stores changesets internally, so the decision may just be more smaller changesets vs fewer large ones. The more I think about this, the more it looks like daily is cheaper in the long run.
    – jldugger
    Jan 3 '11 at 17:56
  • @jldugger I was now wondering if the svn hosting service is going to remove the oldest versions by default when the maximum size is reached. I don't want to discover after some months, there is no space anymore and I've to manually delete all old files from repository.
    – aneuryzm
    Jan 4 '11 at 8:48
  • @jldugger Also, are the changes to binary files detected as well by svn ? If I overwrite my sql.gz file, is this committed again in svn ?
    – aneuryzm
    Jan 4 '11 at 10:05
  • SVN won't clear out old copies, it's purpose is to archive everything forever. You can script the dumpfilter to toss all revisions older than you like, but it's a bit hacky. I wouldn't bother with that until you figure out how fast the archive will grow, how much disk budget you want for this, and how to model the price of disk into the future.
    – jldugger
    Jan 5 '11 at 19:45

Hows about doing an automated dump of the backup with automysqlbackup and then creating auto-managed full/diffs with BackupPC?

BackupPC hard-links between non-changed files within different backups to minimise disk space usage.



  • It is not about the system, but the free space for the backup
    – aneuryzm
    Jan 4 '11 at 8:50

Dumping your database to a file and the committing that file to svn is a fine approach. There are a couple of things I recommend changing though... You should overwrite the same file every time you dump (remove the date from the filename) and remove the zip.

Just like editing your source code, you are essentially modifying the database dump file. Also, as someone else mentioned, if you don't zip it, in the long run it will compress much better on the server because the diffs will be much smaller.

The great part about this approach is that it ties together the database with the code at a particular time. For example, you need to run revision 1428. No problem... update your local repo to rev 1428 and you have all of the code and the database dump that runs with 1428. Load that dump, compile your code, and you're in business.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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