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

Anonnya Ghosh
Written by Anonnya Ghosh
Last updated: Nov 6, 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.

Table of Contents Expand

Key Takeaways

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

Requirements

Process Flow Chart

Distro Used Throughout the Tutorial: Ubuntu 22.04.1 LTSFlowchart for deleting MySQL user in Ubuntu.

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 >

  1. At first, open the Ubuntu terminal.
  2. 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.

    Logging into MySQL root account.

  3. 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.

    Listing available MySQL users.

  4. 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.

    Deleting idle MySQL user in Ubuntu.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 >

  1. At first, launch the Ubuntu terminal.
  2. 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.

    Starting regular user session in MySQL server from Ubuntu.

  3. 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.

    Switching to root MySQL user.

  4. 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.

    Listing running process ID inside MySQL server.

  5. 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.
  6. 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.
  7. 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.

    Deleting active MySQL user in Ubuntu.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.

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 >

  1. At first, go to the Ubuntu terminal.
  2. 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.

    Logging into MySQL root account.

  3. 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.

    Listing available MySQL users.

  4. 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.

    Deleting multiple MySQL users in Ubuntu.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 >

  1. At first, open the Ubuntu terminal.
  2. 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.

    Logging into MySQL root account.

  3. 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.

    Listing available MySQL users.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

How do I completely DELETE a user?
To completely delete a user, you will need to DROP the user from the “mysql.user” table and then manually delete all objects owned by that user since the DROP command does not automatically remove these objects.
Which command will DELETE a user?
The SQL command DROP USER will delete a MySQL user. You will need to mention the desired username and hostname along with the command. The syntax is as follows: DROP USER <‘username’@’hostname’>’;
How do I delete a user in MariaDB?
You can delete a user in MariaDB using the DROP USER command in SQL. It is the same as deleting a regular MySQL user.
How to remove the user MySQL database in Linux?
To remove the user’s MySQL database at first use the DROP USER command to remove the user account then apply FLUSH PRIVILEGES and exit from the server.
How do I delete or remove MySQL?
To delete the MySQL server from your Ubuntu, firstly you will need to stop the running server on your machine using the “sudo systemctl stop mysql” command. Then uninstall the server package using the apt-get remove command.
How to remove the MySQL key?
You can use the “sudo apt-key del <KEY_ID>” command to remove the MySQL key from Ubuntu. To get the key associated with the MySQL repository run the “sudo apt-key list” command.
How to remove the MySQL user password?
Use the ALTER USER command in SQL to remove the MySQL user password. To do so, you will need to assign a blank space in the password field. Follow this syntax: ALTER USER ‘username’@’hostname’ IDENTIFIED BY ”;
How to check all users in MySQL in Linux?
You can view the users and corresponding hostnames from the mysql.users table. To do so, you will need to run the SELECT query on the table’s User and Host columns.
How to remove MySQL from Ubuntu 22?
You can remove the MySQL server from Ubuntu 22  using the apt-get remove command. But at first, you will need to stop the running server.

Related Articles

5/5 - (1 vote)
Anonnya Ghosh

Hello there! I am Anonnya Ghosh, a Computer Science and Engineering graduate from Ahsanullah University of Science and Technology (AUST). Currently, I am working as a Linux Content Developer Executive at SOFTEKO. The strong bond between Linux and cybersecurity drives me to explore this world of open-source architecture. I aspire to learn new things further and contribute to the field of CS with my experience. Read Full Bio

Leave a Comment