Export WP database for import using WP-CLI on Vagrant Box

by

Problem

Now that I have a new environment with WP-CLI db import / export I need to import a database from a website that never had WP-CLI installed.

Question

What’s the best Non-WP-CLI export method for WP-CLI import?

Backstory: I’ve been using Basic WordPress Vagrant Environment for local testing and recently the author added WP-CLI. The more I’ve been playing around with it the more I’m in love. I even went so far as to create a bash script to spin up new Vagrant boxes using just a name.

Is it better to do an export in WXR format through the WordPress export panel or script then use WP-CLI’s import?

require ABSPATH . 'wp-admin/includes/export.php';

// Buffer output
ob_start();

// Generates the WXR export file for download.
$defaults = array (
    'content'    => 'all', 'author' => false, 'category' => false,
    'start_date' => false, 'end_date' => false, 'status' => false,
);

export_wp( $defaults );

// Get the buffer contents
$xml_export_data = ob_get_clean();
echo $xml_export_data;

Or is there a similar way like above, that I can leverage the WP-CLI db export code and produce a file ready to be imported.

$ wp db export /vagrant/site/site_export.sql
Success: Exported to /vagrant/site/site_export.sql

The easiest route for my main (most important server) is to just do a mysqldump directly through SSH.

# backup:
mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

# restore:
mysql -u root -p[root_password] [database_name] < dumpfilename.sql

I’m just not sure that the regular SQL dump will hold all the values required for a WP-CLI import.

Update

A simple mysqldump could be the only thing needed. Now I’m thinking I should have transferred over all the files before import… I’ll keep ya’ll posted but this could be a two-line process.

1 Answer
1

THE SHORT

Export

sudo mysqldump -u root -pPASSWORD DBNAME > filename.sql

Import

wp db import filename.sql

THE LONG

So this turns out to be very painless. The server dump is exactly what WP-CLI needs to import.

REMOTE

  1. sudo mkdir backup
    • Make a directory for the file.
  2. sudo chown -f -R ec2-user:group backup
    • Set permissions to the directory for my SSH user.
  3. cd backup
    • Enter the directoy
  4. Run sudo mysqldump -u root -pPASSWORD DBNAME > filename.sql
    • Initially I was getting permission errors and that was because my EC2-USER needed control of the output directory.

LOCAL

  1. Download filename.sql to my Vagrant folder
  2. vagrant ssh
    • into the box for WP-CLI access
  3. wp db import /vagrant/filename.sql
  4. wp search-replace old-site.com new-site.dev --test-run
    • Test the operation first
  5. wp search-replace old-site.com new-site.dev

NOTES

  • Make sure plugins and themes are in the correct directories
  • Any errors in your PHP code will break the WP-CLI since it loads WP
  • WP Codex recommends to not change the GUID. You can skip the GUID in search and replace with wp search-replace 'http://example.dev' 'http://example.com' --skip-columns=guid

Leave a Comment