WooCommerce data migration without plugin ⋆ Zack's

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.

TABLE OF CONTENT

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%';

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;

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

If you're logged in on your server as a root user, be aware that the WP-CLI tool will not let you execute any commands without supplying the --allow-root parameter. Based on that, the commands in the example above would look like this:

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

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

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;

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;

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

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;

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;

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;

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;

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;

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

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

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;

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

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

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

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

Share if you like. Thank you in advance!

Hi. Thank you for putting this guide together! I used this as a basis to migrate my orders as this was exactly my scenario (cloned live site, build new site on clone, live site now has more orders and need to bring these across). I did it slightly differently to your process – only using phpmyadmin, and running imports of sql files via the Import tab, but for the most part have a successful migration of users and orders.

My problem is I have some posts on the dev site that have conflicting ids with orders from the live site. For example, in my dev site, post id 314814 in the wp_posts table references the new home page, but in the live site, that post id was allocated to an order that happened after we cloned the site. Do you know of a way / method to resolve this? essentially I need to allocate new ids to these orders and their associated ordermeta data … I am missing around 131 orders (out of 5250) as a result of this.

Any insight on how to resolve this would be much appreciated.

22/11/2023 Reply
Željko Jagušt

Hello Sian,

Thank you for your comment. From what I know, when importing any kind of data, the following happens. If the ID of “something” (in your case, order_ID) is specifically not a product ID, then yes, conflict can happen. To avoid it, I’d suggest one of the following:

What I usually do in a case such as yours, where I’m building a new version of a website, I first build the whole new site with some test data set. Once I’m sure everything is OK (and the customer is satisfied), I flush that test data and import all the required data from the “live” site.

In your specific case, I’d suggest you either compare all post_id’s on your dev and live site, find the conflicting IDs, and try and update them manually (prior to import), or that you write a custom import SQL query that will check existing post_id’s and perform the update during the data import (if it matches the post_id(s) in the import data file).
1. 22/11/2023 Reply
  Sian
  
  Hi Zeljko
  Thanks for the reply.
  Yes I will make a note of that the next time I have to do this (hindsight is a wonderful teacher hey?)
  I have identified the problem ids and am currently thinking about how to run the update on these specific records, as I will need to update the posts and post meta table accordingly for each.
  Thanks for the insight there too 🙂

31/07/2023 Reply

Tom

I ran your queries, but they do not work. SQL queries affect the appearance of your WooCommerce store. After adding products to the shopping cart, you cannot purchase them. M.in the store footer disappears, etc…. Orders are mixed up, not correctly assigned. The solution in me did not work.

02/08/2023 Reply
Željko Jagušt

Hello Tom,

I’m sorry to hear this didn’t work for you. If you did this on a production website, I hope you had a backup before executing any queries.

This worked for me on many WordPress instances, and I still use the same procedure when doing it, so I don’t know how to help you further without knowing more details about your setup. Just to mention, due to the complexity and possible “damage” queries could do, this is something one should first do in a development or stage environment first.

15/05/2023 Reply

Fabien

hi there, this is a wonderful solution… I created a whole new shop (new theme, largely new products) and am now looking to migrate legacy customer and order data.
I have tried countless import/export plugins but with 12,000 orders over almost a decade, they all get a timeout at some point (regardless of server spects). I have been able to export all successfully, and import customers successfully, however importing all four wp_posts, wp_postmeta, wp_woocommerce_order_items, and wp_woocommerce_order_itemmeta wrecks the shop (I’m guessing creating products that don’t exist any more???) and also breaks part of the theme. I’ve tried importing only wp_woocommerce_order_items and wp_woocommerce_order_itemmeta, but nothing shows in the admin…

Any idea how to import orders without touching any of the new product inventory and taking into consideration that the new site works on a new theme?

15/05/2023 Reply
Željko Jagušt

Hello Fabien,

Thank you for your comment. Unfortunately, the issue you’re having may be hard to resolve. To be more accurate, I don’t think it is possible. If your legacy data (orders) contain products you don’t have any more in the shop, then there is no “reference point” to which that data can be connected (order to product). So your legacy orders cannot reference products that were ordered simply because those products don’t exist anymore. But I would try the following:

01) Remove all new products from the new shop, leaving it blank
02) Import old products, set their stock to 0, and hide them from the catalog (“do not display products with 0 stock” option in WooCommerce)
03) Import legacy orders
05) Recreate new products

This way, you would have a reference point for your imported/legacy orders, but old products would be invisible in your new shop.

07/02/2023 Reply

Ngoni

Hi Marko, I was looking for SQL to export WooCommerce products from production to a test instance of WooCommerce. The built-in export to csv works fine, but importing the csv on the other end can take hours, with many timeouts. I have already synchronised the /wp-content/uploads folder (both servers are on a gigabit ethernet lan), but I really need a sequence of sql statements to export the data from prod, and a separate sequence of sql statements to recreate the data in the test server.

Your examples above export and import everything (orders, customers, etc.) but not the products themselves… which is the part that I need. Any help would be appreciated.

Regards,
Ngoni

12/02/2023 Reply
Željko Jagušt

Hello Ngoni,

Thank you for your comment, and I apologize for the late reply.

As stated near the beginning of this guide, we don’t have a MySQL procedure for product export/import because it can be done from within WooCommerce itself. If product import takes a long time for you, the most probable reason is (quite obviously) a large number of products. Since you said you synched the /wp-content/uploads folder, then you may be skipping one step when you are importing products. I presume (when importing products) you click on the Products tab in the main WP menu, and from there, you click on the Import button. From there, you can select your CSV file and click on the Continue button. Now, on the next screen, where you can define correct mappings, there is a field called Images, and if mapped, it will actually “pull” all images from your production site to a test one. Since you already synched the/wp-content/uploads folder, you can un-map the Images field by selecting the Do not Import option from the drop-down menu. This should substantially reduce the import time, but I do not guarantee the product images will be mapped correctly, so please feel free to test it out. I hope I was able to help, and if you have any other questions, please feel free to ask them.

Best regards

Home/Small Office – Introduction

Computers – Choosing the right one

Ubuntu Desktop 20.04 – Installation Guide

BBQ – A south-east European specialty

Thunderbird – Installation & Configuration

Customize Ubuntu 20.04 Desktop Environment

Ubuntu Desktop 20.04 – Recommended Apps

Filter email and spam with Thunderbird