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

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.

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.

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 a 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;

SELECT * FROM mysql.user;

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

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

select User from mysql.user;

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

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

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;

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.

This displays the list of unique users:

SELECT DISTINCT User FROM mysql.user;

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

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;

SELECT User FROM mysql.user;

Use the above query to get the MySQL users.

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;