How to Drop a User in MySQL
The best way to delete a user in MySQL is with the DROP USER
command. This post walks you through it.
Understanding the DROP USER Command
The DROP USER
command is used to remove one or more MySQL user accounts and their privileges.
Syntax
DROP USER [IF EXISTS] user_name@host_name;
IF EXISTS
: Optional clause to prevent an error from occurring if the user does not exist.user_name@host_name
: Specifies the username and the host from which they connect. The host name can be a specific IP,%
for any host, or omitted for the default host.
How to Remove a Single User
To remove a specific user, use the following command, replacing username
and hostname
with the appropriate values:
DROP USER username@hostname;
How to Remove Multiple Users
To delete multiple users in a single command, separate each user by a comma:
DROP USER user1@host1, user2@host2;
Using IF EXISTS
If you're unsure whether a user exists, you can use IF EXISTS
to avoid errors:
DROP USER IF EXISTS username@hostname;
Revoke Privileges Before Deletion
You should probably revoke all of the user’s privileges before deleting them. That way they’ll lose access even if, for whatever reason, the deletion command fails.
REVOKE ALL PRIVILEGES, GRANT OPTION FROM username@hostname; DROP USER username@hostname;
Flush Privileges
After deleting a user, it's important to run the FLUSH PRIVILEGES
command. This will make sure the changes are applied immediately.
FLUSH PRIVILEGES;
Check Existing Users
Before deletion, you may want to check the existing users. Use the following command:
SELECT User, Host FROM mysql.user;
Basedash
If you’re looking for a solid GUI for your MySQL database, check out Basedash. Basedash uses AI to instantly give you an admin panel on top of your SQL database, so you don’t need to waste time building custom internal tools. You can write and share SQL queries, generate charts, perform CRUD operations and more.
Invite only
Fast. Opinionated. Collaborative. Local-first. Keyboard centric. Crafted to the last pixel. We've got 50 slots for Alpha access.
How to Add Columns to MySQL Tables with ALTER TABLE
Robert Cooper
How to Add Columns to Your MySQL Table
Max Musing
Pivot Tables in MySQL
Robert Cooper
How to Rename a Table in MySQL
Max Musing
How to Optimize MySQL Tables for Better Performance
Robert Cooper
How to Display MySQL Table Schema: A Guide
Jeremy Sarchet