Roots Discourse

Issue exporting a database using wp-cli from local development to remote staging server

workflow
#1

Hello! I’m having an issue exporting a database using wp-cli from my local development server to a remote staging server.

I’ve run vagrant ssh, and am in the /srv/www/{mysite.com}/current folder - i’m trying to run:
wp db export --ssh=root@vas.mysite.com.au /srv/www/mysite.com.au/current/
but it’s returning the following error:
Error: This does not seem to be a WordPress install. Pass --path=`path/to/wordpress` or run `wp core download`.
I’m able to successfully run other wp-cli commands like wp db check which returns green across the board.

I’m just trying to export my local database to my remote staging server. I assume that once it’s exported the remote location above, I should just be able to run wb db import mysite_dbase.sql?

Any help would be great, thank you.

#2

I would just break it up into a few commands.

wp db export

rsync -azP /srv/www/{mysite.com}/current/mysite_com_development.sql root@vas.mysite.com.au:/srv/www/mysite.com.au/current/

Then just hop on the other server and import like you said.

But realistically you should run the rsync with your web user instead of root on the remote server, so that the file will have the proper permissions for the import and not be owned by root in your web folder.

Normally I just export my database inside my local root directory and just rsync the whole current/ folder to the remote server. Rsync will only move files that have changed as well after the first time, so it gets much faster the next couple go rounds.

https://linux.die.net/man/1/rsync

1 Like
#3

Also, you are running the command once you ssh into your local server correct?

#4

I’ve been playing with WP-CLI aliases as per: Leveraging WP-CLI Aliases in Your WordPress Development Workflow. It’s great being able to run WP-CLI commands on remote servers and within vagrant right from the local site directory.

I also found the shell script @benword posted up made loads of sense. I tweaked it a bit for my liking and I use it for pushing the database and uploads from dev up to staging:

read -r -p "Would you really like to overwrite the staging database with dev? [y/N] " response
if [[ $response =~ ^([yY][eE][sS]|[yY])$ ]]
then
    wp @development db export - > sql-dump-development.sql
    scp sql-dump-development.sql web@staging.example.com:/srv/www/example.com/current

    wp @staging db export just-in-case.sql
    wp @staging db reset --yes

    wp @staging db import sql-dump-development.sql
    wp @staging search-replace https://example.dev https://staging.example.com

    rm sql-dump-development.sql
    ssh web@staging.example.com 'rm /srv/www/example.com/current/sql-dump-development.sql'

    read -r -p "Did everything run smoothly? Remove the temp backup sql file? [y/N] " response
    if [[ $response =~ ^([yY][eE][sS]|[yY])$ ]]
    then
        ssh web@staging.example.com 'rm /srv/www/example.com/current/just-in-case.sql'
    fi

    read -r -p "Sync uploads from dev to staging? [y/N] " response
    if [[ $response =~ ^([yY][eE][sS]|[yY])$ ]]
    then
        scp -r web/app/uploads/ web@staging.example.com:/srv/www/example.com/shared
    fi
else
    exit 0
fi

Could be improved I’m sure but works for me at the moment!

1 Like
#5

Hi @RiFi2k - thanks for your responses! I’ll give the one-two step process a shot - yep I’m running the command after i’ve ssh’d into my local vagrant server.

#6

Hey @nathobson thanks for the shell script option - if the standard two step wp cli option doesn’t work I’ll give the shell script a go :slight_smile:

#7

Let me know how it goes.

The shell script is cool, its basically doing the same thing just saving you from having to ssh into the server yourself. I’m pretty much always logged in to every server I have anyways.

I actually use https://github.com/c9/core installed in the web root of my servers running under the web user. I run a proxy pass in nginx from localhost to a configured URL running under Cloudflare forced SSL, restricting access by IP and also using basic auth. Been running it this way for close to 8 months, super secure, never had a problem, and I get a full code editor, command line access, and ability to make edits and drag and drop files and folders right in my web root, with the web user as pilot, anytime I want. It’s probably the best thing (other than Trellis) that I have found to make my life easier in years. Game changer once its set up.

2 Likes
#8

Oh, if anyone is interested, I keep it running always with https://www.npmjs.com/package/forever and my other good buddy Monit

1 Like
#9

Thanks for the rsync tip @RiFi2k - worked like a charm, just exported the database locally, rsynced to the remote server, imported it, ran a search and replace dry test on the domain name, then ran the actual search and replace, then removed the sql file. Fastest and most pain free database sync I’ve ever had the pleasure of doing. :smiley:

Edit: Would be awesome if this process could be some kind of flag option we could add to the standard deploy command like ./bin/deploy.sh <server> <domain.com> --sync-database - not sure how that would work though :confused:

1 Like
#10

Having said that, I suppose the commands I used to export and rysnc the database locally could just be added to the build-before.yml file? Hmmm…

#11

For sure, for me though, I think you can over automate, then you don’t actually know what needs to be done if your scripts and automation break or fail. I actually enjoy and feel better overall when I’m running the migrations server to server myself.

2 Likes
#12

Yeah thats a fair point too - always a good idea to keep the process fresh in your head via repetition :thumbsup: