How To Remove or Delete a MySQL User Account

December 1, 2019

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.

Was this article helpful?
YesNo
Sofija Simic
Sofija Simic is an aspiring Technical Writer at phoenixNAP. Alongside her educational background in teaching and writing, she has had a lifelong passion for information technology. She is committed to unscrambling confusing IT concepts and streamlining intricate software installations.
Next you should read
How to Find and Terminate / Kill MySQL Process
January 23, 2020

Killing a MySQL process can help you boost the performance of your server. By running a few commands, you can...
Read more
How to Fix MySQL "Command Not Found" (Linux, Windows, mac OS)
December 11, 2019

The 'Command Not Found' error is a general error not only found in MYSQL. By learning how to deal with it...
Read more
How To Show a List of All Databases in MySQL
July 23, 2019

With Structured Query Language (SQL), you can easily access and manage content in all your databases. This...
Read more
How to Check the MySQL Version In Linux
July 11, 2019

It is essential to know which version of MySQL you have installed. The version number helps to determine if...
Read more
  • © 2021 Copyright phoenixNAP | Global IT Services. All Rights Reserved.