Change WordPress Default Table Prefix

Every information on your site is going to be stored in a WordPress database thus making it hacker’s favorite target. Hence, it’s very important to change the default table prefix (wp_) to something else, because the spammers and hackers out there knows what the default table prefix is (wp_) and that allows them to perform SQL injection kind of attacks.

During installation:

The best and the easiest way is to provide an unique database prefix during installation of WordPress on your server. During the WordPress installation, it was ask what database prefix you want. The default is “wp_” but change it to something else. That way, it is set to begin with and you don’t need to change it again.

After installation:

If you’ve already installed WordPress and want to change your database prefix, you’re stuck with the hard way. But it’s really not that hard, just hard compared to changing a single line in your wp-config.php (as shown above). To change your prefix after installing, set aside around ten minutes and follow these steps:

Step 1: Preparations

Before changing your table prefix, make sure you have a recent backup and about 10 minutes of downtime for your site. It may be a good idea to redirect visitors to a temporary maintenance page.

Step 2: Change table prefix

Change your database table prefix in wp-config.php from wp_ to something more secure, like VzQCxSJv7uL_ or something.

Step 3: Change all WordPress database tables

Go to your database (using phpMyAdmin or whatever) and rename all WordPress table prefixes from wp_ to whatever you specified in your wp-config.php file. Here are SQL commands to rename the 11 default WP tables:

RENAME table `wp_commentmeta` TO `VzQCxSJv7uL_commentmeta`;
RENAME table `wp_comments` TO `VzQCxSJv7uL_comments`;
RENAME table `wp_links` TO `VzQCxSJv7uL_links`;
RENAME table `wp_options` TO `VzQCxSJv7uL_options`;
RENAME table `wp_postmeta` TO `VzQCxSJv7uL_postmeta`;
RENAME table `wp_posts` TO `VzQCxSJv7uL_posts`;
RENAME table `wp_terms` TO `VzQCxSJv7uL_terms`;
RENAME table `wp_term_relationships` TO `VzQCxSJv7uL_term_relationships`;
RENAME table `wp_term_taxonomy` TO `VzQCxSJv7uL_term_taxonomy`;
RENAME table `wp_usermeta` TO `VzQCxSJv7uL_usermeta`;
RENAME table `wp_users` TO `VzQCxSJv7uL_users`;

If there are other WordPress-related tables from plugins or whatever, just rename them too. The goal here is to rename all of the tables that begin with the default prefix. If you’re using something like phpMyAdmin to interface with your database, you can execute multiple commands at the same time, so edit the above code with your table prefix, paste it into the SQL field, and WHAM! – all tables changed in the blink of an eye.

Step 4: Edit the WordPress options table

Now search the options table for any instances of the old prefix. To do this, enter the following SQL query:

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

That search will return the wp_user_roles option along with any other options created by plugins, custom scripts, etc. The goal here is to rename any options that begin with wp_ to the new prefix.

Step 5: Edit the usermeta table

Now search the usermeta for all instances of the old wp_ prefix. Here is an SQL command to accomplish this:

SELECT * FROM `VzQCxSJv7uL_usermeta` WHERE `meta_key` LIKE '%wp_%'

The number of fields that you need to rename may vary depending on plugins and other factors, but as before, just remember to rename any entry that begins with the default WordPress table prefix, wp_.

Final Step: Test, backup, and done!

Ideally at this point, all instances of the old table prefix (wp_) have been replaced with the new (VzQCxSJv7uL_ in our example). Once this is done, go check your site for proper functionality. Test the Admin, pages, posts, search, and everything else you can think of (or have time for). If your site seems to be working as before, chances are good that the surgery was a success. Now make another database backup for good measure.