How to reset WordPress password using MySQL CLI

How to reset WordPress password using MySQL CLI

So bizarrely, I forgot my WordPress admin password, including my login name. I did not add my login details to my password manager either. The container image that I built lacking outgoing email support (SMTP) too. I got stuck. Fortunately, I have full root access to my MySQL/MariaDB server hosted at AWS EC2, and here is how to reset a forgotten WordPress password using MySQL command-line.

Tutorial details
Difficulty level Easy
Root privileges No
Requirements Linux or Unix with mysql CLI
Est. reading time 2m

Step to reset WordPress password using MySQL CLI on Linux

The procedure is as follows:

  1. First login as root server using ssh command:
    ssh ec2-user@debian-10-ec2-server-ip
  2. Next, find out the mysql version by issuing:
    mysqld --version
    I am using the latest stable version:
    /usr/sbin/mysqld Ver 8.0.23 for Linux on x86_64 (MySQL Community Server - GPL)
  3. Get WordPress database name using the grep DB_NAME wp-config.php
    Here is what I saw:
    define( 'DB_NAME', 'nixcraftwp' );
  4. Run mysql client as mysql -u root -p nixcraftwp
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 32562
    Server version: 8.0.23 MySQL Community Server - GPL
     
    Copyright (c) 2000, 2021, Oracle and/or its affiliates.
     
    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>
  5. Display table list by running show tables; SQL command and note down table name that ends with _users.
    +-----------------------+
    | Tables_in_nixcraftwp  |
    +-----------------------+
    | wp_commentmeta        |
    | wp_comments           |
    | wp_links              |
    | wp_options            |
    | wp_postmeta           |
    | wp_posts              |
    | wp_term_relationships |
    | wp_term_taxonomy      |
    | wp_termmeta           |
    | wp_terms              |
    | wp_thesis_backups     |
    | wp_usermeta           |
    | wp_users              |
    +-----------------------+
    13 rows in set (0.00 sec)
  6. Get a list of user that stored in your WordPress and note down the ID:
    SELECT ID, user_login, user_pass, user_email FROM wp_users;
    Here is what I saw on my MySQL server:
    +----+------------+------------------------------------+-------------------------+
    | ID | user_login | user_pass                          | user_email              |
    +----+------------+------------------------------------+-------------------------+
    |  1 | vadmin     | $P$xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxy | webmaster@cyberciti.biz |
    +----+------------+------------------------------------+-------------------------+
    1 row in set (0.00 sec)
  7. Finally, reset the WordPress user password by typing the following command that replaces the existing password with a new one for ID # 1 (user named vadmin):
    UPDATE wp_users SET user_pass = MD5('my-new-password-here') WHERE ID = 1;
    We will see confirmation as follows on the screen:
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
  8. Exit the MySQL session by typing:
    quit
    Bye
    ec2-user@debian-aws-mysql:~$
  9. Note that under WP, the passwords are salted, you can still replace the password with an MD5 hash, and WordPress will let you log in. After login WP will rewrite that password using its hash. So this hole thingy is for resetting the password but nothing else. The passwords are always salted regardless of the method, and this is a temporary workaround to reset the password.

    How to reset WordPress password with wp command

    We can install the wp-cli and run it as follows:

    ## Grab it ##
    curl -O https://raw.githubusercontent.com/wp-cli/builds/gh-pages/phar/wp-cli.phar
     
    ## Make sure it is working ##
    php wp-cli.phar --info
     
    ## Switch to your webserver user ##
    su - wwwuser
     # OR #
    sudo -u wwwuser -i
    chmod +x wp-cli.phar
     
    ## Get a list of users ##
    ./wp-cli.phar --path=/http/public user list
     
    ## Set up a new password for user ID number  1 ##
    ./wp-cli.phar --path=/http/public user update 1 --user_pass=NEW_PASSWORD_HERE
     
    ## Remove wp-cli if you are not going to use it again (optional) ##
    rm wp-cli.phar
     
    ## Get back to our root shell ##
    exit

    Test it

    Try to login:
    https://your-domain-com/wp-login.php
    https://your-domain-com/blog/wp-login.php

    Getting help about resetting your WordPress MySQL admin password

    I tried both methods as described above, and they worked perfectly. WordPress wiki has tons of other tips that you find useful when resetting the forgotten admin WordPress user password.

    Summing up

    I saved my new password using a password manager. I will fix my Docker image to add SMTP support either in PHP or a tiny SMTP server that will route outgoing email using AWS SES service.


    🐧 Get the latest tutorials on Linux, Open Source & DevOps via RSS feed or Weekly email newsletter.

    🐧 2 comments so far... add one

    CategoryList of Unix and Linux commands
    Disk space analyzersdf ncdu pydf
    File Managementcat tree
    FirewallAlpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
    Network UtilitiesNetHogs dig host ip nmap
    OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
    Package Managerapk apt
    Processes Managementbg chroot cron disown fg jobs killall kill pidof pstree pwdx time
    Searchinggrep whereis which
    User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
    WireGuard VPNAlpine CentOS 8 Debian 10 Firewall Ubuntu 20.04
2 comments… add one
  • Casper Feb 13, 2021 @ 18:05

    I’m actually shocked that WP is using something as insecure as MD5 hashed passwords.. no wonder why it’s the most hacked CDM.

  • Keyle Feb 27, 2021 @ 10:52

    Hi,

    wp_cli method worked for me. The first method was long and didn’t worked at all. May i suggest adding wp_cli method as a default method for resetting wordpress password?

Leave a Reply

Your email address will not be published.

Use HTML <pre>...</pre> for code samples. Still have questions? Post it on our forum