Super sexy mysql backups

Written by David Banham

Note from the future - 4th March 2014

This is a super dumb way to do this. Your git repo will eventually get completely out of hand. jgit will barf all over everything. Things will generally be terrible. Probably use tarsnap instead.

End note.

This will give you backups of all your mysql data, nicely versioned with git, securely stored in S3.

There are plenty of guides out there on how git, and S3 work. There are less on how jgit works, but there are still some around. This is just the bare bones to get you up and running as quickly as possible. If anything is unclear, hit me in the comments.

Download jgit.sh (http://www.eclipse.org/jgit/download/) <del>enable partner repository</del> <del> sudo apt-get update</del> <del> sudo apt-get install sun-java6-jre</del>

edit:: jgit seems to work fine with the openjdk, and AWS has a kernel bug where installing the sun package on a t1.micro instance causes the machine to freak out. Only occurs in some availability zones. Had some fun figuring that out!

sudo apt-get install openjdk-6-jre-headless

vim ~/.jgit accesskey: ACCESSKEY secretkey: SECRETKEY

mkdir s3backup cd s3backup git init mysqldump DBNAME -u USERNAME -r dump.sql —skip-comments git add dump.sql git commit dump.sql -m "Initial commit" git remote add s3 amazon-s3://.[email protected]/SOMEPROJECTNAME.git ~/jgit.sh push s3

Cron script contents:

!/bin/bash

d="date" cd /home/ubuntu/s3backup mysqldump DBNAME -u USERNAME -r dump.sql —skip-comments git commit cpanel.sql -m "$d" git gc ~/jgit.sh push s3

To get data back:

~/jgit.sh clone amazon-s3://.jgit@BUCKETNAME/PROJECTNAME.git git log git reset —hard THEHASHIDOFYOURDATABASEBEFOREYOUMESSEDITUP