Skip to main content

Securely pull a remote database to your local

Submitted by Trevor on Fri, 03/24/2023 - 16:14

Still trying to get things up and going on my blog.  Things are mostly in shape, but the site needs to go live before I can tune everything.  In the meantime I'd like to share a little trick I've built in the past few weeks.

When you're working on local site development, you usually have a live site sitting somewhere in the cloud, and a local development environment.  Long gone are the days of running apache on your local machine, instead today we use docker containers - virtual machines that run web servers and databases so we don't need to install a whole bunch of software locally.  I can keep multiple websites segregated and distinct so some weird setup on one machine doesn't conflict with another.

As a general rule, code moves up (from your local machine to the dev, stage and then live server) and databases move down (fresh live data from the server that you can examine and break on your local computer.

Getting a fresh copy of that data can be a bit tricky.  Web servers now do their best to secure that data and it can be a little tricky to access remotely - usually you need root access on the web server to access it.  I was hoping to write a quick script to  pull that DB without actually logging into the remote server - and I think I have it here:

$ cat /usr/local/bin/mysqlgrab #!/bin/bash if [ -z "$1" ]; then echo "Usage: mysqlgrab <database_name>"; exit; fi; if [ ! -d "./db/" ]; then echo "Must be in a directory with a ./db/ subdirectory"; exit; fi; database_name=${1//./_}; date=$(date '+%Y%m%d-%H%M%S'); ssh -t "sudo mysqldump $database_name | gzip > ~trevor/db/$database_name-$date.sql.gz"; scp$database_name-$date.sql.gz db/ ssh "rm ~trevor/db/*"

This does make a few assumptions.  My cloud webserver is, and my username is "trevor".  My local installs all have a "/db" directory where the db's are downloaded to.  But you can adapt to your needs.

The real trick here is "ssh -t", which will run a command on a remote machine while forcing a pseudo-terminal. This lets "sudo mysql" to run correctly without getting stuck.

I can run "mysqlgrab" and my server's db is right there, ready to import on my local!