KNOWNHOST KNOWLEDGE BASE

Hosting Question? Find the Solution - Browse our Guides, Articles, and How-To's

How can I reset my WordPress admin password with MySQL?

Category: WordPress
Tags: #

If you forget the admin password to your WordPress site, you may need to reset it. Although normally resetting the password can be done from within WordPress, that isn’t much help if the reason you need it changed is because you do not know the old one. Here, we go over how to change the WordPress password using mysql directly, so that you can then log into the WordPress admin area to change the password normally. As always, you can open a Support Ticket if you need any assistance. Here are the things you will need to get started:

  • SSH access to your server
  • The exact name of the database for this specific WordPress installation.

First, log into the server via SSH.

Next, we choose a temporary password. You can learn more about passwords in general here. Since this one is only temporary, it is sufficient for our purposes to use 23 alphanumeric characters of both cases. We can generate one of these as follows. When you run it you should get different output. Do not use the example password shown in this article!

  root@host [~]# head -c 23 /dev/urandom | base64 | tr -d '/+=' | cut -c1-23
  wAXMRV05x8iMaAoW5BsNewa

If your server has a recent version of MySQL, the md5 hash can be calculated within the same MySQL command where we set the password. First, we log into MySQL:

  root@host [~]# mysql
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 123007
  Server version: 5.6.33 MySQL Community Server (GPL)

  Copyright (c) 2000, 2016, 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 your server does not use cPanel, you may need to supply a username and password for MySQL by running the command like this:

  # mysql -u mysqluser -p

then entering the needed password at the prompt.

Once you are logged into MySQL, you want to use the database for the particular site. You can see the list of databases with

  mysql> show databases;
  +-----------------------------------------------------------------+
  | Database                                                        |
  +-----------------------------------------------------------------+
  | information_schema                                              |
  | cpuser_dbname                                                   |
  | cphulkd                                                         |
  | eximstats                                                       |
  | cpuser2_dbname                                                  |
  | leechprotect                                                    |
  | modsec                                                          |
  | mysql                                                           |
  | performance_schema                                              |
  | roundcube                                                       |
  | cpuser3_dbname                                                  |
  +-----------------------------------------------------------------+
  11 rows in set (0.00 sec)

  mysql> 

To choose the database, we use the “use” command:

  mysql> use cpuser3_dbname;
  Database changed

Next, we look for the name of the specific table we want. We want the table whose name ends in “users”:

  mysql> show tables like '%users';
  +-----------------------------------+
  | Tables_in_cpuser3_dbname (%users) |
  +-----------------------------------+
  | wp_users                          |
  +-----------------------------------+
  1 row in set (0.00 sec)

We want to look at the list of users, to find the ID# of the account we are changing the password for:

  mysql> select ID,user_login,user_pass from wp_users;
  +----+-------------------------+------------------------------------+
  | ID | user_login              | user_pass                          |
  +----+-------------------------+------------------------------------+
  |  2 | someuser                | $P$BPy4uj0AQuInJXUAg95NbKmz7udQR6/ |
  |  3 | test                    | $P$BiXazWPE.9wyLQ67aO.dfLZ0mFrnM81 |
  +----+-------------------------+------------------------------------+
  2 rows in set (0.00 sec)

These are only hashes of passwords, not the passwords themselves. In this case, we want to change the password of user “test”, which has ID of “3”. So, keeping in mind the password we generated earlier, we can proceed as follows:

  mysql> update wp_users set user_pass = MD5('wAXMRV05x8iMaAoW5BsNewa') where ID = 3;
  Query OK, 1 row affected (0.00 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
Remember: Do NOT use the password shown in the example. Use a different, randomly-generated password, using a command like the one demonstrated above.

Next, log into the WordPress site with the new password. Because we just set the password unsalted, we want to reset the password again through the WordPress interface. Once logged into WordPress, you can go to the “Profile” page and scroll down to the “Account Management” section. Next to “New Password”, there is a button for “Generate Password”.

If it is an admin user you are logging in as, the profile page is in the “Users” section, and is called “Your Profile”.

To make sure the password is random, we can generate it using /dev/urandom again as before, once we have logged out of MySQL. If we want to be extra-careful, we can use fifty characters:

  root@host [~]# head -c 50 /dev/urandom | base64 | tr -d '/+=' | cut -c1-50
  fD6E8bX2NGWcKOeqpVy63v4EXIHcDhqMZd903nnh04WtqaoMgk

but as long as the password really is random, 23 is still sufficient:

  root@host [~]# head -c 23 /dev/urandom | base64 | tr -d '/+=' | cut -c1-23
  i8elmaGIVPerj7SdyzyQUWU

If you want to generate several to choose between, you can make a loop:

  root@host [~]# for each in $(seq 23); do head -c 23 /dev/urandom | base64 | tr -d '/+=' | cut -c1-23; done
  BZ7ObDNVxLTYVZ9nMKgn5mF
  033vOQwm4RjuC7ICZMHiN2t
  LaY8Hu8vcgRGlZwFKxddRVv
  f8n6haYdsOM12CWhkdJe0JU
  1jrEkqs9YqIErIlv1M2QHDt
  iJnyUChfUGowEkBMLJgxWXn
  IQDTRs7pEXxjK06x8XWI3aP
  Al8UFBmTLlF0qAKEfRbK8aC
  j7s0VMxguvMqB7s4TSfXKp8
  7AiS6Q9g832l2IAgHAMLZSn
  bvjSZBJSzK86pbHSwsjx6Y4
  1RkSop5zgGGjeMMHlZ5fpZw
  4SDnWr7iStzIy2aZZS93NkY
  IYBz05ugzm2YnAUuAIUhipC
  kCLJmGxnToYgLW0FTjW6yIu
  uxbXglSNqs7IieyOOWOaYxw
  OOldEpyEV1mzV5QtIlK9Lod
  mfKH9aOsxh89tps870OSTCJ
  z59C97dAabjS5XBYgLysOO4
  Z6REJyA4PZSUUCCd9becZyq
  ApR357yDAkAL14D6N6tdq2J
  7ZBb8q2kXAQyMfxZpycqf8w
  OveV0IZN6D0preuHZ2eWcLx
Remember not to actually use any of the example passwords shown in this article! You can still use the same commands to generate passwords, but do not use the specific results shown on this page.

Once you have chosen one of these passwords, click the “Generate Password” button next to the “New Password” label. In recent versions of WordPress, the automatically-generated password might be strong already, but if it is not, you can use one of the commands shown here to make a different one. To use one of the randomly generated passwords created elsewhere, empty out what WordPress tries to generate, and then copy and paste in the chosen password you have created. Make sure the password you use is long, randomized, and unique!

Whatever password you choose, write it down somewhere safe while you work on memorizing it. Do not store it on the computer. It is often recommended not to write down passwords, in case someone else finds the paper, but as long as you are careful with that piece of paper, it is likely to be better than storing it in your computer, or using a shorter or less randomized password that might be “easier to remember”.