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';

Leave a Reply

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