Ever had that “oh no” moment when you realize that the URLs in your WordPress database are pointing all over the place, especially after a domain change or a significant site update? It’s a common scenario, triggering a need to dive deep into the database for a swift clean-up.
Today, we’ll explore how to find and replace a URL in a WordPress database—a must-know skill for maintaining the integrity and functionality of your website.
Whether it’s moving your site to a new domain or correcting old links that no longer work, mastering this technique can save you from SEO disasters and skewed user experiences.
By the end of this article, you’ll learn the step-by-step process of updating URLs seamlessly and efficiently using tools like phpMyAdmin, WP-CLI, and reliable plugins designed for safe URL transitions.
We’ll look into direct SQL queries, the nuances of handling serialized data, and ensuring that every link reflects the current, correct path—keeping both your users and search engines happy.
Reasons for Replacing URL or Text in a WordPress Database
There are many reasons to find and replace URLs and text in a WordPress database. The most common reasons are moving to a new domain or adding an SSL certificate to a website.
At times, site owners may want to modify or change a specific text phrase such as the company name. Knowing how to perform a bulk find and replace can save lots of time.
It’s also possible to employ the same method on more than text. It can switch out URLs and images in your WordPress database. Mass find and replace capability proves useful when needing to:
- Update the Search Engine Optimization (SEO) keywords
- Correct spelling or grammar mistakes (by using a grammar and punctuation checker)
- Remove special characters or specific words
- Change and update URLs in the WordPress database when moving the website to a new host
- Fix broken links
- Update outdated content, shortcodes, or strings of text
Updating a website’s URL means more than changing the URL on the settings page. There are other things to remember and handle.
If the images on the website do not have an external CDN, the links to the previous URL are still embedded in the post. These links will display as broken when anyone visits the page.
In the case of small websites or blogs, it is workable to replace a couple of URLs manually. However, it is not feasible on large websites and blogs that have many images across the site.
The only practical way is to mass replace all the appropriate URLs in the database.
Note that having performed a bulk find and replace, there is no “undo” button. It is not possible to go back to the previous version.
The only way would be to conduct another massfind and replace process. So it is advisable to carry out a backup before a mass WordPress search and replace procedure.
The best method to find and replace content in a WordPress database is to run SQL queries. Input the required SQL queries into phpMyAdmin, a shell command line, or a PHP script.
For those who may prefer a plugin, there is a list of recommended plugins at the end of the article.
How to Write a MySQL Query to Conduct Find and Replace On a WordPress Database
Maybe you don’t want to install any more plugins but you do know a little bit about coding. In this case, it’s possible to use phpMyAdmin to conduct a find and replace in the WordPress database.
First though a little guidance on how to find your WordPress database name. This is necessary for the next step.
Click on the public_html folder (or www, htdocs, or httpdocs, depending on the hosting provider). This is usually found in the navigation menu on the left-hand side of the screen.
Find the wp-config.php file and double-click to open it. The name of your database is in the line:
define(‘DB_NAME’, ‘Database Name‘);
Note it down and go back to the dashboard. In the Databases section, click on the phpMyAdmin icon.
Find the WordPress database and click on the Enter phpMyAdmin button. Then, on the top menu bar, click on the SQL tab.
Insert 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’);
Here’s a brief explanation of what each of the placeholder texts means. This will help you to replace them with the correct information.
TABLE_NAME stands for the name of the table to alter.
Hey, did you know data can be beautiful too?
wpDataTables can make it that way. There’s a good reason why it’s the #1 WordPress plugin for creating responsive tables and charts.
And it’s really easy to do something like this:
- You provide the table data
- Configure and customize it
- Publish it in a post or page
And it’s not just pretty, but also practical. You can make large tables with up to millions of rows, or you can use advanced filters and search, or you can go wild and make it editable.
“Yeah, but I just like Excel too much and there’s nothing like that on websites”. Yeah, there is. You can use conditional formatting like in Excel or Google Sheets.
Did I tell you you can create charts too with your data? And that’s only a small part. There are lots of other features for you.
Find the names of all tables in the Structure tab in the phpMyAdmin database. These include wp_comments, wp_posts, and wp_users.
FIELD_NAME stands for the name of the field where the targeted data is. All the field names are displayed in the table you wish to modify.
‘Text to search’ stands for the word or phrase that requires editing.
‘Text to replace it with’ stands for the word or phrase that will replace the original text.
For example:
A food blogger notices that they have misspelled the word “raspberry” in all their posts. To correct this mistake and replace all occurrences of “rasberry” with “raspberry”, the MySQL query would look like this:
update wp_posts set post_content = replace(post_content, 'rasberry, 'raspberry');
Having ensured that the query is written correctly, click on the Go button to make the required change.
Running a Database Query in the Command Line
Running a database query directly in the command line is possible if you have shell access to the webserver.
If you are using MySQL, sign in using your WordPress database credentials.
mysql -u root -p database_name
At the mysql> prompt, enter the desired SQL queries and press ENTER.
mysql> UPDATE wp_options SET option_value = replace(option_value, 'old_domain.com', 'new_domain.com') WHERE option_name = 'home' OR option_name = 'siteurl';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2 Changed: 0 Warnings: 0
mysql> UPDATE wp_posts SET post_content = replace(post_content, 'http://domain.co', 'https://domain.co');
Query OK, 0 rows affected (0.08 sec)
Rows matched: 354 Changed: 0 Warnings: 0
mysql> UPDATE wp_postmeta SET meta_value = replace(meta_value, 'http://domain.co', 'https://domain.co');
Query OK, 0 rows affected (0.01 sec)
Rows matched: 22 Changed: 0 Warnings: 0
mysql> UPDATE wp_comments SET comment_content = replace(comment_content , 'http://domain.co', 'https://domain.co');
Query OK, 0 rows affected (0.01 sec)
Rows matched: 10 Changed: 0 Warnings: 0
Once finished, exit MySQL.
exit
Writing a Query Using PHP Script
If you don’t have phpMyAdmin or shell access, another option is to use PHP script.
After entering your database credentials, insert the query into the $sql variable.
<?php
$mysqli = db_connect(‘localhost’,’database_username’,’database_password’,’database_name’);
$sql = “UPDATE wp_posts SET post_content = replace(post_content, ‘http://domain.co’, ‘https://domain.co'”;function db_connect($host,$user,$pass,$db) { $mysqli = new mysqli($host, $user, $pass, $db); $mysqli->set_charset(“utf8”); if($mysqli->connect_error) die(‘Connect Error (‘ . mysqli_connect_errno() . ‘) ‘. mysqli_connect_error()); return $mysqli;}if ($mysqli->query($sql) === TRUE) { echo “Record updated successfully”;} else { echo “Error: ” . $sql . “” . $mysqli->error;}$mysqli->close();
Remember that if executed incorrectly, this script could break your WordPress website. If in doubt, double-check with a developer or the web host.
Search and Replace Using a Plugin
Using a plugin is the best option for those without coding experience.
The following tested plugins are reliable and received high ratings from users.
Better Search Replace
This plugin will update URLs across all or several selected tables in a database. Users can execute search and replace commands from inside their WordPress admin area.
WP Migrate DB
This plugin is solid and reliable for searching and replacing data inside serialized data. Users install the plugin on the original site.
Conducting a search and replace on a URL string and web root generates a new database dump. This information is then exported.
Users import this into the new URL hosted database.
Search & Replace
This is a free plugin developed by Inpsyde. Users can replace URLs, words, and phrases in their WordPress database straight from the dashboard.
What makes this plugin special is the ability to create a database backup by clicking one button. To do this, go to the Backup Database tab and click on the Create SQL File button.
Velvet Blues Update URLs
This plugin enables the modification of URLs in posts, pages, excerpts, and more. Users insert the old and new URLs and choose the places in which to update them.
Change the plugin settings in Tools » Update URLs page.
FAQ on how to find and replace URLs in the WordPress database
Why is it necessary to find and replace a URL in a WordPress database?
When moving your WordPress site to a new domain, URLs embedded in your database remain unchanged. These outdated links can break site functionality, disrupt user experience, and affect SEO rankings. Replacing them ensures your site operates flawlessly post-move.
How can you safely replace URLs in a WordPress database?
Using a reliable plugin like Better Search Replace or the WP-CLI command-line tool can ensure a safe replacement process. These methods prevent potential data corruption and handle serialized data, preserving the integrity of your WordPress configurations and content.
What tools are available for replacing URLs in WordPress?
Several tools streamline this process: phpMyAdmin for manual replacements through SQL queries, WP-CLI for command-line efficiency, and plugins such as Velvet Blues or Better Search Replace for user-friendly, GUI-driven updates.
Can you manually change URLs in the WordPress database using phpMyAdmin?
Yes, phpMyAdmin can be used to manually update URLs. However, caution is necessary. Direct SQL queries, such as UPDATE statements, can replace URLs but pose risks if executed incorrectly. Always back up your database first.
What are the risks of incorrectly replacing URLs in the database?
Incorrect URL replacements can lead to broken links, disrupted user access, lost SEO value, and even a complete site breakdown. To avoid these risks, thorough checks and a backup before changes are mandatory.
How does replacing URLs affect SEO?
Properly updating URLs maintains SEO efforts by ensuring links continue redirecting to the correct pages. Incorrect updates could result in 404 errors, negatively impacting search engine rankings and user experience.
Why is handling serialized data important when replacing URLs?
Serialized data in WordPress contains length-sensitive information. Any change in URL lengths without proper adjustment in serialization can corrupt data, leading to potential errors and site functionality issues.
What steps should you take before replacing URLs?
Always backup your database first. Then, plan your approach, whether through a plugin, WP-CLI, or phpMyAdmin. Understanding the scope of URL changes necessary will guide your choice of method.
Is there a way to automate the process of changing URLs across a WordPress site?
Yes, automation is possible using plugins like Better Search Replace, which offers capabilities to search and replace data across multiple tables efficiently, saving time and minimizing human errors.
After replacing URLs, what should be the immediate steps to ensure everything works correctly?
Post-replacement, thoroughly test your WordPress site. Check for broken links, ensure that all pages load correctly, and monitor for unexpected behaviors. Updating your site’s sitemap and resubmitting it to search engines can also help realign SEO indexing.
Conclusion
As we wrap up this exploration into the art of how to find and replace a URL in a WordPress database, remember that mastering this skill is like holding the key to maintaining your site’s integrity during crucial transitions. Whether using phpMyAdmin, WP-CLI, or dependable WordPress plugins, each tool offers a tailored approach to effectively updating your database links.
- Back up your database; it’s your safety net.
- Choose the right tool for the job, something you’re comfortable with that meets the needs of your site.
- Test thoroughly after updates to avoid any disruptions in site functionality.
Getting URLs right post-update not only secures a smoother user experience but also shields your SEO efforts from potential pitfalls. Each step you take—from choosing a method to the final checks—is pivotal in the sustainable management of your digital presence.
If you enjoyed reading this article on how to find and replace URLs in a WordPress database, you should check out this one about how to do a WordPress database reset.
We also wrote about a few related subjects like WordPress database schema, how to do a WordPress database cleanup, and how to scan WordPress database for malware.
