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:
- Update the $table_prefix setting in the wp-options.php file
- 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.
Cover photo by Gavin Allanwood on Unsplash
You may also like
Drupal to WordPress Migration Guide
In this guide, you'll find insights drawn from almost 15 years of specialising in complex Drupal to WordPress migration projects. I'll walk you through the entire migration process, from the initial evaluation to post-launch considerations.
Secure Your AI Workflow Using Local Tokenisation
Don't leak confidential client data when using cloud-based LLMs. Secure your AI workflow with local tokenisation using PaigeSafe.
How To Set Up Drupal 7 Docker Containers for Migration Projects
Learn how Docker is a valuable tool for Drupal 7 end of life migrations. In this post, I'll give a step-by-step guide to setting up a Drupal 7 container for your migration project.