Helping you build a better website
(and other interesting web stuff)

Silva Web Designs - Blog

How to Manually Change the WordPress Database Prefix

WordPress uses PHP to store and retrieve data from the database. The information stored in a WordPress database includes: posts, pages, comments, categories, tags, custom fields, users, and other site options such as site urls etc. The name of each table in WordPress is composed of two parts: a prefix and the table name. The default prefix is ‘wp_’ and this can be defined when you first install WordPress. However, sometimes you may want to change your prefix further down the line; e.g. I may want to change mine to something more unpredictable like swdSIL83VA_. This is a good idea as it will make your site much safer. Spammers and hackers run automated codes for SQL injections and initially they will try and get access to the database using the default prefix (wp_), so why not make their life more difficult by changing the default prefix?

Before you start

We recommend that you backup your WordPress Database before you performing anything suggested in this tutorial. We also recommend that you redirect your visitors to a temporary maintenance page.

Change Table Prefix in wp-config.php

Let’s get started, first 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 swdSIL83VA_

So the line would look like this:

$table_prefix  = 'wp_';

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

Change all Database Table Names

You need to access your database (most likely through phpMyAdmin), and then change the table names to the one you specified in your wp-config.php file. There are a total of 11 default WordPress tables, so changing them manually would be pain:-

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

RENAME table `wp_commentmeta` TO `swdSIL83VA_commentmeta`;
RENAME table `wp_comments` TO `swdSIL83VA_comments`;
RENAME table `wp_links` TO `swdSIL83VA_links`;
RENAME table `wp_options` TO `swdSIL83VA_options`;
RENAME table `wp_postmeta` TO `swdSIL83VA_postmeta`;
RENAME table `wp_posts` TO `swdSIL83VA_posts`;
RENAME table `wp_terms` TO `swdSIL83VA_terms`;
RENAME table `wp_termmeta` TO `swdSIL83VA_termmeta`;
RENAME table `wp_term_relationships` TO `swdSIL83VA_term_relationships`;
RENAME table `wp_term_taxonomy` TO `swdSIL83VA_term_taxonomy`;
RENAME table `wp_usermeta` TO `swdSIL83VA_usermeta`;
RENAME table `wp_users` TO `swdSIL83VA_users`;

You may need to rename other tables depending on the plugins that you have installed on your web site, you will need to rename all the tables that you have to your new prefix.

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_options` WHERE `option_name` LIKE '%wp_%'

This may return quite a few results and you will need to go through them 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_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.

Process Complete!

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.

It’s good to share

Join the discussion