How to change WordPress database table prefix?

Posted in Tutorials

Tweet This Share on Facebook Bookmark on Delicious Digg this Submit to Reddit

By default, WordPress uses “wp_” as the prefix for all its database tables.  For better security, it is recommended that it be set to another random prefix that is difficult for hackers to guess.

1.  Change WordPress database prefix during installation

The best and the easiest way is to provide an unique database prefix during installation of WordPress on your server.  During the WordPress installation, it was ask what database prefix you want.  The default is “wp_” but change it to something else.  That way, it is set to begin with and you don’t need to change it again.

2.  Changing WordPress database prefix after installation

The problem is that many people had install WordPress with the default database prefix and have been posting for a while before they heard that it might be a good idea to change it.  How do you change it after you have already installed WordPress?

No, there is no admin setting to change it.  But there are various plugins that can do it for you.  But please backup your current database (and WordPress files) before you try this.  Changing database prefix in an existing WordPress site is no trivial matter.   If something goes wrong, you can at least have a WordPress developer restore you site back to the way it was.

3.  Plugins that can change database prefix

Some plugins that can change the database prefix are (but not limited to) the following:

If you just need to change prefix, Change DB Prefix would work simply. After installation, go to “WP dashboard -> Settings -> Change DB Prefix” and type in new prefix.

Change database prefix

Change database prefix

After changing, you can uninstall this plugin since you only need to change database prefix once.

For the BulletProof Security plugin, go to “WP Dashboard -> BPS Security -> DB Backup -> DB Table Prefix Changer”

random database prefix

random database prefix

It would provide you with a Randomly Generated prefix.  If you don’t like that, you can type in your own or refresh page to get a new randomly generated one.

For iThemes Security, go to “Security -> Advanced”.  It only let’s you generate a random prefix.

 

4. Changing the WordPress Database Prefix Manually

This is the most difficult way and you have to have the technical skills.  But if you prefer to do it manually without any plugins, there are some articles.  Backup first.

The basic idea is …

1.  Backup database and site

2.  Put up and index page saying down for maintenance to prevent visitor from visiting the site during the process.  Or just put …

exit(‘down for maintenance’);

at the first line in your index.php

3. Rename your database tables …

rename database tables

rename database tables

4. Change the prefix in wp_config.php.  Look for $table_prefix  = ‘wp_’; and change it.

5. Change the values in various fields in the “options” and the “usermeta” tables and possible other tables.  The problem is that WordPress changes and the database depends on what plugins and settings you have installed.  Best is to do a new install of an new empty wordpress with an unique prefix and see where WordPress uses that prefix in the database.

For example, at the time of this writing…

In the options table there is an “option_name” of “wp_user_roles”. That has to change to replace the “wp_” with your new prefix.

Same with these values in the “meta_key” field of the “usermeta” table.

Do a search for possible others in other fields and tables.  Such as query like …

SELECT * FROM `wp_usermeta` WHERE `meta_key` LIKE ‘wp_%’  (replace wp_ with your new prefix when searching).

Here were some of the field values that I had to change …

change prefix in database values

change prefix in database values

TEST and remove the down for maintenance page.