WordPress Security: How to change the WordPress database prefix

This is an example of ‘security through obscurity‘.

This assumes you’ve already installed WordPress.

1. Backup DB

   Make a backup of your WordPress database.

2. Edit wp-config.php

   Edit this line in file wp-config.php:
   $table_prefix = 'wp_';
   so that ‘wp_’ has some short random string made of up of lowercase letters and numbers appended to it, followed by an underscore.
   $table_prefix = 'wp_fa4T_';

   It’s important that the prefix not be too long.  You don’t want to exceed a total name length of 64 characters.

3. Rename Tables

   Run this SQL script to rename the wordpress tables:

   use wordpress_db_name;
   RENAME table `wp_commentmeta` TO `wp_fa4T_commentmeta`;
   RENAME table `wp_comments` TO `wp_fa4T_comments`; 
   RENAME table `wp_links` TO `wp_fa4T_links`; 
   RENAME table `wp_options` TO `wp_fa4T_options`; 
   RENAME table `wp_postmeta` TO `wp_fa4T_postmeta`; 
   RENAME table `wp_posts` TO `wp_fa4T_posts`; 
   RENAME table `wp_terms` TO `wp_fa4T_terms`; 
   RENAME table `wp_termmeta` TO `wp_fa4T_termmeta`; 
   RENAME table `wp_term_relationships` TO `wp_fa4T_term_relationships`; 
   RENAME table `wp_term_taxonomy` TO `wp_fa4T_term_taxonomy`;
   RENAME table `wp_usermeta` TO `wp_fa4T_usermeta`; 
   RENAME table `wp_users` TO `wp_fa4T_users`;

4. Update some rows in the options table

   You will need to find and update the old prefix values in the options table.
   SQL to find:

   use wordpress_db_name;
   SELECT `option_name` 
   FROM `wp_fa4T_options` WHERE `option_name` LIKE 'wp\_%';

   SQL to update (run as many times as needed, changing SET and WHERE values:

   use wordpress_db_name;
   UPDATE `wp_fa4T_options` SET `option_name` = 'wp_fa4T_user_roles' WHERE `option_name` = 'wp_user_roles';

5. Update some rows in the usermeta table

   You will need to find and update the old prefix values in the usermeta table.
   SQL to find:

   use wordpress_db_name;
   SELECT `meta_key` 
   FROM `wp_fa4T_usermeta` WHERE `meta_key` LIKE 'wp\_%';

   SQL to update (run as many times as needed, changing SET and WHERE values:

   use wordpress_db_name;
   UPDATE `wp_fa4T_usermeta` SET `meta_key` = 'wp_fa4T_capabilities' WHERE `meta_key` = 'wp_capabilities';