How to Change the WordPress Database Prefix
Every WordPress database table is prefixed with wp_ by default. This means that if there is a theme, plugin, or a version of WordPress with a vulnerability, hackers can run automated attacks across tens of thousands of websites that use the particular software and target their databases by referencing the standard database prefix. But if you change your WordPress database table prefix, you can avoid scenarios like this and have a second layer of defense against such attacks.
Backup and Prepare
Fortunately, changing the WordPress database table prefix is quite straightforward. However, it is a very technical process and extremely sensitive to errors. We’ll walk you through exactly what you’ll have to do. But before you make any changes, create a full backup of your WordPress site, including your database.
If you don’t know how to create a full backup of your site or your database, see our other articles here that walk you through the process:
Additionally, keep in mind that although this process will take about 20 minutes from end-to-end, some of the features on your site may not function during this time.
Change the Reference
The first step when changing the WordPress database table prefix is to actually make changes to your wp-config.php file.
Your wp-config.php file creates the connection between your files and your database. The database prefix referenced in this file needs to match up with those in your database. So as a first step, download your wp-config.php file and find the row that contains wp_ and change it to something like this: wp_abc123_
The entire row should look like this:
$table_prefix = ‘wp_abc123_’;
In the example above, the _abc123 is the customization. You can use whatever you want but make sure you only use letters, numbers, and underscores.
Change the Database Table Name
Next, and this is where it gets tricky, access your database. Most folks use phpMyAdmin so we’ve included some visualizations below. But let us know if you use a different database manager and we’ll help you through it.
Once you access your databse through phpMyAdmin, click the SQL option so we can run a query what will make mass changes to your database.
You could change the tables manually. However, there are 11 default WordPress tables and other plugins or themes you use could add additional tables. So you would find yourself changing at least 11 tables manually.
To save time, after you click the SQL option, run the following command (copy and paste):
RENAME table wp_commentmeta TO wp_a123456_commentmeta;
RENAME table wp_comments TO wp_a123456_comments;
RENAME table wp_links TO wp_a123456_links;
RENAME table wp_options TO wp_a123456_options;
RENAME table wp_postmeta TO wp_a123456_postmeta;
RENAME table wp_posts TO wp_a123456_posts;
RENAME table wp_terms TO wp_a123456_terms;
RENAME table wp_termmeta TO wp_a123456_termmeta;
RENAME table wp_term_relationships TO wp_a123456_term_relationships;
RENAME table wp_term_taxonomy TO wp_a123456_term_taxonomy;
RENAME table wp_usermeta TO wp_a123456_usermeta;
RENAME table wp_users TO wp_a123456_users;
This will change all 11 of your default WordPress tables. But if you have additional tables that were added by themes or plugins, you’ll have to find those tables and change each one manually.
To do so, you’ll have to run two more queries in two different tables: Options and UserMeta.
To start with the Options Table, click the SQL option again and use this query:
SELECT * FROM wp_abc123_options WHERE option_name LIKE ‘%wp_%’
It’s probably obvious that the query above assumes you used the _abc123. If not, just replace that part with the prefix you decided to use.
Once you run the query, it may return numerous results.
You’ll have to go through each line one by one to change the prefixes on these additional tables.
For the UserMeta table, run this query:
SELECT * FROM wp_abc123_usermeta WHERE meta_key LIKE ‘%wp_%’
Again, the number of entries or tables you have to change may vary on the number of plugins you use and how many tables they add.
Test Extensively
Finally, when you’re done making all of your changes, create a separate (second) backup of your site’s file and database if you can.
Then, load your website and go through your Admin and the front-end as your users would. It’s important to be as comprehensive as possible when testing because your site may appear fine at first glance, but if you missed one table somewhere, a particular feature may not work properly. For example, if you use a plugin to create redirects, the plugin will most likely add a couple of tables to your database. If the database table prefix in your wp-config.php file doesn’t match up because you didn’t change the database tables associated with your redirect plugin, the redirects will stop working and you most likely won’t be able to use your plugin.
Want to read more articles like this one? See 25 Ways to Improve WordPress Security
