I posted a Find and Replace entry before, but here’s a more step-by-step walkthrough for those who have no knowledge of SQL. I posted this as a comment on Owen’s blog, but some other people may be interested, so here’s a basic overview of how to update URLs in your blog (useful if you change domains):
BACK UP YOUR DATABASE FIRST!
You may want to run this on a small test case first as well, to make sure you understand the concept. For instance, you could pick your two latest posts and append the text QZQZQZ at the end. You could then run this example to make sure that the query replaces QZQZQZ with a new string, say SBSBSB.
In this example, several sites that were hosted at ikamashou.net had to move to dasaku.net (but their subdomains remained the same). To make sure that all the absolute URLs continue to work, we do a little find and replace magic on the database in phpMyAdmin.
- Go to your your cpanel, log in and select the icon for phpMyAdmin
- Select your WP database from the dropdown menu, then click on the wp_posts table from the list on the left sidebar (meaning your table name is wp_posts)
- Click on the search tab and search for post_content LIKE
%ikimashou.net%where the percentage marks indicate a wildcard, and note that the field name is post_content - The reason to search first is to make sure that you are searching on the correct term (note that you will not use the
%in the actual query) - The syntax of the SQL query (without brackets) to replace a string is:
UPDATE [table_name] SET [field_name] = REPLACE([field_name],’[string_to_find]‘,’[string_to_replace]‘); - To replace ikamashou.net with dasaku.net in all posts where ikamashou.net appears, your SQL code will be:
UPDATE wp_posts SET post_content = REPLACE (post_content, 'ikimashou.net','dasaku.net'); - To replace ikamashou.net with dasaku.net in all author URLs in comments, your SQL code will be:
UPDATE wp_comments SET comment_author_url = REPLACE (comment_author_url, 'ikimashou.net','dasaku.net'); - Don’t forget the semicolon at the end (;)
- Click on the SQL tab at the top and type/paste the SQL code in before clicking GO to actually run the query and fix all the URLs
- You can repeat this process for as many strings as you would like, but take note of which table you are updating (in the first example it is wp_posts but in the second example it is wp_comments) and which field name you are updating (the field name is typed in twice, and in the first example it is post_content but in the second example it is comment_author_url)
I’m not an SQL guru or anything, and I will refine the tutorial if I have made any mistakes. Keep in mind that find and replace in a database can be a powerful tool, but one that can go horribly awry. Only replace unique values (such as gibberish characters, or URLs) or you may inadvertently replace some values that you didn’t mean to replace.
9 Comments
Ah, I wish I had this info earlier. I moved to my new domain a couple of days ago… but I suppose this would only work if the blog continued to be hosted on the same server?
Eh, a few questions from the ignorant.
1. How do I access database z(0.o)2
2. Is there a program you use to update this, in view of Step #4 and how it seems to recognise changes?
@IcyStorm: If you had a copy of the blog from the old server, you could import that copy and then run these steps.
@Owen: You need to have access to cpanel and then go to phpMyAdmin. All of the changes can be done in phpMyAdmin with just some cutting and pasting. The code will automatically be displayed when you search or change an entry, so you can splice the code pieces together.
Thanks for trying to help out my poor users. Though, I have concerns that your query will replace entire post contents with “dasaku.net” and because the query you put forth will FIND posts with ikimashou.net, but changing a word… well… MySQL has a function to do it.
update [table_name] set [field_name] = replace([field_name],’[string_to_find]‘,’[string_to_replace]‘);
And if anyone still has blogs that need fixups, let me know on dasaku with the contact form or whatever and I will patch things up. I am a busy man, so I missed some people I am sure.
Eh, the blog was on wordpress.com and the export/import process went terribly, so I just manually transferred most of my posts over. Whatever, it’s not like I’ll be transferring domains anytime soon. I will keep this bookmarked though, for future reference.
And anyone who needs MySQL access to their blogs, let me know, I can tell you guys how to get it.
Randall, I am running through this right now with Owen, and I think you might have a point. This method works well for fields that have just a line of text (e.g. post_author_url), but not as well for post_content. The SQL query you ran is similar to what I did in correcting gibberish characters (due to the UTF-8 conversion that had hiccups), so I may rework this tutorial a bit… once I have finished experimenting on Owen’s guinea pig blog =D.
We ended up using:
UPDATE wp_posts SET post_content = REPLACE (post_content, 'sorenara.ikimashou.net','omaemo.dasaku.net');and
UPDATE wp_comments SET comment_author_url = REPLACE (comment_author_url, 'sorenara.ikimashou.net','omaemo.dasaku.net');Thanks, Kabitzin and Randall! I recently changed my site’s forum software, which changed the URL format for linking to topics. I didn’t know how to search and replace in phpmyadmin so I was going through my main site posts correcting URLs one by one.
Anyway, I’m done now, and in a flash. Backing up the database took more time and energy than fixing all the links! Thanks again!
I’ve updated the tutorial with input from Randall and my experience through literally going through the process step by step with Owen on IRC. I’m glad to hear that it has already been helpful to someone.
Also, note that if you are making backups through SQL exports, you need to be sure that you are using UTF-8. If you notice your queries in search have some code talking about converting to latin-1, you probably need to convert your database to UTF-8. Without the conversion, your outputted SQL for your backup may be messed up (so you should make a backup of the actual database file instead).