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.

Tutorial on how to remove or delete a MySQL user account.

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;

mysql shell user list

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.

mysql user list after drop user command

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';

Note: If you removed an existing MySQL account and want to create a new one, refer to our article on How to Create MySQL Account and Grant Privileges.

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;

mysql show processlist command

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.

Next you should also read