Database Download Script

Photo of Greg Harvey
Fri, 2009-05-29 12:21By greg

Just thought I'd throw this one up there. It's a Linux bash shell script I wrote this morning. You know when you want to take a copy of your production database down to localhost for testing? Not rocket science, but a bit of a pain. This dramatically speeds things up.

In the past I've copied and pasted an old shell script that's been kicking around on my hard disk for years, changed the details and run it. Well this morning I thought "Enough!" I tidied it up so it accepts arguments and even has a --help argument response. This is the code:

if [ $1 = "--help" ];
echo "

Syntax for running this script is:
./ sshuser sshdomain remotedatabaseuser databasename

sshuser - string, username for ssh connection
sshdomain - string, the actual domain where the remote database is
remotedatabaseuser - string, username for remote database
databasename - string, name of database
." &
echo "Getting remote db called $4 from $1@$2 ..."
ssh $1@$2 "mysqldump -u $3 $4 | bzip2" | bzip2 -d > dump.sql
echo "Resetting local db called $4 ..."
echo "drop database $4;" | mysql -u $3
echo "create database $4;" | mysql -u $3
echo "Logging in and restoring db dump to $4 ..."
mysql -u root -h $4 echo "Deleting db dump ..."
rm dump.sql
echo "Done!"

Save it as a .sh file, make it executable and run it with the required arguments. Job done!

You might need to edit it if you have database user passwords on either the remote or local environments, but that's trivial - just throw in a -p on the mysql and mysqldump lines where appropriate. =)