Unlock MySQL User Accounts with ACCOUNT UNLOCK

Use ALTER USER ... ACCOUNT UNLOCK to unlock one or more MySQL accounts. It reverses an explicit ACCOUNT LOCK and can also reset a temporary lock caused by failed-login tracking.

Basic syntax of Unlocking users

The following is the syntax for unlocking a user account:

ALTER USER [IF EXISTS] user@host [, user@host, ...]
ACCOUNT UNLOCK;

here:

• The user@host includes the username and hostname. Use commas to separate multiple accounts.
• The UNLOCK keywords are used to unlock users. Use LOCK if you want to lock a user.

Check the lock state of the target accounts

The account_locked column in mysql.user reports the explicit account-lock state: Y means locked and N means not explicitly locked. Temporary failed-login blocking is separate and uses FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME; see MySQL password management.

Check only the target user and host. MySQL treats each user-host pair as a distinct account:

SELECT User, Host, account_locked
FROM mysql.user
WHERE User IN ('app_user', 'app_user2') AND Host = 'localhost';

+-----------+-----------+----------------+
| User      | Host      | account_locked |
+-----------+-----------+----------------+
| app_user  | localhost | Y              |
| app_user2 | localhost | Y              |
+-----------+-----------+----------------+

These illustrative accounts were locked in the MySQL account-locking tutorial.

Unlock user accounts

Unlock the example accounts with an administrator account authorized to modify MySQL accounts:

ALTER USER 'app_user'@'localhost', 'app_user2'@'localhost' ACCOUNT UNLOCK;

The statement must name the same user and host pairs that were locked.

To query the lockout status of these two user accounts, run the following SQL statement:

SELECT User, Host, account_locked
FROM mysql.user
WHERE User IN ('app_user', 'app_user2') AND Host = 'localhost';

+-----------+-----------+----------------+
| User      | Host      | account_locked |
+-----------+-----------+----------------+
| app_user  | localhost | N              |
| app_user2 | localhost | N              |
+-----------+-----------+----------------+

N shows that these accounts no longer have an explicit ACCOUNT LOCK. If login still fails, inspect the exact error: repeated wrong passwords can trigger a separate temporary block, reported as Error 3955; ACCOUNT UNLOCK can reset that block too. See the MySQL Error 3955 reference.

Conclusion

Unlock the exact account names and hosts with ALTER USER ... ACCOUNT UNLOCK. For more information about explicit account locks and their exceptions, see the MySQL account-locking rules.