How to Change the WordPress Database Prefix
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.
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`;
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.
Click ‘edit’ to go forth and change the prefix to our new one.
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.
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.