WordPress allows you to specify the prefix used for naming all your tables.  Unfortunately, there are several places where it hard-codes the full names of the tables, including the prefixes, so you can’t just rename the tables.  You also must modify values in the tables.  I’ve had to do this a few times recently as I’ve reorganized my sites, so I figured it was time to write an article about it.

Here are the steps to change the prefix for your WordPress site.  I use phpMyAdmin v3, but you can use any tool that allows you to manage your MySQL databases.  These steps assume you are changing your prefix from prefix1_ to prefix2_.

  1. Rename all the tables beginning with prefix1_ to prefix2_.  You do this in phpMyAdmin by doing the following:
    1. Click on the database name.
    2. Click on each table whose name begins with prefix1_ in turn.
    3. Click on the Operations tab.
    4. In the Table options section to the left, replace prefix1_ with prefix2_ in the table name and click Go.
  2. Rename the values in the meta_key column of the wp_usermeta table using this SQL command:
    UPDATE `prefix2_usermeta` SET `meta_key` = REPLACE( `meta_key` , 'prefix1_', 'prefix2_' );
  3. Rename the values in the option_name column of the wp_options table using this SQL command:
    UPDATE `prefix2_options` SET `option_name` = 'prefix2_user_roles' WHERE `option_name` ='prefix1_user_roles' AND `blog_id` =0;

It’s possible that some plugins may need additional attention.  You may still need to deactivate and then re-activate some plugins, and you may even need to deactivate some plugins, delete their options from the prefix2_options table, then re-activate them.  Finally, you may find that the prefix is embedded in other places as well.

I found the following blog post to be very helpful as I was tracking down this problem: