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
EXPLANATION- mysql -u: Logs in to the MySQL server with the specified username.
- root: Username of the MySQL account.
- -p: Asks for the password for the user account.
- To get the list of usernames and hostnames on your MySQL server, run the command below.
SELECT User, Host FROM mysql.user;
EXPLANATION- SELECT: Query to view data from MySQL tables.
- User, Host: Column names selected from the specified table.
- FROM: Indicates the table name to run SQL.
- user: Name of the database table to run the query.
- Finally, delete the desired user with the following command.
DROP USER 'jerry'@'localhost';
EXPLANATION- DROP USER: Deletes an existing MySQL user.
- ‘jerry’@’localhost’: Username and hostname for the MySQL user.
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
EXPLANATION- mysql -u: Logs in to the MySQL server with the specified username.
- tom: Username of the MySQL account.
- -p: Asks for the password for the user account.
- Now, switch to the root user account of MySQL server using the given command.
SYSTEM mysql -u root -p
EXPLANATION- SYSTEM: Executes commands from the operating system shell.
- mysql -u: Logs in to the MySQL server with the specified username.
- tom: Username of the MySQL account.
- -p: Asks for the password for the user account.
- Then, get the running process IDs by typing the command below.
SHOW PROCESSLIST;’
EXPLANATION- SHOW PROCESSLIST: Displays a list of active connections and sessions running on a MySQL.
- Run the following command to end the corresponding user session.
KILL 42;
EXPLANATION- KILL: Terminates a particular session.
- 42 : Session/Process ID to be terminated.
- Finally, delete the desired MySQL user account with the command given below.
DROP USER 'tom'@'localhost';
EXPLANATION- DROP USER: Deletes an existing MySQL user.
- ‘tom’@’localhost’: Username and hostname for the MySQL user.
- Additionally, view the updated user table using following command.
SELECT User, Host FROM mysql.user;
EXPLANATION- SELECT: Query to view data from MySQL tables.
- User, Host: Column names selected from the specified table.
- FROM: Indicates the table name to run SQL.
- user: Name of the database table to run the query.
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
EXPLANATION- mysql -u: Logs in to the MySQL server with the specified username.
- root: Username of the MySQL account.
- -p: Asks for the password for the user account.
- To get the list of usernames on your MySQL server, run the command below.
SELECT User, Host FROM mysql.user;
EXPLANATION- SELECT: Query to view data from MySQL tables.
- User, Host: Column names selected from the specified table.
- FROM: Indicates the table name to run SQL.
- user: Name of the database table to run the query.
- Finally, delete the desired users with the following command.
DROP USER 'jerry'@'localhost', 'tomy'@'localhost';
EXPLANATION- DROP USER: Deletes an existing MySQL user.
- ‘jerry’@’localhost’: 1st username and hostname for the MySQL user.
- ‘tomy’@’localhost’: 2nd username and hostname for the MySQL user.
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
EXPLANATION- mysql -u: Logs in to the MySQL server with the specified username.
- root: Username of the MySQL account.
- -p: Asks for the password for the user account.
- To get the list of usernames and hostnames on your MySQL server, run the command below.
SELECT User, Host FROM mysql.user;
EXPLANATION- SELECT: Query to view data from MySQL tables.
- User, Host: Column names selected from the specified table.
- FROM: Indicates the table name to run SQL.
- user: Name of the database table to run the query.
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