APUSMS User Migration Strategy

The Multisite wp_users table is shared across all sites; i.e.: there’s only one wp_users table and one wp_usermeta table.

The Multisite wp_users table is shared across all sites; i.e.: there’s only one wp_users table and one wp_usermeta table.

If you have many users, this will be the most difficult part of the migration, per the fact that the users must be merged from your migrated wp_##_users table into the multisite wp_users table, the user IDs must be updated with new, unique user IDs, and all data associated with that user will need to be refactored based on the updated ID.

Start by creating a table for managing the user migration

CREATE TABLE IF NOT EXISTS _mig_wp_users (
	user_login varchar(60), 
    original_user_id bigint, 
    site_id bigint, 
    migrated_user_id bigint, 
    actual_user_id bigint
);

That table serves as the anchor point for all other updates.

user_login is the username
original_user_id is the user id before any migration steps, as stored in the wp_##_users table
site_id corresponds to wp_blogs.blog_id
migrated_user_id is the new user id in the wp_users table, which is just the original id plus 1000 * the blog_id
actual_user_id is what the final id will be in wp_users, which is the min of all ids for this user_login

Next, create the following procedures:

DROP PROCEDURE IF EXISTS _mig_migrate_users;
DELIMITER ;;

CREATE PROCEDURE _mig_migrate_users()
BEGIN
	DECLARE blog INT DEFAULT 0;
    
    SELECT MIN(blog_id) FROM wp_blogs WHERE blog_id > 1 INTO blog;
    WHILE NOT blog IS NULL DO       
        SET @sql_stmt = CONCAT('
INSERT INTO _mig_wp_users (user_login, original_user_id, site_id, migrated_user_id, actual_user_id)
SELECT b.user_login, b.original_id, ', blog, ' AS site_id, b.new_id, 0 AS actual_user_id
	FROM (
		SELECT IF (b.ID < 1000, (b.ID + (1000 * ', blog, ')), b.ID) AS new_id, IF (b.ID > 1000, (b.ID - (1000 * ', blog, ')), b.ID) AS original_id, b.user_login, b.user_pass, b.user_nicename, b.user_email, b.user_url, b.user_registered, b.user_activation_key, b.user_status, b.display_name
			FROM wp_', blog, '_users b) b LEFT JOIN _mig_wp_users u ON b.original_id = u.original_user_id AND u.site_id = ', blog, '
	WHERE u.user_login IS NULL;      	
		');            
		PREPARE stmt FROM @sql_stmt;
		EXECUTE stmt;  
        DEALLOCATE PREPARE stmt;
        
        SET @sql_stmt = CONCAT('
INSERT INTO wp_users (ID, user_login, user_pass, user_nicename, user_email, user_url, user_registered, user_activation_key, user_status, display_name)  
SELECT b.new_id, b.user_login, b.user_pass, b.user_nicename, b.user_email, b.user_url, b.user_registered, b.user_activation_key, b.user_status, b.display_name
	FROM (
		SELECT IF (b.ID < 1000, (b.ID + (1000 * ', blog, ')), b.ID) AS new_id, IF (b.ID > 1000, (b.ID - (1000 * ', blog, ')), b.ID) AS original_id, b.user_login, b.user_pass, b.user_nicename, b.user_email, b.user_url, b.user_registered, b.user_activation_key, b.user_status, b.display_name
			FROM wp_', blog, '_users b) b LEFT JOIN wp_users u ON b.new_id = u.ID
	WHERE u.user_login IS NULL;            	
		');            
		PREPARE stmt FROM @sql_stmt;
		EXECUTE stmt;  
        DEALLOCATE PREPARE stmt;
        
        SET @sql_stmt = CONCAT('
INSERT INTO wp_usermeta (user_id, meta_key, meta_value)
SELECT  b.user_id, b.meta_key, b.meta_value
	FROM (
		SELECT  IF (b.user_id < 1000, (b.user_id + (1000 * ', blog, ')), b.user_id) AS user_id, 
				IF (LEFT(meta_key, 3) = ''wp_'' AND SUBSTRING(meta_key, 4, LENGTH(''', blog, ''')) <> ''', blog, ''', CONCAT(''wp_'', ', blog, ', RIGHT(meta_key, LENGTH(meta_key) - 2)), meta_key) AS meta_key, 
				meta_value
			FROM wp_', blog, '_usermeta b ) b LEFT JOIN wp_usermeta u ON b.user_id = u.user_id AND b.meta_key = u.meta_key
	WHERE u.umeta_id IS NULL;		
		');            
		PREPARE stmt FROM @sql_stmt;
		EXECUTE stmt;  
        DEALLOCATE PREPARE stmt;
		SELECT MIN(blog_id) FROM wp_blogs WHERE blog_id > blog INTO blog;
    END WHILE;
End;
;;
DELIMITER ;

DROP PROCEDURE IF EXISTS _mig_update_tables;
DELIMITER ;;
CREATE PROCEDURE _mig_update_tables()
BEGIN
	DECLARE done INT DEFAULT FALSE;
	DECLARE tbl VARCHAR(200);
    DECLARE col VARCHAR(200);
    DECLARE site_id INT;
    
    DECLARE crs CURSOR FOR
		SELECT TABLE_NAME, COLUMN_NAME, CAST(REPLACE(SUBSTRING(TABLE_NAME, 4, 2), '_', '') AS SIGNED INTEGER) AS site_id		
			FROM information_schema.columns
			WHERE table_schema = 'apusms'
				AND DATA_TYPE IN ('int', 'bigint')
				AND 
				(
					COLUMN_NAME LIKE '%user%'
					OR COLUMN_NAME LIKE '%author%'
				)
				AND NOT COLUMN_NAME LIKE '%status%' AND NOT COLUMN_NAME LIKE '%lockout%' AND NOT COLUMN_NAME LIKE 'user_field'
				AND NOT TABLE_NAME LIKE '%_usermeta' AND NOT TABLE_NAME LIKE '%_users'
				AND NOT TABLE_NAME IN ('wp_comments', 'wp_posts', 'wp_usermeta', 'wp_users', '_mig_wp_comments', '_mig_wp_users')
			ORDER BY table_name, ordinal_position;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN crs; 
    read_loop: LOOP
		FETCH crs INTO tbl, col, site_id;
        IF done THEN
			LEAVE read_loop;
		END IF;        
        SET @sql_stmt = CONCAT('
        UPDATE ', tbl, ' t INNER JOIN _mig_wp_users u ON site_id = u.site_id AND (t.', col, ' = u.original_user_id OR t.', col, ' = u.migrated_user_id)
			SET t.', col, ' = u.actual_user_id');            
		PREPARE stmt FROM @sql_stmt;
		EXECUTE stmt;  
        DEALLOCATE PREPARE stmt;
	END LOOP;
    CLOSE crs;
END;    

;;
DELIMITER ;

_mig_migrate_users copies each blog’s wp_##_users into _mig_wp_users with the correct IDs set. It also inserts the users and their metadata into wp_users and wp_usermeta respectively.
_mig_update_tables goes through all of the tables in all of the blogs and updates any relevant field with the migrated user’s ID.

Run the following statements in the order below (don’t worry – they only add data that is missing)

CALL _mig_migrate_users();

UPDATE _mig_wp_users u JOIN (
		SELECT MIN(migrated_user_id) AS new_user_id, user_login
			FROM _mig_wp_users
			GROUP BY user_login ) m ON u.user_login = m.user_login
	SET actual_user_id = new_user_id;
    
CALL _mig_update_tables();

Confirm that this all looks good. If so, cleanup any extraneous records from wp_users and wp_usermeta

DELETE u FROM wp_usermeta u LEFT JOIN _mig_wp_users m ON u.user_id = m.actual_user_id WHERE m.user_login IS NULL AND u.user_id > 1000;
DELETE u FROM wp_users u LEFT JOIN _mig_wp_users m ON u.ID = m.actual_user_id WHERE m.user_login IS NULL AND u.ID > 1000;

That should do it. This process leaves the source wp_##_users tables untouched, so that should help if there is a problem with a particular account.