The best answers to the question “How can I get a list of user accounts using the command line in MySQL?” in the category Dev.
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.
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;
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.
To avoid repetitions of users when they connect from a different origin:
select distinct User 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;