Changing Table Prefix for an Existing WordPresss Site

Hoping someone can assist with WordPress table prefix for an already established site.

What is the best approach in changing the existing table prefix within both DB and wp-config file to reflect new table prefix.

Actually, it’s more the backend DB changes required.

Is there a decent plugin or can someone pls point me to the manual steps required. Just worried that I might cause issues with the DB.

Thanks.

Solutions Collecting From Web of "Changing Table Prefix for an Existing WordPresss Site"

This is pretty straightforward. You can use phpMyAdmin or MySQL Workbench to change the prefix on all the tables at once, or you can do it one-at-a-time with a tool like SequelPro. If you need to run the SQL by hand, the syntax is…

RENAME TABLE `old_name` TO `new_name`;

Once all the table names are updated, you simply update the $table_prefix value in your wp-config.php to match the new prefix.

You shouldn’t do it manually (SQL), or by using a plugin. It’s possible, but quite dangerous.

Because there are some WordPress core tables (like wp_options) that often refer to custom tables (i.e. plugin tables). And moreover, some of the data in those core tables are often serialized (with fixed length and positions), which means that even changing them manually would result in a buggy installation.

You should give a try to a tool like WP-CLI. That really does the job, as it takes in account the serialized data, and parses all the database.

use this plugin : https://wordpress.org/plugins/change-table-prefix/

OR

There are a total of 11 default WordPress tables, so changing them manually would be pain.

SQL Query

That’s why to make things faster, we have a SQL query that you can use.

RENAME table wp_commentmeta TO wp_a123456_commentmeta;
RENAME table wp_comments TO wp_a123456_comments;
RENAME table wp_links TO wp_a123456_links;
RENAME table wp_options TO wp_a123456_options;
RENAME table wp_postmeta TO wp_a123456_postmeta;
RENAME table wp_posts TO wp_a123456_posts;
RENAME table wp_terms TO wp_a123456_terms;
RENAME table wp_termmeta TO wp_a123456_termmeta;
RENAME table wp_term_relationships TO wp_a123456_term_relationships;
RENAME table wp_term_taxonomy TO wp_a123456_term_taxonomy;
RENAME table wp_usermeta TO wp_a123456_usermeta;
RENAME table wp_users TO wp_a123456_users;

You may have to add lines for other plugins that may add their own tables in the WordPress database. The idea is that you change all tables prefix to the one that you want.
The Options Table

We need to search the options table for any other fields that is using wp_ as a prefix, so we can replace them. To ease up the process, use this query:

SELECT * FROM wp_a123456_options WHERE option_name LIKE ‘%wp_%’

This will return a lot of results, and you need to go one by one to change these lines.
UserMeta Table

Next, we need to search the usermeta for all fields that is using wp_ as a prefix, so we can replace it. Use this SQL query for that:

SELECT * FROM wp_a123456_usermeta WHERE meta_key LIKE ‘%wp_%’

Number of entries may vary on how many plugins you are using and such. Just change everything that has wp_ to the new prefix.