How to get a list of MySQL user accounts

Question

I'm using the MySQL command line utility and can navigate through a database. Now I need to see a list of user accounts. How can I do this?

I'm using MySQL version 5.4.1.

Answer 1

Use this query:

SELECT User FROM mysql.user;

Which will output a table like this:

+-------+
| User  |
+-------+
| root  |
+-------+
| user2 |
+-------+

As Matthew Scharley points out in the comments on this answer, you can group by the User column if you'd only like to see unique usernames.

Answer 2

A user account comprises the username and the host level access.

Therefore, this is the query that gives all user accounts

SELECT CONCAT(QUOTE(user),'@',QUOTE(host)) UserAccount FROM mysql.user;

Answer 3

The mysql.db table is possibly more important in determining user rights. I think an entry in it is created if you mention a table in the GRANT command. In my case the mysql.users table showed no permissions for a user when it obviously was able to connect and select, etc.

mysql> select * from mysql.db;
mysql> select * from db;
+---------------+-----------------+--------+-------------+-------------+-------------+--------
| Host          | Db              | User   | Select_priv | Insert_priv | Update_priv | Del...

Answer 4

I find this format the most useful as it includes the host field which is important in MySQL to distinguish between user records.

select User,Host from mysql.user;

Answer 5

$>  mysql -u root -p -e 'Select user from mysql.user' > allUsersOnDatabase.txt

Executing this command on linux prompt will first ask for the password of mysql root user, on providing correct password it will print all the database users to the text file.

Answer 6

Login to mysql as root and type following query

select User from mysql.user;

+------+
| User |
+------+
| amon |
| root |
| root |
+------+