Use PHPMyAdmin script for Search and Replace in Database

MySQL lets you execute raw queries to find and replace and is used to update old URLs in the database. Using PHPMyAdmin 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 via PHPMyAdmin

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.

In case you are not confident about running the MySQL queries then you can choose to do it with a WordPress Plugin.

WordPress Plugins – SearchReplace

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.

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.