HOWTO: Change Wordpress Table Prefix

30May07

In an attempt to tidy up my backend Wordpress databases, I thought I’d change the Wordpress table prefix and the move all my Wordpress tables into a single database.

Before actually moving the tables, I took the plunge of just renaming them by changing the table prefix to uniquely identify which Wordpress installation they belong to.

I did this as follows…

Dump the table names and prepend the new prefix:
# for x in `/usr/local/bin/mysqlshow --user=${DB_USER} \ --password=${DB_PASSWD} \ --host=${DB_HOST} \ ${DB_NAME} | grep ${OLD_WP_PREFIX} | grep -v ^Database | tr -d " | "` do echo "RENAME TABLE ${DB_NAME}.$x TO ${DB_NAME}.${NEW_WP_PREFIX}_$x;" done

You’ll see all I did was to prepend a new prefix to the old one as all I’m doing is identifying which Wordpress installation the tables relate to.
Copy the output from the above into phpMySQL and ran it. This output can also be directed back into mysql on the CLI. All my tables took on their new names successfully.
Modify my wp-config.php to reflect the correct $table_prefix

Voila!!! At face value this appears to work and your blog will appear as it did before. That is until you attempt to login. You’ll be presented with this lovely message:

You do not have sufficient permissions to access this page.

Even when attempting to login as admin. The cause? Simple, some of the Wordpress options are actually saved using the Wordpress prefix. You will now need to open up your favourite MySQL admin tool and modify the following:

In table ${NEW_WP_PREFIX}_options:

Option ${OLD_WP_PREFIX}_user_roles

In table ${NEW_WP_PREFIX}_usermeta:

Option ${OLD_WP_PREFIX}_capabilities
Option ${OLD_WP_PREFIX}_user_level
Option ${OLD_WP_PREFIX}_autosave_draft_ids

The last will only exist if you have saved a draft whilst editing. For each of these, you need to change the ${OLD_WP_PREFIX} part to reflect your new prefix, eg: wp_user_roles becomes new_wp_user_roles.

You may need to change other options from plugins that store the prefix as part of the variable name.

If you’re using the Bad Behaviour plugin, you’ll also need to disable it, remove it’s option entries from the _options table and re-enable the plugin.

Filed under: HOWTO, Wordpress | . |

16 Responses to “HOWTO: Change Wordpress Table Prefix”

1 SmellyGeekBoy on Apr 2nd, 2008 said:

Hi, I just moved a Wordpress install over to a different server and had to change my table prefixes. This is exactly what I needed to let me log in again after it broke spectacularly - thanks a lot!
2 Max on Jul 8th, 2008 said:

Thank you!

You would only wish that in a later version this is changed, I see no reason for storing the prefix as field names in the db.
3 Brandon on Jul 22nd, 2008 said:

Thanks! That was very helpful.

I got that exact error message today after renaming my table_prefix, “You do not have sufficient permissions to access this page.”, and your post made it very simple for me to get things working quickly.

Brandon Zehm
TSheets.com
4 Ruthvick on Nov 1st, 2008 said:

Beautiful!! Thanks for the research.
5 Eric on Dec 21st, 2008 said:

Great post, very helpful. FYI, I found that phpMyAdmin on my host’s control panel could not change the ${OLD_WP_PREFIX}_user_roles item. I ended up using CocoaMySQL-SBG instead. Also note, there will be more than one occurrence of the items in ${NEW_WP_PREFIX}_usermeta if you have more than one account registered.
6 Alexis on Dec 15th, 2009 said:

Same problem, good solution. It happened to me when i moved a blog.
Why there are table data with the prefix???
Thanks
Alexis
7 Jennifer Wagner Bunce on Feb 21st, 2010 said:

THANK YOU THANK YOU THANK YOU! I have been trying for days to fix my blog after moving to a different host. I had found and fixed all the wp_ prefix differences except the one you mention:

${OLD_WP_PREFIX}_user_roles

which was buried in the wp_options table.

That was the key! I can now access my dashboard again and fix the rest of my issues. Thank you so much!
8 Chris on Mar 5th, 2010 said:

@Jennifer, you just saved my life, thanks for mentioning the user_roles table, I found it using the search function after reading your comment, now I could log in again. I almost gave up and redo everything, thank you so much.

@Max, thanks mate, very useful post, keep up the good work, cheers!
9 Colin on Mar 5th, 2010 said:

@Chris: Glad you found it useful. I actually mentioned the user_roles table in the post. This is what motivated me to write the post