Delete or Drop a User in MySQL

[Linux Hint]

MySQL is a well-known database that can be used with ease and no hassle and is used in a lot of big firms. Data integrity and Data administrators mean a lot to such huge companies. But when it comes to data integrity and taking care of the users, their privileges, and the creation and deletion of them, the database administrator takes responsibility for such tasks. So, in this article, we are going to learn about different methods to delete or drop a user in MySQL.

Before we get started learning about the user’s deletion in MySQL, it is assumed that you know how to create and list the users and have already installed MySQL on your system. So, figure out the version of MySQL using the command below:

mysql -V

If you could see the version, it means that it’s already installed. Moving forward, we will figure out the status of the system’s mysql.service. Then, we would be able to sign in to the MySQL server.

sudo systemctl status mysql

In case the service hasn’t started, you can initiate it with the following command:

sudo systemctl start mysql

Once the service begins, you can connect yourself to the MySQL shell as a root user so, you can pretty much access everything inside.

sudo mysql -u root -p

After logging into MySQL, list the user names and hostnames from mysql.user by running the following command:

SELECT user, host FROM mysql.user;

After having a look at the list of users, select the user you want to drop/delete.

There are two ways to delete a user with a subtle difference. If you want to delete an already existing user and you know its name, you can run the simple “DROP USER” command, along with the user name and its hostname. Like this:

DROP USER 'user_name'@'host_name';

But if you do not know or remember the name of the user and have a hunch of the user’s name, then MySQL provides the IF EXISTS clause to help in such scenarios. If the user’s name exists in the MySQL against the provided name in the query, it will get deleted for sure. Otherwise, it won’t get deleted. However, if we do not utilize the IF EXISTS clause, MySQL won’t work, and you would see an error. So, it is recommended to use the IF EXISTS clause if you are not sure about the existence of the user’s name in MySQL. The general syntax if you want to use the IF EXISTS clause is shared below:

DROP USER IF EXISTS 'user_name'@'host_name';

You can delete or drop multiple users in a single query as well by running the following command in MySQL’s shell:

DROP USER 'user_name1'@'host_name1' 'user_name2'@'host_name2';

When you have deleted the user, you can check the list of users again, whether the user still existed in the list or not.

SELECT user, host FROM mysql.user;

You can see in the list that the deleted user or users are not there anymore.

So, this is how we can delete or drop a user in MySQL using the DROP command.

Conclusion

In this article, we have learned two different syntaxes for deleting the user in MySQL. We have also learned to delete multiple users in a single query as well.

View the full article