Change Database Table Prefix in WordPress

July 25, 2014

This article teaches updating table prefix from “wp_” to “test_”.

1. Change wp-config.php

Find line $table_prefix  = 'wp_'; update it to $table_prefix  = 'test_';

2. Rename all the table to have prefix “test_”

3. Update entries in “test_options” table

First find entries of meta_key that starts with “wp_”:
SELECT * FROM test_options WHERE option_name LIKE 'wp\_%'

Then update records with meta key “wp_user_roles” with below queries:
UPDATE test_usermeta SET option_name = 'test_user_roles' WHERE meta_key = 'wp_user_roles'

3. Update entries in “test_options” table

First find entries of meta_key that starts with “wp_”:
SELECT * FROM test_usermeta WHERE meta_key LIKE 'wp\_%'

Then update records with meta key “wp_capabilities” with below queries:
UPDATE test_usermeta SET option_name = 'test_capabilities' WHERE meta_key = 'wp_capabilities'

Then update records with meta key “wp_dashboard_quick_press_last_post_id” with below queries:
UPDATE test_usermeta SET option_name = 'test_dashboard_quick_press_last_post_id' WHERE meta_key = 'wp_dashboard_quick_press_last_post_id'

Then update records with meta key “wp_user-settings” with below queries:
UPDATE test_usermeta SET option_name = 'test_user-settings' WHERE meta_key = 'wp_user-settings'

Then update records with meta key “wp_user-settings-time” with below queries:
UPDATE test_usermeta SET option_name = 'test_user-settings-time' WHERE meta_key = 'wp_user-settings-time'

Then update records with meta key “wp_user_level” with below queries:
UPDATE test_usermeta SET option_name = 'test_user_level' WHERE meta_key = 'wp_user_level'