brett@3riverdev.com 260.349.5732

How to Backup an OpenShift MySQL Database with a Shell Script

Posted on November 8 , 2014

As a part of my ongoing consulting with nonprofits, I oversee over a dozen web applications running on OpenShift.  I needed an easy way to backup all of the MySQL databases in one shot.  So, I cooked up the following shell script.  It’s pretty dirty, but it works.  The script assumes Linux and is run as a cron job, but the concept could be easily adapted to other operating systems.  I thought I’d throw it out there in case it’s useful to anyone else.

#!/bin/sh
now=$(date +"%Y-%m-%d")
NAME=OpenShift$now
LOCALDIR=/home/username/backup/$NAME
rm -rf $LOCALDIR
mkdir -p $LOCALDIR
cd $LOCALDIR

backupSql() {
 # TODO: It would be better to 'source' all the environment variables in on shot, but I wasn't able to find a way to do that. For now, just scp the env files and use them.
 scp $1:mysql/env/OPENSHIFT_MYSQL_DB_USERNAME .
 scp $1:mysql/env/OPENSHIFT_MYSQL_DB_PASSWORD .
 scp $1:.env/OPENSHIFT_MYSQL_DB_HOST .
 scp $1:.env/OPENSHIFT_MYSQL_DB_PORT .
 local username="`cat OPENSHIFT_MYSQL_DB_USERNAME`"
 local password="`cat OPENSHIFT_MYSQL_DB_PASSWORD`"
 local host="`cat OPENSHIFT_MYSQL_DB_HOST`"
 local port="`cat OPENSHIFT_MYSQL_DB_PORT`"

 ssh $1 "rm -f app-root/data/$2.sql ; mysqldump --user="$username" --password="$password" --host="$host" --port="$port" --complete-insert $2 > app-root/data/$2.sql"
 scp $1:app-root/data/$2.sql .

 rm OPENSHIFT_MYSQL_DB_USERNAME
 rm OPENSHIFT_MYSQL_DB_PASSWORD
 rm OPENSHIFT_MYSQL_DB_HOST
 rm OPENSHIFT_MYSQL_DB_PORT
}

backupSql "[SSH HOST]" "[APP NAME]"
... (backup multiple apps at once by repeating the above)

cd ..
tar -zcvf $NAME.tar.gz $NAME
rm -rf $NAME

You’ll need to edit a few things:

  • LOCALDIR’s targeted location
  • The actual calls to the ‘backupSql’ function.

UPDATE: A few folks have asked why I don’t simply use ‘rhc snapshot’ for DB backups.  Honestly, I can’t quite remember the circumstances that led to this approach.  This post had sat in my queue for a while before I actually published it.

Here’s what I think happened.  I’m using OpenShift to host about a dozen platforms for nonprofit organizations.  We first started using OpenShift months after OpenShift Online was started.  Since it was in its early stages, I wanted to make sure that the backups would be portable to some other solution, if that became an urgent need.  At the time, I think the snapshots didn’t include an actual .sql export of the MySQL DB.  It was more of a binary approach that was applicable solely for an OpenShift restore.  No idea if that assumption was correct or not, nor do I know if that’s the case anymore.

Virtual IT Director

Instead of dealing with data backups on your own, consider offloading the stress!  We’d love to discuss our fully managed services.

Leave a Reply

Your email address will not be published. Required fields are marked *