WordPress Database is like a brain for your entire WordPress site
because every single information is stored in there thus making it
hacker’s favorite target. Spammers and hackers run automated codes for
SQL injections. Well, unfortunately many people forget to change the
database prefix while they install WordPress. This makes it easier for hackers to plan a mass attack by targeting the default prefix
wp_.
The smartest way you can protect your database is by changing the
database prefix which is really easy to do on a site that you are
setting up. But it takes a few steps to change the WordPress database
prefix properly for your established site without completely messing it
up.
Preparation
We recommend that you backup your WordPress Database before you perform anything suggested in this tutorial. It is important to keep daily backups of your site, we recommend BackupBuddy plugin for doing that. Next thing we recommend is that you redirect your visitors to a temporary maintenance page.
Change Table Prefix in wp-config.php
Open your wp-config.php file which is located in your WordPress root directory. Change the table prefix line from wp_ to something else like this wp_myprefix_
So the line would look like this:
$table_prefix = 'wp_myprefix_';
Note: You can only change it to numbers, letters, and underscores. Feel free to mix uppercase and lowercase.
Change all Database Tables Name
You need to access your database (most likely through phpMyAdmin),
and then change the table names to the one we specified in wp-config.php
file.
Login to your database vis phpMyAdmin, there are a total of 11 default WordPress tables, so changing them manually would be pain.
So please run the sql query below to make it easier
RENAME table `wp_commentmeta` TO `wp_myprefix_commentmeta`;
RENAME table `wp_comments` TO `wp_myprefix_comments`;
RENAME table `wp_links` TO `wp_myprefix_links`;
RENAME table `wp_options` TO `wp_myprefix_options`;
RENAME table `wp_postmeta` TO `wp_myprefix_postmeta`;
RENAME table `wp_posts` TO `wp_myprefix_posts`;
RENAME table `wp_terms` TO `wp_myprefix_terms`;
RENAME table `wp_term_relationships` TO `wp_myprefix_term_relationships`;
RENAME table `wp_term_taxonomy` TO `wp_myprefix_term_taxonomy`;
RENAME table `wp_usermeta` TO `wp_myprefix_usermeta`;
RENAME table `wp_users` TO `wp_myprefix_users`;
You may have to add lines for other plugins that may add their own
tables in the WordPress database. The idea is that you change all tables
prefix to the one that you want.
The Options Table
We need to search the options table for any other fields that is
using wp_ as a prefix, so we can replace them. To ease up the process,
use this query:
SELECT * FROM `wp_myprefix_options` WHERE `option_name` LIKE '%wp_%'
This will return a lot of results, and you need to go one by one to change these lines.
UserMeta Table
Next, we need to search the usermeta for all fields that is using wp_
as a prefix, so we can replace it. Use this SQL query for that:
SELECT * FROM `wp_myprefix_usermeta` WHERE `meta_key` LIKE '%wp_%'
Number of entries may vary on how many plugins you are using and such. Just change everything that has wp_ to the new prefix.
Backup and Done
You are now ready to test the site. If you followed the above steps,
then everything should be working fine. Now, you should make a new
backup of your database just to be on the safe side.