How can I change the WordPress database table name prefix?
Submitted by Robert Abela on July 11, 2011 - 10:46 am 5 Comments**Do not do the below change unless you are comfortable with PHPMyAdmin and making changes to MySQL. If not, ask someone who is familiar with WordPress and MySQL to assist you. Also, backup your blog; it is of utmost importance that before doing any changes to your WordPress installation or database, to backup the whole blog including the database.
WP Security Scan offers the functionality to automatically change the database table name prefix for you, so you are protected against zero day SQL injections. Some people prefer to do such change manually. Below is a detailed step by step procedure on how you can change the WordPress database table name prefixes manually.
- Backup your WordPress database to a sql file. You can do so by using the phpmyadmin (typically available from your hosting service control panel) or if you have direct access to the server, you can use MySQL command line parameters to backup the database.
- Make a copy of the WordPress database backup file (.sql) in case you would need to restore the database.
- Open the WordPress database file (*.sql) using a text editor.
- Find and replace all “wp_” prefixes to “anything_” (anything_ being any value or word you would like to use).
- Drop all database tables of your WordPress database. Make sure that the tables ONLY are dropped and not the whole database.
- Import the modified WordPress database backup file (*.sql) using PHPMyAdmin or MySQL commands. You just restored the WordPress database using new table prefixes.
- Edit your wp-config.php file (which can be found in the WordPress root directory) and replace the value ‘wp_’ in the line $table_prefix = ‘wp_’; to the new assigned table prefix.
- There is a slight chance that your plugins will be deactivated automatically after following the above procedure. In that case activate them again manually. We always recommend that before doing such a change, the plugins should be all deactivated.
Else, you can use the Database tool in WP Security Scan to AUTOMATICALLY rename the WordPress database table prefixes.
1 Trackbacks/Pingbacks
- Pingback: New version of WP Security Scan is available - WebsiteDefender.com on July 12, 2011
4 Comments
Post a comment
Add your comment below, or trackback from your own site. You can also subscribe to these comments via RSS. Be nice. Keep it clean. Stay on topic. No spam.
I’ve been having trouble with this. See trac:
http://core.trac.wordpress.org/ticket/4780
Your edit would handle the options as well as the table names, but I haven’t had a definitive answer about whether any options *should* start with “wp_”.
I’ve also had trouble editing the SQL file – particularly problems with character sets. Is there a Windows editor you’d recommend that handles UTF-8 correctly?
Hi Nancy,
Unfortunately I am unable to understand “but I haven’t had a definitive answer about whether any options *should* start with “wp_” Can you please explain in more detail?
As regards Windows Editors to edit SQL files, there are a couple of good ones. Mainly I use PSPad and Notepad ++.
Great and easy to follow. One other mention you might want to add is that widgets used on front page are deactivated as well (reset to default). Not a big deal to redo especially with a warning in your write up as they can be placed into inactive widgets and then just drug back out into place after change.
Thanks!
Thanks @Cindy for mentioning that – It was doing my head in and thanks to your message I see what happened! Thanks for posting.