Delete mysql user example and tutorial

22 June, 2011 (11:05) | mysql, VPS | By: sblogadmin

Note: deleting mysql users will not work on most types of shared accounts. You will likely need to enter a support request unless you have a VPS.

Here’s how to delete a user mysql from.

In the example the test user is called “jdoe”

This step is not necessary but you can use it to verify you are removing the intended username :


mysql> show grants for jdoe;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for jdoe@% |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jdoe'@'%' IDENTIFIED BY PASSWORD '*4CB51346C69F4302F0371CB61DE7076D14837C48' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `jdoedb`.* TO 'jdoe'@'%' |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Next, perform the actual removal of the user:

mysql> drop user jdoe;
Query OK, 0 rows affected (0.08 sec)

Next run flush privileges – this is likely not needed. However lacking time to look up the correct answer I’m going to call it anyway. When in doubt, remember that flushing privileges never hurts anything. It simply reloads all the privileges currently assigned (thus “unloading” any that are deleted and should be removed).

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

If you want to verify the user was deleted, run the show grants command again:

mysql> show grants for jdoe;
ERROR 1141 (42000): There is no such grant defined for user 'jdoe' on host '%'

It should come back empty now.

Write a comment