Home » Blog » How to Delete MySQL User in Ubuntu? [2 Cases]

Ubuntu

How to Delete MySQL User in Ubuntu? [2 Cases]

Anonnya Ghosh

April 25, 2023

Being able to delete MySQL users in the Ubuntu distribution is as necessary as creating new MySQL users. MySQL is an open-source database management server readily available to deploy in Linux operating systems. Using the basic Structured Query Language (SQL) you can delete MySQL users from your Linux distributions. In this article, I will show you ways to delete a MySQL user from Ubuntu.

Key Takeaways

• Deleting a regular user from MySQL.
• Deleting an Active user from MySQL.

Requirements

• Have MySQL installed on Ubuntu.
• Need to be a root user or have root/sudo privileges to access MySQL.
• Need to be a MySQL root user to delete MySQL.

Process Flow Chart

Distro Used Throughout the Tutorial: Ubuntu 22.04.1 LTS

2 Cases to Delete MySQL User in Ubuntu

Deleting a MySQL user in Ubuntu requires logging in to the MySQL server as the server’s root user. Once you have access to the server you can remove available users using the basic SQL command DROP USER. The command will drop the corresponding user information row from MySQL’s user table which will result in removing the user from the server.

While deleting the MySQL users, you may come across two cases. Firstly, you may want to delete an idle user available on the server. Secondly, you may need to remove a user whose process is currently running or who is logged in/ active on the server. In this section, I will demonstrate both cases of deleting MySQL users in Ubuntu.

Case 01: Delete an Idle MySQL User Account in Ubuntu

To delete an idle MySQL user from Ubuntu, you will need to log in to the MySQL server as the MySQL root user. Then you can run the “DROP USER” SQL command along with the desired username and its hostname.

In this example, I will delete the MySQL user named “jerry”. To do so I will retrieve the hostname of the corresponding user from the MySQL User Table called “mysql.user”. You can follow the steps below to do the same.

Steps to Follow >

➊ At first, open the Ubuntu terminal.

➋ Now, log in to your root user account of MySQL server using the command below.

mysql -u root -p

➌ To get the list of usernames and hostnames on your MySQL server, run the command below.

SELECT User, Host FROM mysql.user;

❹ Finally, delete the desired user with the following command.

DROP USER 'jerry'@'localhost';

In the above image, you can see that I have successfully deleted the desired user from the MySQL server in my Ubuntu machine. Upon deletion, you can view the updated “mysql.user” table by again running the SELECT query as shown in step 3.

Case 02: Delete an Active MySQL User in Ubuntu

To remove an active MySQL user from Ubuntu, termination of the active user’s session is required. You will need to switch to the MySQL root user and apply the SQL command KILL to end the running session. Once the session is ended you can use the basic SQL command DROP USER for deleting the user.

In this example, I will delete the active MySQL user “‘tom’@’localhost’” in Ubuntu. You can follow the process below to do the same.

Steps to Follow >

➊ At first, launch the Ubuntu terminal.

➋ Start a regular user session by logging into the MySQL server account with the command below.

mysql -u tom -p

➌ Now, switch to the root user account of MySQL server using the given command.

SYSTEM mysql -u root -p

❹ Then, get the running process IDs by typing the command below.

SHOW PROCESSLIST;’

❺ Run the following command to end the corresponding user session.

KILL 42;

❻ Finally, delete the desired MySQL user account with the command given below.

DROP USER 'tom'@'localhost';

❼ Additionally, view the updated user table using following command.

SELECT User, Host FROM mysql.user;

Upon completion of the aforementioned steps, you can see that I have deleted an active user from the MySQL server. You can run the SELECT query on the “mysql.user” table to view the updated users and their hosts.

• How to Delete User Account In Ubuntu? [2 Methods]
• Delete a User from an Ubuntu Server in Just 3 Steps
• Remove User from a Group Using “gpasswd” Command in 3 Steps

Complementary Information

Besides, knowing about deleting a MySQL user on Linux, you will find the below information helpful.

Delete Multiple MySQL User Accounts in Ubuntu

You can delete more than one user using the DROP USER command of SQL. In this example, I will delete multiple MySQL users from my Ubuntu distributions. You can follow the steps below to do the same.

Steps to follow>

➊ At first, go to the Ubuntu terminal.

➋ Now, log in to your root user account of MySQL server using the command below.

mysql -u root -p

➌ To get the list of usernames on your MySQL server, run the command below.

SELECT User, Host FROM mysql.user;

❹ Finally, delete the desired users with the following command.

DROP USER 'jerry'@'localhost', 'tomy'@'localhost';

In the above image, you can see that I have successfully deleted multiple MySQL users using the DROP USER command from my Ubuntu operating system.

Show List of MySQL Users in Ubuntu

Inside the MySQL server, you can easily run the SELECT query on the “mysql.user” table to view the available MySQL users and their hosts. In this section, I will display all the available users of the MySQL server on my machine. Now, you can do the same by following the steps below.

Steps to Follow >

➊ At first, open the Ubuntu terminal.

➋ Now, log in to your root user account of MySQL server using the command below.

mysql -u root -p

➌ To get the list of usernames and hostnames on your MySQL server, run the command below.

SELECT User, Host FROM mysql.user;

After completing the given steps, you can see that I have displayed all the MySQL users in Ubuntu.

Conclusion

In this article, I have illustrated the deletion of two kinds of MySQL users in Ubuntu. This tutorial will introduce you to some of the basic SQL commands and ways of using them. You will be able to switch and list users along with removing them from the server. I hope this article will improve your interaction with the MySQL service in Linux and enhance your overall experience.

People Also Ask

Related Articles

• 3 Ways to Delete the User Password in Ubuntu
• How to Delete User and All Files in Ubuntu [3 Methods]
• How to Remove A User from Group in Linux [4 Methods]
• How to Delete Multiple Users in Linux? [2 Steps]