Reset password and unblock login for admin (or any account) in Drupal 8 with SQL query in MySQL or MariaDB

My Drupal 8 site got some problem and I restored it from an old backup.
The problem is, I forgot the password of admin account, and I don't have a working MTA (ex, Postfix Mail Transport Agent) on the server for sending password resetting mail.
The solutions to reset password and unblock the account in database directly.

Hope this HOWTO can save a tree and a kitten.

Environment

  • Ubuntu 16.04 LTS (xenial, 64bit, server)
  • Drupal 8.x (8.3.7)
  • MariaDB (10.0, also apply to MySQL 5.x)

Resetting Password for account

Generate new password hash

Use the built-in tool to generate a new hash from your password.
In the example below, drupal-8.3.7 is your Drupal installed folder. Replace it with your real path.

$ ./drupal-8.3.7/core/scripts/password-hash.sh 'p@55word!'

password: p@55word!             hash: $S$EkF8cUoixoqEq9.rlMQzRaQICVU.BE5deDc1Eqs8p/HLxNQM0R/t

Copy the generated hash for later use.

Update user password filed in database

Connect to MySQL or MariaDB and use your Drupal installed database for later operations.

$ sudo mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 218
Server version: 10.0.31-MariaDB-0ubuntu0.16.04.2 Ubuntu 16.04

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

And use drupal database.

MariaDB [(none)]> use drupal;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [drupal]> 

And update the password filed with new hash for admin account.
If you want to update other account, you can find it's uid with select uid,name from users_field_data; SQL query. Or, you can replace the uid=1 in following query with name='the-account-name'.
In the example we use uid=1 for admin user.

MariaDB [drupal]> UPDATE users_field_data SET pass='$S$EkF8cUoixoqEq9.rlMQzRaQICVU.BE5deDc1Eqs8p/HLxNQM0R/t' WHERE uid = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Clear cache

You need to clear cached data for the account, or flush all caches without the WHERE cid = 'values:user:1' part in SQL query.

MariaDB [drupal]> DELETE FROM cache_entity WHERE cid = 'values:user:1';
Query OK, 1 row affected (0.00 sec)

Unblock your account

You may already get the Login blocked after 5 failed login attempts warning before trying to reset password.
Without unblocking account, you won't be able to login again event you have the correct password.

I'll just unlock all from the flood protection history.

MariaDB [drupal]> truncate flood;
Query OK, 0 rows affected (0.02 sec)

Testing

Now go to http://[your-site]/user/login and try to login.
Enjoy!

Hope this HOWTO can save a tree and a kitten.

Comments

Finally found the information that how to reset a password and unblock login for admin. Thanks for sharing please do keep sharing on...
SQL Server Online Training | IoT Training

I simply wanted to write down a quick word to say thanks to you for those wonderful tips and hints you are showing on this site.

oracle training in jaya nagar

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.