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:
- Search & Replace, by Inpsyde GmbH
- Better Search Replace, by Delicious Brains
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.
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:
- MySQL Workbench, the official tool from MySQL.
- SQLyog, the best front-end MySQL for Windows System: we already talked about it in the past.
- phpMyAdmin, the most popular and used web-based to configure and maintain MySQL database servers.
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 freeware) Notepad++.
- 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!
