How To Change Your WordPress Database Table Prefix Easily

how-to-change-your-wordpress-database-table-prefix-easily

WordPress started in 2003 with a single bit of code to enhance the typography of everyday writing and with fewer users than you can count on your fingers and toes. Since then it has grown to be the largest self-hosted blogging tool in the world, used on millions of sites and seen by tens of millions of people every day.

Recently we have discussed about How To Increase the Loading Speed of WordPress Site Easily. Today I’m going to share a very useful tip to my readers. If your table prefix is “wp_” or “wp1_” or even “wordpress_”, then changing it will bring your WordPress site security to a higher level.

By default Fantastico installation sets “wp_” as a prefix for each WordPress table name. Since this is a known vulnerability, malicious users can exploit your data easily.

They specifically look for the wp_options table, because it will alter your WordPress site look. Through wp_options they can set the url to redirect to their sites, leaving you the impression that your site was defaced.

If you already have a WordPress site, take a look at either your config.php file or go to phpMyAdmin in cPanel to check your tables names.

Attackers can easily send malicious code using JavaScript injecting SQL targeting your wp_ based tables. To make your wordpress site really secure, change the prefix to something that is difficult to guess. I would pick something almost like a password, except you are limited here to only numbers, letters, and/or underscores.

I strongly recommend you to do change the prefix, if it is plain wp_. Just follow the next 6 steps and you should be in good shape. I have tested these steps already with a WordPress 3.3.1 installation:

1- Take a backup

Since this is a change in your WordPress table structure, you will have to take a backup first. In cPanel click on the “Backups” icon and click on “Generate/Download a full Backup” and proceed with a “Home Directory Backup”.

2- Edit your wp-config.php file and change

$table_prefix = ‘wp_’;

to something like

$table_prefix = ‘akhil875489_’;

3- Change all your your WordPress table names

Go to phpMyAdmin and choose your WordPress database. Click on sql menu item and enter the command to rename all your tables. Do it one table at a time.

Note: You might have more tables that start with “wp_” prefix, change all the tables.

Every time you paste one line into the SQL window, click on GO and see the table name change on your left. Keep changing the table names until all your wordpress tables have the new prefix.

Rename table wp_commentmeta to akhil875489_commentmeta;
Rename table wp_comments to akhil875489_comments;
Rename table wp_links to akhil875489_links;
Rename table wp_options to akhil875489_options;
Rename table wp_postmeta to akhil875489_postmeta;
Rename table wp_posts to akhil875489_posts;
Rename table wp_terms to akhil875489_terms;
Rename table wp_term_relationships to akhil875489_term_relationships;
Rename table wp_term_taxonomy to akhil875489_term_taxonomy;
Rename table wp_usermeta to akhil875489_usermeta;
Rename table wp_users to akhil875489_users;

4- Edit wp_options

Then you need to edit in the akhil875489_options table ( formaly wp_options ) table

Click on the table name link and then click on “Browse” menu item. You will see all the data stored in that table. Look under the option_name column header and change wp_user_roles to akhil875489_user_roles. You will be able to change it by clicking on the edit button for that record.

Note: wp_user_roles might not appear on the 1st page under options. Keep looking for it, otherwise you will not be able to login afterwards.

5- Edit wp_usermeta

And finally apply changes to akhil875489_usermeta formally ( wp_usermeta). Don’t miss any records.

In phpMyAdmin highlight akhil875489_usermeta link and click browse menu.

Change every value under meta_key column header, that starts with the old prefix wp_ to the new prefix akhil875489_ the number or records might be different for your web site.

I have changed the following in my installation:

wp_capabilities to akhil875489__capabilities
wp_autosave_draft_ids to akhil875489__autosave_draft_ids
wp_user_level  to akhil875489__user_level
wp_usersettings to akhil875489__usersettings

You can run a query in phpMyAdmin to find out how many records you need to change:

Simply click on the search link, add the following search condition (meta_key like ‘wp_%’ ), and click the GO button. (see image) This will get you the exact number of record you need to update.

how-to-change-your-wordpress-database-table-prefix-easily

6- Done! Test your WordPress site now

It should be a lot more secure giving you the peace of mind to focus on blogging.

Oh, one more thing. Do another backup:-)

If I can be of any help, just let me know.

4 comments

  1. Hey akhil, is their any plugin can do this?

  2. I’m working on my first WP site. I found this hardening step after the install so now I want to rename the prefix. I have 3 table with fields that seem to use the wp_ prefix in them. They are: wp_commentmeta, wp_postmeta & wp_usermeta.

    1st question: Do I need to update the meta_key fields in all three tables to use my new prefix? I guessing yes but am very new to WP.

    2nd question: The fields that begin with wp_ are easy to find and replace. Do I need to change fields that have _wp_ in them? ex: dismissed_wp_pointers or _wp_attached_file

    Thanks for you help

Leave a Reply

Your email address will not be published. Required fields are marked *

*