Using MySQL, users can create multiple accounts to access the database(s) from anywhere, giving them different privileges like modifying, dropping, or creating tables according to their requirements. The problem arises when you are in an organization and one of the employees leaves, so you’ll need to delete their accounts or revoke privileges in certain conditions.
This guide to deleting MySQL User Accounts covers the following aspects.
We’d now explain the points mentioned above in detail.
Step 1: Login as the root User
It is required to log in as the root user because, in the world of MySQL, the root can do almost anything. Execute this command to login as root.
$ sudo mysql -u root -p
The default password is set as “EMPTY STRING” or blank and if you haven’t set any password, press the “Enter key” to login as root to MySQL Shell.
Step 2: List User Accounts
There could be multiple users whose names are similar, so it is recommended to view a list of users to avoid confusion; this command can be done using MySQL Shell.
> SELECT USER,HOST FROM mysql.user;
As seen in the above image, we’ve displayed the Users and their Host Name (could also be a remote IP Address) so that it is easier to know what user you wish to remove.
Step 3: Delete User Accounts on MySQL
We’ve viewed the user accounts that use MySQL Databases on our server. The “user1” is no longer a member of my organization, so we’ll delete that account using the DROP USER command in this format.
> DROP USER 'user1'@'localhost';
When executed, the above command deletes the “User1” account from MySQL and all the permissions granted by Database Administrator.
The above method only works after the user is logged out of MySQL, which somehow affects privacy.
Logout and Then Delete the User
To overcome that problem, let’s forcefully log out the user and then delete the account. The following command will be executed to find the id of the connection made to the MySQL Database.
> SHOW PROCESSLIST;
The process Id of the “test” user is 12, so to kill it, use this command in MySQL Shell.
> kill 12;
When the root kills the process (12) that the “test” user held, then “test” will see the following message when a query is executed in MySQL Shell.
We’ll remove the “test” user from MySQL Server using the drop command in this format.
> DROP USER 'test'@'localhost';
The root user has successfully dropped the user account of “test” as seen in the above image. To learn more about the above command, use this help command of MySQL in the terminal.
$ man mysql
That’s how the MySQL user’s account can be deleted.
Conclusion
Creating and deleting user accounts in MySQL is one of any database administrator’s major responsibilities. However, typical users may also need to do this at some point in their work. This article has elaborated on how to delete user accounts in MySQL on Linux.
