The database of WordPress is the most important place as all the data of your site gets stored in it therefore hackers always targets it. The automated SQL codes target WordPress default prefix i.e. wp_ which majority of the people forget to change while installing WordPress and makes it easier for the hackers and spammers to target their site’s database.
It is comparatively easy to change the prefix of a WordPress site which you’re installing from scratch compared to one already installed and operational. However this tutorial will guide you to change prefix for both cases.
Before we shall start it is highly recommended to take backup of your WordPress Database. However, it is always great to keep a daily backup of your site to avoid lost of data due to any change you make on the site. Taking daily backup is not difficult you can use plugins for daily database backup. Moreover, if your site is live you may need to do 2 things:
1. Perform this at off-peak hours
2. Redirect your visitors to some temporary maintenance page.
Instructions for Changing Database Table Prefix
1: Go to the directory of your WordPress and edit wp-config.php file.
2: Find wp_ the default prefix and change it based on your intention e.g. my_wish123. The prefix can only be replaced with alphanumeric values and underscores.
The line within your wp-config.php will look something like this:
$table_prefix = 'my_wish123';
3: Login to your cPanel and click on phpMyAdmin which can be found within databases section.
4: Go to SQL and paste the following query in it, but make sure to replace my_wish123 with your intended prefix. This will make it easy for you to change all 11 tables manually. 🙂
RENAME table `wp_commentmeta` TO `my_wish123_commentmeta`; RENAME table `wp_comments` TO `my_wish123_comments`; RENAME table `wp_links` TO `my_wish123_links`; RENAME table `wp_options` TO `my_wish123_options`; RENAME table `wp_postmeta` TO `my_wish123_postmeta`; RENAME table `wp_posts` TO `my_wish123_posts`; RENAME table `wp_terms` TO `my_wish123_terms`; RENAME table `wp_term_relationships` TO `my_wish123_term_relationships`; RENAME table `wp_term_taxonomy` TO `my_wish123_term_taxonomy`; RENAME table `wp_usermeta` TO `my_wish123_usermeta`; RENAME table `wp_users` TO `my_wish123_users`;
5: Use the following code to search for any other table still using the default prefix.
SELECT * FROM `my_wish123_options` WHERE `option_name` LIKE'%wp_%'
It may return a lot of result so you probably need to change all of them manually.
6: Use the following query to find out the usermeta table for the fields that are still using wp_ prefix.
SELECT * FROM `my_wish123_usermeta` WHERE `meta_key` LIKE'%wp_%'
Make sure you have followed all the steps mentioned above properly so that nothing is left to be changed.
Using a WordPress Plugin Change Database Prefix
1. Download & Install Change DB Prefix plugin on your WordPress site.
2. Go to Settings > Change DB Prefix where you can see the existing prefix.
3. Change the prefix from wp_ according to your wish but make sure to keep it unique so that it can’t be easily guessed.
d. Click Update Settings and it’s done.
Congratulations! You successfully change the default WordPress prefix. In case if your site gives some error check for the table created by plugins and change their prefix to and your site should be working fine.
Make sure to take a backup again with new prefix.