How to search and replace data in the WordPress database?

Changing the system domain in WordPress often may require more work than just modifying some system constants or specific values. Depends on the system it may require direct changes in the database, and that’s why it is good to know how to make such changes without spending hours on manual work.

Problem

Why is this so important? WordPress database contains serialized data that may be incorrectly changed without using proper tools. Let’s analyze a simple example of storing serialized array with the value stored in url key:

a:1:{s:3:"url";s:17:"https://example.com";}
Code language: CSS (css)

Please pay attention to s:17 part that just informs that the value consists of 17 characters. If the value that has another length will be changed without paying attention to this, there is a huge chance that more parts will be broken.

Solution

wp search-replace

Using WordPress CLI is the best way to perform such database operations. It provides simple command search-replace that search all the occurrences of the required structure and changes to the required one.

wp search-replace example.org example.com --all-tables
Code language: CSS (css)

In the example above, all occurrences of example.org will be changed to example.com. It is good to add --all-tables option to make changes in all the database tables. It is important, because if you work in multisite, not all tables may be analyzed without that option.

This is recommended way when it comes to the development and deployment process on local machines. But if you need to work directly on the server, you’ll need to have wp-cli installed in your infrastructure. If that’s not possible, you can use the SRP script that is described in the next chapter.

More information about this command is available here.

Search Replace Script

Another possibility is using the script provided by interconnect that provides GUI for changing the required occurrences with the new ones. That’s the preferable way when there is no access to wp-cli on the server or local machine.

  1. Perform installation using the official documentation. Please be aware of security vulnerabilities – if you put this script in the server without any password validation, all the database credentials will be exposed to public view.
  2. Input the sentence or structure that needs to be changed, and input the sentence that should be used instead.
  3. Check the results using test mode by clicking do a safe test run.
  4. If everything goes well, click Search and Replace and wait until finished.

Add the first comment and start a discussion

Leave a Reply

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

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

Cookies

Service namePurposeSettings
Cookies funkcyjneCookies required for the operation of the website.