Error message

Deprecated function: The each() function is deprecated. This message will be suppressed on further calls in menu_set_active_trail() (line 2404 of /usr/share/drupal7/includes/menu.inc).

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.

Add new comment