Skip to content

How To Do A Search & Replace On Your Website’s MySQL Database

For year’s I’ve used the WordPress Plugin SyntaxHighlighter to display code on my site.  Yesterday I ran into a huge problem. I wanted to change the way code is displayed on my site, which required changing hundreds of tags. SytaxHighlighter uses bash tags in shortcode format, but I needed to change all of those to standard pre tags. Not a fun night!

Every plugin a tried to do a search and replace didn’t work. I’m pretty sure the plugins weren’t able to handle the special characters properly. I hosed my database a few times along the way. Everything from screwed up Primary Key’s to bizarre Auto Increment problems. In the end, simplicity prevailed.

 

Quick Tip:  Don’t forget to create a backup of your database before making changes.  Just click the “Export” tab and select “Go!”

How To Search & Replace In MySQL

 

Most every website these days has PHPmyAdmin installed.  If not, it’s easy to do so (I’ll write a post on that soon). This is precisely the tool to do this job quickly and correctly.

Go ahead and log into PHPmyAdmin and select your database in the left-hand column.  Then select “SQL,” which is located in the top row of buttons.

 

phpmyadmin_mysql_search_and_replace_query_1

 

Quick Tip:  With WordPress, if you don’t know what your database is named, you can find out by looking inside of the wp-config.php file, located in the root of your HTML directory.

Now, copy & paste (ctrl-v to paste) the following code into the white box, making sure to remove anything that might be in there already.  Replace Item to replace here with the old word/tag/link, and Replacement text here with the new word/tag/link.  Be sure to keep the ‘ on each side.  Refer to the image below for a reference.

 

Code:

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

 

Finally, click the Go button.  It shouldn’t take more than a second or two.  Once it’s finished you should see a confirmation letting you know how many rows were affected; this is how you know it was successful.  If it says 0 rows affected, it means nothing was changed.

 

phpmyadmin_mysql_search_and_replace_success

 

Repeat these steps for each word/link/tag  you need to replace.  I had to run it twice; once to replace the opening tag, and a second time to replace the closing tag.  After, hundreds of posts had been updated and it only took a few minutes.

Conclusion

This is, by far, the best method to do a search and replace in a MySQL database.  In addition to not having to install another plugin on your website, you can include special characters without having to worry about what’s going to happen to your database.  If you need help or just want to say hi, feel free to leave a comment below!  As always, don’t forget to do a backup of your database beforehand!