create or replace database wp_site3 character set utf8mb4 collate utf8mb4_unicode_520_ci;
create or replace database wp_site4 character set utf8mb4 collate utf8mb4_unicode_520_ci;
create or replace database wp_site5 character set utf8mb4 collate utf8mb4_unicode_520_ci;
create or replace database wp_site6 character set utf8mb4 collate utf8mb4_unicode_520_ci;
create or replace database wp_site7 character set utf8mb4 collate utf8mb4_unicode_520_ci;cat ~/code/local/multiple-singles-to-multisite-migration/mysqldump/wp_site5_wp_commentmeta.sql \
~/code/local/multiple-singles-to-multisite-migration/mysqldump/wp_site5_wp_comments.sql \
~/code/local/multiple-singles-to-multisite-migration/mysqldump/wp_site5_wp_links.sql \
~/code/local/multiple-singles-to-multisite-migration/mysqldump/wp_site5_wp_posts.sql \
~/code/local/multiple-singles-to-multisite-migration/mysqldump/wp_site5_wp_termmeta.sql \
~/code/local/multiple-singles-to-multisite-migration/mysqldump/wp_site5_wp_term_relationships.sql \
~/code/local/multiple-singles-to-multisite-migration/mysqldump/wp_site5_wp_terms.sql \
~/code/local/multiple-singles-to-multisite-migration/mysqldump/wp_site5_wp_term_taxonomy.sql \
| mysql -u root -proot wp_site5
cat ~/code/local/multiple-singles-to-multisite-migration/mysqldump/wp_site5_wp_options.sql \
~/code/local/multiple-singles-to-multisite-migration/mysqldump/wp_site5_wp_usermeta.sql \
~/code/local/multiple-singles-to-multisite-migration/mysqldump/wp_site5_wp_users.sql \
| mysql -u root -proot --binary-mode wp_site5
cat ~/code/local/multiple-singles-to-multisite-migration/mysqldump/wp_site3.sql \
| mysql -u root -proot wp_site3
cat ~/code/local/multiple-singles-to-multisite-migration/mysqldump/wp_site4.sql \
| mysql -u root -proot wp_site4
cat ~/code/local/multiple-singles-to-multisite-migration/mysqldump/wp_site6.sql \
| mysql -u root -proot wp_site6
cat ~/code/local/multiple-singles-to-multisite-migration/mysqldump/wp_site7.sql \
| mysql -u root -proot wp_site7
mysql -u root -proot wp_site3 < ~/code/local/multiple-singles-to-multisite-migration/mysqldump/wp_site3.sql & \
mysql -u root -proot wp_site4 < ~/code/local/multiple-singles-to-multisite-migration/mysqldump/wp_site4.sql & \
mysql -u root -proot wp_site5 < ~/code/local/multiple-singles-to-multisite-migration/mysqldump/wp_site5.sql & \
mysql -u root -proot wp_site6 < ~/code/local/multiple-singles-to-multisite-migration/mysqldump/wp_site6.sql & \
mysql -u root -proot wp_site7 < ~/code/local/multiple-singles-to-multisite-migration/mysqldump/wp_site7.sql & \
create or replace table wp_site5.wp_commentmeta like wp_site5_old.wp_commentmeta;
create or replace table wp_site5.wp_comments like wp_site5_old.wp_comments;
create or replace table wp_site5.wp_links like wp_site5_old.wp_links;
create or replace table wp_site5.wp_options like wp_site5_old.wp_options;
create or replace table wp_site5.wp_postmeta like wp_site5_old.wp_postmeta;
create or replace table wp_site5.wp_posts like wp_site5_old.wp_posts;
create or replace table wp_site5.wp_termmeta like wp_site5_old.wp_termmeta;
create or replace table wp_site5.wp_term_relationships like wp_site5_old.wp_term_relationships;
create or replace table wp_site5.wp_terms like wp_site5_old.wp_terms;
create or replace table wp_site5.wp_term_taxonomy like wp_site5_old.wp_term_taxonomy;
create or replace table wp_site5.wp_usermeta like wp_site5_old.wp_usermeta;
create or replace table wp_site5.wp_users like wp_site5_old.wp_users;
mysql -u root -proot wp_site5 < ~/code/local/multiple-singles-to-multisite-migration/mysqldump/wp_site5_wp_postmeta.sql & \
mysql -u root -proot wp_site5 < ~/code/local/multiple-singles-to-multisite-migration/mysqldump/wp_site5_wp_commentmeta.sql & \
mysql -u root -proot wp_site5 < ~/code/local/multiple-singles-to-multisite-migration/mysqldump/wp_site5_wp_comments.sql & \
mysql -u root -proot wp_site5 < ~/code/local/multiple-singles-to-multisite-migration/mysqldump/wp_site5_wp_links.sql & \
mysql -u root -proot wp_site5 < ~/code/local/multiple-singles-to-multisite-migration/mysqldump/wp_site5_wp_posts.sql & \
mysql -u root -proot wp_site5 < ~/code/local/multiple-singles-to-multisite-migration/mysqldump/wp_site5_wp_termmeta.sql & \
mysql -u root -proot wp_site5 < ~/code/local/multiple-singles-to-multisite-migration/mysqldump/wp_site5_wp_term_relationships.sql & \
mysql -u root -proot wp_site5 < ~/code/local/multiple-singles-to-multisite-migration/mysqldump/wp_site5_wp_terms.sql & \
mysql -u root -proot wp_site5 < ~/code/local/multiple-singles-to-multisite-migration/mysqldump/wp_site5_wp_term_taxonomy.sql & \
mysql -u root -proot --binary-mode wp_site5 < ~/code/local/multiple-singles-to-multisite-migration/mysqldump/wp_site5_wp_options.sql & \
mysql -u root -proot --binary-mode wp_site5 < ~/code/local/multiple-singles-to-multisite-migration/mysqldump/wp_site5_wp_usermeta.sql & \
mysql -u root -proot --binary-mode wp_site5 < ~/code/local/multiple-singles-to-multisite-migration/mysqldump/wp_site5_wp_users.sql &
For union queries to work, all tables must be using the same collation. Note this is a collation change only, as all 5 databases are already using utf8mb4 encoding. Therefore there is no data integrity risk in this operation.
Query-ception: This query generates the alter table queries for all customer tables:
select concat(
'alter table ',
table_schema,
'.',
table_name,
' convert to character set utf8mb4 collate utf8mb4_unicode_520_ci;'
)
from information_schema.tables where table_schema like 'site%';Updates table prefix from wp_site6_ to wp_, which is the default. The other sites are using wp_.
rename table wp_site6.wp_site6_commentmeta to wp_site6.wp_commentmeta,
wp_site6.wp_site6_comments to wp_site6.wp_comments,
wp_site6.wp_site6_links to wp_site6.wp_links,
wp_site6.wp_site6_options to wp_site6.wp_options,
wp_site6.wp_site6_postmeta to wp_site6.wp_postmeta,
wp_site6.wp_site6_posts to wp_site6.wp_posts,
wp_site6.wp_site6_term_relationships to wp_site6.wp_term_relationships,
wp_site6.wp_site6_term_taxonomy to wp_site6.wp_term_taxonomy,
wp_site6.wp_site6_termmeta to wp_site6.wp_termmeta,
wp_site6.wp_site6_terms to wp_site6.wp_terms,
wp_site6.wp_site6_usermeta to wp_site6.wp_usermeta,
wp_site6.wp_site6_users to wp_site6.wp_users;
update wp_site6.wp_options set option_name = 'wp_user_roles' where option_name = 'wp_site6_user_roles';
update wp_site6.wp_usermeta set meta_key = replace(meta_key, 'wp_site6_', 'wp_');-- customerName.wp_blogs definition
update customerName.wp_site
set domain = 'site1.com';
where id = 1;
update customerName.wp_sitemeta
set meta_value = 'https://site1.com/'
where meta_key = 'siteurl';
create or replace table customerName.wp_blogs (
blog_id bigint auto_increment,
site_id bigint default 0 not null,
domain varchar(200) default '' not null,
path varchar(100) default '' not null,
registered datetime default '0000-00-00 00:00:00' not null,
last_updated datetime default '0000-00-00 00:00:00' not null,
public tinyint(2) default 1 not null,
archived tinyint(2) default 0 not null,
mature tinyint(2) default 0 not null,
spam tinyint(2) default 0 not null,
deleted tinyint(2) default 0 not null,
lang_id int default 0 not null,
primary key (blog_id),
key domain (domain(50),path(5)),
key lang_id (lang_id)
);
insert into customerName.wp_blogs
(blog_id, site_id, domain, path, registered)
values
(1, 1, 'site1.com', '/', now()),
(2, 1, 'site2.com', '/', now()),
(3, 1, 'site3.com', '/', now()),
(4, 1, 'site4.com', '/', now()),
(5, 1, 'site5.com', '/', now()),
(6, 1, 'site6.com', '/', now()),
(7, 1, 'site7.com', '/', now());
create or replace table customerName.wp_2_commentmeta like customerName.wp_commentmeta;
create or replace table customerName.wp_2_comments like customerName.wp_comments;
create or replace table customerName.wp_2_links like customerName.wp_links;
create or replace table customerName.wp_2_options like customerName.wp_options;
create or replace table customerName.wp_2_postmeta like customerName.wp_postmeta;
create or replace table customerName.wp_2_posts like customerName.wp_posts;
create or replace table customerName.wp_2_term_relationships like customerName.wp_term_relationships;
create or replace table customerName.wp_2_term_taxonomy like customerName.wp_term_taxonomy;
create or replace table customerName.wp_2_termmeta like customerName.wp_termmeta;
create or replace table customerName.wp_2_terms like customerName.wp_terms;
create or replace table customerName.wp_commentmeta like customerName.wp_2_commentmeta;
create or replace table customerName.wp_comments like customerName.wp_2_comments;
create or replace table customerName.wp_links like customerName.wp_2_links;
create or replace table customerName.wp_options like customerName.wp_2_options;
create or replace table customerName.wp_postmeta like customerName.wp_2_postmeta;
create or replace table customerName.wp_posts like customerName.wp_2_posts;
create or replace table customerName.wp_term_relationships like customerName.wp_2_term_relationships;
create or replace table customerName.wp_term_taxonomy like customerName.wp_2_term_taxonomy;
create or replace table customerName.wp_termmeta like customerName.wp_2_termmeta;
create or replace table customerName.wp_terms like customerName.wp_2_terms;create or replace table customerName.wp_vip_migration_users (
source varchar(20) default '' not null,
old_id bigint unsigned default 0 not null,
new_id bigint unsigned default 0 not null,
user_login varchar(60) default '' not null,
user_pass varchar(255) default '' not null,
user_nicename varchar(50) default '' not null,
user_email varchar(100) default '' not null,
user_url varchar(100) default '' not null,
user_registered datetime default '0000-00-00 00:00:00' not null,
user_activation_key varchar(255) default '' not null,
user_status int default 0 not null,
display_name varchar(250) default '' not null,
primary key (old_id, source),
key new_id (new_id),
key display_name (display_name)
);
insert into customerName.wp_vip_migration_users ( source, old_id, user_login, user_nicename, user_email, user_url, user_registered, display_name )
select 'site3' as source, ID as old_id, user_login, user_nicename, user_email, user_url, user_registered, display_name from wp_site3.wp_users
union select 'site4' as source, ID as old_id, user_login, user_nicename, user_email, user_url, user_registered, display_name from wp_site4.wp_users
union select 'site5' as source, ID as old_id, user_login, user_nicename, user_email, user_url, user_registered, display_name from wp_site5.wp_users
union select 'site6' as source, ID as old_id, user_login, user_nicename, user_email, user_url, user_registered, display_name from wp_site6.wp_users
union select 'site7' as source, ID as old_id, user_login, user_nicename, user_email, user_url, user_registered, display_name from wp_site7.wp_users;create or replace table customerName.wp_users (
ID bigint unsigned auto_increment,
user_login varchar(60) default '' not null,
user_pass varchar(255) default '' not null,
user_nicename varchar(50) default '' not null,
user_email varchar(100) default '' not null,
user_url varchar(100) default '' not null,
user_registered datetime default '0000-00-00 00:00:00' not null,
user_activation_key varchar(255) default '' not null,
user_status int default 0 not null,
display_name varchar(250) default '' not null,
spam tinyint(2) default 0 not null,
deleted tinyint(2) default 0 not null,
primary key (ID),
key user_login_key (user_login),
key user_nicename (user_nicename),
key user_email (user_email)
);The wp_vip_migration_users table contains duplicate users from across the individual site databases. We want to keep reference data intact, but our wp_users table should not have any duplicate users.
The following query excludes duplicates, using the display_name field for comparison. Thankfully, the display_name field was already consistently the same between the duplicate users. The query will use the latest user_registered value to decide which duplicate "wins".
SQL Explanation: See https://stackoverflow.com/a/26124759
insert into customerName.wp_users (user_login, user_nicename, user_email, user_url, user_registered, display_name)
select t1.user_login, t1.user_nicename, t1.user_email, t1.user_url, t1.user_registered, t1.display_name
from customerName.wp_vip_migration_users as t1
left join customerName.wp_vip_migration_users as t2
on t1.display_name = t2.display_name
and t1.user_registered < t2.user_registered
where t2.old_id is null
order by t1.user_registered;
-- For testing the above:
-- select t1.*
-- from customerName.wp_vip_migration_users as t1
-- left join customerName.wp_vip_migration_users as t2
-- on t1.display_name = t2.display_name
-- and t1.user_registered < t2.user_registered
-- where t2.id is null
-- order by display_name;update customerName.wp_vip_migration_users
join customerName.wp_users
on wp_vip_migration_users.display_name = wp_users.display_name
set wp_vip_migration_users.new_id = wp_users.ID;
-- For testing the above:
-- select _u.old_id, _u.new_id, u.ID, u.display_name as u_display_name, _u.display_name as _u_display_name
-- from customerName.wp_vip_migration_users _u
-- join customerName.wp_users u
-- on u.display_name = _u.display_name;create or replace table customerName.wp_vip_migration_usermeta (
id bigint unsigned auto_increment,
source varchar(20) default '' not null,
old_user_id bigint unsigned default 0 not null,
new_user_id bigint unsigned default 0 not null,
meta_key varchar(255) null,
meta_value longtext null,
primary key (id),
key old_user_id (old_user_id),
key new_user_id (new_user_id)
);
insert into customerName.wp_vip_migration_usermeta ( source, old_user_id, meta_key, meta_value )
select 'site3' as source, user_id as old_user_id, meta_key, meta_value from wp_site3.wp_usermeta
union select 'site4' as source, user_id as old_user_id, meta_key, meta_value from wp_site4.wp_usermeta
union select 'site5' as source, user_id as old_user_id, meta_key, meta_value from wp_site5.wp_usermeta
union select 'site6' as source, user_id as old_user_id, meta_key, meta_value from wp_site6.wp_usermeta
union select 'site7' as source, user_id as old_user_id, meta_key, meta_value from wp_site7.wp_usermeta;-- Cleanup rows with empty meta_values
delete from customerName.wp_vip_migration_usermeta where meta_value = '';
-- Cleanup unneeded meta keys
delete from customerName.wp_vip_migration_usermeta where meta_key in (
'_two_factor_backup_codes',
'_two_factor_email_token',
'_two_factor_email_token_timestamp',
'_two_factor_enabled_providers',
'_two_factor_fido_u2f_register_request',
'_two_factor_nonce',
'_two_factor_provider',
'_two_factor_totp_key',
'admin_color',
'apple_news_notice',
'as3cf_dismissed_notices',
'autodescription-user-settings',
'comment_shortcuts',
'community-events-location',
'dark_mode',
'dismissed_wp_pointers',
'enable_custom_fields',
'manageedit-postcolumnshidden',
'manageedit-videocolumnshidden',
'managenav-menuscolumnshidden',
'nav_menu_recently_edited',
'pending',
'rich_editing',
'session_tokens',
'show_admin_bar_front',
'show_try_gutenberg_panel',
'show_welcome_panel',
'syntax_highlighting',
'upload_per_page',
'use_ssl',
'users_per_page',
'wp_dashboard_quick_press_last_post_id',
'wp_media_library_mode',
'wp_metaslider_optin_notice_dismissed',
'wp_metaslider_user_saw_callout_gallery',
'wp_user-settings',
'wp_user-settings-time',
'wp_user_avatar',
'wp_yoast_notifications',
'wpseo-remove-upsell-notice',
'wpseo_ignore_tour'
);
delete from customerName.wp_vip_migration_usermeta
where meta_key regexp('^(closedpostboxes_|edit_.+_per\_page|meta-box-order_|metaboxhidden_|screen_layout_)(.*)$');
-- For testing the above:
-- select meta_key from customerName.wp_vip_migration_usermeta
-- where meta_key regexp('^(closedpostboxes_|edit_.+_per\_page|meta-box-order_|metaboxhidden_|screen_layout_)(.*)$')
-- group by meta_key;update customerName.wp_vip_migration_usermeta
set meta_key = case
when source = 'site3' then 'wp_3_capabilities'
when source = 'site4' then 'wp_4_capabilities'
when source = 'site5' then 'wp_5_capabilities'
when source = 'site6' then 'wp_6_capabilities'
when source = 'site7' then 'wp_7_capabilities'
end
where meta_key = 'wp_capabilities';
update customerName.wp_vip_migration_usermeta
set meta_key = case
when source = 'site3' then 'wp_3_user_level'
when source = 'site4' then 'wp_4_user_level'
when source = 'site5' then 'wp_5_user_level'
when source = 'site6' then 'wp_6_user_level'
when source = 'site7' then 'wp_7_user_level'
end
where meta_key = 'wp_user_level';Update wp_vip_migration_usermeta.new_user_id using the source/old_user_id/new_user_id fields from wp_vip_migration_users
update customerName.wp_vip_migration_usermeta
join customerName.wp_vip_migration_users
on wp_vip_migration_usermeta.old_user_id = wp_vip_migration_users.old_id
and wp_vip_migration_usermeta.source = wp_vip_migration_users.source
set wp_vip_migration_usermeta.new_user_id = wp_vip_migration_users.new_id;
-- Delete orphaned user meta (user ID 1 on site3 had 3 rows of user meta, but user_id didn't exist)
delete from customerName.wp_vip_migration_usermeta
where new_user_id = 0;create or replace table customerName.wp_usermeta (
umeta_id bigint unsigned auto_increment,
user_id bigint unsigned default 0 not null,
meta_key varchar(255) null,
meta_value longtext null,
primary key (umeta_id),
key user_id (user_id),
key meta_key (meta_key(191))
);Using the same technique as with wp_users, this query will populate the wp_usermeta table while also doing some work for us.
The following query excludes duplicate rows, based on combined values of new_user_id, meta_key, and meta_value.
insert into customerName.wp_usermeta (user_id, meta_key, meta_value)
select t1.new_user_id as user_id, t1.meta_key, t1.meta_value
from customerName.wp_vip_migration_usermeta as t1
left join customerName.wp_vip_migration_usermeta as t2
on t1.new_user_id = t2.new_user_id
and t1.meta_key = t2.meta_key
and t1.id > t2.id
where t2.source is null
order by t1.new_user_id, t1.meta_key;create or replace table customerName.wp_3_posts like customerName.wp_posts;
create or replace table customerName.wp_4_posts like customerName.wp_posts;
create or replace table customerName.wp_5_posts like customerName.wp_posts;
create or replace table customerName.wp_6_posts like customerName.wp_posts;
create or replace table customerName.wp_7_posts like customerName.wp_posts;
create or replace table customerName.wp_3_postmeta like customerName.wp_postmeta;
create or replace table customerName.wp_4_postmeta like customerName.wp_postmeta;
create or replace table customerName.wp_5_postmeta like customerName.wp_postmeta;
create or replace table customerName.wp_6_postmeta like customerName.wp_postmeta;
create or replace table customerName.wp_7_postmeta like customerName.wp_postmeta;
insert into customerName.wp_3_posts select * from wp_site3.wp_posts;
insert into customerName.wp_4_posts select * from wp_site4.wp_posts;
insert into customerName.wp_5_posts select * from wp_site5.wp_posts;
insert into customerName.wp_6_posts select * from wp_site6.wp_posts;
insert into customerName.wp_7_posts select * from wp_site7.wp_posts;
insert into customerName.wp_3_postmeta select * from wp_site3.wp_postmeta;
insert into customerName.wp_4_postmeta select * from wp_site4.wp_postmeta;
insert into customerName.wp_5_postmeta select * from wp_site5.wp_postmeta;
insert into customerName.wp_6_postmeta select * from wp_site6.wp_postmeta;
insert into customerName.wp_7_postmeta select * from wp_site7.wp_postmeta;
update customerName.wp_3_posts left join customerName.wp_vip_migration_users
on wp_vip_migration_users.source = 'site3' and wp_vip_migration_users.old_id = wp_3_posts.post_author
set wp_3_posts.post_author = if ( wp_vip_migration_users.new_id is not null, wp_vip_migration_users.new_id, wp_3_posts.post_author );
update customerName.wp_4_posts left join customerName.wp_vip_migration_users
on wp_vip_migration_users.source = 'site4' and wp_vip_migration_users.old_id = wp_4_posts.post_author
set wp_4_posts.post_author = if ( wp_vip_migration_users.new_id is not null, wp_vip_migration_users.new_id, wp_4_posts.post_author );
update customerName.wp_5_posts left join customerName.wp_vip_migration_users
on wp_vip_migration_users.source = 'site5' and wp_vip_migration_users.old_id = wp_5_posts.post_author
set wp_5_posts.post_author = if ( wp_vip_migration_users.new_id is not null, wp_vip_migration_users.new_id, wp_5_posts.post_author );
update customerName.wp_6_posts left join customerName.wp_vip_migration_users
on wp_vip_migration_users.source = 'site6' and wp_vip_migration_users.old_id = wp_6_posts.post_author
set wp_6_posts.post_author = if ( wp_vip_migration_users.new_id is not null, wp_vip_migration_users.new_id, wp_6_posts.post_author );
update customerName.wp_7_posts left join customerName.wp_vip_migration_users
on wp_vip_migration_users.source = 'site7' and wp_vip_migration_users.old_id = wp_7_posts.post_author
set wp_7_posts.post_author = if ( wp_vip_migration_users.new_id is not null, wp_vip_migration_users.new_id, wp_7_posts.post_author );
--- For testing the above:
-- select wp_vip_migration_users.source, wp_vip_migration_users.old_id, wp_vip_migration_users.new_id, wp_3_posts.ID, wp_3_posts.post_author, wp_3_posts.post_name, wp_3_posts.post_type from customerName.wp_3_posts
-- left join customerName.wp_vip_migration_users
-- on wp_vip_migration_users.source = 'site3'
-- and wp_vip_migration_users.old_id = wp_3_posts.post_author;create or replace table customerName.wp_3_commentmeta like wp_site3.wp_commentmeta;
create or replace table customerName.wp_4_commentmeta like wp_site4.wp_commentmeta;
create or replace table customerName.wp_5_commentmeta like wp_site5.wp_commentmeta;
create or replace table customerName.wp_6_commentmeta like wp_site6.wp_commentmeta;
create or replace table customerName.wp_7_commentmeta like wp_site7.wp_commentmeta;
create or replace table customerName.wp_3_comments like wp_site3.wp_comments;
create or replace table customerName.wp_4_comments like wp_site4.wp_comments;
create or replace table customerName.wp_5_comments like wp_site5.wp_comments;
create or replace table customerName.wp_6_comments like wp_site6.wp_comments;
create or replace table customerName.wp_7_comments like wp_site7.wp_comments;
create or replace table customerName.wp_3_links like wp_site3.wp_links;
create or replace table customerName.wp_4_links like wp_site4.wp_links;
create or replace table customerName.wp_5_links like wp_site5.wp_links;
create or replace table customerName.wp_6_links like wp_site6.wp_links;
create or replace table customerName.wp_7_links like wp_site7.wp_links;
create or replace table customerName.wp_3_options like wp_site3.wp_options;
create or replace table customerName.wp_4_options like wp_site4.wp_options;
create or replace table customerName.wp_5_options like wp_site5.wp_options;
create or replace table customerName.wp_6_options like wp_site6.wp_options;
create or replace table customerName.wp_7_options like wp_site7.wp_options;
create or replace table customerName.wp_3_term_relationships like wp_site3.wp_term_relationships;
create or replace table customerName.wp_4_term_relationships like wp_site4.wp_term_relationships;
create or replace table customerName.wp_5_term_relationships like wp_site5.wp_term_relationships;
create or replace table customerName.wp_6_term_relationships like wp_site6.wp_term_relationships;
create or replace table customerName.wp_7_term_relationships like wp_site7.wp_term_relationships;
create or replace table customerName.wp_3_term_taxonomy like wp_site3.wp_term_taxonomy;
create or replace table customerName.wp_4_term_taxonomy like wp_site4.wp_term_taxonomy;
create or replace table customerName.wp_5_term_taxonomy like wp_site5.wp_term_taxonomy;
create or replace table customerName.wp_6_term_taxonomy like wp_site6.wp_term_taxonomy;
create or replace table customerName.wp_7_term_taxonomy like wp_site7.wp_term_taxonomy;
create or replace table customerName.wp_3_termmeta like wp_site3.wp_termmeta;
create or replace table customerName.wp_4_termmeta like wp_site4.wp_termmeta;
create or replace table customerName.wp_5_termmeta like wp_site5.wp_termmeta;
create or replace table customerName.wp_6_termmeta like wp_site6.wp_termmeta;
create or replace table customerName.wp_7_termmeta like wp_site7.wp_termmeta;
create or replace table customerName.wp_3_terms like wp_site3.wp_terms;
create or replace table customerName.wp_4_terms like wp_site4.wp_terms;
create or replace table customerName.wp_5_terms like wp_site5.wp_terms;
create or replace table customerName.wp_6_terms like wp_site6.wp_terms;
create or replace table customerName.wp_7_terms like wp_site7.wp_terms;
insert into customerName.wp_3_commentmeta select * from wp_site3.wp_commentmeta;
insert into customerName.wp_4_commentmeta select * from wp_site4.wp_commentmeta;
insert into customerName.wp_5_commentmeta select * from wp_site5.wp_commentmeta;
insert into customerName.wp_6_commentmeta select * from wp_site6.wp_commentmeta;
insert into customerName.wp_7_commentmeta select * from wp_site7.wp_commentmeta;
insert into customerName.wp_3_comments select * from wp_site3.wp_comments;
insert into customerName.wp_4_comments select * from wp_site4.wp_comments;
insert into customerName.wp_5_comments select * from wp_site5.wp_comments;
insert into customerName.wp_6_comments select * from wp_site6.wp_comments;
insert into customerName.wp_7_comments select * from wp_site7.wp_comments;
insert into customerName.wp_3_links select * from wp_site3.wp_links;
insert into customerName.wp_4_links select * from wp_site4.wp_links;
insert into customerName.wp_5_links select * from wp_site5.wp_links;
insert into customerName.wp_6_links select * from wp_site6.wp_links;
insert into customerName.wp_7_links select * from wp_site7.wp_links;
insert into customerName.wp_3_options select * from wp_site3.wp_options;
insert into customerName.wp_4_options select * from wp_site4.wp_options;
insert into customerName.wp_5_options select * from wp_site5.wp_options;
insert into customerName.wp_6_options select * from wp_site6.wp_options;
insert into customerName.wp_7_options select * from wp_site7.wp_options;
insert into customerName.wp_3_term_relationships select * from wp_site3.wp_term_relationships;
insert into customerName.wp_4_term_relationships select * from wp_site4.wp_term_relationships;
insert into customerName.wp_5_term_relationships select * from wp_site5.wp_term_relationships;
insert into customerName.wp_6_term_relationships select * from wp_site6.wp_term_relationships;
insert into customerName.wp_7_term_relationships select * from wp_site7.wp_term_relationships;
insert into customerName.wp_3_term_taxonomy select * from wp_site3.wp_term_taxonomy;
insert into customerName.wp_4_term_taxonomy select * from wp_site4.wp_term_taxonomy;
insert into customerName.wp_5_term_taxonomy select * from wp_site5.wp_term_taxonomy;
insert into customerName.wp_6_term_taxonomy select * from wp_site6.wp_term_taxonomy;
insert into customerName.wp_7_term_taxonomy select * from wp_site7.wp_term_taxonomy;
insert into customerName.wp_3_termmeta select * from wp_site3.wp_termmeta;
insert into customerName.wp_4_termmeta select * from wp_site4.wp_termmeta;
insert into customerName.wp_5_termmeta select * from wp_site5.wp_termmeta;
insert into customerName.wp_6_termmeta select * from wp_site6.wp_termmeta;
insert into customerName.wp_7_termmeta select * from wp_site7.wp_termmeta;
insert into customerName.wp_3_terms select * from wp_site3.wp_terms;
insert into customerName.wp_4_terms select * from wp_site4.wp_terms;
insert into customerName.wp_5_terms select * from wp_site5.wp_terms;
insert into customerName.wp_6_terms select * from wp_site6.wp_terms;
insert into customerName.wp_7_terms select * from wp_site7.wp_terms;update customerName.wp_3_terms terms
join customerName.wp_3_term_taxonomy termtax
join customerName.wp_vip_migration_users old_users
join customerName.wp_users new_users
on terms.term_id = termtax.term_id
and termtax.taxonomy = 'author'
and termtax.description like concat('% ', old_users.old_id, ' %')
and old_users.new_id = new_users.ID
and old_users.source = 'site3'
set
terms.name = new_users.user_login,
terms.slug = concat( 'cap-', new_users.user_nicename )
where 1=1;
update customerName.wp_3_term_taxonomy termtax
join customerName.wp_3_terms terms
join customerName.wp_vip_migration_users old_users
join customerName.wp_users new_users
on terms.term_id = termtax.term_id
and termtax.taxonomy = 'author'
and termtax.description like concat('% ', old_users.old_id, ' %')
and old_users.new_id = new_users.ID
and old_users.source = 'site3'
set
termtax.description = concat(
new_users.display_name, ' ',
coalesce(concat((select meta_value from wp_usermeta where user_id = new_users.ID and meta_key = 'first_name'), ' '), ''),
coalesce(concat((select meta_value from wp_usermeta where user_id = new_users.ID and meta_key = 'last_name'), ' '), ''),
coalesce(concat(new_users.user_login, ' '), ''),
new_users.ID, ' ', new_users.user_email
)
where 1=1;
update customerName.wp_4_terms terms
join customerName.wp_4_term_taxonomy termtax
join customerName.wp_vip_migration_users old_users
join customerName.wp_users new_users
on terms.term_id = termtax.term_id
and termtax.taxonomy = 'author'
and termtax.description like concat('% ', old_users.old_id, ' %')
and old_users.new_id = new_users.ID
and old_users.source = 'site4'
set
terms.name = new_users.user_login,
terms.slug = concat( 'cap-', new_users.user_nicename )
where 1=1;
update customerName.wp_4_term_taxonomy termtax
join customerName.wp_4_terms terms
join customerName.wp_vip_migration_users old_users
join customerName.wp_users new_users
on terms.term_id = termtax.term_id
and termtax.taxonomy = 'author'
and termtax.description like concat('% ', old_users.old_id, ' %')
and old_users.new_id = new_users.ID
and old_users.source = 'site4'
set
termtax.description = concat(
new_users.display_name, ' ',
coalesce(concat((select meta_value from wp_usermeta where user_id = new_users.ID and meta_key = 'first_name'), ' '), ''),
coalesce(concat((select meta_value from wp_usermeta where user_id = new_users.ID and meta_key = 'last_name'), ' '), ''),
coalesce(concat(new_users.user_login, ' '), ''),
new_users.ID, ' ', new_users.user_email
)
where 1=1;
update customerName.wp_5_terms terms
join customerName.wp_5_term_taxonomy termtax
join customerName.wp_vip_migration_users old_users
join customerName.wp_users new_users
on terms.term_id = termtax.term_id
and termtax.taxonomy = 'author'
and termtax.description like concat('% ', old_users.old_id, ' %')
and old_users.new_id = new_users.ID
and old_users.source = 'site5'
set
terms.name = new_users.user_login,
terms.slug = concat( 'cap-', new_users.user_nicename )
where 1=1;
update customerName.wp_5_term_taxonomy termtax
join customerName.wp_5_terms terms
join customerName.wp_vip_migration_users old_users
join customerName.wp_users new_users
on terms.term_id = termtax.term_id
and termtax.taxonomy = 'author'
and termtax.description like concat('% ', old_users.old_id, ' %')
and old_users.new_id = new_users.ID
and old_users.source = 'site5'
set
termtax.description = concat(
new_users.display_name, ' ',
coalesce(concat((select meta_value from wp_usermeta where user_id = new_users.ID and meta_key = 'first_name'), ' '), ''),
coalesce(concat((select meta_value from wp_usermeta where user_id = new_users.ID and meta_key = 'last_name'), ' '), ''),
coalesce(concat(new_users.user_login, ' '), ''),
new_users.ID, ' ', new_users.user_email
)
where 1=1;
update customerName.wp_6_terms terms
join customerName.wp_6_term_taxonomy termtax
join customerName.wp_vip_migration_users old_users
join customerName.wp_users new_users
on terms.term_id = termtax.term_id
and termtax.taxonomy = 'author'
and termtax.description like concat('% ', old_users.old_id, ' %')
and old_users.new_id = new_users.ID
and old_users.source = 'site6'
set
terms.name = new_users.user_login,
terms.slug = concat( 'cap-', new_users.user_nicename )
where 1=1;
update customerName.wp_6_term_taxonomy termtax
join customerName.wp_6_terms terms
join customerName.wp_vip_migration_users old_users
join customerName.wp_users new_users
on terms.term_id = termtax.term_id
and termtax.taxonomy = 'author'
and termtax.description like concat('% ', old_users.old_id, ' %')
and old_users.new_id = new_users.ID
and old_users.source = 'site6'
set
termtax.description = concat(
new_users.display_name, ' ',
coalesce(concat((select meta_value from wp_usermeta where user_id = new_users.ID and meta_key = 'first_name'), ' '), ''),
coalesce(concat((select meta_value from wp_usermeta where user_id = new_users.ID and meta_key = 'last_name'), ' '), ''),
coalesce(concat(new_users.user_login, ' '), ''),
new_users.ID, ' ', new_users.user_email
)
where 1=1;
update customerName.wp_7_terms terms
join customerName.wp_7_term_taxonomy termtax
join customerName.wp_vip_migration_users old_users
join customerName.wp_users new_users
on terms.term_id = termtax.term_id
and termtax.taxonomy = 'author'
and termtax.description like concat('% ', old_users.old_id, ' %')
and old_users.new_id = new_users.ID
and old_users.source = 'site7'
set
terms.name = new_users.user_login,
terms.slug = concat( 'cap-', new_users.user_nicename )
where 1=1;
update customerName.wp_7_term_taxonomy termtax
join customerName.wp_7_terms terms
join customerName.wp_vip_migration_users old_users
join customerName.wp_users new_users
on terms.term_id = termtax.term_id
and termtax.taxonomy = 'author'
and termtax.description like concat('% ', old_users.old_id, ' %')
and old_users.new_id = new_users.ID
and old_users.source = 'site7'
set
termtax.description = concat(
new_users.display_name, ' ',
coalesce(concat((select meta_value from wp_usermeta where user_id = new_users.ID and meta_key = 'first_name'), ' '), ''),
coalesce(concat((select meta_value from wp_usermeta where user_id = new_users.ID and meta_key = 'last_name'), ' '), ''),
coalesce(concat(new_users.user_login, ' '), ''),
new_users.ID, ' ', new_users.user_email
)
where 1=1;
--- For testing the above:
-- select termtax.term_id, termtax.description,
-- concat(
-- new_users.display_name,
-- ' ',
-- (select meta_value from wp_usermeta where user_id = new_users.ID and meta_key = 'first_name'),
-- ' ',
-- (select meta_value from wp_usermeta where user_id = new_users.ID and meta_key = 'last_name'),
-- ' ',
-- new_users.user_login,
-- ' ',
-- new_users.ID,
-- ' ',
-- new_users.user_email
-- ) as new_description,
-- new_users.user_login as new_name,
-- concat( 'cap-', new_users.user_nicename ) as new_slug
-- from customerName.wp_3_term_taxonomy termtax
-- join customerName.wp_3_terms terms
-- join customerName.wp_vip_migration_users old_users
-- join customerName.wp_users new_users
-- on terms.term_id = termtax.term_id
-- and termtax.taxonomy = 'author'
-- and termtax.description like concat('% ', old_users.new_id, ' %')
-- and old_users.new_id = new_users.ID
-- and old_users.source = 'site3';delete from customerName.wp_options where option_name like '%\_transient\_%';
delete from customerName.wp_2_options where option_name like '%\_transient\_%';
delete from customerName.wp_3_options where option_name like '%\_transient\_%';
delete from customerName.wp_4_options where option_name like '%\_transient\_%';
delete from customerName.wp_5_options where option_name like '%\_transient\_%';
delete from customerName.wp_6_options where option_name like '%\_transient\_%';
delete from customerName.wp_7_options where option_name like '%\_transient\_%';
delete from customerName.wp_comments where comment_type = 'pingback';
delete from customerName.wp_2_comments where comment_type = 'pingback';
delete from customerName.wp_3_comments where comment_type = 'pingback';
delete from customerName.wp_4_comments where comment_type = 'pingback';
delete from customerName.wp_5_comments where comment_type = 'pingback';
delete from customerName.wp_6_comments where comment_type = 'pingback';
delete from customerName.wp_7_comments where comment_type = 'pingback';
delete from customerName.wp_postmeta where meta_key IN ('amazonS3_info', 'amazonS3_cache');
delete from customerName.wp_2_postmeta where meta_key IN ('amazonS3_info', 'amazonS3_cache');
delete from customerName.wp_3_postmeta where meta_key IN ('amazonS3_info', 'amazonS3_cache');
delete from customerName.wp_4_postmeta where meta_key IN ('amazonS3_info', 'amazonS3_cache');
delete from customerName.wp_5_postmeta where meta_key IN ('amazonS3_info', 'amazonS3_cache');
delete from customerName.wp_6_postmeta where meta_key IN ('amazonS3_info', 'amazonS3_cache');
delete from customerName.wp_7_postmeta where meta_key IN ('amazonS3_info', 'amazonS3_cache');
--- Yoast will get reindexed
drop table if exists
customerName.wp_yoast_indexable,
customerName.wp_yoast_indexable_hierarchy,
customerName.wp_yoast_migrations,
customerName.wp_yoast_primary_term,
customerName.wp_yoast_seo_links,
customerName.wp_2_yoast_indexable,
customerName.wp_2_yoast_indexable_hierarchy,
customerName.wp_2_yoast_migrations,
customerName.wp_2_yoast_primary_term,
customerName.wp_2_yoast_seo_links,
customerName.wp_3_yoast_indexable,
customerName.wp_3_yoast_indexable_hierarchy,
customerName.wp_3_yoast_migrations,
customerName.wp_3_yoast_primary_term,
customerName.wp_3_yoast_seo_links,
customerName.wp_4_yoast_indexable,
customerName.wp_4_yoast_indexable_hierarchy,
customerName.wp_4_yoast_migrations,
customerName.wp_4_yoast_primary_term,
customerName.wp_4_yoast_seo_links,
customerName.wp_5_yoast_indexable,
customerName.wp_5_yoast_indexable_hierarchy,
customerName.wp_5_yoast_migrations,
customerName.wp_5_yoast_primary_term,
customerName.wp_5_yoast_seo_links,
customerName.wp_6_yoast_indexable,
customerName.wp_6_yoast_indexable_hierarchy,
customerName.wp_6_yoast_migrations,
customerName.wp_6_yoast_primary_term,
customerName.wp_6_yoast_seo_links,
customerName.wp_7_yoast_indexable,
customerName.wp_7_yoast_indexable_hierarchy,
customerName.wp_7_yoast_migrations,
customerName.wp_7_yoast_primary_term,
customerName.wp_7_yoast_seo_links
;
IMPORTANT: Must perform these next steps in the given order, and one at a time.
convert-domains-to-vip-go-placeholder.shconvert-s3-urls-to-vip-media.shconvert-domains-to-production.sh- If testing locally, run
wp cache flush --network --url=site1.com
convert-domains-to-vip-go-placeholder.sh
#!/bin/bash
searchReplaceFlags="--url=site1.com --all-tables --skip-plugins --skip-themes --skip-columns=guid,path,option_name,autoload,meta_key,post_status,comment_status,ping_status,post_password,to_ping,pinged,post_type,post_mime_type,comment_author,comment_author,comment_author_email,comment_author_url,comment_author_IP,comment_content,comment_approved,comment_agent,comment_type,link_url,link_name,link_image,link_target,link_description,link_visible,link_rel,link_notes,link_rss,post_title,post_name,taxonomy,slug,type,db_version,user_login,title,activation_key,IP,description,name,user_nicename,user_email,user_activation_key,display_name,tags,source,fields --report-changed-only"
cd ~/server/wpvip-go || exit
function replace-domains() {
wp search-replace "www.${1}.com" "${1}.com" $searchReplaceFlags
wp search-replace "http://${1}.com" "https://${1}.com" $searchReplaceFlags
wp search-replace "${1}.com" "vip-go-${1}.site2.com" $searchReplaceFlags
echo "COMPLETE: $1 =================================================="
}
# run in parallel
replace-domains site1 &
replace-domains site2 &
replace-domains site3 &
replace-domains site4 &
replace-domains site5 &
replace-domains site6 &
replace-domains site7 &convert-domains-to-production.sh
#!/bin/bash
searchReplaceFlags="--url=site1.com --all-tables --skip-plugins --skip-themes --skip-columns=guid,path,option_name,autoload,meta_key,post_status,comment_status,ping_status,post_password,to_ping,pinged,post_type,post_mime_type,comment_author,comment_author,comment_author_email,comment_author_url,comment_author_IP,comment_content,comment_approved,comment_agent,comment_type,link_url,link_name,link_image,link_target,link_description,link_visible,link_rel,link_notes,link_rss,post_title,post_name,taxonomy,slug,type,db_version,user_login,title,activation_key,IP,description,name,user_nicename,user_email,user_activation_key,display_name,tags,source,fields --report-changed-only"
cd ~/server/wpvip-go || exit
function replace-domains() {
wp search-replace "vip-go-${1}.site2.com" "${2}${1}.com" "" $searchReplaceFlags
echo "COMPLETE: $1 =================================================="
}
replace-domains site2 &
replace-domains site3 &
replace-domains site4 &
replace-domains site5 &
replace-domains site6 &
replace-domains site7 &Done on local machine because my Ryzen 9 5950X is 🔥. During testing this took 20 minutes to run.
wp plugin activate wordpress-seo --network --url=site1.com
# Then in seperate terminals run at same time
wp yoast index --url=site2.com
wp yoast index --url=site3.com
wp yoast index --url=site4.com
wp yoast index --url=site5.com
wp yoast index --url=site6.com
wp yoast index --url=site7.com