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. Firstly, you need to sign to the MySQL database using PHPMyAdmin before you start with the process to update old URLs in the database.
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:
[sql] 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’);
[/sql]
- 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.
In case you are not confident about running the MySQL queries via phpMyAdmin then you can simply use 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
Basically, 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”. 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 certainly a simple plugin to update URLs in a WordPress database. Basically the Better Search Replace plugin allows a user to replace the URL within all or a few selected database tables.
Final Thoughts regarding PHPMyAdmin and Mysql database search
Webmasters may often need to change the URLs for their WordPress websites. Additionally 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. Basically 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.
