WordPress: how to change tables prefix
In a basic WordPress installation, database tables prefix is “wp_“.
First of all, let’s try to understand why should this prefix be changed, instead of leaving the default one: because of the impressive number of WordPress websites, and of the huge amount of information stored in those databases, spammers and hackers continuosly execute automated codes for SQL injection, so they can gain access to website information.
Since those automated scripts are triggered on the default prefix, a good starting point is to change it, so to avoid to be in the potential victims of such attacks.
If the prefix is changed during first installation, we’re already set and done: if not, to change tables prefix (i.e. from wp_ to xy_12345_) of an already populated database, we need to follow this procedure, after executing a database backup just in case of problems:
- change the prefix in wp-config.php file:
$table_prefix = 'xy_12345_';
- rename all database tables via SQL:
RENAME table `wp_commentmeta` TO `xy_12345_commentmeta`; RENAME table `wp_comments` TO `xy_12345_comments`; RENAME table `wp_links` TO `xy_12345_links`; RENAME table `wp_options` TO `xy_12345_options`; RENAME table `wp_postmeta` TO `xy_12345_postmeta`; RENAME table `wp_posts` TO `xy_12345_posts`; RENAME table `wp_terms` TO `xy_12345_terms`; RENAME table `wp_term_relationships` TO `xy_12345_term_relationships`; RENAME table `wp_term_taxonomy` TO `xy_12345_term_taxonomy`; RENAME table `wp_usermeta` TO `xy_12345_usermeta`; RENAME table `wp_users` TO `xy_12345_users`;
- manually change all references in options table, selecting them with this query:
SELECT * FROM `xy_12345_options` WHERE `option_name` LIKE '%wp_%'
- manually change all references in usermeta table, selecting them with this query:
SELECT * FROM `xy_12345_usermeta` WHERE `meta_key` LIKE '%wp_%'
At this point, after verifying that everything works, you can make a backup of the new database and your job is done!