Find and Replace all URLs or Text in a WordPress Database

Introduction

There are many reasons why you would need to find and replace URLs or text in a WordPress database. Often it is because you are migrating from one domain to another, or changing from non-SSL to SSL. Sometimes you may need to replace a certain text phrase such as company name.

The best way to find and replace content in your WordPress database is by running SQL queries in either phpMyAdmin, shell command line or a PHP script. If you are not comfortable working directly with the database, we have included some plugins at the bottom of the page.

⚠ Always make sure to back up your WordPress database before running any queries or plugins.

“One Fell Swoop”

This article gives instructions on how to update individual tables in your WordPress DB. However, if you want to just find and replace everything in your DB, you could export your entire WordPress DB from phpMyAdmin as a .sql file, then open that file in a good text editor such as Notepad++ or Sublime, do a find and replace for the domain name or text phrase you want to replace, then re-import the database via phpMyAdmin. Of course, you would want to make absolutely sure you have a complete backup of your DB and that you know what you’re doing. I plan to update this guide with instructions for this particular process, but in the meantime, you can run the SQL commands below to update individual tables.

Domain Change and SSL Examples

Whether it’s a domain name change and/or a change from non-SSL (http://) to SSL (https://), we can use the MySQL replace function to replace any URLs in the WordPress database.

In the following queries, we will find and replace old_domain.com with new_domain.com, useful if you are migrating your WordPress site to a different domain. You could also use these queries to replace non-https links, e.g. https://devanswe.rs with https://devanswe.rs.

There are a few different tables you should update if you are changing your domain name:

Table: wp_options

wp_options – this is the WordPress configuration table, which contains your site URL in the home and siteurl fields.

UPDATE wp_options SET option_value = replace(option_value, 'old_domain.com', 'new_domain.com') WHERE option_name = 'home' OR option_name = 'siteurl';

Table: wp_posts

wp_posts – this table contains all your post content in the post_content field. If you have any internal permalinks in your posts to other posts, you will need to replace the domain in all permalinks here. Also within this table is the guid field, containing the permalink of the post. Only change this field if you know what you are doing (see below).

UPDATE wp_posts SET post_content = replace(post_content, 'old_domain.com', 'new_domain.com');

Within the wp_posts table is also the guid field.

The Global Unique Identifier appears in this field in the form of a URL. Official documentation states that you are not meant to change this field, even if your site URL changes. Think of the GUID as a unique hash. Altering it will mean that feedreaders will suddenly display all your content in the user’s reader again as new content (read more).

However, the guid is used by the Advanced Custom Fields Relationship field to relate other posts, so if you are using ACF, you should most definitely change the guid.

UPDATE wp_posts SET guid = replace(guid, 'old_domain.com', 'new_domain.com');

Table: wp_postmeta

wp_postmeta – this contains all your attributes and custom fields, including Advanced Custom Fields (ACF) in the meta_value field. If you have any permalinks in your custom fields, you will need to replace them here.

UPDATE wp_postmeta SET meta_value = replace(meta_value, 'old_domain.com', 'new_domain.com');

Table: wp_comments

wp_comments – contains all your post comments, which may contain permalinks to other posts on your site.

UPDATE wp_comments SET comment_content = replace(comment_content , 'old_domain.com', 'new_domain.com');

Table: wp_yoast_indexable – Yoast SEO Plugin

If you are using the Yoast SEO plugin and have upgraded your db to Yoast ‘indexibles’, and are migrating from one domain to another, you will need to find and replace the fields permalink, twitter_image, open_graph_image, and open_graph_image_meta in the wp_yoast_indexable table.

UPDATE wp_yoast_indexable SET permalink = replace(permalink, 'old_domain.com', 'new_domain.com');

UPDATE wp_yoast_indexable SET twitter_image = replace(twitter_image, 'old_domain.com', 'new_domain.com');

UPDATE wp_yoast_indexable SET open_graph_image = replace(open_graph_image, 'old_domain.com', 'new_domain.com');

UPDATE wp_yoast_indexable SET open_graph_image_meta = replace(open_graph_image_meta, 'old_domain.com', 'new_domain.com');

Table: wp_links

I’ve read several other guides suggesting you alter the link_url field in the wp_links table. This now-deprecated table was used for the WordPress Links feature, discontinued in Dec 2012. You can probably ignore this field unless your WordPress install is really old.

Replace Text in Posts

Let’s say you are rebranding and changing your company name from “DevAnswers Co.” to DevAnswers Inc.”, you can use the SQL replace function exactly the same way.

There are a few different tables you should update if you want to replace all instances of text:

Table: wp_options

wp_options – this is the WordPress configuration table, which contains the name of your site in the blogname field.

UPDATE wp_options SET option_value = replace(option_value, 'DevAnswers Co.', 'DevAnswers Inc.') WHERE option_name = 'blogname';

Table: wp_posts

wp_posts – this table contains all your post content in the post_content field.

UPDATE wp_posts SET post_content = replace(post_content, 'DevAnswers Co.', 'DevAnswers Inc.');

Table: wp_postmeta

wp_postmeta – this contains all your attributes and custom fields, including Advanced Custom Fields (ACF) in the meta_value field. If you have any content in your custom fields, you will need to replace your text here also.

UPDATE wp_postmeta SET meta_value = replace(meta_value, 'DevAnswers Co.', 'DevAnswers Inc.');

Table: wp_comments

wp_comments – contains all your post comments, which may contain instances of the text you want to replace.

UPDATE wp_comments SET comment_content = replace(comment_content , 'DevAnswers Co.', 'DevAnswers Inc.');

Update the Database

Now that you understand which queries to run, we’ll go through a few different methods on how to run your queries.

Method 1 – phpMyAdmin

The easiest way to find and replace text in your WordPress database without having to install a plugin is through phpMyAdmin. Most shared hosts provide phpMyAdmin where you can administer your databases.

Log in to phpMyAdmin and click your database name in the left-hand pane.

Select the SQL tab and paste in your prepared queries.

⚠ Back up your database before running these queries because you cannot undo them!

Click Go and your query should run.

Method 2 – Command Line

If you have shell access to your web server, you can run your database queries directly in command line.

If using MySQL, log in using your WordPress database credentials.

mysql -u root -p database_name

Once you see the mysql> prompt, enter your SQL queries and press ENTER.

⚠ Back up your database before running these queries because you cannot undo them!

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, 'https://devanswe.rs', 'https://devanswe.rs');

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, 'https://devanswe.rs', 'https://devanswe.rs');

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 , 'https://devanswe.rs', 'https://devanswe.rs');

Query OK, 0 rows affected (0.01 sec)
Rows matched: 10 Changed: 0 Warnings: 0

Once finished, exit MySQL.

exit

Method 3 – PHP Script

If you don’t have phpMyadmin or shell access, you can use a PHP script.

Simply enter your database credentials and place your query into the $sql variable.

⚠ Back up your database before running these queries because you cannot undo them!

<?php
$mysqli = db_connect('localhost','database_username','database_password','database_name'); 

$sql = "UPDATE wp_posts SET post_content = replace(post_content, 'https://devanswe.rs', 'https://devanswe.rs'"; 

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();

Method 4 – Use a Plugin

Our ethos is not to install plugins unless absolutely necessary! But if you are not comfortable working directly with the database, a plugin might be for you.

There are two plugins we’ve tested which are highly rated and reliable.

1. Search & Replace

This will search and replace URLs or any text. It also includes a backup facility.

2. Better Search Replace

This performs the same task but has more features and a Pro upgrade option.

Let me know if this helped. Follow me on Twitter, Facebook and YouTube, or 🍊 buy me a smoothie.