WordPress - How to perform Search and Replace on whole Database (post, pages, wp_options) using WP plugins or MySQL tools

Wordpress: Theme or Plugin not working as it should? Check for duplicate CSS or JS files

Those who are frequently working with Worpress often find themselves operating mass-replace tasks throughout the whole WP Database. Such situation most likely arises in the following scenarios:

  • WebSite URL change (from  www.url-a.com to www.url-b.com), meaning we have to replace the old one in all of our posts/pages.
  • Invalid character issues (mostly due to charset)
  • Having to rename one or more keywords, tags, categories or more (for standard URL & links consistency).
  • Presence of a typo within multiple posts, which you would like to fix without performing thousands of edits.

When we need something like that, we can choose between two possible routes: going for a WordPress plugin to handle such issue in a managed way or work at database level with the help of some free and/or open MySQL management tools.

Method 1: WordPress Plugin

These are 2 among the best plugins available today for such task:

Both of them do a good job: they let the admin choose the source text, the target text and the table that will be affected for a search & replace, then click on OK and wait.

This slideshow requires JavaScript.

Method 2: Find/Replace from MySQL dump

This task, which also requires a high-priviledge access to the database itself, should only be done by competent people. There are at least three freeware/opensource tools that can be used to profit:

Regardless of the one you choose to pick, it's highly advisable to perform a FULL BACKUP of the whole archive before proceeding.

Once done, perform the following steps:

  • Export/extract the whole DB content into a .sql script (full dump / full export, structure and data).
  • Open the .sql file with a text-editor: if your DB is > 10GB we strongly suggest to install a text-editor expecially designed to open big files, such as the great (and freewareNotepad++.
  • Perform the search/replace action(s) with the text file editor.
  • Empty the source database, deleting all contents.
  • Import the .sql modified file to entirely re-populate your database.

Whe performing the SQL dump it's imperative to select all tables, views, stored procedures and functions: in case you've missed something and you happen to get it a bit too late, remember that you have the backup and you'll be good to go.

That's it for now: happy replace!

 

About Ryan

IT Project Manager, Web Interface Architect and Lead Developer for many high-traffic web sites & services hosted in Italy and Europe. Since 2010 it's also a lead designer for many App and games for Android, iOS and Windows Phone mobile devices for a number of italian companies. Microsoft MVP for Development Technologies since 2018.

View all posts by Ryan

Leave a Reply

Your email address will not be published. Required fields are marked *


The reCAPTCHA verification period has expired. Please reload the page.

This site uses Akismet to reduce spam. Learn how your comment data is processed.