What happened: I thought I had saved my admin user and password safely in my password manager. Two weeks later after installing a WP instance, I realized I had not. Oops… Obviously the first thing I did was try the ‘reset password’ link on the /wp-login.php page. Oddly this failed. It would not send me an email, whether I used the username or the email (both were present in database, too, I confirmed).
My skills: Not very good. Limited to some very basic programming, but thankfully, I knew how to get inside MYSQL and roughly how that worked. If you are reading this you might need to get caught up on at least that much. There are lots of great tutorials and documentation on it and the syntax isn’t too crazy. Otherwise, you can try this tutorial and it might just work for you 🙂
This post is mainly to leave a quick workflow for the world who happens to have the exactly same setup and situation. You can adjust to your own situation too, as some of the MYSQL commands and WP stuff should be useful regardless of whether you are on the one-click Digital Ocean (“DO” from hereon in) droplet.
Special thanks to this page.
Key Specific info from Digital Ocean setup
The first thing I encountered when I realized my situation was that I didn’t have a mysql user / password as I normally would have if I manually set up my own server. Thankfully when you ssh into your DO server they have a helpful greeting page that tells you what’s up. It tells you this:
The MySQL root password and MySQL wordpress user password are saved
It took me a really long time to try to figure out where this file is. Thankfully the ‘root’ part made me realize I likely had to be logged in as root user to do this. As such I ran
sudo su, entered my password.
Then, it turned out as simple as
Now you should be able to see the file when you run
Assuming you can see that, now you can just run
nano .digitalocean_password to show the contents. It will show you two entries (at least it did for me) like this:
Copy the second password (wordpress_mysql_pass) safely as that’s the one you’ll need for this.
The second thing you’ll need is the database name. After logging in to mysql you can run this command to see all databases on the server
SHOW DATABASES; however I can save you that time by telling you the name of the database is:
- DB NAME: wordpress
Working in MYSQL to Right Our Wrong
Assuming you now have your WP mysql password for the database and the database name, you can now log in to the database with this and enter your password when prompted:
mysql -u wordpress -p
The two fields we are working with are called:
These three commands get you in to look around at all the user-related data tables:
Shows you the users:
SELECT user_login FROM wp_users;
Shows you the password (in MD5 encrypted form – effectively useless, by the way, see below…):
SELECT user_pass FROM wp_users;
Shows you ID of the users you are targeting. If you only have admin it will be ID 1:
SELECT ID, user_login FROM wp_users;
I learned (the long and hard way of course), that the password that is easily pulled with the commands above is encrypted with MD5 encryption meaning that you can’t just copy the password, go back to your WP login page, enter it, and enjoy your life. You have to reset it, using MD5 encryption again to encrypt your ‘normal’ password into the database. Good for security but an extra step for us. Consider it worth the pain I guess…
This next part assumes you have a current version of mysql. If you don’t, you’ll need to read the blog post way above and go the really long hard way…
To do the next part, you will need the ID of the user you are trying to reset the password for. This command I showed you from above will confirm for you:
SELECT ID, user_login FROM wp_users;
Now you can copy this template command I made for you below, paste it into a text editor (really bad idea to copy/paste/edit in your terminal as root!), update it with your new password, confirm the ID number and adjust accordingly (my number 1 is just an example so change accordingly), and then re-copy it for pasting back into your terminal:
UPDATE wp_users SET user_pass = MD5('insert_normal_password_here_between_apostrophes') WHERE ID = 1;
(Reminder – make sure you leave those apostrophes around your password!)
(Reminder – make sure your ID is correct!)
After running that command, you can run this next command to see that changes have happened. You’ll see another random MD5 thing for the password, but hopefully it is different from the one that was there before 🙂
SELECT ID, user_login, user_pass FROM wp_users;
You should now be able to go back to your WordPress /wp-login.php page and use your ‘normal_password’ with your old user name and get in.
Hope this saves someone much time and pain.