CODEWITHSUNDEEP
mysqlwordpressdatabase-migration
metaColin
metaColin

Reputation: 1971

Which tables in a Wordpress database need to be edited for migration to a new URL?

I have seen many questions related to database migration, but none which clearly state: When editing a database which of the tables do I actually need to edit?

As a force of habit I edit the entire mySQL database, and usually that works out. (However on occasion this can mess up URL left in user comments for instance.) However it would be good to know specifically which tables I actually need to edit in order to complete a migration correctly.

EDIT: I already understand how a migration works and which tool to use, and I have read the codex entry on migration. I am not having a specific problem migrating.

This is really more of a best practices question.

What I am looking for is a definitive list of what tables I can exclude from my search and replace. For instance I know that the basic URL info is in wp_options, I know that (some) image paths are stored in wp_postmeta. Basically I want to exclude every table I possibly can, while still preserving the site's widgets, images, settings, etc.

Upvotes: 0

Views: 1135

Answers (2)

Matthew R.
Matthew R.

Reputation: 4350

The only references that HAVE to change are in the wp_options table. One is the home url and the other is the siteurl. These will allow you to log into the admin and view the frontend. However, you will still have to update your permalinks and rewrite rules using the admin.

I would still suggest an automated solution, however. I use this tool to find and replace database references. It is specifically made for wordpress, but it will work for any database. It also will allow you to select which tables to update and will work with serialized strings so you should be able to avoid errors in the comments section.

Simply drop the file on the root of your wordpress install and run through the prompts. Make sure to deselect the wp_comments and wp_commentmeta tables. Also, make sure you remove the file on production as it presents a potential security threat.

UPDATE BASED ON COMMENTS

Other than the two spots above, there are several places in the database that URLs are stored. Most plugins will store their options in the wp_options table. Typically, plugins will also serialize the data to avoid a ton of queries. You can't simply change the URL in the serialized data however, because there are length references in the serialized string. So if your current URL is 15 characters long and the new one is 20, you need to update the URL and the string length too. If you don't, PHP will just ignore the value. I believe this is a security measure to avoid code injection.

For assets in the media section the URLs are stored in the wp_posts under the post type attachment. If you are hardcoding absolute links in your posts, you may need to parse those as well (if this is the case, you can probably just parse the entire table). If you are using any sort of custom field plugin or doing anything with post meta for URLs you are also going to want to go through wp_postmeta.

One other thing to mention is that some plugins will add their own database tables. These are obviously on a per case basis, but a good rule of thumb is to try running a query for "%http%" in any string columns to see if there are hardcoded URLs. Here is the query I will use:

SELECT * FROM `table` WHERE `column` LIKE '%http%'

Upvotes: 2

Maruti Mohanty
Maruti Mohanty

Reputation: 217

Download the file from the following link http://interconnectit.com/products/search-and-replace-for-wordpress-databases/. Put it in the root folder and access the file and follow the steps and replace the urls.

I have been using it for quite a long time without any issues.

Hope it helps!

Thanks

Upvotes: 0

Related Questions