How to Change the WordPress URLs in MySQL Database

In WordPress, the URLs of your website are stored in the MySQL database. If you need to change the URLs of your WordPress site, whether due to a domain change or moving from a local development environment to a live server, you can make the necessary changes directly in the database. In this guide, we will walk you through the steps to change the WordPress URLs in the MySQL database using the command line.

Step 1: Access MySQL:

First, you need to access your MySQL database using the command line. Open your terminal or SSH into your server and execute the following command:

mysql -u username -p

Replace ‘username’ with your MySQL username. You will be prompted to enter your MySQL password.

Step 2: Select the WordPress Database:

Once you are logged into MySQL, you need to select the WordPress database. If you know the name of your WordPress database, use the following command:

use database_name;

Replace ‘database_name’ with the actual name of your WordPress database.

For example:

mysql> use wordpressdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Check current value for ‘siteurl’ and ‘home’:

mysql> SELECT option_name, option_value FROM wp_options WHERE option_name IN ('siteurl', 'home');
+-------------+-----------------------------------+
| option_name | option_value                      |
+-------------+-----------------------------------+
| home        | https://www.webhostinggeeks.local |
| siteurl     | https://www.webhostinggeeks.local |
+-------------+-----------------------------------+
2 rows in set (0.00 sec)

If you are not sure about the database name, you can list all the available databases using the following command:

show databases;

Then, select the appropriate database using the use command as shown above.

Step 3: Update the URLs:

To change the URLs in the database, you need to update two specific tables: wp_options and wp_posts. Execute the following commands:

UPDATE wp_options SET option_value = replace(option_value, 'old_url', 'new_url') WHERE option_name = 'home' OR option_name = 'siteurl';

Replace ‘old_url’ with the current URL of your WordPress site and ‘new_url’ with the new URL you want to set.

For example:

mysql> UPDATE wp_options SET option_value = 'https://webhostinggeeks.com/howto' WHERE option_name IN ('siteurl', 'home');
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

Check the updated value for ‘siteurl’ and ‘home’ :

mysql> SELECT option_name, option_value FROM wp_options WHERE option_name IN ('siteurl', 'home');

+-------------+-----------------------------------+
| option_name | option_value                      |
+-------------+-----------------------------------+
| home        | https://webhostinggeeks.com/howto |
| siteurl     | https://webhostinggeeks.com/howto |
+-------------+-----------------------------------+
2 rows in set (0.00 sec)

Next, execute the following command to update the URLs in the wp_posts table:

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

Again, replace ‘old_url’ with the current URL and ‘new_url’ with the new URL.

Step 4: Confirm the Changes:

To ensure that the changes have been made successfully, execute the following commands:

SELECT option_value FROM wp_options WHERE option_name = 'home' OR option_name = 'siteurl';
SELECT guid FROM wp_posts WHERE post_type = 'post' OR post_type = 'page';

These commands will display the updated URLs in the wp_options table and the guid column of the wp_posts table.

Step 5: Exit MySQL:

Once you have confirmed the changes, you can exit the MySQL prompt by typing:

exit

Commands Mentioned:

• sudo htpasswd – Command to create a password file and set a password for a user.
• cat – Command to display the contents of a file.
• sudo service apache2 restart – Command to restart Apache web server on Ubuntu/Debian systems.
• sudo systemctl restart httpd – Command to restart Apache web server on CentOS/RHEL systems.
• sudo apachectl restart – Command to restart Apache web server on general systems.
• mysql -u username -p – Command to access MySQL database using the command line.
• use database_name; – Command to select a specific MySQL database.
• show databases; – Command to display all available MySQL databases.
• UPDATE wp_options SET option_value = replace(option_value, ‘old_url’, ‘new_url’) WHERE option_name = ‘home’ OR option_name = ‘siteurl’; – Command to update the URLs in the `wp_options` table of a WordPress database.
• UPDATE wp_posts SET post_content = replace(post_content, ‘old_url’, ‘new_url’); – Command to update the URLs in the `wp_posts` table of a WordPress database.
• SELECT option_value FROM wp_options WHERE option_name = ‘home’ OR option_name = ‘siteurl’; – Command to display the updated URLs in the `wp_options` table of a WordPress database.
• SELECT guid FROM wp_posts WHERE post_type = ‘post’ OR post_type = ‘page’; – Command to display the updated URLs in the `guid` column of the `wp_posts` table of a WordPress database.
• exit – Command to exit the MySQL prompt.

Conclusion:

In this guide, we have learned how to change the WordPress URLs in the MySQL database using the command line. By following these steps, you can update the URLs of your WordPress site directly in the database, which is useful when you need to change domains or migrate your site. Remember to take a backup of your database before making any changes and double-check the URLs to avoid any errors.

Feel free to comment below if you have any questions or suggestions for improvement.