How to Delete MySQL Users Accounts

MySQL allows you to create multiple user accounts and grant appropriate privileges so that the users can connect and manage databases.

If the user account is no longer needed, it is a good idea to either remove the user privileges or to completely delete the user account.

This tutorial explains how to delete MySQL/MariaDB user accounts.

DROP USER Statement

In MySQL, you can remove one or more users and assigned privileges with the DROP USER statement. The general syntax of this statement is as follows:

DROP USER [IF EXISTS] USER_ACCOUNT [, USER_ACCOUNT] ...

For example to remove the brian@localhost user account login to the MYSQL shell and run:

DROP USER 'brian@localhost';

On success the command will return:

Query OK, 0 rows affected (0.00 sec)

To remove multiple user accounts in a single command, run the DROP USER statement followed by the users you want to remove separated by space:

DROP USER 'brian@localhost' 'any@localhost';

If you try to drop a user account that does not exist and the IF EXISTS a clause has not been used the command will return an error.

If the user you are trying to remove is currently logged in, the user session will not be closed and the user will be able to run queries until the session ends. Once the session is closed the user is removed and it will no longer be able to log in to the MySQL server.

The databases and objects created by the user are not automatically removed.

Removing MySQL User Accounts

This section step by step instructions on how to list and remove MySQL user accounts.

First, log in to the MySQL shell with the root or another administrative user. To do so type the following command:

sudo mysql

If you are using the old, native MySQL authentication plugin to log in as root run the command below and enter the password when prompted:

mysql -u root -p

The commands below are executed inside the MySQL shell.

MySQL stores information about the users, in the user table in the mysql database. Use the following SELECT statement to get a list of all MySQL user accounts :

mysql> SELECT User, Host FROM mysql.user;

The output should look something like this:

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | localhost |
| luke             | %         |
| jabba            | localhost |
| jabba            | 10.10.8.8 |
| chewbacca        | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

Note: On Linux, MySQL database and table names are case sensitive.

Let’s say the chewbacca@localhost user account is no longer needed and we want to remove it.

To delete the user run:

mysql> DROP USER 'chewbacca'@'localhost'

Query OK, 0 rows affected (0.00 sec)

The command will remove the user account and its privileges.

Now that the user is removed you may also want to remove the databases associated with that user.

Conclusion

To remove a MySQL user account use the DROP USER statement followed by the name of the user you want to remove.

If you have any questions or feedback, feel free to leave a comment.