How to get list of MySQL user accounts

Many a time it is required to get a list of MySQL users on your system for auditing purpose or maintenance. The following few options will give you a quick way to do the same.

SELECT user FROM mysql.user;

This will generate the the following output depending on your system.

+----------+
| User     |
+----------+
| root     |
+----------+
| sameer   |
+----------+
| jeff     |
+----------+
| jeff     |
+----------+
| mysql.sys|
+----------+

If you have different hosts than there could be some accounts with the same username. You can use the DISTINCT keyword to get unique usernames.

SELECT DISTINCT user FROM mysql.user;

OR use the following to see how many of each user there are in the the database.

SELECT user, COUNT(user) as total FROM mysql.user GROUP BY user;

This will show you the following which includes the username and how many of each username there are with different hosts.

+-----------+-------+
| user      | total |
+-----------+-------+
| jeff      |     2 |
| mysql.sys |     1 |
| root      |     1 |
| sameer    |     1 |
+-----------+-------+

So where does MySQL get the usernames from? MySQL stores the user related information in its own database, not surprisingly named ‘mysql’. Within the database user related information is stored in the table ‘user’, which besides the username has other fields like password, host etc.

One thought on “How to get list of MySQL user accounts

Comments are closed.