Beginner's Guide for WordPress - Start your WordPress Blog in minutes.
Choosing the Best
WordPress Hosting
How to Easily
Install WordPress
Recommended
WordPress Plugins
View all Guides

How to Change the WordPress Database Prefix to Improve Security

Last updated on by
Elegant Themes
How to Change the WordPress Database Prefix to Improve Security

WordPress Database is like a brain for your entire WordPress site because every single information is stored in there thus making it hacker’s favorite target. Spammers and hackers run automated codes for SQL injections. Well, unfortunately many people forget to change the database prefix while they install WordPress. This makes it easier for hackers to plan a mass attack by targeting the default prefix wp_. The smartest way you can protect your database is by changing the database prefix which is really easy to do on a site that you are setting up. But it takes a few steps to change the WordPress database prefix properly for your established site without completely messing it up.

Preparation

We recommend that you backup your WordPress Database before you perform anything suggested in this tutorial. It is important to keep daily backups of your site, we recommend BackupBuddy plugin for doing that. Next thing we recommend is that you redirect your visitors to a temporary maintenance page.

Change Table Prefix in wp-config.php

Open your wp-config.php file which is located in your WordPress root directory. Change the table prefix line from wp_ to something else like this wp_a123456_

So the line would look like this:

$table_prefix  = 'wp_a123456_';

Note: You can only change it to numbers, letters, and underscores.

Change all Database Tables Name

You need to access your database (most likely through phpMyAdmin), and then change the table names to the one we specified in wp-config.php file. If you are using the cPanel WordPress hosting, then you can find the phpMyAdmin link in your cPanel. Look at the image below:

phpMyAdmin

There are a total of 11 default WordPress tables, so changing them manually would be pain.

SQL Query

That’s why to make things faster, we have a SQL query that you can use.

RENAME table `wp_commentmeta` TO `wp_a123456_commentmeta`;
RENAME table `wp_comments` TO `wp_a123456_comments`;
RENAME table `wp_links` TO `wp_a123456_links`;
RENAME table `wp_options` TO `wp_a123456_options`;
RENAME table `wp_postmeta` TO `wp_a123456_postmeta`;
RENAME table `wp_posts` TO `wp_a123456_posts`;
RENAME table `wp_terms` TO `wp_a123456_terms`;
RENAME table `wp_term_relationships` TO `wp_a123456_term_relationships`;
RENAME table `wp_term_taxonomy` TO `wp_a123456_term_taxonomy`;
RENAME table `wp_usermeta` TO `wp_a123456_usermeta`;
RENAME table `wp_users` TO `wp_a123456_users`;

You may have to add lines for other plugins that may add their own tables in the WordPress database. The idea is that you change all tables prefix to the one that you want.

The Options Table

We need to search the options table for any other fields that is using wp_ as a prefix, so we can replace them. To ease up the process, use this query:

SELECT * FROM `wp_a123456_options` WHERE `option_name` LIKE '%wp_%'

This will return a lot of results, and you need to go one by one to change these lines.

UserMeta Table

Next, we need to search the usermeta for all fields that is using wp_ as a prefix, so we can replace it. Use this SQL query for that:

SELECT * FROM `wp_a123456_usermeta` WHERE `meta_key` LIKE '%wp_%'

Number of entries may vary on how many plugins you are using and such. Just change everything that has wp_ to the new prefix.

Backup and Done

You are now ready to test the site. If you followed the above steps, then everything should be working fine. Now, you should make a new backup of your database just to be on the safe side.


Editorial Staff at WPBeginner is a team of WordPress lovers led by Syed Balkhi. Page maintained by Syed Balkhi.

WPBeginner's Video Icon
Our HD-Quality tutorial videos for WordPress Beginners will teach you how to use WordPress to create and manage your own website in about an hour. Get started now »

Comments

  1. gabe says:

    I got syntax error when following this (my version of SQL is 5.5.x).

    I had success after referring to the SQL manual. Needed to leave the quotes out of the query:

    [WRONG] RENAME table ‘wp_links’ TO ‘wp_xx_links’;
    [RIGHT] RENAME TABLE wp_links TO wp_xx_links;

  2. Karen says:

    I have changed the prefixes of a new install and then built a whole new site! I suddenly realised that I might not be able to update wordpress as normal from the admin panel..

    Does changing the prefixes affect being able to update wordpress as normal???

  3. Pablo says:

    Nice.

    You can use this as well:
    UPDATE `wp_a123456_options` SET `option_name`=REPLACE(`option_name`,’wp_’,'wp_a123456_’) WHERE `option_name` LIKE ‘%wp_%’;

    UPDATE `wp_a123456_usermeta` SET `meta_key`=REPLACE(`meta_key`,’wp_’,'wp_a123456_’) WHERE `meta_key` LIKE ‘%wp_%’;

  4. Haary says:

    Please answer ” How to create a plugin for take a backup of speific table in wordpress database?” in the stackoverflow

  5. Haary says:
  6. David Appleby says:

    Very nice guide thanks.

  7. Andrew Rickards says:

    Thanks for the useful info. I just tried changing my DB prefix and everything seems to have worked perfectly.

  8. John says:

    Thank you for doing the work to inform us on this topic. I have zero experience with WordPress, mySQL and PHP, so your help is greatly appreciated. A couple of questions:

    You have a graphic right below the words “There are a total of 11…”, with SQL circled. Am I supposed to check all the checkboxes?

    In the section titled “The Options Table”, which I’m getting to next, you say “This will return a lot of results, and you need to go one by one to change these lines.” How is this done (or will it be perfectly obvious)?

    • WPBeginner Support says:

      John, you need to click on the SQL which will open a Text Area, copy and paste the query given below the circled screenshot into SQL textarea and click Go button.

      When updating options table you will run another SQL query to search for fields which have wp_ in them and replace those fields with your new database prefix. The query will return a number of rows you need to click on the Edit button next to each row to edit it and manually replace wp_ with your new database prefix.

  9. Iftekhar says:

    Dear writer, I have tested this in my local server. I am having problem to get access in my admin panel after changing table prefix. I have found “dismissed_wp_pointers” this in my database. Do I need to change it also?

    Thanks in advance

    • WPBeginner Support says:

      No we don’t think you need to change that.

      • Iftekhar says:

        Problem solved :). Actually I forgot to change option table. Thanks for reply.

      • AMSGATOR says:

        `dismissed_wp_pointers` shows up when querying SELECT * FROM `wp_a123456_usermeta` WHERE `meta_key` LIKE ‘%wp_%’

        So I changed it since this says to change all the wp_ to the new prefix. I hope it doesn’t break anything.

  10. Kobbe says:

    Is this tutorial for an already installed blog…? Please kindly brief me on how to do this on a FRESH installations.

    • AMSGATOR says:

      If you have already installed WordPress (regardless of how much you published) and you want to change the prefix then follow this tutorial.

  11. blurped says:

    Great guide, works like a charm. One question- why did you leave ‘wp_’ in the new prefix? Seems like a whole lot of effort to change your table prefixes but still leave that fragment in there. Just remove it completely or replace it with something else more random (like ‘eh_’ or whatever)

  12. yerom says:

    Well, everything is just fine… But when i’m go back to my site, it makes me the 5 minutes install again…
    I think i missed something.

    Anyone had the same issue ?
    Tks !

  13. ideal ismail says:

    hi Admin,

    Regarding the naming convention for the table prefix, “Note: You can only change it to numbers, letters, and underscores. Feel free to mix uppercase and lowercase.”

    this is not true. You CAN’T use uppercase as it will wreak havoc with your database entries. i personally encountered this and the solution is to restrict to using numbers, underscores and lowercase letters.

    many other people have encountered this. a quick google search gave me the following:
    http://wordpress.org/support/topic/case-sensitive-wp_table_prefix?replies=1
    http://stackoverflow.com/questions/9827164/wordpress-keeps-redirecting-to-install-php-after-migration
    http://esdev.net/wordpress-error-you-do-not-have-sufficient-permissions-to-access-this-page/#.Ui_pHtJkMwB

    hope that helps.

  14. Steve says:

    Couldn’t you just back everything up,
    export the DB to a DBbackup.sql file
    open it with a text editor.
    do a global search and replace and replace wp_ with mynewprefix_
    Save the file,
    drop all the tables in the DB
    and import the new DBbackup.sql?

    • Editorial Staff says:

      You could do that :)

      • Steve says:

        Update – The global search and replace works. However, it might work too good. One of the side effects is that it returns all of your widgets to the default (fresh install) state.

        Luckily – it returns them to the “inactive section” so you don’t have to completely re-do them. My lesson learned was to take a screen shot of the dashboard (before) so it’s easier to remember where you had them all.

  15. Ahsan says:

    Hey after changing table prefix and table name from mysql when i refresh the website it says website has a redirect loop, what should i do?

  16. Katie says:

    Tried to do this on a multisite database install… totally failed. I seemed to put all the queries in correctly, but I got errors and at the end of all the steps my site was just redirecting itself indefinitely…

  17. Mike says:

    I did these changes as instructed but now I can’t get to my admin page.

  18. Mark Pescatrice says:

    Well after about 30 minutes of sweating bullets, I was able to do this. I made one tiny typo on wp-config.php. but otherwise it went smoothly. I did use Duplicator to create a backup before starting all of this.

    I recommend users to do the following additional steps:

    Before starting, put a dummy index.html in the root folder of your WP install, and renaming index.php to index.php.tmp (or something similar). After making a tiny typo in the wp-config.php file, I found myself at the WP install page.

    After you are done, rename index.php.tmp to index.php and remove or rename the index.html page.

    Thanks for the great article. I’m curious to see how the changes will affect the spam count.

    Mark Pescatrice

  19. Al Lemieux says:

    In terms of process, do I make these security changes locally first? Or do I make them on WordPress?

  20. Corey says:

    What about things like this? Do we need to change the wp in this, or only when it starts with wp?

    dismissed_wp_pointers

    • Editorial Staff says:

      You should be able to change it to whatever you like.

      • ana says:

        I am confussed about ‘ _site_transient_timeout_wporg_theme_feature_list ‘ these, do I have to change wp here as well? and if yes then plz give an example.

        • AMSGATOR says:

          You should only have to change it if wp is followed by an underscore (i.e., wp_)

  21. Eric says:

    Awesome information security for wp anti thief..But is there any free plugin or software to automate these processes?

  22. Orion says:

    just tried this out, everything changed according to your instructions, hopefully this keeps the russians out….for a while at least.. Thank you for posting.

  23. Benno says:

    Thanks, worked great on my new blog!

  24. Debra says:

    I must be a total idiot because I sure can sort this out. Can’t even find the wp database. Geez this is frustrating

  25. Scott Semple says:

    Successfully changed the database prefixes, but now I can’t sign in?

    My ##_capabilities in ##_usermeta is for an admin: a:1:{s:13:”administrator”;s:1:”1″;}

    Thoughts on why I still can’t sign in? Thanks!

    • Editorial Staff says:

      It loads the website properly, but you can’t sign in? What error does it say… Incorrect password? or incorrect username?

  26. mckenzie says:

    thanks so much! i searched all over the internet and you are the only blog entry to get this right on the spot!!

  27. rawalbaig says:

    Please Help me I’m not able to process the last two steps For “The Option Table” I’m Here http://imageshack.us/photo/my-images/819/img00.png

    & ‘”UserMeta Table” Here http://imageshack.us/photo/my-images/84/img002o.png

    What to do next I’m not able to login my wordpress account

  28. TrentJessee says:

    Excellent security post, and well written. What would you charge to do this service for people? Thanks!

    http://trentjessee.com

  29. WesHopper says:

    @wpbeginner @WesHopper

  30. wpbeginner says:

    @WesHopper You just manually change it. Because the number vary based on the plugins you have.

  31. Keith Davis says:

    Hi Admin
    Good clear instructions but I’ve never had the confidence to attempt a database prefix change – just in case!

    You boys provide some great stuff – much appreciated.

  32. Leonco says:

    Very interesting security steps…

    But surely there has to be a security plugin that addresses
    the issue of preventing hacking.

    • João says:

      There are several plugins that do this, but the truth is that it’s always good to know how to do this yourself.

      For example I had an (apparently) buggy plugin change my WordPress database prefix just now, and i was locked out of my own WP installation.

      This simple guide showed me how to undo the damage.

  33. Vivek Parmar says:

    Thanks a lot for sharing this. it is essential to secure WordPress first before posting any content your blog.

Add a Comment

We're glad you have chosen to leave a comment. Please keep in mind that all comments are moderated according to our comment policy, and all links are nofollow. Do NOT use keywords in the name field. Let's have a personal and meaningful conversation.