Posts Tagged ‘subversion’

Backing up a MySQL database to Subversion

Monday, October 26th, 2009

As you may know, I recently set up subversion. As of this morning I was backing up my database with a daily cron job that sent mysqldump’s output to a file called database_[date].sql.bz2. That worked, but I’d rather not have to manage the files myself.

When I created the script to commit the newly created backup files, I intentionally removed the zipping step because I wanted to be able to use subversion’s diff feature, which would be useless if I commit binaries.

# Dump the database and put it in a repository.
# Inspired by
# Written on Mon Oct 26 02:59:02 PST 2009
DATE=`/bin/date +%s`
/usr/bin/mysqldump -u user -ppassword --skip-opt --comments=0 --all-databases>$FILE
# Must commit a whole directory even though
# we're only dealing with one file. The file name
# cannot change from day to day.
/bin/cp $FILE $WORKING_DIR/server.sql
/usr/bin/svn commit -m "daily backup; file saved as $FILE"
# I could delete $FILE now that $WORKING_DIR has
# been committed, but I like to have a backup
# backup in case the commit fails.
#     If you want to live dangerously and assume
# everything went as planned, feel free to
# uncomment the following line to delete.
#/bin/rm $FILE

I believe this will fail if cron tries to commit the file a remote repository over ssh because the user is unable to address the password prompt. I’m told ssh is designed to not let you redirect input or otherwise script around the password prompt. A potential solution is having the server authenticate ssh users by key instead of password.

Oct 26 Edit: Minor bug fix. I haven’t tested it post-change. I don’t think I added any new bugs, but nobody ever thinks that, so use discretion before running this script.

Setting up Subversion

Sunday, October 25th, 2009

Every six months or so I decide to set up subversion on my server. Inevitably, I get some error (usually at the svn+ssh stage) I can’t find a solution to and lose interest after several hours of trying. I don’t know if I’m smarter or if errors are better documented, but I was successful.

I primarily used these instructions. Some of the errors I did encounter include:

  • When testing the online repository, I kept getting a forbidden error, even after inputting the correct password. It turns out I was giving it the wrong repository name. This would have taken me forever to diagnose without tail /var/log/httpd/error_log.
  • On import, “Unable to open an ra_local [sic] session to URL”. I had the arguments in the wrong order. It should go:
    svn import [files] [protocol]://[repository]
    I wish subversion’s otherwise excellent documentation would use more explicit terminology in distinguishing between your working copy and the repository.
  • I deleted a directory in my working copy, and when I try to check it in I get the error “Directory [directory] is missing”. I thought I could solve that by deleting it with svn delete instead of rm, but apparently that’s not the case. After a couple hours of googling and still lacking a better idea, I ran svn delete again immediately before committing. That did the trick, but I’m not sure why it was necessary
  • I have two separate config files, one for the development version on my laptop and another for the live version on the server. Now that both are drawing from a common repository, I made two config files in the same package, one for debugging and the other for live. I check in the files from my laptop and ran svn update on the server to bring them up to synchronize them. This did not synchronize them. I tried checking out a new version on the server, and although subversion indicated that it completed the operation successfully, the live version is still not using Although they both claim to be using revision 6, the live version is still showing the old config files.
  • Spent several hours finding out the hard way that can’t just add a file to a working directory and have subversion include it automatically, you need to run svn add file then commit changes. This is my least favorite feature of subversion.

I toyed with making the repositories available online. While it worked, I’d like to make them pretty before I introduce them to the public