How to Change the WordPress Database Prefix

Posted OnNov 5, 2013     CategoryDevelopment     CommentsNo comment

This tutorial will walk you through the process of changing your Database prefix on an existing WordPress installation. You’ll need FTP/SFTP/SSH Access to your server where WordPress is installed, as well phpMyAdmin Access, presuming you use a host with CPanel, or alternatively, MySQL Terminal Access.

All of the tables in your WordPress’ MySQL Database have a prefix, the default being ‘wp_’. Normally, when you install WordPress and depending on how you install it, you can choose your own Database prefix, and using a prefix other than the default can be advantageous for securing your site, as it’s one less detail that an intruder can assume.

Important: Please be aware that your site will be temporarily unavailable while performing this.

Step 1) Backup your Database! It’s very important to backup your Database regularly in any case, but especially before attempting modifications.

Step 2) SFTP/SSH into your server, open wp-config.php, change the database prefix to something unique.

 * WordPress Database Table prefix.
 * You can have multiple installations in one database if you give each a unique
 * prefix. Only numbers, letters, and underscores please!
$table_prefix  = 'ab90_';

Step 3) Log in to your Host’s CPanel or, if you have SSH’d to your server and have sufficient privileges, open the MySQL Terminal.


Step 4) Select your Database from the list on the left hand side.

select mysql database phpmyadmin

Step 5) Click the SQL link in the top menu. This will open a prompt for us to run commands. Use the following, though be sure change it specific to your needs, put it in and click go when ready.

RENAME table `wp_commentmeta` TO `ab90_commentmeta`;
RENAME table `wp_comments` TO `ab90_comments`;
RENAME table `wp_links` TO `ab90_links`;
RENAME table `wp_options` TO `ab90_options`;
RENAME table `wp_postmeta` TO `ab90_postmeta`;
RENAME table `wp_posts` TO `ab90_posts`;
RENAME table `wp_terms` TO `ab90_terms`;
RENAME table `wp_term_relationships` TO `ab90_term_relationships`;
RENAME table `wp_term_taxonomy` TO `ab90_term_taxonomy`;
RENAME table `wp_usermeta` TO `ab90_usermeta`;
RENAME table `wp_users` TO `ab90_users`;

bulk sql queries to change database table prefixes

Step 6) Run another SQL command to find any instances of the old ‘wp_’ prefix in the Options table.

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

It will return, at the very least, one entry, that of wp_user_roles.

changing wp_user_roles in the options table

Click ‘edit’ to go forth and change the prefix to our new one.

actually changing the wp_ prefix in the options table

Step 7) Run another SQL command to find instances of the old prefix, and update them to the new one.

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

Depending on the age, usage, plugins installed, and so on, you may have a large number of changes to make. In this example we can see there are three.

usermeta wp_ prefix changes

Once you have updated each of these entries with the new prefix you should be complete. Naturally, now is a great time to backup your Database.

Leave a Reply

Your email address will not be published. Required fields are marked *

Send this to friend