Security

How to Change Your WordPress Table Prefix

August 3, 2011   ·   By   ·   14 Comments   ·   Posted in Security, Wordpress

Lately, WordPress users seem to have had an increased interest in security – and with good reasons. Yesterday’s timthumb.php vulnerability, plus a slew of others in the past few months have really put most site owners on edge.

Often it’s recommended that as a WordPress site owner, you change your WordPress database table prefix to something other than wp_. It’s not a bad idea – in certain situations, doing so might prevent a hacker from gaining more access to your site, or limit his destructive capability, and it’s a very easy thing to change.

If You’re Installing WordPress

If you’re installing WordPress, and you havent run through the install process, changing your table prefix is incredibly simple. During the install process, just set the Table Prefix to anything you’d like:

If You’ve Already Installed WordPress

If you’ve already installed WordPress, you can still change your table prefix, but it’s a little more complicated. You’re going to need to have access to your database through PHPMyAdmin or a similar system.

  1. First, open your wp-config.php file (You’ll need to download this via FTP, or get at it from your host’s file manager). This file is usually located in the web root of your site.
  2. Now, scan down until you see a line that starts with ‘$table_prefix’. It will look something like this:
    /**
     * WordPress Database Table prefix.
     *
     * You can have multiple installations in one database if you give each a unique
     * prefix. Only numbers, letters, and underscores please!
     */
    $table_prefix  = 'wp_';
    

    Change the value between the quotes (after the = sign) to whatever you’d like:

    /**
     * WordPress Database Table prefix.
     *
     * You can have multiple installations in one database if you give each a unique
     * prefix. Only numbers, letters, and underscores please!
     */
    $table_prefix  = 'mysecretprefix_';
    
  3. Upload the file to your site, replacing the old wp-config.php file (If you made these changes from the built in file editor for your host – like cPanel, you just need to click the “save” button).

    At this point, your site will totally stop working. Yikes! Fortunately, we can fix that by changing some things in the database (Remember how you were supposed to make sure you had database access before we started? If you don’t, change wp_config.php back to the way it was, quick!).

  4. Fire up PHPMyAdmin (or the MySQL client of your choice), and connect to the database for this WordPress install.
  5. Run through each table starting with wp, and rename it, as follows:
    1. Click on the table name in the left sidebar
    2. Click on the “Operations” tab
    3. Change “wp_” to “mysecretprefix_” in the upper right field
    4. Repeat for each table
    5. At this point, your site should be working properly again, with one important caveat: You get a permissions error when you try to log in. That leads us to our last set of steps:

      Choose the mysecretprefix_usermeta table, and look for a row with a user_id of 1 (or whatever your user’s id is – it’s probably 1), and a “meta_key” value of “wp_capabilities”. Once you’ve found this. Click the pencil toward the left (edit)

    6. Replace the wp_ in the meta_key row to “mysecretprefix_”
    7. You need to make this same change to 2 more records – wp_user_level (located in wp_usermeta – again, make sure you’re changing it for the right user), and wp_user_roles, which is in the mysecretprefix_options table. Both of them should have wp_ replaced with mysecretprefix_.
    8. All done!

    If You’re Installing WordPress, and You’re Not Interested in all that Hassle:

    This one probably should have gone first: The plugin WP Security Scan from WebsiteDefender will do all the dirty work for you in most cases. Just go to the “Database” page, and switch your prefix.

14 Comments
  1. Ravi Vamos

    Always worked with the db …never knew abt the plugin..thx man ..you saved my day:)

  2. nice dude..you save my day. I working on wordpress right now. I’m wondering why my simple query don’t work :
    $posts = $wpdb->get_results(“Select * from wp_posts”);

    when I look my wordpress tables , I found out that wp_84wrw6_ instead of wp_ as default .

    Thank you for sharing your post.

  3. WordPress 3.2.1. I tried your process, but no joy. The wp_user_level and wp_user_roles weren’t to be found, so I just changed all stuff with a “wp_” prefix to my new prefix. Cannot log on now.

    Has other stuff changed since you posted this process?

    Many thanks,

    Dan

  4. Quite Simply, YOU ROCK! Thanks so much for posting this the way you did. I literally got my wp changed in 10 minutes across my site. Thanks again!

  5. Thanks for the post – great info .
    There are however also other entries , like *_user-settings, *_user-settings-time, *dashboard_quick_press_last_post_id . Those might be minor and not important – but as long as you describe the process, it might be helpful to replace them all (best way is to query a search on wp_ and change all entries.. )

  6. I always knew that changing the prefix manually was a pain – especially on an existing site that has additional tables. I have always used the plugin you recommend.

    What, if any, is the downside of using a plugin to do all the work?

    Thanks!
    Paul.

  7. I’m years late to the conversation, but just in case anybody’s reading this for reference:

    gtwebworx – it’s almost always a really bad idea to put explicit SQL in your WordPress code. The WordPress API should let you do most things you’re likely to want to do and, by using the API, you isolate yourself from any table changes. Your case is a perfect example.

    Dan – for various weird reasons, I just had to do exactly this on a WP 3.2.1 installation, even though it’s now 18 months out of date. My guess is that a) the wp_user_level etc values were hiding there in your table (they definitely exist in 3.2.1, and in 3.5.1), probably on another page – the _options table can get quite big and b) the fact that the installation’s now using a new prefix now and so can’t find its user settings is almost certainly the cause of the problem you’re now seeing.

    I had to do this change yesterday for a client site when I moved it from dev to live. Usually when I change the table prefix at this stage I do a search & replace on the database backup file before I import it. In this particular case though it was running a theme that, for whatever crazy reason, decided to prefix all its options with wp_ … but they were FIXED at wp_ … they didn’t obey the table prefix. So after I’d done my search & replace the theme’s options were renamed but the theme was looking for them with the original names so it defaulted all its settings. Had to do the whole database import again, far more carefully the second time around. Next time I’ll probably use a plugin!!

  8. I have a plugin and I want to change a particular column name of that plugin.But while doing that the column deleted automatically .Any on that

Submit a Comment