How to change your WordPress installation’s table prefix?

This might sound like a strange thing to do, but it could happen that you need to do it. For example, imagine that you just have one database both for your staging and production environment. I know this is probably not an ideal scenario, but if you consider monthly expenses for database services (like Amazon RDS) and your website is a not high-traffic one, plus you don’t want to go over budget, then maybe it is not that crazy.

So what would you do? Yes, you’re right: you would allow staging, testing and production installations to share a common database instance by setting different table prefixes for each one of them.

Here I’m going to walk you through the process of cloning an existing WordPress installation and ending up with a new prefix. Of course you could change your tables name directly, but it wouldn’t be as safe (and useful) as the approach I’m presenting to you.

1- Copy your current WordPress directory to its new location

Ok, perhaps an obvious step.

2- Create tables with the new prefix but preserving the original structure

For instance, if your old prefix was “wp_” and your new prefix is going to be “wp_new_”, you could do this in MySQL:

CREATE TABLE wp_new_commentmeta LIKE wp_commentmeta;
CREATE TABLE wp_new_comments LIKE wp_comments;
CREATE TABLE wp_new_links LIKE wp_links;
CREATE TABLE wp_new_options LIKE wp_options;
CREATE TABLE wp_new_postmeta LIKE wp_postmeta;
CREATE TABLE wp_new_posts LIKE wp_posts;
CREATE TABLE wp_new_term_relationships LIKE wp_term_relationships;
CREATE TABLE wp_new_term_taxonomy LIKE wp_term_taxonomy;
CREATE TABLE wp_new_terms LIKE wp_terms;
CREATE TABLE wp_new_usermeta LIKE wp_usermeta;
CREATE TABLE wp_new_users LIKE wp_users;

3- Migrate data from your old tables to the ones we just created

INSERT wp_new_commentmeta SELECT * FROM wp_commentmeta;
INSERT wp_new_comments SELECT * FROM wp_comments;
INSERT wp_new_links SELECT * FROM wp_links;
INSERT wp_new_options SELECT * FROM wp_options;
INSERT wp_new_postmeta SELECT * FROM wp_postmeta;
INSERT wp_new_posts SELECT * FROM wp_posts;
INSERT wp_new_term_relationships SELECT * FROM wp_term_relationships;
INSERT wp_new_term_taxonomy SELECT * FROM wp_term_taxonomy;
INSERT wp_new_terms SELECT * FROM wp_terms;
INSERT wp_new_usermeta SELECT * FROM wp_usermeta;
INSERT wp_new_users SELECT * FROM wp_users;

4- Set the new prefix in your “wp-config.php” file

$table_prefix = 'wp_new_';

5- Set the new URL in your “wp-config.php” file

define('WP_HOME','https://www.example.com/');
define('WP_SITEURL','https://www.example.com/');

6- Replace ocurrences of the old prefix “wp_” in the data with the new prefix “wp_new_”

You can do this automatically by using the “wp” command (if you don’t have it installed, you can go to http://wp-cli.org/):

wp search-replace wp_ wp_new_ --precise