Change WordPress Database Prefix

In one of my earlier post I have mentioned that it is good to change the wordpress database prefix to secure your wordpress installation.

By default wordpress database tables have [code]wp_[/code] prefix. But it is recommended to change this prefix other word, just for an example: [code]dfdh45_[/code].

Main benefit for this way is no one can guess your database table names. :)

You can change this prefix while installing the wordpress. But if you have not changed and want to change the prefix to make your site secure then you are at correct place now.

This article is about to explain “How to change WordPress database Prefix for Existing site”. This process will be covered in 4 easy steps. You should be somewhat familiar with the SQL for this process.

Make sure you backup your database before executing any of the below steps. Because that will be useful if you fail at any step in this process.

1. Edit your wp-config.php

First and very easy step to edit your wordpress config file ([code]wp-config.php[/code]) which should be at root directory of the site.

You need to change the value of the database prefix variable, for now it will be [code]wp_[/code] and you have to change it with anything else which is hard to guess, for example [code]fdgd3s_[/code].

[cc lang=”php”]
$table_prefix = “wp_”;
//to
$table_prefix = “fdgd3s_”;
[/cc]

2. Rename Existing Tables

Now its time to rename all your tables. If you have not installed any plugins which creates own tables then you just have to rename default wordpess tables. You need to perform below queries.

[cc lang=”mysql”]
Rename table wp_commentmeta to fdgd3s_commentmeta;
Rename table wp_comments to fdgd3s_comments;
Rename table wp_links to fdgd3s_links;
Rename table wp_options to fdgd3s_options;
Rename table wp_postmeta to fdgd3s_postmeta;
Rename table wp_posts to fdgd3s_posts;
Rename table wp_terms to fdgd3s_terms;
Rename table wp_term_relationships to fdgd3s_term_relationships;
Rename table wp_term_taxonomy to fdgd3s_term_taxonomy;
Rename table wp_usermeta to fdgd3s_usermeta;
Rename table wp_users to fdgd3s_users;
[/cc]

3. Update Relevant Entries in Database

WordPress stores few table name in two tables. So we have to update those rows with the new database prefix. Just have a look at below for which tables to update.

First need to edit one row from the wp_option table which is now fdgd3s_options. Execute below query in database and you will get one row with old database prefix in option_name fields. Edit this row with the new database prefix.

[cc lang=”mysql”]
SELECT * FROM `fdgd3s_options` WHERE `option_name` LIKE “wp_user_roles”;
[/cc]

Above step must be completed otherwise you will not be able to login to your wordpress backend.

Now perform below query in user_meta table, You will find few rows which you also needs to update new database prefix.

[cc lang=”mysql”]
SELECT * FROM `fdgd3s_usermeta` WHERE `meta_key` LIKE “%wp_%”;
[/cc]

Update all fetched rows with the new table prefix.

4. You are Done.

Yes you are done with this process. Now your wordpress tables are now more secure than earlier. Make sure you are able to login to back end.

Conclusion

If you do not have custom table prefix then its recommended to change the database prefix which makes you more secure than before. Your thought on this?