One of your responsibilities as a WordPress site owner is managing your database. While you don’t need to understand everything about how a database works, a basic understanding can enable you to perform a few essential tasks like fixing the “error establishing database connection” message.
Learning how to automatically update text in your database is another important part of database management. Say you’re moving to a new domain or adding an SSL certificate to your site. Or maybe you’ve renamed a product line and need to replace all instances across your site. In these cases, knowing how to perform a bulk WordPress search and replace can save you hours of time that otherwise you’d have to spend manually updating URLs and text.
In this post, we’ll look at how you can run a database search and replace query using a plugin or phpMyAdmin. We’ll walk through each of these methods below so you can decide which suits you and your level of technical expertise.
How to Perform Search and Replace on a WordPress Database
There are multiple ways you can perform a search and replace on a WordPress database. If you have no coding knowledge or would simply prefer not to deal with code, then you can use a plugin. If you have some technical experience, then you can write a custom SQL query and add it to phpMyAdmin in your hosting account’s control panel.
There are other ways to carry out database-wide search and replace actions, including installing a PHP script called Search Replace DB in the directory of your site or using the wp search-replace command. However, these methods can be difficult and risky for non-developers. One mistake might break your site or make it more vulnerable to hackers. In fact, site owners that had the searchreplacedb2.php script running on their servers were the target of a serious malware campaign in 2017.
For these reasons, we’ll limit our focus to using a plugin or MySQL query to search and replace text in your database. Both these methods pose less risk to your site’s security and are recommended for users with little to no technical knowledge.
No matter what method you choose, you should backup your WordPress site. That way, in case you enter the wrong information when making changes to the database, you can easily restore your site without losing any data.
Search and Replace WordPress Database Plugins
If you have no programming experience, then making changes to the WordPress database may seem daunting — but you don’t have to worry. A plugin will let you easily search and replace text in your database without touching any code.
Let’s look at the most popular search and replace plugins below.
1.Search & Replace
Search & Replace is a free plugin created by Inpsyde. With this plugin, you can replace URLs as well as words and phrases in your database right from your WordPress dashboard.
Say, for example, you added an SSL certificate to your site http://www.playground.dev. You’d write this HTTP version in the field labeled “Search for” and the HTTPS version in the field labeled “Replace with.” Then click the Do Replace Domain/URL button to update all the URLs in your database.
When updating words and phrases in the Search and Replace tab, you can check off the box labeled “Dry run” to review the changes before they’re made. This is an important feature since changes to the database are irreversible. It’s important to note that this feature is not offered on the Domain/URL tab, however.
What sets this plugin apart from other search and replace plugins is that users can create a backup of their database with one click of a button. Just navigate to the Backup Database tab and click the Create SQL File button, as shown below.
2.Better Search Replace
Better Search Replace is another free plugin that allows users to run database search and replace queries from their WordPress dashboard. This plugin by Delicious Brains does not have the functionality to let users create a backup of their database, but it does have a few features that Search & Replace does not.
When searching and replacing URLs or any text in your database, for example, you can perform a dry run to see how many sections (called tables) would be affected before actually replacing the text.
You can also use this plugin to search and replace text for a WordPress multisite installation rather than just a single site.
Writing a MySQL Query to Perform Search and Replace on a WordPress Database
If you have some coding knowledge and would rather not install another plugin on your site, then you can use phpMyAdmin to perform a search and replace in the WordPress database.
Before we discuss performing a search and replace in the database, let’s walk through how you can find your WordPress database name. You’ll need it in the next step.
From your control panel in your hosting account, navigate to Files > File Manager.
In the navigation menu on the lefthand side of your screen, click on the public_html folder. Please note that, depending on your hosting provider, this folder may also be called www, htdocs, or httpdocs.
Locate the wp-config.php file. Double-click the file to open it.
You can find the name in the following line:
define(‘DB_NAME’, ‘Database Name‘);
Make a note of it and then return to your dashboard. Navigate to the Databases section and click on the phpMyAdmin icon.
Locate your WordPress database, then click the Enter phpMyAdmin button. On the phpMyAdmin page, click on the SQL tab in the top menu bar.
You’ll add the following line of code into the text box:
update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, 'Text to search, 'Text to replace it with');
You’ll need to replace the placeholder texts with the correct information. Let’s define what each of these placeholder texts mean below.
- TABLE_NAME represents the name of the table (the section of the database) you want to modify. When you open the phpMyAdmin database, you can see your tables in the Structure tab. Some of the most common are wp_comments, wp_posts, and wp_users.
- FIELD_NAME represents the name of the field where the targeted data resides. You can see the field names when opening the table you want to edit.
- ‘Text to search’ represents the word or phrase that you want to change.
- ‘Text to replace it with’ represents the word or phrase that will replace the original text.
Imagine you run a food blog and realize you’ve misspelled the word “raspberry” in all your posts. To replace all instances of the misspelled word “rasberry” with the correct word “raspberry” in the body content of all posts and pages, your MySQL query would be:
update wp_posts set post_content = replace(post_content, 'rasberry, 'raspberry');
Once you are done writing your custom query, click on the Go button to execute it. All instances of the incorrect word will be replaced.
Simplifying Database Management
Whether you’re planning to change the name of a product line, move your site to a new server, change your domain name, or add an SSL certificate to your site, you can use either of the methods above to search and replace text in your database.
