At Universal Yums we have over 100GB of customer data in our database. This is too much to work with locally, but we still need to have a local database with settings, pages and posts from our site for doing development work.

Before updating to use High Performance Order Storage, it was very difficult to extract customer data since it was mixed in the posts and postmeta table. However, in HPOS the majority of customer is stored in dedicated tables which are easy to truncate or just skip in a database export.

In order to generate a fresh database for local use in HPOS, we first copy our production site to staging (WP Engine and most managed hosts offer this feature). Then we run a WP CLI script to truncate all the tables that contain customer data.

Here’s what this script looks like:

<?php
/**
 * This clears all customer data from the database.
 * This allows use to export a copy of the database that can be used locally.
 *
 * To run: wp eval-file freshdb.php
 */

if ( DB_NAME === 'proddb' ) {
	WP_CLI::error( '🙅 Uh, this is production!' );
}

/** @var \wpdb $wpdb */ // phpcs:ignore
global $wpdb;

WP_CLI::log( 'Clearing customer data from the database.' );

// Tables to truncate.
$tables = [
	// Action scheduler.
	$wpdb->prefix . 'actionscheduler_actions',
	$wpdb->prefix . 'actionscheduler_claims',
	$wpdb->prefix . 'actionscheduler_groups',
	$wpdb->prefix . 'actionscheduler_logs',

	// AutomateWoo.
	$wpdb->prefix . 'automatewoo_customers',
	$wpdb->prefix . 'automatewoo_events',
	$wpdb->prefix . 'automatewoo_guests',
	$wpdb->prefix . 'automatewoo_referrals',
	$wpdb->prefix . 'automatewoo_log_meta',
	$wpdb->prefix . 'automatewoo_logs',
	$wpdb->prefix . 'automatewoo_referral_advocate_keys',
	$wpdb->prefix . 'automatewoo_referral_invites',

	// Users.
	$wpdb->prefix . 'users',
	$wpdb->prefix . 'usermeta',

	// Order data.
	$wpdb->prefix . 'wc_orders',
	$wpdb->prefix . 'wc_orders_meta',
	$wpdb->prefix . 'wc_order_addresses',
	$wpdb->prefix . 'wc_order_operational_data',
	$wpdb->prefix . 'woocommerce_order_itemmeta',
	$wpdb->prefix . 'woocommerce_order_items',

	// Comments.
	$wpdb->prefix . 'comments',
	$wpdb->prefix . 'commentmeta',

	// WooCommerce and Subscriptions.
	$wpdb->prefix . 'wc_rate_limits',
	$wpdb->prefix . 'woocommerce_payment_tokenmeta',
	$wpdb->prefix . 'woocommerce_payment_tokens',
	$wpdb->prefix . 'woocommerce_sessions',
	$wpdb->prefix . 'wcs_payment_retries',

	// Plugins.
	$wpdb->prefix . 'taxjar_record_queue',
	$wpdb->prefix . 'gf_entry_meta',
	$wpdb->prefix . 'gf_entry',
	$wpdb->prefix . 'gf_entry_notes',
];

// Truncate listed tables.
WP_CLI::log( '' );
foreach ( $tables as $table ) {
	$result = $wpdb->query( "TRUNCATE TABLE {$table}" );
	if ( $result ) {
		WP_CLI::success( "Truncated: {$table}." );
	} else {
		WP_CLI::error( "Error: Could not truncate {$table}." );
	}
}

// Clean up posts table.
WP_CLI::log( '' );
WP_CLI::log( 'Cleaning up posts table.' );

// Clear payment retries.
$wpdb->delete( $wpdb->posts, [ 'post_type' => 'payment_retry' ] );
$records = $wpdb->rows_affected;
WP_CLI::success( "Posts: $records deleted." );

// Clean up postmeta table.
WP_CLI::log( '' );
WP_CLI::log( 'Cleaning up postmeta table.' );

$meta_keys        = [
	'_used_by',
	'_rule_email_template_customer',
	'_rule_retry_after_interval',
	'_rule_email_template_admin',
	'_rule_status_to_apply_to_order',
	'_rule_status_to_apply_to_subscription',
];
$meta_keys_string = implode( ',', $meta_keys );
$wpdb->query( "DELETE FROM $wpdb->postmeta WHERE meta_key IN ($meta_keys_string)" );
WP_CLI::success( 'Postmeta: data deleted.' );

WP_CLI::log( '' );
WP_CLI::success( 'Finished clearing customer data from the database.' );

WP_CLI::log( '' );
WP_CLI::log( 'To export the final database run:' );
WP_CLI::log( 'wp db export db.sql' );

Once the database is exported, we download it and import it locally.

wp db import db.sql

To update the domain for local, we run a search and replace with WP CLI:

wp search-replace example.com example.local

Then a new admin user can be created with WP CLI command:

wp user create username [email protected] --role=administrator --user_pass=password

This gives us a recent lean database copy that can be used locally without customer data.

Partial Syncs

We also have another script we use for partial syncs. In that script we just do a partial export of the production database with the following tables:

tables=(
  wp_woocommerce_shipping_table_rates
  wp_woocommerce_shipping_zone_locations
  wp_woocommerce_shipping_zone_methods
  wp_woocommerce_shipping_zone_shipping_methods
  wp_woocommerce_shipping_zones
  wp_woocommerce_tax_rate_locations
  wp_woocommerce_tax_rates
  wp_options
  wp_posts
  wp_postmeta
  wp_terms
  wp_term_relationships
)

And import those locally.