WordPress Security: How to change the WordPress database prefix

Last Updated on June 5, 2020 by Colin

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 *

This site uses Akismet to reduce spam. Learn how your comment data is processed.