How to get a list of user accounts using the command line in MySQL?
How to get a list of user accounts using the command line in MySQL?
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.
Accepted Answer
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.
Read more… Read less…
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;
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;
to avoid repetitions of users when they connect from different origin:
select distinct User from mysql.user;
MySQL stores the user information in its own database. The name of the database is MySQL. Inside that database, the user information is in a table, a dataset, named user. If you want to see what users are set up in the MySQL user table, run the following command:
SELECT User, Host FROM mysql.user;
+------------------+-----------+
| User | Host |
+------------------+-----------+
| root | localhost |
| root | demohost |
| root | 127.0.0.1 |
| debian-sys-maint | localhost |
| | % |
+------------------+-----------+
If you are referring to the actual MySQL users, try:
select User from mysql.user;