How to Unlock User Account in MySQL

MySQL database allows administrators to lock/unlock user account in MySQL. In our previous article, we have learnt how to lock MySQL database users. In this article, we will learn how to unlock MySQL Users. Typically, database administrators may need to lock users if they are performing some maintenance or updates that requires preventing database users from logging in. Once this task is over, you can unlock database user accounts. In this article, we will learn how to unlock user account in MySQL.

How to Unlock User Account in MySQL

When you create a new user, it is unlocked by default. But if an existing user account is locked, then you can use ALTER USER statement with ACCOUNT UNLOCK clause, as shown below, to unlock account. Here is the syntax for this statement.

ALTER USER [IF EXISTS] username
ACCOUNT UNLOCK;

You need to specify username to be unlocked after ALTER USER statement, and include ACCOUNT UNLOCK clause after username.

If you want to unlock multiple users you can specify them one after the other in a comma-separated manner as shown below.

ALTER USER [IF EXISTS] 
    username1 
    [, username2, ...]
ACCOUNT UNLOCK;

In every database system, the column mysql.users stores the information about the account status for each user – whether it is locked or not. It contains Y value if the account is locked, and N value if it is unlocked. If you want to check the status of user account test_user, you can use the following statement.

SELECT 
    user, 
    host, 
    account_locked
FROM 
    mysql.user
WHERE 
    user = 'test_user' AND
    host = 'localhost';

You will see following kind of output.

user      | host      | account_locked
test_user | localhost | N

In this article, we have learnt how to unlock MySQL user account. You can run these commands from terminal, or from within your application via database connector.

Also read:

How to Lock User Account in MySQL
How to Create Cartesian Product of Lists in Python
How to Disable Triggers in PostgreSQL
Convert Text File to Excel in Shell Script
How to Move Directory to Another Partition in Linux

Sreeram Sreenivasan

Sreeram has more than 10 years of experience in web development, Python, Linux, SQL and database programming.