Advertisement
Advertisement


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.

2019/05/13
1
1351
5/13/2019 11:28:37 AM

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.

2017/05/23
1853
5/23/2017 12:34:51 PM

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;
2012/07/06

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;
2013/09/29

to avoid repetitions of users when they connect from different origin:

select distinct User from mysql.user;
2013/09/29

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 |
|                  | %         |
+------------------+-----------+
2017/11/27

If you are referring to the actual MySQL users, try:

select User from mysql.user;
2009/07/16

Source: https://stackoverflow.com/questions/1135245
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]