Database Export/Import Explorations

Thought I would share my explorations with exporting the local development database info the the production DB.

I’m looking at a few options. Sequel Pro, command line mysql, wp_cli and the wordpress import/export tool.

The details come from the group_vars/development file.

      - # this is the SSH host
    #... more stuff
        #this is where the wordpress database, user stuff is

Looked at using Sequel Pro, where the connection panel looks like

I actually had to click away from the ssh pane then back after filling in the SSH Host in order for the fields in which to enter the SSH username and password to appear.

Testing the connection, since there’s no certificate on my test site (Vagrant box), got the message:

The authenticity of Host can’t be established. Do you wish to continue?


Once connected, user can export whichever tables are desired in various formats (Under File>Export).

Wanted to try wp-cli, which if I remember correctly was installed as part of Trellis provisioning.

wp db export
Error: This does not seem to be a WordPress install.
Pass --path='path/to/wordpress' or run 'wp core download'.

We need to do this from within the wordpress directory, OR pass the location of that directory.

The path to WP in a Vagrant box is
wp --path='/srv/www/server_name_set_in-group_vars/development-file/current/web/wp

I installed wp-cli on the production server (shared hosting, so it is only accessible by full path to phar file:


Used rsync to send up the exported file, but when running:

/home/user_name/public_html/web/wp-cli.phar import wordpress.thedate.xml

I was notified that “wordpress importer” needs to be installed.

You can install it with wp-cli, but that would create a divergence with local and production installs, so took the opportunity to use composer to include it in the bedrock remo.

Added a few lines ('cause there are other desired plugins) to composer.json:

  "require": {
    "php": ">=5.4",
    "composer/installers": "~1.0.12",
    "vlucas/phpdotenv": "^2.0.1",
    "johnpbloch/wordpress": "4.2.4",
    "wpackagist-plugin/advanced-custom-fields": "*",
    "wpackagist-plugin/posts-to-posts": "1.4.x",
    "wpackagist-plugin/wordpress-importer": "0.6.x"

When looking in Packagist for plugins, you have to replace spaces with hyphens. “Advanced Custom Fields” was not found, but “advanced-custom-fields” was.

I had installed wp-cli inside of the public_html/web directory so it was gone after having deployed. Tracked it down in the recent release directory and moved it to the home folder or something. Then I couldn’t remember where I had uploaded the exported sql file to so just used the wordpress importer from within the admin panel.

At a glance - this looks like a good read, and this is roots-biblical.

Input, advice, feedback and questions welcome.

The Sequel Pro method worked for me but it took a minute to figure out. All the DB/host information is available in group_vars/development. But SSH into the Vagrant box? Not straightforward nor explained anywhere I could find.

###SSH Host:
You need to use the virtualhost your Vagrant instance establishes when it boots up. In my example it’s ltkbedrock.local but it could be anything.

###SSH User:
Vagrant also establishes itself with a root user of vagrant which is standard. You might have modified this when you setup your box but that’s unlikely if you used Trellis.

###SSH Password:
This was the missing piece of the puzzle for me. I realized the vagrant box setup by Trellis has its own SSH key. This is obvious in hindsight but just didn’t occur to me for whatever reason.

You can find it here:

Use that blue key in the SSH Key input field to use Finder to locate the file. Make sure you can view hidden files.

I’m getting login errors on MySQL using “admin” as my user (from {{ admin_user }}) and my local db password as the password…

Any ideas?