How to Update the WordPress Table Prefix
The default table prefix used for WordPress is
wp_, and I've noticed a few WordPress developers mention that it is more secure to use a random table prefix for your WordPress installation. It's easy to set the table prefix variable in your wp-config.php file when you are installing your WordPress site, but what if you would like to make that change post-installation. Here's a few steps you can make to update the WordPress table prefix. It's helpful to have some knowledge of MySQL before beginning these steps.
First Step: Update the table prefix variable in wp-config.php
This step is pretty simple and straight forward. Just open up the wp-config.php file and change the
$table_prefix variable from
/** * WordPress Database Table prefix. * * You can have multiple installations in one database if you give each a unique * prefix. Only numbers, letters, and underscores please! */ $table_prefix = 'new_prefix_';
Running the queries
In my situation I'm using Sequel Pro on my local machine to run the MySQL statements but you can also use phpMyAdmin or even just the good old command line. I would recommend testing these statements locally before running on a production server.
Renaming the tables
The first set of statements that need to be run basically update the main table names to the new prefix and can use the
RENAME TABLE statement. There are 11 tables total that need to be updated.
RENAME TABLE `wp_commentmeta` TO `new_prefix_commentmeta`; RENAME TABLE `wp_comments` TO `new_prefix_comments`; RENAME TABLE `wp_links` TO `new_prefix_links`; RENAME TABLE `wp_options` TO `new_prefix_options`; RENAME TABLE `wp_postmeta` TO `new_prefix_postmeta`; RENAME TABLE `wp_posts` TO `new_prefix_posts`; RENAME TABLE `wp_term_relationships` TO `new_prefix_term_relationships`; RENAME TABLE `wp_term_taxonomy` TO `new_prefix_term_taxonomy`; RENAME TABLE `wp_terms` TO `new_prefix_terms`; RENAME TABLE `wp_usermeta` TO `new_prefix_usermeta`; RENAME TABLE `wp_users` TO `new_prefix_users`;
There is an option in the options table called
wp_user_roles that you can see uses the
wp_ prefix and needs to be updated. The following statement accomplishes this. You can see that since we have already changed the table name to the new prefix, we using the new table name in the statement.
UPDATE `new_prefix_options` SET option_name='new_prefix_user_roles' WHERE option_name='wp_user_roles';
usermeta table you can see that there are multiple records where the meta keys are using the original
wp_ prefix. Here's a statement that update all records that start with
wp_ in the
meta_key column. It's also using the
REPLACE method to replace the keys appropriately.
UPDATE `new_prefix_usermeta` SET meta_key = REPLACE(meta_key, 'wp_', 'new_prefix_') WHERE meta_key LIKE 'wp_%';
And that's it! A very simple way to update the WordPress table prefix post-installation. One thing to note is that there are various plugins that may use the table prefix. If any plugins are causing an issue after updating the prefix, then you will need to reactivate those plugins. This will cause the plugin's settings to return to the default. If plugin authors are smart, then they should be using their own custom prefix for any new tables or options.