Manually Changing WordPress Database Table Prefixes

Today it came to my attention that my wife’s site’s hosting was running a very old version of PHP, so I thought it was time to update it. It’s hosted at Webfaction, which I really like, but I didn’t see an easy way to update PHP from their control panel. So, I decided it would be fairly easy (ha) to just create a new app, clone the site there, and then point the website to that new app.

After logging into the server via SSH, I successfully copied the entire site using the terminal command:

ssh cp -r ~/webapps/oldapp/* ~/webapps/newapp

That worked like a charm and was probably about 1000% faster than doing by FTP.

I then went to delete the old app once I confirmed the new one was working. Hold on, there, compadre—it wanted to delete the MySQL DB along with it! Turns out there’s no way to remove the app without it also removing the associated DB. So, I exported the DB to a gzip, tried to import it into the fresh DB, and got an error:

1075 – Incorrect table definition; there can be only one auto column and it must be defined as a key

For whatever reason, phpMyAdmin was exporting the tables without the proper “Primary Key” definition. I used WP Migrate DB Pro to export the DB instead, and that worked.

Then I realized that the tables were originally created with the default “wp_” prefix, which I don’t like for security reasons. Just add a new prefix to the existing tables in phpMyAdmin, right?

That’s what I did. Of course I updated the wp-config.php file as well, but when I tried to access the site’s dashboard, I got a “permission denied” error.

Back to Google we go. I found out that there are a few DB entries that use the table prefix in their metakey name. Here’s the SQL command to update them:

update newprefix_usermeta set meta_key = 'newprefix_capabilities' where meta_key = 'wp_capabilities';
update newprefix_usermeta set meta_key = 'newprefix_user_level' where meta_key = 'wp_user_level';
update newprefix_usermeta set meta_key = 'newprefix_autosave_draft_ids' where meta_key = 'wp_autosave_draft_ids';
update newprefix_options set option_name = 'newprefix_user_roles' where option_name = 'wp_user_roles';

…where “newprefix” is the new table prefix defined in wp-config.php.

Phew. That turned out to be a lot of work, but it’s all working now, running a nice new instance of PHP 7.