Defining custom table prefix during WordPress installation is very important. Because we know (those who’re installed WordPress at least one time by his own) by default WordPress give us a table prefix called wp_. See the screenshot below –
As a general WordPress user, WordPress developer or a hacker is very familiar on it. That’s not a problem though! But what if, you left your database table prefix as default by anyway and “Mr. Hacker” find that in your database, ha? He left your site as is, as you did also, right? 😛 Hey! Don’t be so silly!
So, if you did that mistakenly or anyway and after you’ve done all the setup, settings completed there is no way to roll back the installation rather than deleting the entire site and install it again. But you could rename your default table prefix using a simple MySQL query.
So, here I’m going to show you the simplest (not much) method of how you could rename your WordPress default table prefix manually. It could be in localhost or in remote server but the method is same. Let’s dig in –
1. Open your WordPress database where you’ve already install your WordPress website and check the default prefix exists or not, see the screenshot below –
2. If yes, then – click on SQL tab. See the screenshot below –
3. After couple of seconds SQL tab will give you a nice window where you could write any SQL query. See the screenshot below –
4. But don’t try to be over smart with SQL or your whole site will be mess-up. Now it’s time to write our appropriate MySQL query to change the default table prefix to our own. Assuming that my new prefix will be new_ and it will replace the default wp_. That’s the main point of this article.
5. We know by default WordPress install 11 tables with our given/default prefix name. If you don’t know yet and wants to know which tables are install during installation, please check back step 2 in the above.
6. Now the SQL query matter – we’re going to use MySQL RENAME TABLE… To… query to perform our job. The step is very simple. Simply write the code in the SQL tab and hit on Go button as I’m writing below to accomplish my job –
RENAME TABLE wp_commentmeta To new_commentmeta;
7. Now check your table listed in the database from the left side panel. The prefix has been changed. But other tables are unchanged with their default prefix. See the screenshot below –
8. But again think about that, we’ve now 10 tables remaining that needs the prefix to be changed. What do you think? How should we accomplish this at once? Again the simple way, we should now perform the same query but for 10 tables in 10 individual lines with their old and new prefix and name. Let’s write –
Rename TABLE wp_comments TO new_comments;
Rename TABLE wp_links TO new_links;
Rename TABLE wp_options TO new_options;
Rename TABLE wp_postmeta TO new_postmeta;
Rename TABLE wp_posts TO new_posts;
Rename TABLE wp_terms TO new_terms;
Rename TABLE wp_term_relationships TO new_term_relationships;
Rename TABLE wp_term_taxonomy TO new_term_taxonomy;
Rename TABLE wp_usermeta TO new_usermeta;
Rename TABLE wp_users TO new_users;
Now hit Go again. You can now see the left side panel and all the tables name has been changed successfully. Easy ha?
9. But this is not ended here, some more steps remain on the fly.
10. Most importantly you should now change you table prefix in the wp-config.php file, in line 66. See the screenshot below –
Now, refresh your browser and see your website working or not. If not then you should follow some steps to get rid of the problem. Check out –
a) Go to your new_options table and find the ‘wp_user_role’ option in ‘options_name’ column. Just double-click on it and rename by ‘new_user_role’ or whatever your choose earlier.
b) Go to your new_usermeta table and find the options wp_capabilities, wp_user_level in ‘meta_key’ column and change their prefix as well followed by the above step. Now check your website and I hope you site is now ready to go.
Note: You should check all of your tables that does not contain anything that start with the old prefix. If it does, your site definitely down again near future or do not up in the first time. As I’m writing this article based on a fresh installation so I don’t have many options, meta, key values in my database but if your website is old and you want to rename your prefix then you have to find out more.
Lastly and most importantly, do not forget to back up your database before you do anything in it. It’s really good practice to back up database before any modification. I guaranty it’ll make your WordPress website secure from any kind of harmful attract.
That’s all I’ve for this article.
So, If you’ve enjoyed my article, please don’t hesitate to share this with your friends who live with WordPress. 🙂
Cheers! (Y)
You can find the video replica of this post here.
