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 a WordPress site is interlinked with the database:

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, people’s most common mistakes are forgetting to change their WordPress Database prefix and allowing attackers to run automated SQL injections. So, taking preventive measures is crucial to protect your website from being hacked.

By default, the WordPress table prefix is wp_.

Please Note Before Beginning

How to Change the WordPress Database Prefix

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

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

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

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

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.

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

You’re done by clicking the “Change DB Prefix” button.

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

Change the Table Prefix in wp-config.php config file

Change the table prefix value in the wp-config.php file 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 (depending 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_';

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 and select the button.

You can use the SQL query below to change the database’s WordPress table prefix on an existing WordPress website.

Update the SET properties to your needs:

• database name
• oldprefix_
• newprefix_

That is the SQL query:

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

After customizing the query, you’ll get something like this:

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

RENAME oldprefix_options to newprefix_options;
RENAME oldprefix_users to newprefix_users;

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

Rename prefix in user meta 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_%';

Update prefix in wp_options Table

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

Use this query:

UPDATE wp_options SET option_name = replace(option_name, 'wp_', 'new_') WHERE option_name LIKE 'wp_%';

That’s all.

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

Change the table prefix in the wp-config.php.

Change the table prefix in the wp-config.php file 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 (depending 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

Suppose you are using cPanel, and access PhpMyAdmin. On the left side, you can see your databases. If you are not using cPanel, contact your host and access your site’s database.

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 the ‘check all’ checkbox to select all tables.
3. Click on the drop-down and select ‘Replace table prefix.’

4. Replace the old prefix with the new prefix.’

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

Rename Prefix in Options Table

Search the wp_ prefix in the options table using this query.

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

Replace all old prefixes with 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_%'

Another option is that 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 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_', 'new_')
WHERE meta_key LIKE 'wp_%';

UPDATE wp_options SET option_name = replace(option_name, 'wp_', 'new_') WHERE option_name LIKE 'wp_%';

That’s all.

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

Can Not Login to WordPress Admin Dashboard After Renaming Table Prefix

This is the most popular error user are running into when renaming the WordPress table prefix.
Verify that you updated the prefix of all these column names and that you don’t overlook one of them:

In the Table wp_option

• wp_user_roles

In the Table wp_user_meta

• wp_capabilities
• wp_user_level

After renaming the prefix of these columns to the same prefix of the table names then you will be able to login again to your WordPress website.

Related Articles

• How to Change WordPress Table Prefix of MySQL Database
• Update WordPress Core Manually if Automatic Update Does Not Work
• How to Create and Add Admin User With MySQL in WordPress