Have you locked yourself out of WordPress due to some upgrade or restore mishap in combination with the lack of a valid email address to restore it to? Welcome to the Club of lucky administrators! There is a solution to this particular problem that just begs to be shouted from the rooftops: why don’t you reset your WordPress password using MySQL (or MariaDB). Let’s get right to it.
Resetting a user’s password for any CMS, in fact, involves roughly the same steps.
Step 1. Connect to the remote WordPress host via ssh
Connect to your remote WordPress host using SSH (our preferred SSH client is PuTTY, but any other client will do just as well):
ssh -i "your-secret-key-file.pem" username@ip.add.re.ss
You may need to become root in order to access the web server document directory tree. This is done using:
sudo su
Step 2. Navigate to the directory containing the affected WordPress installation
The web server document directory may be located in any number of places. Its exact location depends on how the server was set up originally (find out more here). NGINX can use several web server directories and each of them can be located in a different place. This information is found in the configuration file that corresponds to each particular website. NGINX site-specific configuration files are stored in /etc/nginx or one of its subdirectories (try sites-available/). Open the appropriate configuration file and make a note of the location of the web server root for your website in a notepad on your local computer or on good old-fashioned piece of paper, then cd to this place, for example:
cd /var/www/www.website1.tld/
Navigate to the WordPress installation that is found inside the web server document directory.
Step 3. Open wp-config.php and extract some useful intel about the database
Once inside the WordPress directory, find and open the file wp-config.php for read access with page-by-page scrolling :
cat wp-config.php | more
(HINT: Hit return to scroll down line-by-line; hit Return to scroll down page by page or Q to quit.)
This file contains important configuration information that you will need to talk to the database. From this WordPress configuration file, you need to extract the database name, the database user’s name, and that user’s password. Copy this information and paste it into a text editor on your local computer (WARNING: Don’t even thing of pressing any Windows keyboard shortcuts when using PuTTY! The current selection in PuTTY automatically ends up in your clipboard, so all you need to do is paste it somewhere in an app on your local computer).
Find this line:
define('DB_NAME', 'your_database');
Your database name is your_database. Now look at this line:
define('DB_USER', 'dbuser_wrdp1');
Your user name is Whatever_wrdp1. How about the password? WordPress happens to store it in clear text in the very same configuration file wp-config.php. (That’s one of the reasons why the database user should have only strictly limited privileges and why the web server directory should only be accessible by the root user and perhaps the website owner; the user nginx should never have the ability to start an interactive login session!)
Here is your password for the database user’s access to the database system:
define('DB_PASSWORD', '99123456');
This is NOT the same as the WordPress user’s password. The database password for DB_USER (99123456 in the above example) grants this user access to the WordPress database via the mysql client, not the WordPress frontend.
The main administrator of WordPress is not even a user in the MySQL sense. That’s merely a WordPress user whose account credentials happen to be stored in a MySQL database.
Make a note of this line as well:
$table_prefix = 'wp1_';
It may simplify identifying the database table later on.
Here is what you need to do next.
Step 4. Connect to MySQL (or MariaDB)
Connect to your database using the mysql client (MySQL and MariaDB support identical SQL syntax, so these commands will work for either one of these database systems):
mysql -u dbuser_wrdp1 -p
Enter your password at the prompt (it’s 99123456 in our example).
This is the prompt for MySQL and MariaDB:
MySQL [(none)]> MariaDB [(none)]>
Whatever appears after these characters in the examples below, is the command you need to enter.
Tell the mysql client which database you want to look into (in the following command, replace the string ‘your_database’ with the actual name of your database):
MariaDB [(none)]> use your_database;
Let the mysql client show you the tables:
MariaDB [info_content]> show tables;
Depending on the table prefix set during WordPress installation, what you are looking for could be named wp_users or wp1_users, or something to that effect. Make your pick in accordance with the value of the $table_prefix from wp-config.php; that’s how you know what to look for.
Adjust the name of the wp_users table for your specific scenario in the following line, then issue this command at the mysql prompt:
MariaDB [info_content]> SELECT ID, user_login, user_pass FROM wp1_users;
The output of this command will show you a table that looks something like this:
+----+----------------+------------------------------------+ | ID | user_login | user_pass | +----+----------------+------------------------------------+ | 1 | WPadmin | $4$5p21hqTn45fghskdfgjhsjhkdgtrhf/ | +----+----------------+------------------------------------+ 1 row in set (0.00 sec)
Look at the second column (user_login), find the name of the WordPress user whose password you want to reset, then find that user’s ID (in our example: 1). Issue this command at the mysql prompt (pay attention to the quotation marks!!):
MariaDB [info_content]> UPDATE wp1_users SET user_pass = MD5('NEWPASSWORD') WHERE ID=1 LIMIT 1; Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Congratulations, you have changed the WordPress password of the main WordPress administrator and restored access to the CMS!
Enter
exit
a couple of times to leave the mysql command line and end the ssh session.
Navigate to your website in a web browser and feel free so sign in. Done!
If you are, by any chance, in the process of migrating WordPress, you so need to read this: „WordPress Migration the Easy Way: with the All-in-One WP Migration Plug-In“.
Leave a Reply