1

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.

1

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.

| improve this answer | |
  • Cool. My only other query is that I saw a post from wpbeginner which mentions that you also need to look at both the Options and UserMeta tables as well for fields using the $table_prefix. Is this correct? – tonyf Aug 5 '15 at 1:42
  • It's possible a [very poorly programmed] plugin might, but it's not something that WordPress core will do and I've never seen that. The table prefix is stored as a constant in wp-config.php - so there's no reason for anyone to ever store it in the database for any reason. I wouldn't worry about it unless you run into a specific problem in staging/testing, before you deploy. – Matt van Andel Aug 5 '15 at 20:34
  • How exactly does one change the prefixes on all tables at once using MySQL Workbench? – slightlyfaulty Sep 20 '15 at 21:48
1

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.

| improve this answer | |
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.