How to find / replace in mysql for wordpress permalinks that have changed

Posted February 4, 2009

Recently we’ve helped a few of our clients move wordpress installations between domain names. This is a fairly easy process except that links that were made inside of posts, such as links to other posts, pages and images hosted on the site are likely to break. Also, if you change permalinks around a lot or move stuff around inside of your wordpress “uploads” folder, you might find lots of broken links.Recently we’ve helped a few of our clients move wordpress installations between domain names. This is a fairly easy process except that links that were made inside of posts, such as links to other posts, pages and images hosted on the site are likely to break. Also, if you change permalinks around a lot or move stuff around inside of your wordpress “uploads” folder, you might find lots of broken links.

You’ll need access to run SQL statements on your database. For most people, using phpmyadmin is the easiest way to accomplish this. phpmyadmin is usually available on the control panel backend that your webhost provides.

Before you do anything, its a good idea to backup your entire database. If you are using phpmyadmin, you can do this by selecting your wordpress database and going to “export”. Select “Save as File” and “zipped” or “gzipped”.

Once you are ready to do the find replace, you can enter your SQL statements in the “SQL” tab of phpmyadmin. Here is the syntax for a find replace in mySQL:

          update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, 'find this string', 'replace found string with this string');
        

In wordpress, to search the content of all posts and pages, you would use the table wp_posts and the field post_content:

          update wp_posts set post_content = replace(post_content, 'find this string', 'replace found string with this string');
        

Its important to note that a forwardslash (/) is an invalid character, so you can use a backslash (\) to escape each forward slash. Its best to use a full URL when find/replacing on the post content as if you just use the slug you might end up changing more than you intended and this is searching the actual text of every post as well as the URLs contained inside of link and image tags.

For example, to change URLs from http://www.oldsite.com/blog to http://newsite.com would use:

          update wp_posts set post_content = replace(post_content, 'http://www.oldsite.com/blog', 'http://newsite.com');