WooCommerce data migration without plugin

This guide will cover the migration of users (customers) and orders from one WooCommerce webshop to another. While plugins and WooCommerce extensions can make this possible, most of them either don't do the job right, or they require a subscription and are not free. With a little bit of MySQL magic, you can do it the right way.

Prerequisites

For this to work, you will need access to MySQL command-line interface (CLI) or a graphical UI such as PHPMyAdmin. WP-CLI tool is optional (if you have remote access to your host and required privileges).

Regarding products, this guide will not cover migration as it is pretty trivial to export/import products within WooCommerce itself.

Remove current WooCommerce data (optional)

Let's say you are in a situation where you need to build a completely new website for an already existing WooCommerce-enabled webshop. Common sense dictates that you will clone the existing production version of the site to some local development environment and build a new site from there. And it will take some time for you to create the new version of the site.

Once you have the new site ready, you will probably notice that some new products were added to the old production version of the site, a few new customers opened accounts, and some new orders have been created. And, of course, you need to do another data sync before releasing the site's latest version.

The best course of action in such a situation would be to remove all your "test" data and completely sync the most current data from the old production site. You can start by deleting all products and product-related media from within the WordPress admin dashboard. Once done with that, you can also remove all orders and users marked as Customer or Subscriber. While products and related media are relatively easy to remove (from within the WordPress admin dashboard), I will demonstrate below how to remove orders and customers.

Delete WooCommerce orders

A few MySQL queries are required to delete WooCommerce orders, so you will need access to MySQL CLI or PHPMyAdmin. You need to execute the following queries to delete orders:

delete from wp_posts where post_type='shop_order';
delete from wp_wc_customer_lookup;
delete from wp_postmeta where post_id not in (select ID from wp_posts);
delete from wp_options where option_name like '_transient_wc_report%';

SQL

Please pay attention to the WordPress table prefix. Observe the line in the example above; delete from wp_posts where post_type='shop_order';. The query will delete all shop_order post types in the table wp_posts. So, the table's prefix is wp_, which is the default WP table prefix. This may not always be the case. You can check the table prefix in MYSQL CLI by selecting the database and list its tables:

use $DATABASE_NAME;
show tables;

SQL

Once you execute all four queries, all orders will be deleted. With them gone, you can now remove customer/subscriber accounts.

Delete WooCommerce customers

You will need a WP-CLI tool installed to delete customers. Once you have it, please go to your WP website content directory and execute the following commands:

wp user list --role=subscriber --field=ID | xargs wp user delete --yes
wp user list --role=customer --field=ID | xargs wp user delete --yes

Bash

With both commands executed, all users marked as a subscriber and customer will be removed from your website.

Export WooCommerce data

We will export all data related to users and orders. To do so, we will use MYSQL-CLI and execute a few queries that will save all our data to a specific file. Again, you will have to know the correct prefix of your database tables and the correct location where export files will be saved.

Export WooCommerce users

To export all WooCommerce users (subscribers and customers), you need to execute the following set of queries either in MYSQL-CLI or PHPMyAdmin:

select
  wp_users.* 
from
  wp_users 
  join
    wp_usermeta 
    on wp_users.ID = wp_usermeta.user_id 
where
  wp_usermeta.meta_key = 'wp_capabilities' 
  and 
  (
    wp_usermeta.meta_value like '%subscriber%' 
    or wp_usermeta.meta_value like '%customer%'
  )
  into outfile '/var/lib/mysql-files/export-customers' CHARACTER 
SET
  utf8;

SQL

select
  wp_usermeta.* 
from
  wp_usermeta 
where
  user_id in 
  (
    select
      user_id 
    from
      wp_usermeta 
    where
      meta_key = 'wp_capabilities' 
      and meta_value like '%subscriber%' 
      or meta_value like '%customer%'
  )
  into outfile '/var/lib/mysql-files/export-customers-meta' CHARACTER 
SET
  utf8;

SQL

select
  wp_wc_customer_lookup.* 
from
  wp_wc_customer_lookup into outfile '/var/lib/mysql-files/export-wp-wc-customer-lookup' CHARACTER 
SET
  utf8;

SQL

Once all queries are executed, you will have the following three files in /var/lib/mysql-files/ directory: export-customers, export-customers-meta and export-wp-wc-customer-lookup.

Export WooCommerce orders

In order to export orders (LOL) from WooCommerce, you also need to execute a couple of queries either in MYSQL-CLI or PHPMyAdmin:

select
  * 
from
  wp_posts 
where
  post_type like 'shop%' into outfile '/var/lib/mysql-files/export-posts' CHARACTER 
SET
  utf8;

SQL

select
  wp_postmeta.* 
from
  wp_postmeta 
  join
    wp_posts 
    on wp_postmeta.post_id = wp_posts.ID 
where
  wp_posts.post_type like 'shop%' into outfile '/var/lib/mysql-files/export-posts-meta' CHARACTER 
SET
  utf8;

SQL

select
  wp_woocommerce_order_items.* 
from
  wp_woocommerce_order_itemmeta 
  join
    wp_woocommerce_order_items 
    on wp_woocommerce_order_items.order_item_id = wp_woocommerce_order_itemmeta.order_item_id 
  join
    wp_posts 
    on wp_woocommerce_order_items.order_id = wp_posts.ID 
where
  wp_posts.post_type like 'shop%' into outfile '/var/lib/mysql-files/export-order-items' CHARACTER 
SET
  utf8;

SQL

select
  wp_woocommerce_order_itemmeta.* 
from
  wp_woocommerce_order_itemmeta 
  join
    wp_woocommerce_order_items 
    on wp_woocommerce_order_items.order_item_id = wp_woocommerce_order_itemmeta.order_item_id 
  join
    wp_posts 
    on wp_woocommerce_order_items.order_id = wp_posts.ID 
where
  wp_posts.post_type like 'shop%' into outfile '/var/lib/mysql-files/export-order-items-meta' CHARACTER 
SET
  utf8;

SQL

Once all queries are executed, you will have the following four files in /var/lib/mysql-files/ directory: export-posts, export-posts-meta, export-order-items and export-order-items-meta.

Import WooCommerce data

With all export files ready, now you can import the data to another WooCommerce-powered website. To start with, you can first sync all media items from /$docroot/wp-content/uploads. Once done, you can import the files created in the previous step. If both of your sites are on the same server (an old and a new one), you can do everything in place. If they are not, you will first have to copy the export files to a machine where you would like to import them.

Import WooCommerce users

Execute the following MYSQL queries to import WooCommerce users:

load data infile '/var/lib/mysql-files/export-customers' replace into table wp_users CHARACTER 
SET
  utf8;

SQL

load data infile '/var/lib/mysql-files/export-customers-meta' replace into table wp_usermeta CHARACTER 
SET
  utf8;

SQL

load data infile '/var/lib/mysql-files/export-wp-wc-customer-lookup' replace into table wp_wc_customer_lookup CHARACTER 
SET
  utf8;

SQL

Import WooCommerce orders

Execute the following MYSQL queries to import WooCommerce orders:

load data infile '/var/lib/mysql-files/export-posts' replace into table wp_posts CHARACTER 
SET
  utf8;

SQL

load data infile '/var/lib/mysql-files/export-posts-meta' replace into table wp_postmeta CHARACTER 
SET
  utf8;

SQL

load data infile '/var/lib/mysql-files/export-order-items' replace into table wp_woocommerce_order_items CHARACTER 
SET
  utf8;

SQL

load data infile '/var/lib/mysql-files/export-order-items-meta' replace into table wp_woocommerce_order_itemmeta CHARACTER 
SET
  utf8;

SQL

With this in place, you have successfully migrated customer and order WooCommerce data from one website to another. Thanks for reading!