WordPress Change URL in Database After Migrating Website

URLs( Uniform Resource Locators ) are the permanent links to your content or posts in WordPress. It is commonly known as “Permalinks” in WordPress’s vocabulary.

We usually don’t replace the urls in our WordPress site, but in some instances we have to replace the old urls with new ones. Below are some of the cases in which we have to perform the update.

  1. After migrating the site to a new domain.
  2. You have a site in localhost and want to take it live.
  3. You have a SSL certificate updated and a series of mixed content errors.

After we migrate our existing site from production environment to live site or change our domain, the URL strings need to be updated in the database as well. Not only this, but some cases might also include SSL certificate update after which we have to replace the permalinks strings in our database.

As WordPress uses MySQL as its database management system, we’ll be looking at the process of changing and updating WordPress URLs in MySQL database. Various tables in our site database contain these URL strings, therefore the URLs have to be changed and updated in every table that contains the permalinks info.

We are going to swamp the whole MySQL database rather than simple export/import as this method uses the whole MySQL database. Hence we recommend making a copy of old WordPress files/folders, take the required permissions and perform the action.

Caution: If you are not comfortable using the MySQL, then use some plugins for this.

Let’s go.

  • Step 1: Make a copy of the database by exporting it on the old database.
  • Step 2: Create a new fresh database on the new server.
  • Step 3: Import the old database that you have exported in step 1 to the new database that was created on step 2.
  • Step4: Prepare the commands to run on your tables i.e ‘wp_options’, ‘wp_posts’ & ‘wp_postmeta’. But before this make sure you have selected the database that you recently created.

Use the below given MySQL query and change your old Urls with the new one. If required you can change the table prefix to ‘wp_’ wherever required.

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

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

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

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

If you are comfortable using the command line then you can do as shown below.

mysql> UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldurl', 'http://www.newurl') WHERE option_name = 'home' OR option_name = 'siteurl';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2 Changed: 0 Warnings: 0

mysql> UPDATE wp_posts SET guid = replace(guid, 'http://www.oldurl','http://www.newurl');
Query OK, 0 rows affected (0.02 sec)
Rows matched: 964 Changed: 0 Warnings: 0

mysql> UPDATE wp_posts SET post_content = replace(post_content, 'http://www.oldurl', 'http://www.newurl');
Query OK, 0 rows affected (0.05 sec)
Rows matched: 964 Changed: 0 Warnings: 0

mysql> UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://www.oldurl','http://www.newurl');g
Query OK, 0 rows affected (0.01 sec)
Rows matched: 686 Changed: 0 Warnings: 0

Finally, now you have to update your WP config file to make the new database accessible.

For this you need to discover the ‘wp-config.php’ file in your web document root and change the ‘databasename’, ‘username’, ‘password’ & ‘host’ values accordingly.

define('DB_NAME', 'databasename');

/** MySQL database username */
define('DB_USER', 'username');

/** MySQL database password */
define('DB_PASSWORD', 'password');

/** MySQL hostname */
define('DB_HOST', 'localhost');

Now as everything looks fine, you’ll be able to reach your site with the new updated URL.

Serialized Data

Sometimes updating your URLs requires some more effort , this is because of the Serialized data. If you have hooked up the data of your site with the old URLs then after updating the URls those data might be lost.

To overcome this issue, there are some handy tools which can easily handle this issue for you.

Search Replace DB by interconnectit

Database Search and Replace Script

It is a script which makes you able to run easily. You simply need to upload it and browse it after you have completed the migration process of your old database to new one. Rest is handled by the script itself, you just sit back and relax.

WP Migrate DB

WP Migrate DB
Next one in the row is the very popular and widely used plugin. This is very easy to use. You have to install this plugin on your original site and run it from there.

You have to do the find and replace procedure on the domain string and Webroot. This plugin will then dump a new database and is exported for you to import into the new database which you have created on your desired location. All the hectic tasks are done by the plugin itself.

Conclusion:

Changing and updating a WordPress permalinks is a very arduous task and if you don’t have proper knowledge or Common WordPress Errors , you may make your site lost. Therefore, we have created this post for you to understand this arduous task and change it to a very easy one. Having knowledge about MySQL will be an advantage for you.

Inspire us with your love!

Facebook Twitter Pinterest Reddit

Art of Awesome Professional WordPress themes & Site templates. Our responsive themes are designed to work seamlessly for all desktop and mobile devices. Modern, Clean & Easy To Use.

You can check also

Leave a Reply

Your email address will not be published. Required fields are marked *.

*
*