How to change WordPress home and SiteURL through MySQL CommandLine

Oct 24 • All, WordPress • 696 Views • No Comments on How to change WordPress home and SiteURL through MySQL CommandLine

We are all familiar with how to change the WordPress home and siteurl through WordPress Admin page. If you are not, you can read about it [here]. Also, you can override the WordPress settings by specifying the home/siteurl in the wp-config file. You can see how to do this [here].

In this tutorial, you will learn how to change the siteurl specified in WordPress database using MySQL command line method. So let’s start fresh. Assuming you are not sure of the password/database associated with the blog, follow any of the below steps to get the database credentials of the database associated with the blog

1: If you have SSH access to the account, navigate to the document root of the blog and issue the following command.

grep -E ‘DB_NAME|DB_USER|DB_PASSWORD’ wp-config.php

Eg:

user@server [~/public_html/example.com]# grep -E ‘DB_NAME|DB_USER|DB_PASSWORD|DB_HOST’ wp-config.php

define(‘DB_NAME’, ‘example_wp790’);

define(‘DB_USER’, ‘example_wp790’);

define(‘DB_PASSWORD’, ’64(PU7vG[S’);


define(‘DB_HOST’, ‘localhost’);

2: If you have cPanel or any control panel access, login to your account and through the FileManager application, open the wp-config.php file in the document root of the blog and note the entries against the DB_NAME, DB_USER, and DB_PASSWORD.

3: If none of the above is available, then you can FTP to your account and download the wp-config.php file locally and open it to view the fields.

Now that you have the database credentials, the next step is to connect to the database. In this case, the database is on the same server itself as can be seen from the DB_HOST field. If you have SSH Access to your account, issue the following command to connect to your database.

mysql -u database-user -p database_name

Enter the password of the database when prompted.

Eg:

user@server [~/public_html/example.com]# mysql -u example_wp790 -p example_wp790


Enter password:

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A


Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 59306024

Server version: 5.5.45-cll-lve MySQL Community Server (GPL)


Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.


Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.


mysql>

If you are connecting to the database remotely then use the following format.

mysql -u database-user -p database_name -h server_name

It is necessary to whitelist the IP you are connecting from in cPanel >> RemoteMySQL if you are on a cPanel server and sometimes also through the server depending upon the host configuration. If you find trouble connecting, contact your host for exact instructions to sort it out. You can find the IP you are connecting from by visiting whatsmyip

Now that you have logged in, let’s look at how to get the home/siteurl changed.

The following command will display all the tables in your database.

SHOW TABLES;

This below command will display all the columns in wp_options table.

SELECT option_name FROM wp_options;

The following command will list out the option name “home” from wp_options table.

SELECT * FROM wp_options WHERE option_name = ‘home’;

To change the value for ‘home’, issue the following command. Remember to change the http://www.newwebsite.com/ field as the website name to be assigned. Also, remember to keep the trailing slash ‘/’

UPDATE wp_options SET option_value=”http://www.newwebsite.com/” WHERE option_name = “home”;

And now, to view the SiteURL currently used, issue the following query.

SELECT * FROM wp_options WHERE option_name = ‘siteurl’;

and to update siteurl

UPDATE wp_options SET option_value=”http://www.newwebsite.com/” WHERE option_name = “siteurl”;

Now the website should load up correctly with the new URL. 🙂

Related Posts

Leave a Reply

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

« »