How to Delete MySQL User in Ubuntu [Step-by-Step Guide]

LINUX
FUNDAMENTALS A Complete Guide for Beginners Enroll Course Now

Enroll Course Now

Being able to delete MySQL users in the Ubuntu distribution is as necessary as creating new MySQL users. To delete a “MySQL user” is to remove the account along with revoking all its privileges from the grant tables.  Using the basic Structured Query Language (SQL) you can delete MySQL users from your Linux distributions.

In this article, I will show how to delete a MySQL user from Ubuntu.

Process Flow Chart to Delete MySQL User in UbuntuDistro Used Throughout the Tutorial: Ubuntu 22.04.1 LTS

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

As an example, I will delete the MySQL user named “jerry” by retrieving the hostname from the MySQL User Table called “mysql.user”. So, here’s a step-by-step guide to delete a MySQL user in Ubuntu using the command line:

Step 1: Connect to MySQL Server

The foremost step to remove a MySQL user is to connect to the MySQL server. To do so, follow the steps below:

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.

   As you can see, after entering the MySQL root user password, I am connected to the MySQL server. 

Step 2: List the Existing MySQL Users

The second step is to view the MySQL users which is a good practice to verify that the user account you wish to drop exists. So, to get the list of usernames and hostnames on your MySQL server, run the command below:

SELECT User, Host FROM mysql.user;

In the above image, you see that the MySQL user jerry exists that I intend to remove from the server.

Step 3: Remove the 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 2.

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:

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.

   

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.

   

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.

   

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 the 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

Delete Multiple MySQL Users 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:

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.

   

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.

   

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.

   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 mentioned below:

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

   

2. Finally, 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 how to delete 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 to remove MySQL users from Ubuntu using the command line?

To remove a MYSQL user from the command line: connect to the MYSQL database as root using the command mysql -u root -p, find the exact name of the MYSQL user by running SELECT User, Host FROM mysql.user;and remove the specified user with the command DROP USER 'username'@'host'. Replace username and host with the original user and hostname.

Can I delete multiple MySQL users from the command line?

Absolutely. To delete multiple MYSQL  user accounts at the same time, use the DROP USER syntax and mention users separated by a comma and a space. For example, to remove users  user1, user2, user3 at once, run DROP USER 'user1'@'localhost', 'user2'@'localhost', 'user3'@'localhost';.

How do I completely DELETE a MySQL 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.

What does it mean to delete a MySQL user?

To delete a MYSQL user means to remove the user account and all its privileges from all grant tables. This action revokes the user’s access rights, preventing them from interacting with the database, running queries, and managing data within the MYSQL system.

Which command will DELETE a MYSQL 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?

To 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 server?

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?

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 completely remove MySQL from Ubuntu 22?

To remove the MySQL server from Ubuntu 22, open the terminal and run the command sudo apt remove mysql-server mysql-client. This will remove the MySQL packages. However, at first, you will need to stop the MySQL application background services by using the command sudo systemctl stop mysql.

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]