Tag: fetch

How To: Fetch Remote Production Databases For Local Development

How To: Fetch Remote Production Databases For Local Development

Ever had that feeling you’re working with outdated data? Sure you have, that’s about the time when you ssh into the production server, make an sql dump and import in into your local development project. I personally find the whole thing extremely boring and rather gay, considering the fact it could all be done automatically… which is what this post is about.

I made a bash script a while ago to help automate fetching of a remote database into my local environment and I’ve been using it with countless projects ever since. It has become a key part if many of my projects’ development branches and I’ve grown very attached to it.

Prerequisites:

  • You’re using MySQL;
  • You have SSH access to production server;
  • You have MySQL credentials (for the root user);
  • If you’re using Windows, you’re also using Git Bash and have your MySQL binaries added to the PATH environment variable.

Script:

Copy the following code into a new file and save it as update-db.sh (you can call it anything else once you earn its trust):

#!/bin/bash

HOST=<ssh host address here>   # host address to connect via ssh (i.e. 123.123.123.123 or somehost.net)
PORT=<ssh port here>           # port for ssh connection (default is 22)
SSH_USER=<ssh username>        # ssh username 
Password=<ssh password>        # password for ssh user
ROOT_PW=<sql root password>    # password for mysql root user
TMP_DIR=/tmp                   # temp remote directory for the sql dump

# Fetching the actual hostname of the ssh server
HOSTNAME=$(ssh -p $PORT $SSH_USER@$HOST "hostname")  

# Gracefully handling potential script arguments
echo "Choose remote database to fetch (<default database name>)"
read -p "Press (ENTER) for default, or type ALL: " DATABASE
if [ -z $DATABASE ] 
then
 echo "Fetching remote database..."
 DATABASES=<default database name>
 FILENAME=$HOSTNAME-$(date +"%Y-%m-%d")
else
 FILENAME=$HOSTNAME-$(date +"%Y-%m-%d")
 echo "Fetching remote databases..."
 DATABASES=$(ssh -p $PORT $SSH_USER@$HOST "mysql --user=root --password=$ROOT_PW -BNe \"SHOW DATABASES;\" | grep -Ev \"information_schema|mysql|performance_schema\" | awk '{ printf \" %s\", \$0 }'")
fi

# Generating SQL dump for selected database(s)
IGNORE=$(ssh -p $PORT $SSH_USER@$HOST "mysql --user=root --password=$ROOT_PW -BNe \"SELECT CONCAT( TABLE_SCHEMA , '.' , TABLE_NAME ) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'rec%';\" | awk '{ printf \" --ignore-table=%s\", \$0 }'")
ssh -p $PORT $SSH_USER@$HOST "mysqldump --force --user=root --password=$ROOT_PW $IGNORE --databases $DATABASES | gzip > $TMP_DIR/$FILENAME.sql.gz"

# Fetching the SQL dump to local temp directory and cleanup the remote temp directory
scp -P$PORT -c blowfish $SSH_USER@$HOST:$TMP_DIR/$FILENAME.sql.gz $TMP_DIR
ssh -p $PORT $SSH_USER@$HOST "rm -f $TMP_DIR/$FILENAME.sql.gz" >/dev/null 2>&1
gunzip $TMP_DIR/$FILENAME.sql.gz

# Purge the local database (if existing) and import the remote database from sql dump
echo $DATABASES | awk '{ printf "DROP DATABASE %s;", $0 }' | mysql --user=root --password=$ROOT_PW
mysql --user=root --password=$ROOT_PW < $TMP_DIR/$FILENAME.sql
rm -f $TMP_DIR/$FILENAME.sql
echo "Updating local database(s) complete."

Make sure you replace the bootstrap variables with your own data and then make the script executable by running chmod:

chmod +x update-db.sh

Now what?

Now you just run it. If you configured it correctly, it will NOT expose your machine by opening all available ports, steal all your sensitive information, format your other partitions or burn your house down:

./update-db.sh

Here’s exactly what happens when you run it:

  • It says Press (ENTER) for default or type ALL, to which you respond by doing so dooh~
  • It connects to the SSH server and generates an sql dump in the temp directory, containing the selected remote database or all of them, except information_schema, mysql and performance_schema
  • It copies the sql dump file (its name would be something like <hostname>-<year>-<month>-<date>.sql.gz) to your local temp directory
  • It unzips the file on your local machine
  • It deletes the remote sql dump file
  • It drop the local database(s) if existing and imports the remote one(s) from the sql dump
  • It says Updating local database(s) complete.

The catch…

Almost too good to be entirely true, yes, the script has a few minor drawbacks… I cannot be entirely sure, but I think you shouldn’t use this script with the ALL option (or typing anything else before pressing ENTER for that matter) – by the looks of it, it might drop all existing local databases and import the ones in the sql dump (which is perfect only if your workstation doesn’t contain other databases than what the server does).

I’ve been successfully using it with the default option for too long to care anymore. Another downside would be that using it makes you about 30% less cool, as compared to your typical routine of manually ssh-ing into the server, dumping databases and scp-ing them back you sexy hacker, you…

Advertisements