How To Remove or Delete a MySQL User Account

Introduction

It’s essential to have control over who has access to a database.

To delete a MySQL user is to remove an account and its privileges from all grant tables. Only users with global CREATE USER or DELETE privileges can perform such tasks. In this tutorial, learn how to remove MySQL user accounts using the DROP USER statement.

Deleting a MySQL Account

1. First, connect to the MySQL database as the root user:

mysql -u root -p

If root does not have access to MySQL on your machine, you can use sudo mysql

2. Enter the password when prompted and hit Enter. A MySQL shell loads.

3. Find the exact name of the user you want to remove by running a command that lists users from the MySQL server:

SELECT User, Host FROM mysql.user;

4. The output displays all users. Locate the name you want to remove, in our case it is MySQLtest. Replace username in the following command with your user:

DROP USER 'username'@'host';

5. Recheck the user list to verify the user was deleted.

DROP USER Syntax

The basic syntax for the DROP USER statement is:

DROP USER 'username'@'host';

Remove Multiple MySQL Users

To delete multiple users at the same time, use the same DROP USER syntax, and add users separated by a comma and a space.

For example:

DROP USER 'user1'@'localhost', 'user2'@'localhost', 'user3'@'localhost';

Drop a Connected / Active User

If the user you want to remove from the MySQL database is active at that time, its privileges will be revoked only once the session is closed. After that, the user will not have access to the database.

If you want the DROP USER statement to go into effect immediately, you need to kill the user session and then remove the account.

1. Find the connection ID for the unwanted user. Prompt a list to see all activity processes and their IDs:

SHOW PROCESSLIST;

2. Locate the Id of the user and add the number to the kill command to kill the process in MySQL:

KILL Id_number;

3. Once the user is no longer active, you can remove it from the MySQL database with:

DROP USER 'username'@'localhost';

Conclusion

In this article, you have learned how to delete one or multiple MySQL user accounts, as well as how to remove active accounts.

For more ways to optimize your MySQL Database, read our MySQL Performance Tuning tutorial.