User Tools

Site Tools


cms:reset-wordpress-admin-via-mysql

How can I reset my WordPress admin password with MySQL?

Difficulty
Hard

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!

[email protected] [~]# 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:

[email protected] [~]# 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 the 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:

[email protected] [~]# 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:

[email protected] [~]# 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:

[email protected] [~]# 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,1) 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.2) 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".

1)
for example, using one of the password-generating commands shown on this page
2)
for example, in web browsers
cms/reset-wordpress-admin-via-mysql.txt · Last modified: 2016/12/26 13:21 by Marjorie S.