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.
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;
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.
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;
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
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…
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,…
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…
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 a…
How to Rename a MySQL Database
May 6, 2019
As an administrator, you may need to rename a database. However, for security, the command to rename a…
How to Connect to MySQL using PHP
May 6, 2019
To access and add content to a MySQL database, you must first establish a connection between the database and…
Author
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.