Find and Update Wordpress Site and Home URL using MySQL

Find and Update Wordpress Site and Home URL using MySQL

It is common practice to use phpmyadmin to query databases like for a wordpress site for example. This guide explains the steps in using MySql in a Terminal to query a wordpress database, (prefix)_options table, to view or update the Site and Home url’s. They are set in the wordpress dashboard, but if incorrect can cause problems logging in. Fixing the url’s can be done using phpmyadmin, or MySql in a terminal while in the wordpress main directory as shown below.

check DB values in config, without using wpcli

Navigate to the user Wordpress install directory. Then we start by using the cat command which reads the contents of a file, in this case wp-config.php. We also use a pipe ( the | character, not the letter L or lower case l) which connects the contents from the cat command to the grep command. The grep command then acts like a filter only looking for the text string ‘DB’. We are only interested in the DB, that is the database, connection parameters for this installation.

$ cat wp-config.php | grep DB

The DB parameters can now be seen in the terminal like this:

define( ‘DB_NAME’, ‘wpcli’ );
define( ‘DB_USER’, ‘root’ );
define( ‘DB_PASSWORD’, ‘root’ );
define( ‘DB_HOST’, ‘localhost’ );
define( ‘DB_CHARSET’, ‘utf8’ );
define( ‘DB_COLLATE’, ‘’ );

open mysql without using wp-cli

Use DB_USER after the -u, DB_PASSWORD when prompted, and DB_NAME at the end

$ mysql -u root -p wpcli
Enter password:
(enter DB_PASSWORD here from above)

OR skip steps above and open mysql with DB settings using wp-cli

$ wp db cli

The steps below work with or without using wp-cli

Use the mysql command show tables to view the database tables. The table with the word ‘options’ is what we are looking for.

$ mysql> show tables;

    ±----------------------+

    | Tables_in_wpcli |

    ±----------------------+

    | wp_commentmeta |

    | wp_comments |

    | wp_links |

    | wp_options |

    | wp_postmeta |

    | wp_posts |

    | wp_term_relationships |

    | wp_term_taxonomy |

    | wp_termmeta |

    | wp_terms |

    | wp_usermeta |

    | wp_users |

    ±----------------------+

    12 rows in set (0.00 sec)
  

Make sure we are using the correct db and options table name!
We are in the wpcli db as shown on top line Table_in_wpcli
The name of the options table is wp_options in this case. The name of the options table might have a different prefix, but look for the word options to get the exact name.

get current values of siteurl and home using options table name, wp_options in the case

The home and siteurl values correspond to the settings in the Wordpress dashboard for Wordpress Address (URL) and Site Address (URL).

 

Sometimes when there are problems and the user can’t login, or working on a migration, it may help to check and update the correct values in the database. Use https in values if using https! First we query to see the existing values.

    $ mysql> select * from wp_options where option_name=‘siteurl’ or option_name=‘home’;
  

±----------±------------±-----------------------±---------+
| option_id | option_name | option_value
±----------±------------±-----------------------±---------+
| 2 | home | http://pjova.reclaim.hosting/wp
| 1 | siteurl | http://pjova.reclaim.hosting/wp
±----------±------------±-----------------------±---------+
2 rows in set (0.00 sec)

update values of siteurl and home to same value

For this article we are just using the value ‘TEST’ to see how it works.

$ mysql> update wp_options set option_value=“TEST” where option_name=‘home’ or option_name=‘siteurl’;

±----------±------------±-------------±---------+
| option_id | option_name | option_value | autoload |
±----------±------------±-------------±---------+
| 2 | home | TEST | yes |
| 1 | siteurl | TEST | yes |
±----------±------------±-------------±---------+
2 rows in set (0.00 sec)

Now change the values back to what they should be. If you migrate a site and have a different domain name, these values would need to be updated to the new domain name. See also Using the WP-CLI to Perform a Search and Replace.

$ mysql> update wp_options set option_value=“http://pjova.reclaim.hosting/wp” where option_name=‘home’ or option_name=‘siteurl’;

OR update siteurl and home one at a time if needed.

If for some reason the siteurl and home need a different value, you could update each one at a time as needed.

$ mysql> update wp_options set option_value=" http://pjova.reclaim.hosting/wp" where option_name=‘home’;
$ mysql> update wp_options set option_value=" http://pjova.reclaim.hosting/wp" where option_name=‘siteurl’;

verify that the siteurl and home match what they are in wp admin dashboard.

±----------±------------±-----------------------±---------+
| option_id | option_name | option_value
±----------±------------±-----------------------±---------+
| 2 | home | http://pjova.reclaim.hosting/wp
| 1 | siteurl | http://pjova.reclaim.hosting/wp
±----------±------------±-----------------------±---------+
2 rows in set (0.00 sec)

quit mysql

$ \q (enter)