WordPress uses a database to store your website’s information. From posts, plugin settings, theme settings, comments, and so on. You can use a single database for multiple WordPress sites using different table prefixes.
What is table prefix?
A WordPress database consists of several tables to store website’s information like mentioned above. By default, WordPress creates about 12 tables on a new website installation. On a WordPress site, each table name has the same prefix. The default table prefix of WordPress is “wp_”.
For a security reason, you are suggested to create a custom table prefix. Using the default table prefix leads your website to be more vulnerable to hacker attacks. the common attack that utilizes the default table prefix is SQL injection.
Two things to note if you want to change your table prefix of your WordPress site:
- Start the prefix with “wp_” to keep things orderly
- End the prefix with an underscore (“_”) so the actual table names (e.g., posts, users, meta) stand out and are easily recognizable.
If you haven’t installed your WordPress and plan to install a new WordPress site, you can easily change the default table prefix on the installation step (if you install WordPress via script installed such as Softacolous).
If you prefer to install WordPress manually, you can change the default table prefix by editing the wp-config.php file. Here is the line you need to change.
How to change the WordPress table prefix if you have already installed your website
Before getting started, we highly recommend you backup your WordPress site just in case everything not going the way you expected.
Step 1: Edit the wp-config.php file
In the WordPress system, the wp-config.php is the main configuration file. This file determines what database name, database username, database address, and so on — used by your website. To change the prefix, find the following line (blue highlighted).
And change it with the prefix you want. Make sure to use a more secure prefix. The combination of uppercase, lowercase, and numbers would be great. Example:
The prefix cannot contain special characters (such as “#”, “$” and “&”). You can only use letters, numbers, and underscores.
Step 2: Change all WordPress database tables
Next, you need to change all of the tables on your WordPress database. You can do so whether via phpMyAdmin or via command line. You need to be a little patient since you need to change the tables one by one.
- Change the tables via phpMyAdmin
Open phpMyAdmin and select the database of your WordPress site on the left panel. Select a table (by clicking it) and click the Operations tab. On the Rename table to section, replace the current table name with your preferred one and click the Go button.
Repeat the steps to change other existing tables.
Well, you can actually rename multiple tables at once via phpMyAdmin. To do so, select the database of your WordPress site. Click the SQL tab and paste the following commands on the available field and click the Go button.
RENAME table `wp_commentmeta` TO `wp_A1b2C3d4_commentmeta`; RENAME table `wp_comments` TO `wp_A1b2C3d4_comments`; RENAME table `wp_links` TO `wp_A1b2C3d4_links`; RENAME table `wp_options` TO `wp_A1b2C3d4_options`; RENAME table `wp_postmeta` TO `wp_A1b2C3d4_postmeta`; RENAME table `wp_posts` TO `wp_A1b2C3d4_posts`; RENAME table `wp_terms` TO `wp_A1b2C3d4_terms`; RENAME table `wp_term_relationships` TO `wp_A1b2C3d4_term_relationships`; RENAME table `wp_term_taxonomy` TO `wp_A1b2C3d4_term_taxonomy`; RENAME table `wp_usermeta` TO `wp_A1b2C3d4_usermeta`; RENAME table `wp_users` TO `wp_A1b2C3d4_users`;
Step 3: Edit the WordPress options table
You need to search for the options table for any instances of the old prefix. To do so, type the following SQL query.
SELECT * FROM wp_A1b2C3d4_options WHERE option_name LIKE '%wp_%'
The search above will return the
wp_user_roles option along with any other options created by plugins, custom scripts, and so on. The goal is to rename any options that begin with “wp_” to the new prefix.
Step 4: Edit the usermeta table
Lastly, search the usermeta for all instances of the old
wp_ prefix. Type the following SQL command to do so.
SELECT * FROM wp_A1b2C3d4<code>_usermeta</code> WHERE <code>meta_key</code> LIKE '%wp_%'
The SQL query above will return the following
usermeta fields were returned:
Once everything is done, go check your website. Test everything from the WordPress dashboard, posts, search, pages and so on.