search and replace in the database

search and replace in the database

I. Presentation

In this tutorial, we are going to use WP-CLI in a way to perform a “find and replace” type action in the database of a WordPress site, in a simple way, without writing our own SQL query.

To manipulate the database of your WordPress site, you can use SQL queries or rely on an interface such as PhpMyAdmin. Nevertheless, this implies mastering the SQL language and knowing the structure of the WordPress database. Also, a bad SQL query can be devastating….

The WP-CLI tool allows you to interact with your WordPress site directly from the command line! Easy to install, it will allow you to perform many actions on the site: install an extension, manage roles, update WordPress, manage articles, etc…. This project is supported by various companies, including Automattic, the company behind WordPress.

As you will have understood by reading the title of this article, today we are going to focus on a specific case: find and replace a string in the WordPress database. Recently, I used this technique for a site that changed its domain name so that it was taken into account on the internal links of articles, in the configuration of certain plugins, etc… Because changing the address of the site in the WordPress settings is not always sufficient.

II. Installation de WP-CLI

The installation of WP-CLI is simple and is done in a few commands, on the server where the WordPress site is deployed. Following the installation, the machine has a new command named “wp”. From your Linux shell, you need to run the following command to get the installer:

curl -O 

Once done, run the command below to get information about the downloaded file and verify that it is ready to work:

php wp-cli.phar --info

All that remains is to change the rights on the file to add execution rights, then we will move it to “/usr/local/bin” under the name of “wp“so that one can use it simply with the command name”wp“.

chmod +x wp-cli.phar
sudo mv wp-cli.phar /usr/local/bin/wp

All you have to do is use the tool!

III. Find and Replace with WP CLI

The WP tool has an action named “search-replace” which corresponds exactly to what we want to do. Different options allow us to refine the query: we will see some of them. Let’s assume that we want to find and replace “” by “”.

The first thing to do is to position yourself at the root of the WordPress installation directory. For example, if my WordPress site is installed in “/var/www/wordpress/“, I have to position myself in this directory otherwise WP-CLi will not work!

cd /var/www/wordpress/

Then, you must first specify the string to search and replace, then the substitution string. Which give :

wp search-replace '' ''

However, we are not going to stop there. The “–dry-run” option is super useful, because it allows to run the query in simulation mode, ie to have the result without actually making the changes. This gives you an idea of ​​the impact of the command you are about to execute. So, if it looks correct, we can run the same query without this option to apply the changes! Although simple to use, WP-CLI is very powerful so it must be used with caution!

Thus, the previous command becomes:

wp search-replace '' '' --dry-run

Running this command, we get a list of database tables, with column names and the number of occurrences to replace each time. In the example below, there are 4,550 replacements to be made! The detail by column of each table is displayed.

WP-Cli Search and replace

Let’s imagine that we want to exclude a table from the processing, because we know that there is information that should not be replaced, we can specify it. For example, with the table “wordpress_users” corresponding to users:

wp search-replace '' '' --skip-tables=wordpress_users --dry-run

Once the result of the simulation is satisfactory, we can apply the changes:

wp search-replace '' '' --skip-tables=wordpress_users

As for the simulation, the operation is relatively fast! Finally, I want to draw your attention to some additional options:

  • –all-tables : include all the tables in the perimeter, because without this option, the tables linked to the extensions are not analyzed! To cover the entire WordPress installation, this option is useful.
  • –allow-root : used to execute WP commands with the root account directly (if you are logged in as root on the server)
  • –skip-columns : exclude a column from a table, rather than an entire table

Here is an example of use:

wp search-replace '' '' --skip-tables=wordpress_users --all-tables --allow-root

IV. Conclusion

You have just taken your first steps with WP-CLI! A very useful tool to find and replace it in the WordPress database! Do not neglect the “–dry-run” option, it is essential to avoid unpleasant surprises! There are other options detailed in the official help available at this address:


#search #replace #database

Leave a Comment

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