One-liner for fetching the live-database on your dev server (or localhost)

Submitted on Mon, 10/14/2013 - 19:40

So when I first realised that I was neglecting this blog, I somewhat found comfort in that at least it hadn't been a year, right? OK, so now it has almost been a year. Does that mean I have stopped finding solutions to my Drupal problems (as the "About me" block states)? Well, no. The biggest problem is remembering to blog about them, or finding the time. But finding the time is not a Drupal problem, and I most definitely have not found the solution to that problem. Anyway, I digress. Let's end the streak with a quick tip that I use all the time: Syncing live databases without having drush aliases.

If you use drush-aliases, you could just do drush sql-sync. But for me, even if I do, I still prefer this method, as I find it extremely easy to debug.

First I make sure I am in my Drupal root:

$ cd /path/to/drupal

Then I usually make sure I can bootstrap Drupal from drush:

$ drush st

If that output says my database is connected, then let's test the remote connection:

$ ssh [email protected] "drush  --root=/path/in/remote/server st"

If that output also says we are connected, it means we are good to go, with my favourite command ever:

$ ssh [email protected] "drush  --root=/path/in/remote/server sql-dump" | drush sql-cli

If you have a drush-alias for the site, you can also go:

drush @live-site-alias sql-dump | drush sql-cli

OK. So what does this do?

The first part says we want to ssh in to our remote live server. Simple enough. The next part in double quotes, tells our terminal to execute the command on the remote server. And the command is telling our remote server to dump the entire database to the screen. Then we pipe that output to the command "drush sql-cli" on our local machine, which basically says that we dump a mysql database into a mysql command line.


If you get this error:

bash: drush: command not found

I guess you could get this error if you are for example on shared hosting, and use a local installation of drush in your home folder (or something). Simply replace the word drush with the full path to your drush command. For example:

$ ssh [email protected] "drush  --root=/path/in/remote/server sql-dump" | drush sql-cli

If you get this error:

A Drupal installation directory could not be found

You probably typed in the wrong remote directory. Try again!

I'm going to end this with a semi-related animated gif. Please share your semi-related workflow commands (or animated gifs) in the comments.