Table of Contents
Introduction
In this tutorial, How to Change WordPress URLs in MySQL Database using the command line. WordPress uses the MySQL database to store all its data, including site URLs.
Determining the Name of WordPress MySQL Database
WordPress stores the MySQL database name and its credentials in the wp-config.php file. Example below
[devopsroles@server1 ~]$ egrep -A 10 DB_NAME /Wordpress/web/wp-config.php
define('DB_NAME', 'wordpressdb');
/** MySQL database username */
define('DB_USER', 'HuuPV');
/** MySQL database password */
define('DB_PASSWORD', 'devopsroles.com');
/** MySQL hostname */
define('DB_HOST', 'localhost');Change WordPress URLs
I will change from the Old site: https://linuxoperatingsystem.net to the New site: http://192.168.122.229
Determining the site URL currently with the command below
mysql> SELECT * FROM wp_options WHERE option_name='siteurl';
+-----------+-------------+----------------------------------+----------+
| option_id | option_name | option_value | autoload |
+-----------+-------------+----------------------------------+----------+
| 1 | siteurl | https://linuxoperatingsystem.net | yes |
+-----------+-------------+----------------------------------+----------+
1 row in set (0.00 sec)
mysql> Command-line change WordPress URLs in MySQL Database
mysql -u root -p Database_Name
mysql> UPDATE wp_options SET option_value = replace(option_value, 'https://linuxoperatingsystem.net', 'http://192.168.122.229') WHERE option_name = 'home' OR option_name = 'siteurl';
mysql> UPDATE wp_posts SET guid = replace(guid, 'https://linuxoperatingsystem.net','http://192.168.122.229');
mysql> UPDATE wp_posts SET post_content = replace(post_content, 'https://linuxoperatingsystem.net', 'http://192.168.122.229');
mysql> UPDATE wp_postmeta SET meta_value = replace(meta_value,'https://linuxoperatingsystem.net','http://192.168.122.229');The output is as below:
mysql> show databases;
+-------------------------+
| Database |
+-------------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| wordpressdb |
+-------------------------+
6 rows in set (0.00 sec)
mysql> use wordpressdb;
Database changed
mysql>
mysql> UPDATE wp_options SET option_value = replace(option_value, 'https://linuxoperatingsystem.net', 'http://192.168.122.229') WHERE option_name = 'home' OR option_name = 'siteurl';
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> UPDATE wp_posts SET guid = replace(guid, 'https://linuxoperatingsystem.net','http://192.168.122.229');
Query OK, 43 rows affected (0.03 sec)
Rows matched: 43 Changed: 43 Warnings: 0
mysql> UPDATE wp_posts SET post_content = replace(post_content, 'https://linuxoperatingsystem.net', 'http://192.168.122.229');
Query OK, 29 rows affected (0.01 sec)
Rows matched: 43 Changed: 29 Warnings: 0
mysql> UPDATE wp_postmeta SET meta_value = replace(meta_value,'https://linuxoperatingsystem.net','http://192.168.122.229');
Query OK, 0 rows affected (0.00 sec)
Rows matched: 73 Changed: 0 Warnings: 0
Conclusion
If you’re managing a WordPress site, you may find yourself needing to change the URLs directly in the MySQL database, especially after a migration or domain change. This process involves accessing your MySQL database through a command line interface and using SQL commands to update the site URL across different tables such as wp_options, wp_posts, and wp_postmeta. It’s a crucial skill for maintaining the integrity of your WordPress site links. Always back up your database before making such changes to avoid any potential data loss. For detailed guidance, consider visiting DevOpsRoles for tutorials.
