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


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');

10 Responses to “How to find / replace in mysql for wordpress permalinks that have changed”

  1. This doesn’t work for me under My SQL 5.0x. I still get an error that doesn’t recognize the backslashes.

    Error
    There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem

    ERROR: Unknown Punctuation String @ 63
    STR: :\/\/
    SQL: update wp_posts set post_content = replace(post_content, ‘http:\/\/keyissues.mu.nu\/archives\/’, ‘http:\/\/www.keymonroe.com\/wp-content\/uploads\/’)

    SQL query:

    update wp_posts set post_content = replace(post_content, ‘http:\/\/keyissues.mu.nu\/archives\/’, ‘http:\/\/www.keymonroe.com\/wp-content\/uploads\/’)

    MySQL said:

    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘:\/\/keyissues.mu.nu\/archives\/’, ‘http:\/\/www.keymonroe.c

  2. Hi Jesse,

    It looks like you might have had some improper characters in your statement, maybe some “smart” or “curly” quotes snuck into our syntax. Try again using the above example.

  3. Jed,

    You’re right, I knocked my head against the wall for a couple of hours trying to figure out what the heck was going on. Phpmyadmin put those “ticks” in place of single quotes after a failed query took me back to the query screen with my previous attempt slightly modified. After figuring this out everything worked as advertised and I was able to find and replace all those urls referring to the old website with ones for the new.

    One note: I was not able to escape the forward slashes. In MySQL 5.x escaping anything other than a small predetermind set of characters (I’d have to go back to look) apparently has no effect. SO I focused on just the actual URL part without the “http://” and that worked just as well.

    Two references to this behavior:

    http://bugs.mysql.com/bug.php?id=20103

    http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html

    I may be missing something.

    Thanks for responding.

  4. Another option is to do an export from MyAdminSQL and then do a find and replace on the text export, rename the table as backup and import the text file

    Jamie

  5. @Jamie: That can quickly become very time consuming; especially with a WP installation that has pre-existing content and permalinks.

    @Brendan: Thanks, this was much quicker than doing the tiresome Search and Replace method… And, it also made me realize I really should buff up on my SQL skills ;-)

  6. hi, thanks for this useful post, i already move my blog to another domain and realize that all images on my post still pointing to old domain, so i need to find and replace all url on my post with my new domain. Thanks.

  7. Thanks, this was much quicker than doing the tiresome Search and Replace method… And, it also made me realize I really should buff up on my SQL skills
    hi, thanks for this useful post, i already move my blog to another domain and realize that all images on my post still pointing to old domain, so i need to find and replace all url on my post with my new domain. Thanks.

  8. Hello, very useful as you mention, what I want to do is look inside
    post a specific keyword and replace it with a link to a web
    position, taking into account that the Keyword may already be inthe wrong.
    ideas perhaps with a cursor?

  9. Super extra useful, I had funny ” ‘ ” instead of the proper ones, I too tried to figure out what was wrong until I saw your post! Thanks :)

  10. Trying to replace the old link to new one and it always fail.
    I’m trying to change .jpg?imgmax=800 to just .jpg
    any advice?
    :-)

Leave a Comment