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.
Begin an captivating journey to discover a world of adult content with our extensive array of top-tier adult sites. Dive into a world where fantasies know no bounds.
Savor a rich spectrum of themes, catering to every desire. From subtle romance to steamy encounters, our selections span the entire gamut of adult entertainment.
Investigate rare categories and uncover what truly turns you on. Our seamless interface makes it straightforward to locate your ultimate content.