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 wp_ to new_prefix_.

/**
 * 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`;

Update wp_user_roles

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';

Update meta_keys

In the 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.