Search and Replace in WordPress MySQL Database

There are times when you must enter the WordPress MySQL database to search and replace some content. If you don’t have to do this, then DO NOT DO THIS. If you have to do this, then your head should be examined carefully, opened up and inspected for programming errors, and then you may proceed with due and dilligent care.

When might this insanity occur? This overwhelming need to destroy your blog? Most commonly it happens when you change domain or email addresses and need to make sure that all the old links are gone, replaced with the new links. Or if you have just imported massive content into the database and you’ve discovered a recurring boo boo across the majority of posts.

Reason why NOT to do this. First, because it can and will destroy your blog and database if used incorrectly. Second, if the item you wish to search and replace is found in 20 or fewer posts, fix them manually. Much less invasive and destructive.

Third, what you search for MUST be unique. It must only be found at the point where you want the change made. Searching for a domain name or email address is pretty unique content, but a group of words or code that can be found in other places will search and replace ALL of the code, screwing up everything. For example, if you search and replace every “there” for a “their”, you will end up with “their’s no place like home” or “theirfore”. I once made the mistake of deciding that “website” was to be spelled consistently on my site as “web site”. A massive search and replace turned links with the word “website” into “web%20site” which broke all such links. Do you know how many people have the word “website” in their links? Too many, and I think I linked to all of them.

If you are still willing to go forward with this amazingly stupid and risky thing to do, here are the final warnings and instructions.

BE VERY CAREFUL WITH THIS. IT WILL HURT.

Should I say, “don’t do this at home”, too? You get the point.

1. BACKUP YOUR DATABASE FIRST.

2. From within PHPMyAdmin, open your database.

3. Check the names of the table and field holding the information you want to change. For WordPress users, the most common search and replace is done on the content so we’ll use the wp-posts table and the post-content field within that table.

4. To search and replace text within your wp-posts table and the post-content field, click on the SQL tab and enter the following with the exact item you want to search for in the third line, and what you want to replace it with in the last line.

UPDATE wp_posts SET post_content = REPLACE (
post_content,
'Item to replace here',
'Replacement text here');

5. Making sure that the ONLY parts you change are within the little single ‘quotes‘.

6. Make sure EVERYTHING is spelled right and that the names of wp-posts and post_content, or whatever table and fields you are using, match the table and field within your database. Make sure that post_content is listed twice and that they match.

4. When you are sure, and you have made arrangements to have your body and brain donated to science when your brain explodes because this destroys everything in your database (okay, not really, but I’m impressing upon you the care you have to take with this – got it? Can I stop now? Good.), then click the GO or APPLY or whatever the DO IT NOW button looks like in your version of PHPMyAdmin.

5. It will then go through your database’s wp_posts table in the post_content field looking for the first line in the search and replace command, and replacing it with the second line.

6. The results will tell you how many records were changed. Let’s hope it’s the number you anticipated. If not, then restore your backup and start over.

While I jest about this process, it is a very serious and risky thing to do, but it is also very helpful. When setting up a test WordPress site, I often want all the pings closed. When I move a test site out to the public, pings need to be turned back on. To turn all the pings back on for every post and Page within my site, I used the following shortcut version of the above search and replace command:

UPDATE wp_posts SET ping_status="open";

If the ping_status was “closed”, it was now “open” and if it was blank, it was also now “open”. I then went through the Pages for my “About”, “Contact”, and other posts and Pages I didn’t want open to comments or pings and turned them off manually.

Again, I never do this without a backup, and even I have screwed this up, so if I can do it, I’m sure you will, too. 😉