Mass substitute WordPress site Old domain URL to new URL in MySQL (MariaDB) database after website migration
If you have just migrated a wordpress blog or site to a new server (domain URL) and you have many articles pointing to the old URL. Out of sudden the new domain will end up with many broken links and that would have a severe negative SEO effect on your website leading to a certain downfall of your number of daily unique visits.
Of course manually changing the URL links is achievable by going through all Published Posts when migrating small websites with 10-20 pages, however it is an impossible tedious task you would definitely want to avoid when you're migrating large WP based websites with few hundred or thousands of posts / pages,
bacause this would be a few weeks of mindless repeatable job to go through each and every post and substite the broken URLs.
Fortunately with a little bit of SQL magic either through MySQL CLI or PHPMyAdmin (if the website is moved to a shared hosting where you have disabled access to MySQL (MariaDB) default connect tcp port 3306.
Depending on the type of WordPress or Website the old broken URLs might be located in various Database tables.
– So when Mass URL substitution is might be required ?
1. You migrate a Website http://what-ever-website.com with (PHP / CSS / HTML / Templates) etc. from Hosting Provider Hostgator.com to UK2.com (because the website target client changed lately to United Kingdom customers) to http://what-ever-website.co.uk and the site is moved to a new domain beacause of Business rebranding
2. Other reason for changing internal URLs from one URL to another might be if you're migrating your website from HTTP to HTTPS for security.
3. You are restructuring file storage / image directories on the server or due to migration of files to external CDN (Content Delivery Network).
For example (http://your-server.com/images/ , http://your-server.com/files ) URLs pointing to old website location subdirectory has to be changed to the new one (http://your-server.com/img/ , http://your-server.com/data)
– So what is the automated approach to solve the task of Mass URL substitution across WP site ?
- Create full backup of all your website database and double-check the backup (try to restore on a test (home) server or other hosting account to make sure the backup is consistent and restore would work normally if necessery
- You can Create Backup either with mysqldump command tool manually … with the right command arguments or use some kind of script such as My tiny mysqlbackupper.sh shell script which I shared under my previous article Make daily MySQL on Linux backup with Shell Script via PhpMyAdmin.
2. Change old website URL to new one directly from Database using MySQL text client
To change incorrect URL with the new correct one the general query to run is:
update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, ‘find string’, ‘replace string’);
To change old website URL to the new website URL across every table within the wordpress database use below queries:
hipo@linux:~$ mysql -u root -p
UPDATE wp_options SET option_value = replace(option_value, 'Existing (old) URL', 'New URL') WHERE option_name = 'home' OR option_name = 'siteurl';
UPDATE wp_posts SET post_content = replace(post_content, 'Existing (old)URL', 'New URL');
UPDATE wp_postmeta SET meta_value = replace(meta_value,'Existing (old) URL','New URL');
UPDATE wp_usermeta SET meta_value = replace(meta_value, 'Existing (old) URL','New URL');
UPDATE wp_links SET link_url = replace(link_url, 'Existing (old) URL','New URL');
UPDATE wp_comments SET comment_content = replace(comment_content , 'Existing (old) URL','New URL');
3. Replace Old website URL to New one after migration using PHPMyAdmin web interface
If you don't have access ssh shell, you can also run the queries via PhpMyAdmin to do so:
1. Open PHPMyadmin URL Panel in browser and login with your user / pass
2. Choose the wordpress database of the wordpress site / blog
3. Select SQL tab and in the panel type on above given SQL queries
If you're lazy to type there is also a web based SQL queries generator tool for moving websites to a new domain
4. Using Search and Replace WordPress plugin to do the old URL to new URL (strings) transition
If you have never used SQL queries and you're totally new to it and don't want to risk breaking up something there is also a bunch of wordpress plugins available that do the URL string substitution throughout each wordpress table in a WP database one such WordPress plugin is Search and Replace I have written earlier an article Change string in all WordPress Posts with Search and Replace plugin.
5. Problems with data-serialization
If you do a simple search and replace of Old domain urls to New ones, using above given commands and you still end up with some broken links on WP Pages that might be due to data-serialization issues (for the cause of issues check out what is data serialization).
Data serialization in wordpress terms is an array of PHP encrypted data that contains the actual URL, thus a simple search and replace as explained above if URLs use data-serialization would not work. There are available tools online that does URL search and replace operation through "serialize-data sensitively" if you stuck with data-serializatoin caused issues.
Besides that for there are written scripts that does URL substitution to a WordPress or Joomla websites so an alternative to above WP plugin to replace the URL after migration is to use one of the scripts available a very famous one that will do pain-free all URL / string substitutions inside your WP, Drupal, Joomla databses is interconnect/it.
Few closure words
As a system administrator and webmaster I have migrated wordpress installations many times with the need to change the old URLs to a new ones for both customer websites and my own wordpress based. On many ocassions because of lack of attention and hurry, I've messed up things.
The moral I got out of this is when you're doing a WordPress migration just like everything you have to be very attentive and do everything step by step slowly and have a good idea on what you're doing in advance …
Even as a person who had overall idea on how MySQL Server works and have experience in writting SQL queries, I have to confess I've made mistakes during URL substitution operations when doing it via the MySQL CLI every now and then.
Thus I would recommend you better use some of the many plugins for wordpress and script tools (few of which I mentioned above), especially if you're not having at least few years with some kind of UNIX variation / Linux / MySQL.