Update WordPress URL easily using MySQL

This post was originally published at webinuse.com

After we migrate to a new URL we need to update WordPress URL. We can use plugins for migration, or we can use Export/Import option from WordPress. Yet, the most straightforward way is to just update the URL using MySQL. Especially if the site stays on the same host.

If you want to learn more about WordPress you can check this awesome course Building websites with WordPress by Nat Miletic. This course is excellent for those who want to start with WordPress. But it is also for those who have experience, and this course can be used as a reminder of some WordPress functionalities.

Affiliated link

Most of the websites that are using WordPress are on shared hosting, and a large number of hosting companies are offering PHPMyAdmin for administering databases. So, all of our examples will be using PHPMyAdmin, plus shell access.

1. Create backup of your database

The first thing when changing a database is always to make a backup. If we make any mistake during the database update, we can easily restore it to the previous state.

Also, we should never do the update on the live site. Especially if we work with a larger database, or with slower connections. It can take a few minutes, even more, to restore the database to the desired state.

In order to back up the database, we should open PHPMyAdmin, select the database that we want to backup, and select the “Export” option. After we select the “Export” option, we can leave everything on default and just click on the “Go”.

If we are changing the hosting/server, we must do the same thing. We have to export the database in order to import it to another host/server.

1.1 Import database

If we are changing the host once we exported the database, we need to import it. We need to create a new database on a new host/server and open PHPMyAdmin. After we’ve done it, we select the database we’ve created, and then we select the “Import” option. Then, we select the file from our computer (the one we exported) and click the “Go”.

2. Update WordPress URL

Once we are ready for the new URL we select the “SQL” option in the menu and paste the following code.

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

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

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

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

Four queries above update WordPress URL in four different tables, respectively. Usually, that is all we need to change. If we use some plugins that are storing our URL in their tables, we should change those.

We can use the command line to update WordPress URL. Please check the following code.

username@[~/Desktop]: mysql -u root -p databasename
Enter password:

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

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

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

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

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

3. Finish

If we used the same hosting provider and the same database, our site should now work on the new URL.

If we migrated to the new hosting/server we may have to change our wp-config.php file.

If you have any questions or anything you can find me on my Twitter, or you can read some of my other articles like Arrow functions in JavaScript. How to easily implement them?.