Changing Default table prefix for WordPress

After my last post where I mentioned some best practices to secure a WordPress installation, I went back to some older installations. As those had the default table prefixes on them; I went ahead and updated the wp-config.php file and preceded to alter the table names. After the precedure the site came up and my posts were displaying – everything is working yeah!!!. Well when I logged in or should I say tried to login I got the ‘You don’t have permission….’ message. I thought to myself crap what did I forget…..

So here are the steps needed to do a database prefix change for WordPress….

Step 1: BACKUP

Before you do anything, make sure you have a recent backup and about 10 minutes to devote to this . If you have a heavily visited site redirecting visitors to a site maintenance page would be nice.

Step 2: Change your configuration

Change wp-config.php file located in the root folder of the WordPress installation. The line generally is found after the Unique Keys and Salts definitions.

/**
* WordPress Database Table prefix.
*
* You can have multiple installations in one database if you give each
* a unique prefix. Only numbers, letters, and underscores please!
*/
$table_prefix = '2UqLHRd_';

Step 3: Alter your DB tables

Change all the WordPress database tables (If you are on a hosted environment you most likely have access to phpMyAdmin. phpMyAdmin gives a nice option to ‘Replace table prefix’, select all table and choose this option.) Rename the table prefixes from wp_ to what you specified in the wp-config.php file. There should be 11 tables from the WordPress installation. If there are other tables related to any plugins you have installed, rename those too. We need to rename all tables that begin with the default wp_ prefix.

Step 4: Edit the WordPress options table

These following steps is what prevented me from logging into my sites….

Search the options table for any instances of the old prefix.

SELECT * FROM `2UqLHRd_options` WHERE `option_name` LIKE '%wp_%';

That search will return the wp_user_roles option along with any other options created by plugins, custom scripts, etc. Again the mission is to rename any options that begin with wp_ to the new prefix.

Step 5: Edit the usermeta table

Now search the usermeta for all instances of the old wp_ prefix.

SELECT * FROM `2UqLHRd_usermeta` WHERE `meta_key` LIKE '%wp_%';

The number of fields that you need to rename may vary depending on plugins, as before, just rename any entry that begins with the default WordPress table prefix, wp_.

Test, backup

All instances of the old table prefix (wp_) should have been replaced with the new (2UqLHRd_ in our example). Go check your site to make sure its proper functioning. Test the Admin, pages, posts, search, plugins etc. If your site seems to be working, the changes were a success. Backup and set that copy aside

Final Comments

Securing WordPress and any application you install on website involves securing your database. Default table prefix are well-known and targeted by script kiddies across the globe. Changing your prefix to something obscure and difficult to guess is an easy way to stop these attacks from compromising your database and website. I cannot stress this enough always keep recent backups. Its not if, but when something compromised with your database, the easiest way to get back to your zen place is to upload a recent backup.