A collection of database statemens to anomyze users within WordPress.

wordpress_anonymize_users.sql

--
-- Requirements:
-- * Users won't get any emails, when doing some test
-- * We get all emails
--

START TRANSACTION;

-- You may set this to the correct value, if you get error #1267
-- SET collation_connection = 'utf8_general_ci';

SET @user_to_ignore = 'XYZ';
SET @user_ids_to_start = 4;
SET @new_mail_suffix = '.domain-dev@some.catch.all';
SET @new_user_password = 'jJ3c9Yk4hh';

-- -------------------------------------
-- - WP Core
-- -------------------------------------

-- Replace email adresses with something else, so the users won't get any mails.
UPDATE wp_users SET `user_email` = CONCAT(user_login, @new_mail_suffix) WHERE ID > @user_ids_to_start AND user_login <> @user_to_ignore;

-- Replace the login to something unified, so we can use it.
UPDATE wp_users SET `user_login` = CONCAT('user_', id) WHERE ID > @user_ids_to_start AND user_login <> @user_to_ignore;

-- Set passwords so we can login
-- I recommend the plugin "User Switching" to switch users
UPDATE wp_users SET `user_pass` = md5(@new_user_password) WHERE ID > @user_ids_to_start AND user_login <> @user_to_ignore;

-- -------------------------------------
-- - Plugin: WooCommerce
-- -------------------------------------

UPDATE wp_postmeta SET meta_value = CONCAT('order_', post_id, @new_mail_suffix) WHERE meta_key IN ('_billing_email', 'Payer PayPal address', '_used_by', '_billing_address_index', '_direct_debit_mandate_mail');
UPDATE wp_usermeta SET meta_value = CONCAT('user_', user_id, @new_mail_suffix) WHERE meta_key IN ('billing_email');
-- -------------------------------------
-- - Plugin: wpShopGermany
-- -------------------------------------

UPDATE wp_wpsg_kunden SET `email` = concat('customer_', id, @new_mail_suffix);

-- -------------------------------------
-- - Plugin: Newsletter
-- -------------------------------------

UPDATE wp_newsletter SET `email` = concat('newsletter_', id, @new_mail_suffix);

-- -------------------------------------
--- Plugin: Tribulant Newsletters
-- -------------------------------

UPDATE wp_wpmlsubscribers SET `email` = concat('newsletter_', id, @new_mail_suffix);
UPDATE wp_wpmlunsubscribes SET `email` = concat('newsletter_', id, @new_mail_suffix);

COMMIT;