As WordPress security has always been a big concern for millions of websites, you cannot miss this tip – securing WordPress databases by changing the database table prefix. As is known, a database is the heart to a website that stores each single piece of information about the site, which makes it a desiring place for hackers.
The default WordPress database table prefix is wp_, which is quite predictable. Therefore, when hackers target at a database using the default table prefix, they can easily gain access, make changes to the database or even damage it. Changing the default value to a custom one is exactly the method that stops most of the simple attacks.
This post discusses the steps to change WordPress database table prefix (after installation) in detail. But before making any change, we suggest you to put your WordPress site into maintenance mode because the change of table prefix will lead to temporary unavailability of your site.
Step 1: Back up Your Database
Yes, you are reading it right. The first thing you need to do is to make a complete backup of your database in case that there is anything wrong. This provides a good chance to prevent data loss. The backup can be done by managing backups manually or utilizing a simple backup plugin. The process is quite quick and easy, but it is a must.
Step 2: Edit the wp-config.php File and Change Database Table Prefix
When backup is completed, open the root directory of your WordPress installation through an FTP client or through a file manager. Then find wp-config.php and edit it in a code editor like Notepad++. When you have opened the file, browse through to find some lines like this.
The codes in the orange block of the image above indicate that the current database table prefix is wp_. Now that we want to change the value to w123table_, we only need to replace wp_ with the new one, save the file, and then upload it to the server to replace the old file. So the code looks like this now:
$table_prefix = 'w123table_';
Note that you can only use letters, numbers and underscores for the name of the new database table prefix. And after you uploading the new wp-config.php file, your site stops working. But don’t worry, once you have accomplished the steps below, your site will come alive again.
Step 3: Change All WordPress Table Names
If you have access to a tool like PHPMyAdmin, this step can be more than easy. Just login into your control panel, find PHPMyAdmin, and open the database of your WordPress site. Now you can see a list of database tables like the screenshot below.
Now you have 2 options to change the table names. The first choice is to change them manually by clicking on the table names one by one, open the Operations tab on the top of the window, and fill in the blank after “Rename table to” with the new table prefix.
The other choice, however, is simpler but requires you to use SQL queries by using the SQL tab. For example, we would like to change the names of all the 11 tables, then we will use the following SQL queries.
RENAME table wp_commentmeta TO w123table_commentmeta; RENAME table wp_comments TO w123table_comments; RENAME table wp_links TO w123table_links; RENAME table wp_options TO w123table_options; RENAME table wp_postmeta TO w123table_postmeta; RENAME table wp_posts TO w123table_posts; RENAME table wp_terms TO w123table_terms; RENAME table wp_term_relationships TO w123table_term_relationships; RENAME table wp_term_taxonomy TO w123table_term_taxonomy; RENAME table wp_usermeta TO w123table_usermeta; RENAME table wp_users TO w123table_users;
These lines should be used one by one. When you enter one line in the SQL query box, click the button saying “Go” to execute it and to make changes effective. After all lines are applied, you will see that all the tables are given a new prefix. But the work has not done here yet, because you still have some other things to do.
Step 4: Edit the Options Table
Now you need to edit the w123table_options table (former wp_options table) to find some other fields using wp_ as the prefix. Simply clicking on the Browse button next to the table name, you can see all the data in the table. In the option_name column, you need to change wp_user_roles to w123table_user_roles by using the edit button. To make things easier, you can also use the SQL query below to find out and replace all the records in the options table containing the prefix wp_.
UPDATE `w123table_options` SET `option_name`=REPLACE(`option_name`,’wp_’,’w123table_’) WHERE `option_name` LIKE ‘%wp_%';
Step 5: Edit the Usermeta Table
To do this is similar to accomplishing Step 4. You need to search the usermeta table for all fields using wp_ and rename them with the new prefix. You can browse the records in the table one by one to figure out which one needs modification, or you may add “meta_key like `wp_%’” in the search tab. Also, there is another way: applying the query below to replace prefix.
UPDATE `w123table_usermeta` SET `meta_key`=REPLACE(`meta_key`,’wp_’,’w123table_’) WHERE `meta_key` LIKE ‘%wp_%';
Now all things have been done. You can test your WordPress site right now to check if everything is right. If not, go back to check again, and if the changes are successful, make a new backup of the database.
Changing table prefix is not the most important security method, but it will effectively protect your site from basic automated attacks. To make your site a safer place, you should take some more actions to secure your WordPress site, including performing regular scanning, using security plugins, securing the login page, and using a safe web hosting solution. Below are some of the good web hosts that can ensure a high level of security.