How to change the WordPress table prefix prior to a migration

When working on a Drupal to WordPress migration project, I like to migrate into a set of intermediary WordPress tables that live in the Drupal database. These are working tables where I can run various scripts to process and clean up the content before exporting to a working WordPress installation. It’s not necessary to do this but I find it convenient to run scripts on the same database rather than deal with two separate database connections.

Note that some people suggest renaming the table prefixes to improve security. My use of the table prefixes is simply to create temporary containers for the migration. While non-standard prefixes might help prevent ‘script kiddie’ attacks, I find it isn’t worth the disadvantages that come with this sort of security through obscurity (or more precisely, security through minority) approach. Here are two articles give a deeper explanation of topic:

• wordfence.com: WordPress Table Prefix: Changing It Does Nothing to Improve Security
• wpkrauts.com: The database table prefix is not a security feature

SQL queries to change the WordPress table prefixes

You can start with a freshly installed WordPress database. Dumping this and importing to your Drupal migration database will give you all the tables with the correct WordPress schema. I use the acc_ prefix but you can use whatever you want.

Rename the tables with these queries:

RENAME table `wp_commentmeta` TO `acc_commentmeta`;
RENAME table `wp_comments` TO `acc_comments`;
RENAME table `wp_links` TO `acc_links`;
RENAME table `wp_options` TO `acc_options`;
RENAME table `wp_postmeta` TO `acc_postmeta`;
RENAME table `wp_posts` TO `acc_posts`;
RENAME table `wp_terms` TO `acc_terms`;
RENAME table `wp_termmeta` TO `acc_termmeta`;
RENAME table `wp_term_relationships` TO `acc_term_relationships`;
RENAME table `wp_term_taxonomy` TO `acc_term_taxonomy`;
RENAME table `wp_usermeta` TO `acc_usermeta`;
RENAME table `wp_users` TO `acc_users`;

Testing the migration results

If you need to point a WordPress installation to these tables for testing, you’ll need to do two things:

1. Update the $table_prefix setting in the wp-options.php file
2. Update the options and usermeta tables

Updating the $table_prefix setting in the wp-options.php file is straightforward. Open the file and edit the line:

$table_prefix  = ‘acc_’;

In WordPress, prefixes are saved as entries in the options and usermeta table. Check for entries containing the prefix:

SELECT * FROM `acc_options` WHERE `option_name` LIKE ‘%wp_%’;
SELECT * FROM `acc_usermeta` WHERE `meta_key` LIKE ‘%wp_%’;

When you have all the entries, update them with the new prefix. The query will probably look something like this:

UPDATE `acc_options` SET `option_name` = ‘acc_user_roles’ WHERE `option_name` = ‘wp_user_roles’;
UPDATE `acc_usermeta` SET `meta_key` = ‘acc_capabilities’ WHERE `meta_key` = ‘wp_capabilities’;
UPDATE `acc_usermeta` SET `meta_key` = ‘acc_user_level’ WHERE `meta_key` = ‘wp_user_level’;
UPDATE `acc_usermeta` SET `meta_key` = ‘acc_user-settings-time’ WHERE `meta_key` = ‘wp_user-settings-time’;
UPDATE `acc_usermeta` SET `meta_key` = ‘acc_user-settings’ WHERE `meta_key` = ‘wp_user-settings’;
UPDATE `acc_usermeta` SET `meta_key` = ‘acc_dashboard_quick_press_last_post_id’ WHERE `meta_key` = ‘wp_dashboard_quick_press_last_post_id’;

A word of warning: it’s easy to forget to change the prefixes back to match the final WordPress installation. If you do, the WordPress user accounts will have problems, such as the Dashboard controls not being visible after logging in. Because of this, I tend to have a separate testing installation that gets an import of the working tables.