MySQL database calls – fix urls – clickety-clack.click

[code style=”php”]

/* The simple query that you need to run to replace all strings that contains http:// with https:// is the following: */

UPDATE `tableName` SET `columnName` = REPLACE( `tableName`.`columnName`, “http://”, “https://” )

UPDATE wp_posts SET post_content=(REPLACE (post_content, ‘https://www.thesword.com’,’http://cdn.thesword.com’));
watch out for curly quotes –

UPDATE wp_posts SET post_content=(REPLACE (post_content, ‘http://cdn.thesword.com’,’http://thesword.com’));

UPDATE wp_posts SET post_content=(REPLACE (post_content, ‘http://thesword-full.local’,’http://cdn.thesword.com’));

UPDATE wp_posts SET post_content=(REPLACE (post_content, ‘http://cdn.cdn.thesword-full.com’,’http://cdn.thesword.com’));

UPDATE wp_posts SET post_content=(REPLACE (post_content, ‘http://cdn.cdn.thesword.com/wp-content/uploads’,’http://cdn.thesword.com/wp-content/uploads’)); 
UPDATE wp_posts SET guid = REPLACE (guid, ‘http://thesword.com”, ‘http://cdn.thesword.com’) WHERE post_type = ‘attachment’;

UPDATE wp_posts SET post_content = REPLACE (post_content, ‘src="http://thesword.com’,%20’src="http://cdn.thesword.com’);

UPDATE wp_posts SET post_content = REPLACE (post_content, ‘src="http://cdn.cdn.thesword.com’,%20’src="http://cdn.thesword.com’);
[/code]

how to change/update links with mysql
You can use the MySQL replace command to search and replace text inside database tables.

For updating the URL we need to run the below query over multiple tables and fields.

update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, ‘find string’, ‘replace string’);

Open the PHPMyAdmin panel and log in.
Click the WordPress database.
For replacing the URL across all database tables,
Click on SQL tab and in the panel type the below code:

UPDATE wp_options SET option_value = replace(option_value, ‘Existing URL’, ‘New URL’) WHERE option_name = ‘home’ OR option_name = ‘siteurl’;
UPDATE wp_posts SET post_content = replace(post_content, ‘Existing URL’, ‘New URL’);
UPDATE wp_postmeta SET meta_value = replace(meta_value,’Existing URL’,’New URL’);
UPDATE wp_usermeta SET meta_value = replace(meta_value, ‘Existing URL’,’New URL’);
UPDATE wp_links SET link_url = replace(link_url, ‘Existing URL’,’New URL’);
UPDATE wp_comments SET comment_content = replace(comment_content , ‘Existing URL’,’New URL’);

If you have images linked in your posts then you need to run the following additional queries.
For images inside posts
UPDATE wp_posts SET post_content = replace(post_content, ‘Existing URL’, ‘New URL’);

For images linked in old link manager
UPDATE wp_links SET link_image = replace(link_image, ‘Existing URL’,’New URL’);

For images linked as attachments
UPDATE wp_posts SET guid = replace(guid, ‘Existing URL’,’New URL’);

The above queries run for default tables that as discussed above are known to contain URL entries in WordPress.
You may also need to add other tables which are not default with WordPress.
Click the ‘Go’ button.
The Existing URL would be updated site-wide to the new URL.

Note:
Further if you like to change any URL/link and need to generate queries for the same, you can use this tool.

How to Mass Change old URLs in Website Database after Migration

How to Mass Change old URLs in Website Database after Migration

When to update WordPress website URLs?

Here at Templatetoaster website maker, Let us take a look at the common scenarios that lead to a requirement to update old URLs in the database.

Migrate a WordPress site to a new web server
Moving the website from one domain to another on the same web server
Moving from HTTP to HTTPS
Change WordPress directories on existing server

When updating a site’s URL we need to know that there are additional things to be taken care of other than just changing the URL in the settings page. If your images do not have an external CDN, the links to the old URL will be embedded in the posts. These links would appear broken whenever someone views the page. For smaller websites and blogs it is feasible to simply replace two or three URLs manually. For larger websites and blogs that have a huge number of images, it’s not feasible to change each one. You need to look for an automated approach to bulk replace all old URLs in the database.

Methods to Change the old URL in Database

The foremost thing to make sure before you change the URLs is that you take a complete backup of your WordPress database. You can easily revert in case the update process goes wrong. You can choose to do a manual backup of the database or use one of the free WordPress plugins. The detailed process of how to backup your database is given here. Let us now look at the various methods to use for changing the URLs for a WordPress site.

1. Manually – for small websites

For a small website, you can choose to manually replace URLs across the Website. Most of the time you have to look out for the content where you have placed a link to home page and other internal pages, and simply edit them. This method is only suggested when you have a really small website or a one-page website since this will avoid the overhead of a plugin installation on your website.

2. Changing the URL directly in the database

Note that there are additional concerns with other methods, as references to the old URL will persist in the database. Two known issues that users face frequently are:

Old URLs in widgets and menus: The old URL can exist not only in posts but also in widgets and menus.
Broken Image and Video links: If you have successfully replaced the site URL, it is possible that the images are not functioning properly. This can cause issues with page display if you do not update image URLs.

The URLs are stored in many database tables and you can change them manually. The URLs for custom menu items are present in the meta_value field in the wp_postmeta table. The image URLs are present inside the posts_content field in the wp_posts table. For the old link manager, the image URLs are present in the link_image fields in the wp_links fields. You need to be careful in what you replace. So be sure you are aware of the meaning of the field before you change it. Here’s a quick list of places where you can find the URL:

Inside posts and pages: “posts_content” field in the “wp_posts” table
The old link manager: “link_url” and “link_image” fields in the ‘wp_links” table
URLs of Custom Menu items: “meta_value” field in the “wp_postmeta” table
Options or themes and plugins: “option_value” field in the “wp_options” table
URLs inside comments: “comment_content” field in the “wp_comments” table

You can update old URLs in the database by making the change in each of the above-mentioned tables and fields. But this method is cumbersome and highly time-consuming. Basically, we choose to implement tools and plugins which are easy, safe and quick to use.

3. Use PHPMyAdmin script for Search and Replace in Database

The MySQL lets you execute raw queries to find and replace and is used to update old URLs in the database. This is a handy way to change URLs if you have a large website with a considerable number of changes to make.

As we have seen above that there is not only the need to update two values in the Settings, but also the countless image references and links may be present in the posts and options table too.

The PHPMyAdmin allows you to do a quick update of all links on your website by directly updating the old URL in WordPress database. You can use the SQL statements based on MySQL replace () function to update the URLs in the database. You first need to sign to the MySQL database using PHPMyAdmin before you start with the process to update old URLs in the database. You can also login to the DB server and run MySQL client as root.

How to Change/Update links with MySQL

You can use the MySQL replace command to search and replace text inside database tables.

For updating the URL we need to run the below query over multiple tables and fields.

update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, ‘find string’, ‘replace string’);

Open the PHPMyAdmin panel and log in.
Click the WordPress database.
For replacing the URL across all database tables, Click on SQL tab and in the panel type the below code:
[code style=”php”]
<!–mep-nl–><code style=’display: none;’><!–mep-nl–></code>UPDATE wp_options SET option_value = replace(option_value, ‘Existing URL’, ‘New URL’) WHERE option_name = ‘home’ OR option_name = ‘siteurl’;
<!–mep-nl–><code style=’display: none;’><!–mep-nl–></code>
<!–mep-nl–><code style=’display: none;’><!–mep-nl–></code>UPDATE wp_posts SET post_content = replace(post_content, ‘Existing URL’, ‘New URL’);
<!–mep-nl–><code style=’display: none;’><!–mep-nl–></code>
<!–mep-nl–><code style=’display: none;’><!–mep-nl–></code>UPDATE wp_postmeta SET meta_value = replace(meta_value,’Existing URL’,’New URL’);
<!–mep-nl–><code style=’display: none;’><!–mep-nl–></code>
<!–mep-nl–><code style=’display: none;’><!–mep-nl–></code>UPDATE wp_usermeta SET meta_value = replace(meta_value, ‘Existing URL’,’New URL’);
<!–mep-nl–><code style=’display: none;’><!–mep-nl–></code>
<!–mep-nl–><code style=’display: none;’><!–mep-nl–></code>UPDATE wp_links SET link_url = replace(link_url, ‘Existing URL’,’New URL’);
<!–mep-nl–><code style=’display: none;’><!–mep-nl–></code>
<!–mep-nl–><code style=’display: none;’><!–mep-nl–></code>UPDATE wp_comments SET comment_content = replace(comment_content , ‘Existing URL’,’New URL’);
<!–mep-nl–><code style=’display: none;’><!–mep-nl–></code>[/code]

If you have images linked in your posts then you need to run the following additional queries.

For images inside posts

UPDATE wp_posts SET post_content = replace(post_content, 'Existing URL', 'New URL');

For images linked in old link manager

UPDATE wp_links SET link_image = replace(link_image, 'Existing URL','New URL');

For images linked as attachments

UPDATE wp_posts SET guid = replace(guid, 'Existing URL','New URL');

The above queries run for default tables that as discussed above are known to contain URL entries in WordPress. You may also need to add other tables which are not default with WordPress.
Click the ‘Go’ button.
The Existing URL would be updated site-wide to the new URL.

Note: Further if you like to change any URL/link and need to generate queries for the same, you can use this tool.

4. WordPress Plugins

In case you are not confident about running the MySQL queries then you can choose to do it with a WordPress Plugin. These plugins let you update old URLs in the database. Some of the plugins provide separate options to replace the site URL and to change the URL in all the database tables. The use of a WordPress plugin for mass URL changing would give you the following benefits:

Complete search and replace in the database.
All links, image links and other media links would be updated.
Image GUIDs for attachments would be updated.
URLs inside custom menu items would be updated.
Serialized Data would be handled appropriately.

Search and Replace Plugin

The Search and Replace plugin provides an interface in WP-Admin to search and replace text across the website or blog. You can perform a simple search or a full “search and replace”. You should try and do a plain search first before you perform the search and replace for all pages and posts. You can select the radio button “All – only search!” and type in the search term which is the old URL. Next, select the “Content” checkbox and click “Go”. This will perform a simple SQL search for all occurrences of the old URL in the posts. You can easily see the broken links.

Better Search Replace Plugin

This is a simple plugin to update URLs in a WordPress database. The Better Search Replace plugin allows a user to replace the URL within all or a few selected database tables.

Velvet Blues Update URLs

The Velvet Blues Update URLs plugin provides options to change the URLs at places like posts and pages, excerpts, etc. You just need to enter the old and new URLs of your website. After that, you choose where the URLs should be updated.

You can configure the plugin settings from Tools » Update URLs page.

The plugin provides the following options to change:

Post and page content
Excerpts
Attachments
Custom fields
All GUIDs

Once you select each item that you want to update you need to click on the ‘Update URLs Now’ button. The plugin will find and replace all occurrences of the old URL with the new URL.

WP Migrate DB Plugin

The WP Migrate DB is a proven and robust plugin that can search and replace data inside serialized data. The plugin is installed on the original site. Once you run a search and replace on URL string and web root, a new database dump is exported. You then need to import this into the new URL hosted database.

URL Replace and Serialized Data

A simple search and replace for changing old URLs in the database works most of the time. However, a very common issue seen these days is the presence of Serialized data in databases. Serialized data is an array of PHP data encrypted with the actual URL. If you do a search and replace to change the old URLs in the database, you can cause problems with data serialization. The issue is that the URL is changed and serialized data is no longer visible. In such cases, you need to use “serialize-data sensitive” tools to search and replace the old URLs. Let us look at some of the recommended tools and plugins available that can handle serialized data and do a search and replace of site URLs.

Interconnectit Tool

This is a Search and Replace script developed by the Interconnect Company. It is also applicable to other popular CMS like Drupal and Joomla. You can run the script using the steps given below:

Backup your database.
Upload the script to a public directory of your hosting (but not in the website root).
Open the uploaded folder on your server from the browser’s address bar.
The script launches automatically. You then need to choose the database and links you are going to replace.

Final Thoughts

Webmasters may often need to change the URLs for their WordPress websites. The process to change old URLs in the database is very detailed and needs a lot of patience if done manually. In most cases, a simple search and replace will work for small websites and blogs. However, “serialized data” inside the database can lead to some serious problems. It is preferable to use a “serialize-data sensitive” search and replace tool or plugin to change the image and site URLs. All these tools would help when moving a website and replacing one URL with another one. However, if you are a beginner and not confident about the process, then you should take help from a specialist. You will not only avoid any possible problems but also preserve your time and efforts.

Search and Replace all URLs and links in WordPress, HTTP to HTTPS

Find and Replace all URLs or Text in a WordPress Database

Last updated on May 18th, 2020

Whether it’s a domain name change or a transition from HTTP to HTTPS, in this guide we will find and replace URLs (or any text) in your WordPress database using a few different methods.

SQL Query Examples

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.

Domain Change Example

We can use the MySQL replace function to replace any text 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.

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

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';

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

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

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

About the guid field – The Global Unique Identifier appears in this field in the form of a URL. 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. Only change this if you understand the possible repercussions. (read more). However, note that the guid may be used by the Advanced Custom Fields Relationship field to relate posts, so if you are using that, you should probably change the guid.

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

HTTP to HTTPS Example

Another common WordPress URL change is when moving your site to SSL. The following queries will find and replace all instances of http://devanswers.co with https://devanswers.co.

There are a few different tables you should update if you are migrating to HTTPS:

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, 'http://devanswers.co', 'https://devanswers.co') WHERE option_name = 'home' OR option_name = 'siteurl';

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 http:// part 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, 'http://devanswers.co', 'https://devanswers.co');

UPDATE wp_postmeta SET meta_value = replace(meta_value, 'http://devanswers.co', 'https://devanswers.co');

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 , 'http://devanswers.co', 'https://devanswers.co');

UPDATE wp_posts SET guid= replace(guid, 'http://devanswers.co', 'https://devanswers.co');

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:

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';

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.');

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.');

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.');

What about the wp_links table?

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.

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.

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, 'http://devanswers.co', 'https://devanswers.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://devanswers.co', 'https://devanswers.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://devanswers.co', 'https://devanswers.co');

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

Once finished, exit MySQL.

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, 'http://devanswers.co', 'https://devanswers.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();

?>

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.

5.00 (2 votes)

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

p.s. I increased my AdSense revenue by 68% using AI 🤖. Read my Ezoic review to find out how.

MySQL database calls – fix urls

When to update WordPress website URLs?

Methods to Change the old URL in Database

1. Manually – for small websites

2. Changing the URL directly in the database

3. Use PHPMyAdmin script for Search and Replace in Database

How to Change/Update links with MySQL

4. WordPress Plugins

Search and Replace Plugin

Better Search Replace Plugin

Velvet Blues Update URLs

WP Migrate DB Plugin

URL Replace and Serialized Data

Interconnectit Tool

Final Thoughts

Find and Replace all URLs or Text in a WordPress Database

SQL Query Examples

Domain Change Example

HTTP to HTTPS Example

Replace Text in Posts

What about the wp_links table?

Update the Database

Method 1 – phpMyAdmin

Method 2 – Command Line

Method 3 – PHP Script

Method 4 – Use a Plugin

1. Search & Replace

2. Better Search Replace

Image Editor - Bitmap PIXIE
Palleon | Palleon Documentation

Image Editor - Vector

When to update WordPress website URLs?

Methods to Change the old URL in Database

1. Manually – for small websites

2. Changing the URL directly in the database

3. Use PHPMyAdmin script for Search and Replace in Database

How to Change/Update links with MySQL

4. WordPress Plugins

Search and Replace Plugin

Better Search Replace Plugin

Velvet Blues Update URLs

WP Migrate DB Plugin

URL Replace and Serialized Data

Interconnectit Tool

Final Thoughts

Find and Replace all URLs or Text in a WordPress Database

SQL Query Examples

Domain Change Example

HTTP to HTTPS Example

Replace Text in Posts

What about the wp_links table?

Update the Database

Method 1 – phpMyAdmin

Method 2 – Command Line

Method 3 – PHP Script

Method 4 – Use a Plugin

1. Search & Replace

2. Better Search Replace

Image Editor - Bitmap PIXIE Palleon | Palleon Documentation

Image Editor - Vector

Image Editor - Bitmap PIXIE
Palleon | Palleon Documentation