How to Create and Delete a User in MySQL

Databases are used to store and manage large amounts of information on computers and servers. They are an essential component to running web-based applications, content management systems, and online eCommerce shops. When working with a MySQL database, you may often need to create and delete users. Creating and removing users in MySQL is an essential skill for any database administrator.

In this post, you will learn how to create and delete a user in MySQL.

Create User in MySQL

The basic syntax to create a user in MySQL is very simple. You can use the CREATE USER command by specifying the user and host for a new user account as shown below:

CREATE USER 'user'@'host' IDENTIFED BY 'password';

To create a new user named user1 and user2 for MySQL locally, use the following command:

CREATE USER 'user1'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
CREATE USER 'user2'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

If you are connecting to MySQL remotely, then you can replace the localhost with remote_ip_address as shown below:

CREATE USER 'user1'@'remote_ip_address' IDENTIFIED WITH mysql_native_password BY 'password';

If you want to create a user that can connect from any machine, run the following command:

CREATE USER 'user1'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

Also Read

How to Check and Repair MySQL

Delete a User in MySQL

Before deleting a user in MySQL, you will need to find the exact name of the user you want to remove. You can list all users with the following command:

SELECT User, Host FROM mysql.user;

You will get a list of all MySQL users in the following output:

+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| testuser         | %         |
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
| user1            | localhost |
| user2            | localhost |
+------------------+-----------+

Next, run the following command to delete a user named user1:

DROP USER 'user1'@'localhost';

To delete multiple MySQL users, run the following command:

DROP USER 'user1'@'localhost', 'user2'@'localhost', 'user3'@'localhost';

Delete an Active MySQL User

Sometimes, the MySQL user that you try to delete is active. In this case, you will need to kill the user session and then remove the user account.

First, get a list of all active MySQL users using the following command:

SHOW PROCESSLIST;

You will get the active user list with session ID:

+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User            | Host      | db   | Command | Time | State                  | Info             |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
|  5 | event_scheduler | localhost | NULL | Daemon  | 5129 | Waiting on empty queue | NULL             |
| 20 | user2           | localhost | NULL | Sleep   |   54 |                        | NULL             |
| 21 | root            | localhost | NULL | Query   |    0 | init                   | SHOW PROCESSLIST |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+

Now, kill the user2 session with the following command:

KILL 20;

Once the user is inactive, you can delete it with the following command:

DROP USER 'user2'@'localhost';

Also Read

How to Connect to MySQL Using PHP

Conclusion

In this post, we explained how to create and delete users in MySQL. We also explained how to delete multiple and active user accounts in MySQL. Try it on dedicated hosting from Atlantic.Net!