Quickly Change the WordPress MySQL Database Table Prefix

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:

  1. 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_.

  2. 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;

  3. 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_roles

    For table wp_new_usermeta
       wp_capabilities becomes wp_new_capabilities

       wp_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 :-)


11 comments on “Quickly Change the WordPress MySQL Database Table Prefix

  1. Luigi on said:

    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

  2. vavk on said:

    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. :D

  3. Qrystal on said:

    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

    EDITOR: glad to hear it all worked out in the end

  4. Qrystal on said:

    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

  5. SharkSpace on said:

    I always wondered how to make this change on wordpress. Thanks for the article.

  6. kolio on said:

    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

  7. pajko on said:

    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.

  8. Calvin on said:

    Pajko – sound like you know ‘a little PHP code that can write a short script that will do the find and replace part in few second. Why not share the code with us – or at least me :)

  9. ADNAN on said:

    Hello,
    first of all thanks for useful post,after changing wordpress database prefix, pages on my site are not working like this
    http://www.filmylinks4u.net/page/136/
    please help me

  10. Chris Lawhorn on said:

    On the single quote reformatting front, I had a question…

    On my site (http://www.runhundred.com) the search works fine–for the most part.

    But, if anyone searches for a post with an apostrophe (like Guess Who’s Coming To Dinner) the search not only fails to find the post, but also gets this error:

    Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource

    Any ideas on how to fix this?

  11. Brian on said:

    Here it is for WordPress… Just use the query below simply replacing newprefix_ with your prefix … that will quickly change each default table in WordPress 3.2. Hope that helps someone.

    RENAME TABLE
    wp_commentmeta TO newprefix_commentmeta,
    wp_comments TO newprefix_comments,
    wp_links TO newprefix_links,
    wp_options TO newprefix_options,
    wp_postmeta TO newprefix_postmeta,
    wp_posts TO newprefix_posts,
    wp_terms TO newprefix_terms,
    wp_term_relationships TO newprefix_term_relationships,
    wp_term_taxonomy TO newprefix_term_taxonomy,
    wp_usermeta TO newprefix_usermeta,
    wp_users TO newprefix_users;

Leave a Reply

Your email address will not be published.

HTML tags are not allowed.