MySQL Clone Plugin: Live Database Provisioning for WordPress Staging

MySQL Clone Plugin: provision a staging database from production

The MySQL Clone Plugin (MySQL 8.0.17+) copies a running MySQL instance at the physical level: the InnoDB data files are streamed to another server while the source keeps working. For a database of tens of gigabytes this is much faster than mysqldump and a restore, which has to rebuild every table and index row by row. That makes it attractive for refreshing a staging server from production. It also has sharp edges that a dump does not have.

Problem: Refreshing the staging database of a large WooCommerce site with mysqldump | mysql takes hours, and the copy contains real customer data.

Solution: Install the Clone Plugin on both servers, run CLONE INSTANCE on the staging server, then anonymize personal data and change the site URLs before anyone uses the copy.

What a clone really does

A clone replaces the whole MySQL instance of the recipient, not one database: every schema on the staging server is deleted and replaced, including the mysql system schema with its users and passwords. After the clone, staging has the production users. Use it only on a MySQL server that is dedicated to this staging copy.

Requirements: the same MySQL version on both sides (the same release series; very old 8.0 releases require the exact same version), the same operating system and architecture, InnoDB tables (other engines are not copied), and enough disk space on the recipient.

Setup

On both servers:

INSTALL PLUGIN clone SONAME 'mysql_clone.so';
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM information_schema.PLUGINS WHERE PLUGIN_NAME = 'clone';

On production (the donor), a user that may be cloned from, reachable only from the staging server:

CREATE USER 'clone_donor'@'10.0.0.20' IDENTIFIED BY 'a-long-random-password' REQUIRE SSL;
GRANT BACKUP_ADMIN ON *.* TO 'clone_donor'@'10.0.0.20';

On staging (the recipient), the user that runs the clone needs CLONE_ADMIN, and the donor must be allowed:

SET GLOBAL clone_valid_donor_list = '10.0.0.10:3306';

Clone

On staging:

CLONE INSTANCE FROM 'clone_donor'@'10.0.0.10':3306
    IDENTIFIED BY 'a-long-random-password'
    REQUIRE SSL;

The data travels over the network, so REQUIRE SSL keeps it encrypted. When the transfer is done, the recipient restarts automatically (if it runs under a supervisor such as systemd) and comes up with the cloned data. Progress and errors:

SELECT STAGE, STATE, BEGIN_TIME, END_TIME FROM performance_schema.clone_progress;
SELECT STATE, ERROR_NO, ERROR_MESSAGE FROM performance_schema.clone_status;

Make it safe before use

The copy contains real customers. Anonymize it right after the clone, before giving anyone access. A starting point for a WooCommerce site (the wp_ prefix and the table set depend on your installation):

UPDATE wp_users
SET user_email = CONCAT( 'user', ID, '@example.invalid' ),
    display_name = CONCAT( 'User ', ID );

UPDATE wp_usermeta
SET meta_value = ''
WHERE meta_key IN ( 'first_name', 'last_name', 'billing_phone', 'billing_address_1', 'shipping_address_1' );

UPDATE wp_usermeta
SET meta_value = CONCAT( 'user', user_id, '@example.invalid' )
WHERE meta_key = 'billing_email';

-- Orders on HPOS
UPDATE wp_wc_orders SET billing_email = CONCAT( 'order', id, '@example.invalid' );
UPDATE wp_wc_order_addresses
SET first_name = 'Test', last_name = CONCAT( 'Customer ', order_id ), email = CONCAT( 'order', order_id, '@example.invalid' ),
    phone = '', address_1 = 'Test street 1', address_2 = '';

Then point the copy to the staging domain and stop it from contacting real people and services:

wp --path=/var/www/staging search-replace 'https://example.com' 'https://staging.example.com' --skip-columns=guid
wp --path=/var/www/staging user update 1 --user_pass="$(openssl rand -base64 18)"

Also block outgoing email on staging (a mail-catching SMTP plugin or pre_wp_mail filter), switch payment gateways to test mode, and disable webhooks to external systems.

NOTE: For most WordPress sites a dump is still the simpler and safer tool: it copies one database, works across MySQL versions and between MySQL and MariaDB, and can be anonymized on the way. Use the Clone Plugin when the database is so large that dumps take hours, and keep backups separate: a clone is not a backup, see backups with mysqldump and cron.