How to change the WordPress database prefix

Steps for changing the default WordPress database setup

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

Regarding necessary changes to the wp-config.php file, let’s review the details involved. Those are covered below.

Access the wp-config.php file for your website over FTP or SSH.

Locate the wp-config.php file and open the file in editing mode.

Locate the following entry within the wp-config.php file, which starts out with the default WordPress database table prefix:

$table_prefix = ‘wp_’;

Replace wp_ with something else. In this example below, we have replaced it with the cmj_ character string:

$table_prefix = ‘cmj_’;

Save the changes in the wp-config.php file.

2. Change the table prefix in the database

The free tool, phpMyAdmin, is a database utility that allows the management of MySQL or MariaDB databases. Using phpMyAdmin, any activities like creation or modification of databases, running of queries, and user account management tasks are handled easily — even for a non-techie person.

The phpMyAdmin interface can be accessed through SiteWorx (non-cloud) or your Nexcess Client Portal. Therefore, how you access phpMyAdmin depends on the type of Nexcess plan that you have. Because we have provided the phpMyAdmin as a management tool, Nexcess makes efficient to manage your databases from the Nexcess Client Portal.

Access the tool phpMyAdmin and select the respective database for the application.

Click on the database name in the menu to the left to unfold all tables.

Select all tables that start with the wp_ character string. You should have 12 table selected in total.

With those tables selected, open the dropdown menu and select Replace Table Prefix. Replace the table prefix as needed.

Type in wp_ in the From field, and the new database table prefix in the To field, which will be cmj_ in our example.

Click Continue to make the change.

3. Change all references to include the new database table prefix

To complete the table prefix change, you will need to replace old table prefix with the new table prefix in the settings that are stored in your database.

Open the SQL tab.

Execute the following commands in the SQL tab of the phpMyAdmin utility or via the MySQL/MariaDB command line (pseudo code example):

update NEWPREFIX_usermeta set meta_key = 'NEWPREFIX_capabilities' where meta_key = 'OLDPREFIX_capabilities';

update NEWPREFIX_usermeta set meta_key = 'NEWPREFIX_user_level' where meta_key = 'OLDPREFIX_user_level';

update NEWPREFIX_usermeta set meta_key = 'NEWPREFIX_autosave_draft_ids' where meta_key = 'OLDPREFIX_autosave_draft_ids';

update NEWPREFIX_options set option_name = 'NEWPREFIX_user_roles' where option_name = 'OLDPREFIX_user_roles';

Real example

We looked at the pseudo code above. Now let’s look at a real example. As part of your new WordPress database setup, replace the old default wp_ prefix with your new one.

In the real example below, we are replacing wp_ with cmj_ as our new database table prefix. The SQL command line would look as follows:

update cmj_usermeta set meta_key = 'cmj_capabilities' where meta_key = 'wp_capabilities';

update cmj_usermeta set meta_key = 'cmj_user_level' where meta_key = 'wp_user_level';

update cmj_usermeta set meta_key = 'cmj_autosave_draft_ids' where meta_key = 'wp_autosave_draft_ids';

update cmj_options set option_name = 'cmj_user_roles' where option_name = 'wp_user_roles';

Once the above steps are complete, click Go to run the commands and complete the change. You have set the new custom database table prefix for your site’s WordPress database.

All is well and good now. The prefix on your WordPress database tables has been successfully changed. Thus, the custom prefix setup is completed, giving you an extra layer of security.