In our earlier post we had discussed how to create a new user account in MySQL. Now in this post, we will discuss how to delete a MySQL user account using the MySQL shell or command line.

The general SQL syntax to delete a user from MySQL is

where account_name is the name of the account in the format of ‘user_name'@'host_name‘.

If you need to delete multiple users in MySQL using single DROP USER statement then use the following syntax

To use DROP USER, you must have the global CREATE USER privilege or the DELETE privilege for the MySQL system database.

On deleting a user, their privileges for all grants also gets deleted.

Example

Before we look into the examples, it is important to know the user name and host name for the MySQL user account which needs to be deleted.

You can use the following syntax in MySQL to list all users along with their hostnames.

users list before deleting MySQL user

Let’s try deleting the ‘pencil_programmer’ user.

Query OK, 0 rows affected (0.23 sec)

If we again look at the user table then we notice that ‘pencil_programmer’ has been deleted successfully.

users list after deleting MySQL user

The important point to note is that while executing the above statements we were connected to MySQL as ‘root’ user.

What could have happened if we were connected to MySQL as ‘pencil_programmer’ and try to delete the same user?

In that case, the user will get deleted but at the same time continue to work as normal until the session expires (i.e next login).

That’s it for this tutorial. Today we have learned how to delete MySQL users using the DROP USER statement in MySQL.

Leave a Reply