Why should we change wp prefix?

Database of wordpress is a core of your wordpress site or blog. All your posts gets stored in a systematic manner in wordpress’s database table structure. Although wordpress is more popular CMS so the hackers and spammers also tries to inject their queries by using SQL Injections. Many developers forget to change the prefix. If you really care for your content you have no choice other than making your system more secure.

WordPress uses ‘wp_‘ as their table prefix. Here we will be discussing simple steps how you can change the wordpress ‘wp_‘ prefix without any plugin.

Precautions before making any change

Before going ahead first you need to take some precautions, else it will mess up an existing running website.

Precaution 1 – Take database backup
– It’s good if you are familiar with command line tools (ie. Linux Terminal or PuTTY) then you can use following command to take a database backup:

shell> mysqldump -u username -p table_name > path/to/save/table_name.sql

– If you are not familiar with command line, you can use phpMyAdmin or SQLyog or other database tools that provides some UI (User Interface). Here I assumed that you are familiar with database tools and SQL queries.

Precaution 2 – Take file system backup
Once the database backup taken you need to take a backup of your existing file system, in-case you have made any changes into PHP files.

Precaution 3 – Redirect your end-users to temporary maintenance page

Once you are done with the backup you are good to move further steps.

Steps to change wp prefix

STEP 1 – Change table prefix in wp-config.php file
wp-config.php file located in the wordpress root directory. The default prefix is $table_prefix  = 'wp_'; You can name it something more relevant and secure name like $table_prefix  = ‘apex_456_’; Please note that prefix can only have numbers, letters, and underscores. You can use Upper/Lower cases for more secure prefix.

STEP 2 – Change wordpress all tables name
By default wordpress has following 11 tables with every new installation:

  1. wp_commentmeta
  2. wp_comments
  3. wp_links
  4. wp_options
  5. wp_postmeta
  6. wp_posts
  7. wp_term_relationships
  8. wp_term_taxonomy
  9. wp_terms
  10. wp_usermeta
  11. wp_users

You need to rename these tables name first. You can rename these table using individual SQL query as follows:

mysql> RENAME TABLE wp_commentmeta TO apex_456_commentmeta;

Running individual query each time is little time consuming. But you can concatenate it with single query using comma separator. So that all the tables get renamed in one shot.

mysql> RENAME TABLE
wp_commentmeta TO apex_456_commentmeta,
wp_comments TO apex_456_comments,
wp_links TO apex_456_links,
wp_options TO apex_456_options,
wp_postmeta TO apex_456_postmeta,
wp_posts TO apex_456_posts,
wp_term_relationships TO apex_456_term_relationships,
wp_term_taxonomy TO apex_456_term_taxonomy,
wp_terms TO apex_456_terms,
wp_usermeta TO apex_456_usermeta,
wp_users TO apex_456_users;

phpMyAdmin users can run above query in SQL mode

There might other tables too as you install different wordpress plugins. Make sure you change all ‘wp_‘ prefix tables to your new prefix name.

STEP 3 – Search _options table for ‘wp_’ values

mysql> SELECT * FROM apex_456_options WHERE option_name LIKE '%wp_%';

Above SELECT query will search number of rows that has ‘wp_‘ prefix. By doing you will have an idea how many rows you need to update in _options table.

STEP 4 – Update option_name ‘wp_user_roles’ to ‘apex_456_user_roles’ in _options table

mysql> UPDATE apex_456_options SET option_name='apex_456_user_roles' where option_name = 'wp_user_roles' limit 1;

STEP 5 – Search _usermeta table for ‘wp_’ values

mysql> SELECT * FROM apex_456_usermeta WHERE meta_key LIKE '%wp_%';

STEP 6 – Update meta_key values in _usermeta table

mysql> UPDATE apex_456_usermeta SET meta_key = 'apex_456_capabilities' WHERE meta_key = 'wp_capabilities' LIMIT 1;
mysql> UPDATE apex_456_usermeta SET meta_key = 'apex_456_user_level' WHERE meta_key = 'wp_user_level' LIMIT 1;
mysql> UPDATE apex_456_usermeta SET meta_key = 'apex_456_dashboard_quick_press_last_post_id' WHERE meta_key = 'wp_dashboard_quick_press_last_post_id' LIMIT 1;
mysql> UPDATE apex_456_usermeta SET meta_key = 'apex_456_user-settings' WHERE meta_key = 'wp_user-settings' LIMIT 1;
mysql> UPDATE apex_456_usermeta SET meta_key = 'apex_456_user-settings-time' WHERE meta_key = 'wp_user-settings-time' LIMIT 1;

You can also use REPLACE method:

mysql> UPDATE apex_456_usermeta SET meta_key = REPLACE (meta_key, 'wp_', 'apex_456_');
mysql> UPDATE apex_456_options SET option_name = REPLACE (option_name, 'wp_', 'apex_456_');

MISTAKES / ERRORS

Once you are done with above changes, its time to view your website/blog. If there is any mistake made while changing the ‘wp_’ prefix, wordpress will throw an error message:

“You do not have sufficient permissions to access this page”

There can be one more possible mistake often made by developers is hard coding the wordpress tables name in their SQL queries for post/pages/categories/tags. They can avoid hard coding wordpress tables name. It can be done by calling global $wpdb variable and in SQL query it will be $wpdb->prefix.

global $wpdb;
$custom_queryy = $wpdb->query ( "SELECT * FROM ". $wpdb->prefix ."posts WHERE post_type = 'custom_post_type' LIMIT 10" );

YOU ARE DONE!

If there is no error message then you are done with the prefix change stuff. Now take a database backup for new prefix change! Now your website or blog is more secure than earlier.

You might also like: