THE TASK
About a week ago, I was tasked to migrate a Disaster Accountability Project’s website from Bluehost to Namecheap. Migrations are usually seamless if the provider(s) allow some symbiotic connection between the two. Case in point: when I worked with Educate Radiate Elevate, they moved their server from a UK-based system to A2 Hosting. A2 Hosting provides a WordPress migration option if you give them access to your WordPress backend and allow them to install a few plugins to help initiate the file transfer. The transition took about a day to make the switch.
Having services like these makes life easier for a developer. It’s not that I don’t know how to perform the transfer, it’s just one less task to focus on. There are bigger steps later in the project.
THE ENCOUNTER
However, with Disaster Accountability Project, the transition option was not a choice. I had to do this manually on my own. Fortunately, I have an account with Delicious Brains: WP Migrate Pro plugin. It provides me the option to export, import, push, and pull from the client’s site to wherever you need the extractions to go. Generally, I usually do a full export of the entire site.
Once I extracted what I needed from the site. I then loaded up a fresh WP instance on my local machine using LocalWP, installed the WP Migrate Pro plugin, and prepared to “Import Database” with the live content and database, but this is where I ran into a snag.
Spinning up a local allows me to freely adjust and alter a copy of the live site locally on my machine, or so I thought. But turns out the prefix was not the same prefix as the newly installed local copy. This is where SQL fudgery came in.
I understand that adjusting the prefix for the database tables in WordPress is one form of “hardening” your WordPress site from security hacks. The issue is reverse engineering the prefix back to “wp_” so that you can rebuild the site and make modifications before pushing it back up the development or staging servers.
THE WORKAROUND
Checking to make sure my tables were indeed colliding with a new copy of WordPress, I accessed the local project with WP-CLI from my terminal. I typed in “wp db tables” and sure enough, the new tables appeared on top of the old. This meant I had to remove the new tables first. The next command I typed in was “wp db reset“. This removed the new tables and allowed me to insert the production tables into the local WordPress site.
Of course, with the production data tables, the local copy of WordPress will not understand the odd prefix. This is where I had to enter the SQL tables interface to adjust the prefixes manually.
With LocalWP, you use Adminer to gain access to the database. I selected the “SQL command” option to begin database alterations. The following snippet of code is what I inserted, which I took from this site:
SET @database = "databasename";
SET @oldprefix = "oldprefix_";
SET @newprefix = "newprefix_";
SELECT
concat(
"RENAME TABLE ",
TABLE_NAME,
" TO ",
replace(TABLE_NAME, @oldprefix, @newprefix),
';'
) AS "SQL"
FROM information_schema.TABLES WHERE TABLE_SCHEMA = @database;After I ran that command I then replaced all the prefixes with my local custom setup. This changes the major tables that are usually associated with the common tables you see within WordPress.
Next, I had to alter the “*_usermeta” and “*_options” tables to match up with the newly installed WordPress copy on my local machine.
UPDATE `wp_usermeta`
SET meta_key = REPLACE(meta_key, 'oldprefix_', 'wp_')
WHERE meta_key LIKE 'oldprefix_%';UPDATE `wp_options`
SET option_value = replace(option_value, 'oldprefix_', 'wp_')
WHERE option_name LIKE 'oldprefix_%';THE CONFUSION
Now, after these changes, I made sure to shut down the local server and spin it up again. This allows the database to cycle again, ensuring that the changes I’ve made are indeed, changed.
The login window opens and I am able to enter my username and password, but after submitting the credentials, I am given a blank white screen. Nothing generated. Thus, the confusion starts.
My only next thought to this dilemma is that the production version of WordPress Core version (6.0.6) and the PHP version (7.4.3.3) that I exported were not compatible with my local version.
Maybe that’s what is causing the issue. I’ve yet to figure this out as of the writing of this article. I’ll investigate a bit further and reach out to the WordPress community for some more answers.