Need of wp prefix change
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 filesystem, 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:
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;
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;
Or you can also use following REPLACE query:
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.
<?php 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.