How do I perform a search-replace on my database?

Modified on Thu, 03 Feb 2022 at 07:06 PM

A search-replace is used to execute a change on several placing in the database. Most of the time it is used to change the domain of a site or when a site has to be configured for HTTPS. During a search-replace the computer will search for a specific prefined piece of text in your database and replace it with a new text. A search-replace is a powerful tool to make adjustments in your database, but can also provide a lot of errors (and even downtime) when used incorrectly. We strongly recommend you to create a backup in admin.savvii.nl before you start in case something goes wrong.


We will discuss two ways to perform a search-replace on your database:

  1. The Better Search Replace plugin
  2. With WP-CLI (SSH required)


The Better Search Replace plugin

The easiest way to perform a search-replace is with the help of the plugin Better Search Replace. There are of course other plugins with the same functionalities, if you prefer another option. In order to keep it simple we will focus on Better Search Replace.


1. Install the plugin in your WordPress admin panel.

2. Click on "Tools" and then "Better Search Replace". 

3. In the example below we will change the URL of our fictional site (http://www.example.com) to HTTPS (https://www.example.com). Enter the values* you want to search and replace and then select all tables. So when Do not change the GUID and leave the box "Replace GUIDs" unchecked. We recommend to execute a dry-run first to test the command.


*Take note that the command will make the change literally. So the changes from "www.example.com" to "https://www.example.com" can result in our URL being "http://https://www.example.com/ ".



4. Check whether the output shows the right results. The image below is an example of a possible output.



5. When the output is correct: remove the check in the box "Run as dry run" and perform a search-replace.

6. Empty all cache to actually see the results of your changes.


With WP-CLI (SSH required)


SSH connection is only possible (in beta) on VPS accounts and not automatically included. Create a support ticket to join our beta. Once you are connected with SSH you can perform a search-replace with WP-cli. With the instructions below we will change your records from HTTP to HTTPS.


1. Go to the folder /wordpress/current/.

2. Enter the command for a search-replace: wp search-replace "old" "new" --skip-columns=guid --dry-run.* The dry-run let's you test the command. It will show you an output similar to the example below:


*Take note that the command will make the change literally. So the changes from "www.example.com" to "https://www.example.com" can result in our URL being "http://https://www.example.com/ ".


$ wp search-replace 'http://www.example.com' 'https://www.example.com' --skip-columns=guid --dry-run
+------------------+-----------------------+--------------+------+
| Table            | Column                | Replacements | Type |
+------------------+-----------------------+--------------+------+
| wp_commentmeta   | meta_key              | 0            | SQL  |
| wp_commentmeta   | meta_value            | 0            | PHP  |
| wp_comments      | comment_author        | 0            | SQL  |
| wp_comments      | comment_author_email  | 0            | SQL  |
| wp_comments      | comment_author_url    | 0            | SQL  |
| wp_comments      | comment_author_IP     | 0            | SQL  |
| wp_comments      | comment_content       | 0            | SQL  |
| wp_comments      | comment_approved      | 0            | SQL  |
| wp_comments      | comment_agent         | 0            | SQL  |
| wp_comments      | comment_type          | 0            | SQL  |
| wp_links         | link_url              | 0            | SQL  |
| wp_links         | link_name             | 0            | SQL  |
| wp_links         | link_image            | 0            | SQL  |
| wp_links         | link_target           | 0            | SQL  |
| wp_links         | link_description      | 0            | SQL  |
| wp_links         | link_visible          | 0            | SQL  |
| wp_links         | link_rel              | 0            | SQL  |
| wp_links         | link_notes            | 0            | SQL  |
| wp_links         | link_rss              | 0            | SQL  |
| wp_options       | option_name           | 0            | SQL  |
| wp_options       | option_value          | 1            | PHP  |
| wp_options       | autoload              | 0            | SQL  |
| wp_postmeta      | meta_key              | 0            | SQL  |
| wp_postmeta      | meta_value            | 0            | PHP  |
| wp_posts         | post_content          | 0            | SQL  |
| wp_posts         | post_title            | 0            | SQL  |
| wp_posts         | post_excerpt          | 0            | SQL  |
| wp_posts         | post_status           | 0            | SQL  |
| wp_posts         | comment_status        | 0            | SQL  |
| wp_posts         | ping_status           | 0            | SQL  |
| wp_posts         | post_password         | 0            | SQL  |
| wp_posts         | post_name             | 0            | SQL  |
| wp_posts         | to_ping               | 0            | SQL  |
| wp_posts         | pinged                | 0            | SQL  |
| wp_posts         | post_content_filtered | 0            | SQL  |
| wp_posts         | post_type             | 0            | SQL  |
| wp_posts         | post_mime_type        | 0            | SQL  |
| wp_term_taxonomy | taxonomy              | 0            | SQL  |
| wp_term_taxonomy | description           | 0            | SQL  |
| wp_termmeta      | meta_key              | 0            | SQL  |
| wp_termmeta      | meta_value            | 0            | SQL  |
| wp_terms         | name                  | 0            | SQL  |
| wp_terms         | slug                  | 0            | SQL  |
| wp_usermeta      | meta_key              | 0            | SQL  |
| wp_usermeta      | meta_value            | 0            | PHP  |
| wp_users         | user_login            | 0            | SQL  |
| wp_users         | user_nicename         | 0            | SQL  |
| wp_users         | user_email            | 0            | SQL  |
| wp_users         | user_url              | 0            | SQL  |
| wp_users         | user_activation_key   | 0            | SQL  |
| wp_users         | display_name          | 0            | SQL  |
+------------------+-----------------------+--------------+------+
Success: 1 replacement to be made.
Text


3. Check the output and the old and new values entered. 

4. Execute the command without the parameter "dry-run". 

5. Empty all cache to actually see the results of your changes.


Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select atleast one of the reasons

Feedback sent

We appreciate your effort and will try to fix the article