How to Mass Change old URLs in Website Database after Migration
Migrating your WordPress website to a new URL and unsure about where and how to update old URLs in database? Do you want to skip the tedious process of manually changing the URLs? Learn the painless and quick methods to update site URLs across your website.
Many times, there is a requirement to migrate your WordPress website to a new URL. This would need you to update the old URLs in the database, including the various MySQL database tables. For smaller websites, this can be managed by manually making the changes. However, for larger websites, the volume of the site makes it impossible to update old URLs manually. Another issue is that Webmasters find it mundane and routine work to bulk change the links on the website. This is a time-consuming process as at times the number of URLs to be updated can be in the hundreds. Added to this is the chance to miss some URLs. Situations like these make it almost mandatory to look for automated solutions to do a mass search and replace of the old URLs. Let us look at some of the convenient ways to update site URLs when migrating a WordPress site.
When to update WordPress website URLs?
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:
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');
- For images inside posts
- 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.
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.
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.
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
- 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.
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.
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.
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.