One of the most common reasons for changing a site's domain is transferring it from a local server to a production server. The process of transferring a site is usually different for different engines, for some engines it is enough to edit the configuration files so that all links on the site are updated. As for the sites implemented in WordPress, it is also necessary to search and replace URLs in the database.
Here we will look at an example of replacing URLs in a database using a few key queries. It is worth immediately agreeing that the database has already been imported to the production server, and we have access to the SQL console at hand. In most cases, queries are easier to execute through the phpMyAdmin utility .
Preparing SQL queries
Take your time to copy and run queries right away into the database. First, they should be inserted into a text editor, and form them in the correct way. First, let's agree that we have two domains old-site.com and new-site.com , respectively, the first domain is the old domain, the second is the one that should be replaced.
Accordingly, there are several tables in which replacement should be performed.
UPDATE wp_options SET option_value = REPLACE(option_value, 'http://old-site.com', 'https://new-site.com') WHERE option_name = 'home' OR option_name = 'siteurl';
UPDATE wp_posts SET post_content = REPLACE (post_content, 'http://old-site.com', 'https://new-site.com');
UPDATE wp_posts SET guid = REPLACE (guid, 'http://old-site.com', 'https://new-site.com');
UPDATE wp_postmeta SET meta_value = REPLACE (meta_value, 'http://old-site.com','https://new-site.com');
UPDATE wp_comments SET comment_content = REPLACE (comment_content, 'http://old-site.com', 'https://new-site.com');
UPDATE wp_comments SET comment_author_url = REPLACE (comment_author_url, 'http://old-site.com', 'https://new-site.com');
As you can see, there are several tables involved in this process.
- wp_options - a table with various parameters (you need the home and siteurl parameters);
- wp_posts - a table with posts, there links can be directly in the text itself;
- wp_postmeta - meta-fields for posts, in fact, these are related tables with wp_posts;
- wp_comments - a table with comments to posts, replacement in case there were any internal links.
Key points to look out for
Firstly, this is the table prefix - in my example, the table prefix is specified as wp_ , this is the default value, but it is possible that it may differ for you. Secondly, pay attention to the spelling of the domains, namely the http and https protocols , so that the replacement is performed correctly. Sometimes there are cases that links on the site are found both with the http protocol and https. In this case, requests to replace links can be performed twice for each type of links separately.
How to Change Domain Name / Website Address / URL of Your Wordpress Site (video)
How to update all urls from WordPress database