Migrating a WordPress site can be a little tricky, especially if you are not using an automated deployment solution or you are not doing this type of work very often. Let’s say you’ve finished building your WordPress site locally and you want to move it on your server, or you want to change your hosting provider, while there are multiple ways you can do this, unless your deployment method includes a search and replace in MySQL (before or after you import the database file on the new server), you will probably have to replace the WordPress URLs in MySQL yourself.

WordPress uses two variables stored in the wp_options table: ‘siteurl‘ and ‘home‘. Now, you can be tempted to just replace these and hope it will work. Actually, while this step will at least bring your website up and you can now see your homepage, chances are, if you try to click on your links, some will send you back to your previous URLs. Why is that? Well, just by updating ‘siteurl’ and ‘home’, you haven’t taken care of the links stored in our posts, attachments or content. So how do we update these ones?

Updating the links after moving a WordPress site by using phpMyAdmin

More often than not, your hosting provider gives you access to phpMyAdmin in order to manage your databases. I remember that beginner feeling about using raw SQL queries that could mess up my entire database and the sweat after pressing the GO button. So, for those not confident enough in their typing skills and especially for the lazy ones, I’ve made a small generator. Just add in your old domain and the new domain and paste the output generated in your phpMyAdmin SQL window (or you can run these queries manually).

  1. Open phpMyAdmin interface, select the new database and click on the SQL tab;
  2. Replace old_url and new_url below and copy the output generated;
http://
http://
UPDATE wp_options SET option_value = replace(option_value, 'http://old_url', 'http://new_url') WHERE option_name = 'home' OR option_name = 'siteurl';

UPDATE wp_posts SET guid = replace(guid, 'http://old_url','http://new_url');

UPDATE wp_posts SET post_content = replace(post_content, 'http://old_url', 'http://new_url');

UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://old_url','http://new_url');

UPDATE wp_options SET option_value = replace(option_value, 'http://www.old_url', 'http://new_url') WHERE option_name = 'home' OR option_name = 'siteurl';

UPDATE wp_posts SET guid = replace(guid, 'http://www.old_url','http://new_url');

UPDATE wp_posts SET post_content = replace(post_content, 'http://www.old_url', 'http://new_url');

UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://www.old_url','http://new_url');

 

Now Paste this in phpMyAdmin and run the query by pressing the GO button. That’s it!

If you want the code for the generator, you can find it on GitHub.