How to Delete MySQL User Using DROP USER

Introduction

Deleting a MySQL user removes the user's account and the associated grant table privileges. Only admins with global CREATE USER or DELETE privileges can remove another user from MySQL.

In this tutorial, learn how to remove one or more MySQL user accounts using the DROP USER statement.

Prerequisites

• Command-line access to the system.
• MySQL installed (read our tutorials for installing MySQL on Ubuntu and Windows).
• Access to a MySQL user with sufficient privileges (e.g., root user).

MySQL DROP USER Statement Syntax

The DROP USER statement has the following syntax:

DROP USER [user];

To delete more than one user, separate multiple unwanted users with commas:

DROP USER [user1], [user2], [user3], ...;

The [user] part of the command usually has the following syntax:

'[username]'@'[host]'

The [username] part refers to the name of the unwanted user, while [host] points to the domain or IP address from which the specified user connects. For example, if the user accesses a database from the local machine, their [host] is localhost.

If DROP USER is executed on a user that does not exist, MySQL returns an error.

To prevent this behavior, add the IF EXISTS statement to DROP USER:

DROP USER IF EXISTS [user];

IF EXISTS outputs a warning for users that do not exist on the server.

Use the SHOW WARNINGS statement to read the warning:

SHOW WARNINGS;

The warning message appears in the list.

Removing MySQL User Examples

There are three common scenarios for removing users in MySQL:

• Deleting a single user.
• Removing multiple users.
• Deleting a currently active user.

Learn how to use the DROP USER statement by referring to the sections below.

How to Remove Single MySQL User

The following is the procedure for deleting a single unwanted user with the MySQL DROP USER statement:

1. Connect to the MySQL server as the root user:

mysql -u root -p

If root does not have access to MySQL on your machine, use the following command:

sudo mysql

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

3. Locate the user to remove by running the command that lists MySQL server users:

SELECT user, host FROM mysql.user;

The output displays all users and the hosts to which they belong. The example below shows user test1, which will be deleted in the next step.

4. Remove the user by executing the following DROP USER command:

DROP USER '[username]'@'[host]';

Replace [username] and [host] with the relevant information about the user you want to delete. For example, execute the following command to delete user test1 on localhost:

DROP USER 'test1'@'localhost';

The output returns the Query OK message.

5. List users again to verify the results of the operation:

SELECT user, host FROM mysql.user;

The example output below shows that user test1 has been removed.

How to Remove Multiple MySQL Users

Use the following DROP USER syntax to delete multiple users at the same time:

DROP USER '[user1]'@'[host]', '[user2]'@'[host]', ...;

For example, to delete users test2 and test3 on localhost, type the following command:

DROP USER 'test2'@'localhost', 'test3'@'localhost';

The Query OK message appears in the output.

How to Remove Connected/Active MySQL User

The privileges of users currently active on a MySQL server cannot be revoked before their session is closed. To take away the unwanted user's privileges immediately, stop the user session and remove the account by following the steps below:

1. Find the connection ID for the unwanted user by displaying the process list:

SHOW PROCESSLIST;

2. Locate the relevant user ID and use the KILL command to kill the process in MySQL:

KILL [ID];

Replace [ID] with an actual user ID from the table. For example, if the ID is 11, type:

KILL 11;

3. Remove the user from the MySQL database using the DROP USER statement:

DROP USER '[username]'@'[host]';

This action now immediately deletes the unwanted user and all their privileges.

Conclusion

After reading this article, you should know how to delete one or more MySQL users from a server. The tutorial also showed how to remove currently active user accounts.

Read our MySQL Performance Tuning tutorial for tips on how to optimize your MySQL database.