Sometimes, you want to do a universal search-and-replace on your WordPress database. For example, if you are migrating a site from one URL to another, then you will want to change all references to the old URL to the new.
A simple search-and-replace on the SQL file will not work, because much data inside WordPress’s database is serialized. If you search-and-replace serialized data, then the data often is broken.
To achieve search-and-replace, many people are using the popular and free searchandreplacedb2.php script from http://interconnectit.com/products/search-and-replace-for-wordpress-databases/.
This script requires you to:
- Upload the script
- Run it, entering your site details
- Remember to delete the script afterwards (as leaving it behind would be a massive security hole)
You ought also to remember to back up your database beforehand – search and replace on a database is something to tread carefully on.
Since version 1.3.86, searchandreplacedb2.php is integrated into WordShell. Now it’s just a matter of one command, using the –searchandreplace switch whilst in database mode.
The syntax is: –database –searchandreplace=<search term>^<replacement>
$ wordshell mysite --database --searchandreplace=http://localhost/testing^http://example.com
Search and replacing (wait, do not abort): Success
Tables examined: 24
Rows examined: 2780
Changes made: 271
SQL update commands run: 229
WordShell will automatically back up your database before performing a search and replace. So if something goes wrong (e.g. you enter your search term wrongly and end up changing things you did not mean to), then you can quickly roll back. Database backups are stored inside ~/.wordshell/current/<site>/database and can be restored using a tool such as phpMyAdmin; or see the relevant manual section.
The switch –tables is also provided, which allows you to restrict the search/replace operation to specific tables. Do not include the WordPress table prefix. e.g.
$ wordshell mysite --database --searchandreplace=http://localhost/testing^http://example.com --tables=options,usermeta