How can I 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.

@Mustapha Why the title change? This answers here are SQL you can run from anywhere, not just a command line. And what does that tag add to the question? — Rup
– Rup, Commented May 14, 2019 at 9:13
The purpose of my edit was to maintain consistency between the title and the description, but I think you have a good point. Make your edit Mr. @Rup — mhadidg
– mhadidg, Commented May 14, 2019 at 13:11

7 revs, 5 users 31% · Accepted Answer · 2025-07-23 16:43:42Z

1915

Use this query:

SELECT User FROM mysql.user;

Which will output a table like this:

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

An advanced and efficient answer is the use of a stored procedure to retrieve MySQL users because it simplifies this repeated query, improves readability, and makes it easier to manage access in a secure, reusable way. You type it once, then you only need to type: CALL mysql.users(); to retrieve your mysql users.

USE mysql;
DELIMITER $$
CREATE PROCEDURE users()
BEGIN
  SELECT CONCAT(QUOTE(user), '@', QUOTE(host)) AS Users
  FROM mysql.user
  ORDER BY user, host;
END $$
DELIMITER ;
CALL mysql.users();

edited Jul 23 at 16:43

community wiki

7 revs, 5 users 31%
George Claghorn

Sign up to request clarification or add additional context in comments.

9 Comments

Matthew Scharley Over a year ago

I think it may be necessary to group on User too, to only get unique user values, since there's a seperate row for each user@host entry.

Divyanshu Das Over a year ago

How to find the same info without sql query ??

RolandoMySQLDBA Over a year ago

@barrycarter DELETE FROM mysql.user; better have WHERE user='someuser' and host='somehost'; If you do DELETE FROM mysql.user;, all users are gone. Logins after the next mysql restart or FLUSH PRIVILEGES; eliminate users from memory. Here is an example of one of my posts on doing DELETE FROM mysql.user responsibly : dba.stackexchange.com/questions/4614/…

fancyPants Over a year ago

@Geoffrey SHOW GRANTS FOR 'user'@'host';

user2683246 Over a year ago

instead of grouping you can just use DISTINCT keyword: SELECT DISTINCT user FROM mysql.user;

|

spkane · Accepted Answer · 2012-07-06 18:21:58Z

469

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;

answered Jul 6, 2012 at 18:21

spkane

6,7743 gold badges20 silver badges20 bronze badges

2 Comments

Prabhakar--PK Over a year ago

Just curious. When will this host come into play when working with mysql databases? [ Mysql Noob]

Ray Baxter Over a year ago

@Packer the host comes into play when you are connecting from a different server. It is possible to grant different access to 'packer'@'example.com' and 'packer'@'google.com'

RolandoMySQLDBA · Accepted Answer · 2013-09-29 22:29:11Z

127

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;

answered Sep 29, 2013 at 22:29

RolandoMySQLDBA

44.4k16 gold badges96 silver badges136 bronze badges

3 Comments

Iain Samuel McLean Elder Over a year ago

This is basically the same as spkane's answer. What is the benefit of concatenating the user and host columns?

Iain Samuel McLean Elder Over a year ago

One example: the user@host format is used for setting passwords. Omitting the host from the SET PASSWORD command produces an error. SET PASSWORD FOR wordpressuser = PASSWORD('...'); produces the error ERROR 1133 (42000): Can't find any matching row in the user table. Include the host and it works. SET PASSWORD FOR wordpressuser@localhost = PASSWORD('...'); produces Query OK, 0 rows affected (0.00 sec).

sjas Over a year ago

Best answer, whatever the naysayers may complain about. Only thing i'd change were appending an ORDER BY user to it.

Peter Mortensen · Accepted Answer · 2021-08-05 20:13:51Z

52

To avoid repetitions of users when they connect from a different origin:

select distinct User from mysql.user;

edited Aug 5, 2021 at 20:13

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Sep 29, 2013 at 22:01

Nicolas Manzini

8,4996 gold badges68 silver badges83 bronze badges

Comments

VPK · Accepted Answer · 2017-11-27 04:31:47Z

35

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 |
|                  | %         |
+------------------+-----------+

edited Nov 27, 2017 at 4:31

answered Oct 26, 2016 at 10:33

VPK

3,0781 gold badge30 silver badges36 bronze badges

Comments

Jesse Vogt · Accepted Answer · 2009-07-16 03:28:16Z

31

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

select User from mysql.user;

answered Jul 16, 2009 at 3:28

Jesse Vogt

16.6k18 gold badges63 silver badges72 bronze badges

Comments

Etzeitet · Accepted Answer · 2009-07-16 03:29:24Z

24

SELECT * FROM mysql.user;

It's a big table so you might want to be more selective on what fields you choose.

answered Jul 16, 2009 at 3:29

Etzeitet

2,1352 gold badges19 silver badges23 bronze badges

2 Comments

emotality Over a year ago

I got 3 root users with different hosts. localhost, 127.0.0.1 and ::1. Which one must I keep and what must I delete? Thanks!

trysis Over a year ago

If you don't want people to connect via the network, the old standard was to delete all of these. Nowadays, though, it seems the recommendation is to keep localhost ones, as they are not accessible over the network anyway; this means you should instead keep all of them.

Peter Mortensen · Accepted Answer · 2021-08-05 20:24:13Z

19

Log in to MySQL as root and type the following query:

select User from mysql.user;

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

edited Aug 5, 2021 at 20:24

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Mar 4, 2014 at 6:46

sandip divekar

1,8965 gold badges25 silver badges40 bronze badges

2 Comments

leenephi Over a year ago

I'd say +1 for mentioning logging in as root. I tried without doing so and it didn't work ;)

sandip divekar Over a year ago

You need to give privileges to user1 to display user list. Without root user you will get error. So first provide the privileges. Log in as root user then type command GRANT SELECT ON mysql.user TO 'user1'@'localhost'; now login as user1 and type command select User from mysql.user; You will see user list displayed. :) +1 Enjoy

Palec · Accepted Answer · 2015-08-22 08:37:16Z

17

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...

edited Aug 22, 2015 at 8:37

Palec

13.8k8 gold badges80 silver badges145 bronze badges

answered Dec 27, 2014 at 20:09

Brad Dre

3,8962 gold badges23 silver badges23 bronze badges

1 Comment

Developer Marius Žilėnas Over a year ago

In my view what is important is what users are allowed to do on the database. So "SHOW GRANTS" is quite useful since it shows who is allowed to do what.

Tobias Holm · Accepted Answer · 2016-11-02 19:01:33Z

14

I use this to sort the users, so the permitted hosts are more easy to spot:

mysql> SELECT User,Host FROM mysql.user ORDER BY User,Host;

answered Nov 2, 2016 at 19:01

Tobias Holm

2612 silver badges6 bronze badges

Comments

Peter Mortensen · Accepted Answer · 2021-08-05 20:13:07Z

12

Peter and Jesse are correct, but just make sure you first select the "mysql" database.

use mysql;
select User from mysql.user;

That should do your trick.

edited Aug 5, 2021 at 20:13

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered May 5, 2012 at 12:19

Armin Nehzat

4186 silver badges13 bronze badges

3 Comments

vitaLee Over a year ago

It's not necessery to use mysql; in case you scope the table to the mysql database like you did. You can just select User from mysql.user;

Dan Over a year ago

Adding use mysql; is just so you can use select User from user; instead select User from mysql.user; since it is usually a one time query, there is no need to use the mysql db

Sнаđошƒаӽ Over a year ago

after use mysql if you had used select user from user; than that could have been something, but instead you are using mysql.user, which makes using use mysql at the beginning unnecessary.

Peter Mortensen · Accepted Answer · 2021-08-05 20:17:16Z

12

This displays the list of unique users:

SELECT DISTINCT User FROM mysql.user;

edited Aug 5, 2021 at 20:17

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Sep 9, 2016 at 11:12

Nikhil Chavda

1411 silver badge4 bronze badges

Comments

Peter Mortensen · Accepted Answer · 2021-08-05 20:19:24Z

6

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

Executing this command on a Linux command line prompt will first ask for the password of MySQL root user. On providing the correct password it will print all the database users to the text file.

edited Aug 5, 2021 at 20:19

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Feb 21, 2017 at 10:04

drmaa

3,74411 gold badges53 silver badges78 bronze badges

Comments

Community · Accepted Answer · 2017-12-14 16:54:32Z

4

I found his one more useful as it provides additional information about DML and DDL privileges

SELECT user, Select_priv, Insert_priv , Update_priv, Delete_priv, 
       Create_priv, Drop_priv, Shutdown_priv, Create_user_priv 
FROM mysql.user;

edited Dec 14, 2017 at 16:54

CommunityBot

11 silver badge

answered Aug 28, 2017 at 23:10

Alper t. Turker

35.3k9 gold badges89 silver badges118 bronze badges

1 Comment

Peter Mortensen Over a year ago

What are "DML" and "DDL"? Can you link to (specific) context?

Javier G.Raya · Accepted Answer · 2022-03-27 16:59:46Z

2

To see your users, it would be to use the mysql database.

USE mysql;

And then make the select.

SELECT user,host FROM user;

Another option is to put the BD.Table.

For example :

SELECT user,host FROM mysql.user;

answered Mar 27, 2022 at 16:59

Javier G.Raya

2581 gold badge3 silver badges15 bronze badges

Comments

Peter Mortensen · Accepted Answer · 2021-08-05 20:21:18Z

1

SELECT User FROM mysql.user;

Use the above query to get the MySQL users.

edited Aug 5, 2021 at 20:21

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Feb 15, 2019 at 5:19

Arun Karnawat

58111 silver badges21 bronze badges

Comments

Erick Gonzalez · Accepted Answer · 2025-07-23 16:32:39Z

0

An advanced and efficient answer is the use of a stored procedure to retrieve MySQL users because it simplifies this repeated query, improves readability, and makes it easier to manage access in a secure, reusable way. You type it once, then you only need to type: CALL mysql.users(); to retrieve your mysql users.

USE mysql;
DELIMITER $$
CREATE PROCEDURE users()
BEGIN
  SELECT CONCAT(QUOTE(user), '@', QUOTE(host)) AS Users
  FROM mysql.user
  ORDER BY user, host;
END $$
DELIMITER ;
CALL mysql.users();

answered Jul 23 at 16:32

Erick Gonzalez

3914 silver badges7 bronze badges

Comments

Tamil 07 · Accepted Answer · 2024-05-31 02:14:04Z

-1

I can get the password with this query

SELECT username, password enter code hereFROM mysql.user;

But the passwords are encrypted, what type of encryption is that and how to decrypt using dictionary attack which is best tool

answered May 31, 2024 at 2:14

Tamil 07

12 bronze badges

Collectives™ on Stack Overflow

How can I get a list of user accounts using the command line in MySQL?

18 Answers 18

9 Comments

2 Comments

3 Comments

Comments

Comments

Comments

2 Comments

2 Comments

1 Comment

Comments

3 Comments

Comments

Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

18 Answers 18

9 Comments

2 Comments

3 Comments

Comments

Comments

Comments

2 Comments

2 Comments

1 Comment

Comments

3 Comments

Comments

Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related