Changing Your Wordpress Database Prefix

A good security tip when installing your Wordpress blog is to change the database table prefix, the idea is that this will hide the tables from any hackers looking to compromise your blog. This can be done in the wp-config.php file and the variable $table_prefix. Changing this value from the default wp_ to, for example, blog_ will change the table wp_posts to blog_posts, making it more difficult for hackers to find it. Using blog_ is only an example, you should treat the prefix like a password, include letters and numbers to make it more difficult to find.

But what happens if you have installed your system and want to change the prefix? Well you need to change the $table_prefix variable in your wp-config.php file and alter the tables in your Wordpress database. Which one you do first is up to you, but you should go them both as fast as you can. Here are some MySQL statements that should help you to do this.

ALTER TABLE wp_comments RENAME TO blog_comments;
ALTER TABLE wp_links RENAME TO blog_links;
ALTER TABLE wp_options RENAME TO blog_options;
ALTER TABLE wp_postmeta RENAME TO blog_postmeta;
ALTER TABLE wp_posts RENAME TO blog_posts;
ALTER TABLE wp_term_relationships RENAME TO blog_term_relationships;
ALTER TABLE wp_term_taxonomy RENAME TO blog_term_taxonomy;
ALTER TABLE wp_terms RENAME TO blog_terms;
ALTER TABLE wp_usermeta RENAME TO blog_usermeta;
ALTER TABLE wp_users RENAME TO blog_users;

This just renames the default tables so that they have the new prefix, just do a find/replace on the text 'blog_' to be something unique. Note that this doesn't include any tables created for any plugins that you have, so you will have to add SQL statements for each of these tables.

This allows the front page of your blog to function properly, but when you try to log in to the admin section you will see the following error.

You do not have sufficient permissions to access this page.

This occurs because of some user settings that are created when the database is created and when new users are added still reference tables with the old prefix. The tables wp_usermeta and wp_options need to be changed as they both reference tables by name and will not produce the desired results if you change the table prefix.

To change the wp_usermeta (or blog_usermeta if you used the above SQL statements) table use the following SQL query.

UPDATE blog_usermeta SET meta_key = REPLACE(meta_key, 'wp_', 'blog_');

This will change values contained in the meta_key column. The values that are changed look something like wp_capabilities, wp_autosave_draft_ids and wp_user_level, although wp_autosave_draft_ids won’t be there unless you have saved drafts of posts while editing them.

To change the wp_options table (again, this would be blog_options) use the following SQL query.

UPDATE blog_options SET option_name = 'blog_user_roles' WHERE option_name = 'wp_user_roles' AND blog_id =0;

You should now be able to access your admin system.

This post applies to Wordpress version 2.3.3.