How to change the WordPress database prefix & improve security

Changing the WordPress database prefix can help you improve security. It’s a technique that falls under the security by obscurity strategy. The premise of this strategy is that obscurity can slow down attacks, perhaps enough for bad actors to move on to easier targets.

Taking steps to secure your WordPress is obviously very important, and it is understandable that making database changes might make you feel uneasy. But fear not! We have laid out a step-by-step plan that will help you ensure you’re keeping right on track.

Let’s get to it then, shall we?

Change WordPress database table prefix

You can change the default WordPress database prefix in one of two ways. If you have not installed WordPress yet, you can simply specify a different database table prefix during the installation process. On the other hand, if you have already installed WordPress, some manual intervention will be required.

This article will explain both.

How to choose a new database prefix

The default prefix for WordPress tables is wp_. WordPress uses this to make it easier to find WordPress tables. The problem is that it makes it easier for everyone to find – including bad actors. By ‘obscuring’ the tables, we, therefore, make them that much harder to find for anyone who is not authorized to view the WordPress tables.

As explained in the introduction, this security measure is called security by obscurity. It involves hiding known elements to make it more difficult for bad actors to suss out your network during their reconnaissance phase. Hiding your WordPress login URL is another prime example of security by obscurity.

It’s important to note that changing the WordPress table prefix is not a be-all and end-all security measure. It only works if it is part of a broader security strategy for your WordPress site.

When it comes to choosing a new prefix, try to stick to letters, numbers, and underscores only. It is equally important not to use dictionary words as this would defeat the purpose. Be creative; you do not need to remember this value.

Fresh WordPress installation

Changing the db prefix during WordPress installation is a walk in the park. One thing to keep in mind here is that many hosting providers install WordPress themselves, and as such, you will not be able to change the table prefix at this stage.

If you do get the opportunity to install WordPress yourself, simply enter the desired value in the Table Prefix textbox during the database configuration option of the installation procedure.

Existing WordPress installation

If you have an existing WordPress installation – whether it’s been operational for a week or a decade, you can still change the table prefix of each table in the WordPress database. It’s just going to take a bit more work than it does when installing WordPress.

Before you start

Before you start anything, it is wise to take a backup of your WordPress database and a backup of the wp-config file. Store these on a pen drive or some other external media and lock them away. We also recommend that you prepare a temporary redirection so that you can redirect visitors during the process.

In this tutorial, we will recommend making the changes to a staging environment first. However, if you still feel uncomfortable, you might want to consider hiring a professional instead.

Step 1: Set up your staging environment (if you don’t have one)

Setting up a staging environment will help you smooth your nerves. It’ll allow you to make sure you’ve got all the changes right before applying them to your live website. Some hosting providers offer staging functionality. Alternatively, you may want to set up an XAMPP WordPress staging environment.

This step is purely OPTIONAL and will not affect the end result. It’s a matter of extra caution – like taking out an insurance policy if you will.

If you’re taking this route, execute the following steps on your staging environment first. If all goes well, you can migrate the changes to the live environment following your usual procedures.

Step 2: Change Table Prefix in wp-config.php file

The wp-config.php file is a PHP file that lists different WordPress configuration settings. The setting that interests us is the variable $table_prefix. The setting should look like this:

$table_prefix=’wp_’;

Open your wp-config.php file, which you’ll find in the WordPress root directory, and change the $table_prefix variable value from wp_ to something else, such as ra66be81_.

After the change, the line in wp-config.php should look like:

$table_prefix=’ra66be81_’;

Make sure you save any changes.

WordPress refers to this file all the time. If you upload it now, WordPress will not be able to access the database until you change the WordPress database prefix in MySQL. You can upload it now or after you’ve finished executing the replacement SQL queries.

Step 3: Change all WordPress database tables name

Next, we will be making changes to the database. Here, we will change each table’s default database prefix to match the value we entered in the wp-config file.

You’ll need access to your WordPress MySQL database to change the database table’s name. In most cases, you should be able to access the WordPress database through phpMyAdmin from your hosting provider’s backend.

Many WordPress hosting providers provide cPanel access with their plans. However, this is not always the case. Refer to your hosting provider’s documentation if you’re unsure.

Depending on your hosting configuration, you might have more than one database in your MySQL server. In some cases, the WordPress database will have some random name, which might make it difficult to find.If you have a hard time locating the WordPress database, look for the default tables. By default, the WordPress MySQL database contains 11 tables. Below is a screenshot of WordPress database tables in phpMyAdmin.

As you can see from the above screenshot, they all start with the wp_ prefix. Keep in mind that you might have additional tables if you installed a plugin that adds its own tables to the database.

Renaming the tables

You can rename the tables manually one by one by running the RENAME SQL query in the following format:

RENAME table `[old_table_name]` TO `[new_table_name]`;

Where old_table_name is the existing table name, and new_table_name is the table name with the new prefix.

Alternatively, you can copy and paste the queries below to rename all tables in one go. Two things to keep in mind:

1. You need to replace ra66be81_ with the prefix you specified in the wp-config.php file
2. If you have other tables in your WordPress database using the same prefix, such as tables created by plugins, you can add them to this query.

To use the below SQL queries, click on the SQL tab (highlighted in the above screenshot) and then copy and paste the queries, keeping in mind the two points mentioned above.

RENAME table `wp_commentmeta` TO `ra66be81_commentmeta`;
RENAME table `wp_comments` TO `ra66be81_comments`;
RENAME table `wp_links` TO `ra66be81_links`;
RENAME table `wp_options` TO `ra66be81_options`;
RENAME table `wp_postmeta` TO `ra66be81_postmeta`;
RENAME table `wp_posts` TO `ra66be81_posts`;
RENAME table `wp_terms` TO `ra66be81_terms`;
RENAME table `wp_term_relationships` TO `ra66be81_term_relationships`;
RENAME table `wp_term_taxonomy` TO `ra66be81_term_taxonomy`;
RENAME table `wp_usermeta` TO `ra66be81_usermeta`;
RENAME table `wp_users` TO `ra66be81_users`;

Once ready, click on Go at the bottom of the page. This will execute the SQL queries and replace each table prefix one by one.

Step 4: Change database entries

Now that we have changed the tables prefix, we need to update database entries that refer to tables using the old prefix. This will ensure that our WordPress website will continue to function.

The Options table

WordPress stores all the global options in the Options table. In this table, you’ll find some entries that also need to have their prefix changed. To retrieve a list of all the entries that are using the wp_ prefix (and therefore need to be updated), use the following SQL query:

SELECT * FROM `ra66be81_options` WHERE `option_name` LIKE '%wp_%'

Do remember to change ra66be81_options to reflect the prefix you set in Step 3. The query might return many entries that need to be changed. It is essential to change them all to avoid experiencing issues.

The UserMeta table

The WordPress user meta table contains all the information about registered users, such as personalized settings. In this table, you’ll also find several entries that need to have their prefix changed, just like we did with the Options table. To retrieve a list of all entries that are using the wp_ prefix, use the following SQL query:

SELECT * FROM `ra66be81_usermeta` WHERE `meta_key` LIKE '%wp_%'

Do remember to change ra66be81_usermeta to reflect the prefix you set in Step 3. The number of entries returned when running this query might vary. It all depends on how your website is configured, such as how many WordPress plugins you have installed, etc.

Backup and test the changes

That should be it. Before you make any further changes, take a backup of WordPress again and proceed with testing the changes. Visit at least one page and one blog post, and try to log in to the WordPress admin dashboard (wp-admin section) to confirm that everything is working fine.

If you’ve carried out the changes in a staging environment first, make sure everything is good to go before pushing to live.

More security by obscurity

Now that your WordPress database is more secure, take security a step further and install Melapress Login Security. This plugin allows you to change your WordPress login URL, set password policies, limit login attempts, and much more!

Frequently Asked Questions