3 Ways to Change the WordPress Database Table Prefix

 

 

WordPress is the strongest candidate out there when we talk about website builders. It provides almost every feature to build a professional and safe website.

Every action in WordPress is interlinked with the database:

This image demonstrates how Databases work in WordPress.
This image demonstrates how Databases work in WordPress.

Therefore, we need a secure connection to the database to keep hackers away from our valuable site data.

Even the slightest changes like the posts, settings, plugins, and everything you ever use on your WordPress site are stored in the database.

While installing WordPress, most of the common mistakes that people make is forgetting to change their WordPress Database prefix and allow attackers to run automated SQL injections. So, it’s crucial to take some precautionary measures to protect your website from being hacked.

By default, the WordPress table prefix is wp_.

Please Note Before Beginning

Changing the WordPress database prefix can be a critical task, and it’s not always a simple, easy job. You need to make sure that you don’t break your website by changing the WordPress table prefix.

Before you change the database prefix, we recommend doing it on a staging site first. So you can check thoroughly if the update worked and can safely push your staging site to the production site without any risk of data loss after changing the DB prefix. A staging site is a copy of your live site, so you can do different types of changes/testing and make those changes live with a single click.

You can create a staging site with WP STAGING.


Follow this step by step guide to safely rename the WordPress database prefix.

How to Change the WordPress Database Prefix

There are three (3) options to change and rename the database prefix of your WordPress website:

  1. Use a plugin to change the database table prefix.
  2. Rename WordPress Database Prefix using Database Query with Adminer.
  3. Rename WordPress Database Prefix using Database Query with PHPmyAdmin.

Method 1: Use a Plugin to Change the Database Table Prefix

At first, we recommend you take a backup of your website (either  with the snapshot option of Via WP STAGING PRO or manually through CPanel) to avoid any mishappening that could be caused by installing a plugin.


We can change the database prefix using a plugin. Here are a few simple steps to change the database prefix in WordPress.

  1. Go to the WordPress dashboard.
  2. Go to the ‘plugins’ tab and click on ‘add new.’
  3. Search Brozzme DB Prefix & Tools Add-ons in WordPress plugin repository.
  4. Click the install now button to install Brozzme DB Prefix & Tools Addon on your site.
  5. Click the activate button to activate this plugin.

updatewp database prefix via plugin

Now, the plugin is activated, and we can start working on changing the WordPress database prefix right away.

6. Go to tools and click DB Prefix. See the current prefix of your database, and change the prefix to the new one.

wordpress database prefix

If your wp-config.php file is not writable, you will get an error notice, because then this plugin can’t change the database prefix in the wp-config.php file.

Click on the “Change DB Prefix” button, and you’re done.

Method 2: Rename WordPress Database Prefix using Database Query with Adminer

Change The Table Prefix in wp-config.php

Then change the table prefix like wp_ to wp_testing123_, in the wp-config.php file, located in the WordPress directory’s root folder.

To edit the wp-config.php file, you can log in via FTP or SFTP using Filezilla or any other FTP client. You can find FTP details on cPanel (depend on your hosting, as some host doesn’t have cPanel but they use their customized panel)

Search for this line in the wp-config.php file:

$table_prefix  = 'wp_testing123_';

This picture shows where the table prefix can be found in the wp-config.php file.
This is how the line looks like in the wp-config file.
This picture shows how the updated line looks like.
This is how the updated line looks like.

You can only add a prefix with numbers, letters, and underscore. Once you are done with the changes in the wp-config.php file, save it.

Rename Database Tables

If you are using Adminer, you can find the SQL command button in the upper left corner, select the button.

This picture shows a screenshot of where to find the SQL command button on Adminer
Where to find the SQL command button

If you like to change the WordPress table prefix of the database on an existing WordPress website, you can use the SQL query below. Update the header and the SET properties to your needs:

  • databasename
  • oldprefix_
  • newprefix_

That is the SQL query:

SET @database  = "databasename";
SET @oldprefix = "oldprefix_";
SET @newprefix = "newprefix_";
 
SELECT
    concat(
        "RENAME TABLE ",
        TABLE_NAME,
        " TO ",
        replace(TABLE_NAME, @oldprefix, @newprefix),
        ';'
    ) AS "SQL"
FROM information_schema.TABLES WHERE TABLE_SCHEMA = @database;

If you update the query and the properties, you’ll get that result:

A SQL query to update the WordPress database Prefix
Use this SQL query to update the WordPress database Prefix

Execute that query and as a result, you’ll get further queries like this:

RENAME oldprefix_options to newprefix_options;
RENAME oldprefix_users to newprefix_users;

Copy these queries and execute them again to rename the tables to the new table names.

Use this SQL query to rename WordPress database tables
Use this SQL query to rename WordPress database tables

Rename Prefix in usermeta Table

After renaming the tables, you also need to replace some values in the table *_usermeta and *_options by using the queries below.

Don’t forget to update the highlighted strings.

To replace the values in the *_usermeta table use this query:

UPDATE `newprefix_usermeta`
SET meta_key = REPLACE(meta_key, 'oldprefix_', 'newprefix_')
WHERE meta_key LIKE 'oldprefix_%';
Use this SQL query to rename the table prefix in table wp_usermeta
Use this SQL query to rename prefix in table wp_usermeta

Update Prefix in options Table

The last step is to replace the values in the *_options table.

Use this query:

UPDATE `newprefix_options` 
SET option_value = replace(option_value, 'oldprefix_', 'newprefix_') 
WHERE option_name LIKE 'oldprefix_%';
This picture shows the queries to replace the values in the options table
Add the queries to replace the values in the options table

That’s all.

Method 3: Rename WordPress Database Prefix using Database Query with PHPmyAdmin

Change the Table Prefix in wp-config.php

Then change the table prefix like wp_ to wp_testing123_, in the wp-config.php file, located in the WordPress directory’s root folder.

To edit the wp-config.php file, you can log in via FTP or SFTP using Filezilla or any other FTP client. You can find FTP details on cPanel (depend on your hosting, as some host doesn’t have cPanel but they use their customized panel)

See this line on the wp-config.php file.

$table_prefix  = 'wp_testing123_';

You can only add a prefix with numbers, letters, and underscore. Once you are done with the changes in the wp-config.php file, save it.

Rename Database Tables

If you are using CPanel access PHPMyAdmin, on the left side, you can see your databases. If you are not using cPanel, you can contact your host and access the database of your site.

Select the database where you want to change the table prefix specified in the file wp-config.php and then do the steps below.

  1. Select the database.
  2. Check on ‘check all’ checkbox to select all tables.
  3. Click on the drop-down and select ‘Replace table prefix.’

change wordpress table prefix

  1. Replace old prefix to new prefix.’

update wordpress database prefix

After entering a new prefix, click on continue, and it will change the prefix in the database.

Rename Prefix in Options Table

Search wp_  prefix in the options table using this query.

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

Replace all old prefixes to new prefixes.

Update Prefix in UserMeta Table

We need to search wp_ as a prefix on the usermeta table and replace it using this query.

SELECT * FROM `wp_testing123_usermeta` WHERE `meta_key` LIKE `%wp_%` 

database prefix of wordpress site

Another option is, if you want to change the database prefix using a database query, you can use this single query to make it work.

If you like to change the WordPress table prefix of the database on an existing WordPress website, you can use the following SQL query:

SET @database = "database_name";
SET @old_prefix = "old_prefix_";
SET @new_prefix = "new_prefix_";

SELECT concat "RENAME TABLE ", TABLE_NAME, " TO", replace(TABLE_NAME, @old_prefix, @new_prefix),';') AS "SQL" FROM information_schema.TABLES WHERE TABLE_SCHEMA = @database;

This query will create another SQL query that you can use to rename all table prefixes to the new ones.

After doing that, you also need to replace some values in the table wp_usermeta and wp_options using the queries below.

UPDATE `wp_testing123_usermeta`
SET meta_key = REPLACE(meta_key, 'wp_', 'wp_testing123')
WHERE meta_key LIKE 'wp_%';

UPDATE wp_options SET meta_key = replace(meta_key, 'wp_', 'wp_testing123_') WHERE option_names LIKE 'wp_%';

That’s all.

That’s the manual method to change the database prefix.