The other day I had to change the WordPress table prefix for my Road Less Travelled Blog.
It took a while but I eventually found instructions at the Pi in the Sky Blog.
They’ve been re written here in a more summarised form:
- Change the table prefix in wp-config.php and upload it to your web server. Let’s say we still have the default prefix wp_.
$table_prefix = 'wp_';
becomes
$table_prefix = 'wp_new_';Note that wp_new_ is just an example. You can call the prefix anything you want as long as you use only letters (a-z), numbers (0-9) and underscores (_), eg: jims_wp22_blog_.
- You need to manually change the name of all the wordpress tables in your MySQL database from the original prefix eg: wp_ to the new prefix using manual SQL queries or PHPmyAdmin which I find much easier to use, eg:
RENAME wp_posts TO wp_new_posts;
- The last step is to manually change some fields in your newly renamed Wordpress tables which still have the old prefix at the beginning of their name:
For table wp_new_options
wp_user_roles becomes wp_new_user_rolesFor table wp_new_usermeta
wp_capabilities becomes wp_new_capabilitieswp_user_level becomes wp_new_user_level
wp_autosave_draft_ids becomes wp_new_autosave_draft_ids
Note: xx_autosave_draft_ids won’t be there unless you have saved drafts of posts while editing them.
Following these instructions I changed the WordPress MySQL Database Table Prefix on all my blogs to something very hard to guess in less than 3 minutes, which improves their protection against hackers.
PS While you’re here, you probably know that Wordpress automatically reformats single quotes when you’re trying to display some code which means it won’t display properly and people can’t cut and paste it.
So next time you need to display a single quote in a Wordpress post do it using this HTML character code:
' and it will display as you intended 
{ 7 comments… read them below or add one }
Ciao,
nice hack,
but, what about wp_usermeta table content?
I have still wp_capabilities, wp_user_leveland, wp_autosave_draft_ids in the field “meta_key”
over there…
is this a problem?
Thanks,
Luigi
EDITOR: I’m not sure what you mean Luigi.
I clearly stated that : wp_capabilities, wp_user_level and (if it exists) wp_autosave_draft_ids have to be renamed
Right now I’m learning MySQL and PHP so I don’t know any thing about MySQL Database Table Prefix but hope it will be useful for me in future so I bookmarked it in my list.
These instructions made me both happy and sad:
- Sad, because your method wasn’t much quicker than what I was already halfway through
- Happy, because I can proudly say I figured out the first two steps on my own
- Happy, because you told me the last thing I need to do!
And then, (sadly!) I had to fight for several hours before I got everything working again.
The fix ended up being:
- comment out the meat of wp-includes/version.php
- run upgrade.php
- uncomment out the meat
- log in as admin
- I still got an error (”You do not have sufficient permissions to access this page.”)
As it turns out, wp-login.php was redirecting to: http://example.com/path-to-wp/wp-admin/
with the index.html strangely missing! I didn’t notice it until I read of a similar problem described by lparra at http://wordpress.org/support/topic/52806#post-299441 … but my fix was easier than that one. I simply added index.html to the URI, loaded the page, and instead of an error, I was prompted to run update again, so excitedly, I did. And then… AND THEN….. YES! EVERYTHING WORKS NOW!
I’m posting my fix here in case anyone else runs into this.
Cheers~!
-Qrystal
I was just reviewing my notes on this adventure of mine, and I realized that I mistyped above… I meant, of course, to say index.php, not index.html.
I’m glad it all worked out too. Cheers!
-Qrystal
I always wondered how to make this change on wordpress. Thanks for the article.
Hallo,
Nice!
just question- I have instaled 2 blogs with single database they are with two different prefixes. in case i want to reinstall them and I allready have backed up the datebase what sould I do? Just the same prefixes and everithing is ok or….?
Thanks
Hmm, I’m just wondering.
If you already use the PhpMyAdmin to do the changes, why not :
- backup your database in the txt format,
-open it up in some text editor like Notepad++,
-find and replace all #old prefix with #new prefix,
-save the txt backup file,
-and import the database with that new file.
You will spend much less time to do that. At least this is the way I was doing it.
If you know a little about PHP you could even write a short script that will do the find and replace part for you in few seconds.