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 long random string made of up of lowercase letters and numbers appended to it, followed by an underscore.
    $table_prefix = 'wp_faefrewfa93422kxc82kds9023_';

  3. Rename Tables

    Run this SQL script to rename the wordpress tables:

    use wordpress_db_name;
    RENAME table `wp_commentmeta` TO `wp_faefrewfa93422kxc82kds9023_commentmeta`;
    RENAME table `wp_comments` TO `wp_faefrewfa93422kxc82kds9023_comments`;
    RENAME table `wp_links` TO `wp_faefrewfa93422kxc82kds9023_links`;
    RENAME table `wp_options` TO `wp_faefrewfa93422kxc82kds9023_options`;
    RENAME table `wp_postmeta` TO `wp_faefrewfa93422kxc82kds9023_postmeta`;
    RENAME table `wp_posts` TO `wp_faefrewfa93422kxc82kds9023_posts`;
    RENAME table `wp_terms` TO `wp_faefrewfa93422kxc82kds9023_terms`;
    RENAME table `wp_termmeta` TO `wp_faefrewfa93422kxc82kds9023_termmeta`;
    RENAME table `wp_term_relationships` TO `wp_faefrewfa93422kxc82kds9023_term_relationships`;
    RENAME table `wp_term_taxonomy` TO `wp_faefrewfa93422kxc82kds9023_term_taxonomy`;
    RENAME table `wp_usermeta` TO `wp_faefrewfa93422kxc82kds9023_usermeta`;
    RENAME table `wp_users` TO `wp_faefrewfa93422kxc82kds9023_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_faefrewfa93422kxc82kds9023_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_faefrewfa93422kxc82kds9023_options`
    SET `option_name` = 'wp_faefrewfa93422kxc82kds9023_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_faefrewfa93422kxc82kds9023_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_faefrewfa93422kxc82kds9023_usermeta`
    SET `meta_key` = 'wp_faefrewfa93422kxc82kds9023_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.