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.
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/
There are a total of 11 default WordPress tables, so changing them manually would be pain.
That’s why to make things faster, we have a SQL query that you can use.
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
option_name LIKE ‘%wp_%’
This will return a lot of results, and you need to go one by one to change these lines.
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
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.