mysql show users | mysql-list-users

Server Management Services

How to Show All Users in MySQL is a common question most beginner MySQL users ask. We have seen this question all over the internet, on our blog and our our clients has asked how they can show all MySQL users too. It’s frequently asked because there are other MySQL commands for displaying information about the database, for example: SHOW DATABASES will list out all the databases, SHOW TABLES will display all the tables in your MySQL database etc. It’s not unusual for people to assume that there should be a “SHOW USERS” command in MySQL. Well, there isn’t one. Luckily there’s a way to list all MySQL users, and in this tutorial we’ll explain how you can list all users in MySQL. Listing all users in MySQL is an easy task – just follow the steps below and you should have  listed all users in MySQL in no time. 

We’ll assume that you already have MySQL installed on your server. If you haven’t already done so, you can follow one of our LEMP guides and install MySQL.

MySQL List Users – How to List MySQL User Accounts via command line?

Login to MySQL Server as root

First of all, you need to login as root in MySQL using the following command:

## mysql -u root -p

List MySQL Users

Use the SQL query below to see a list of all MySQL users.

SELECT User, Host, Password FROM mysql.user;

You should get an output similar to this:

+------------------+--------------+--------------+
| user             | host         | password     |
+------------------+--------------+--------------+
| root             | localhost    | 37as%#8123fs |
| debian-test-user | localhost    | HmBEqPjC5Y   |
| johnsm           | localhost    |              |
| brian            | localhost    |              |
| root             | 111.111.111.1|              |
| guest            | %            |              |
| adrianr          | 10.11.12.13  | RFsgY6aiVg   |
+------------------+--------------+--------------+
7 rows in set (0.01 sec)

If you want to add more columns or exclude some, just edit the command with the columns you need. You may only need the names of the users, so you can use SELECT User FROM mysql.user;
And that’s all. Using this method you will get a list of all the users in MySQL.

If you want to display only unique usernames that won’t be repeated in more rows, you can use SELECT DISTINCT User FROM mysql.user;, which should give you this output:

+------------------+
| user             | 
+------------------+
| root             | 
| debian-test-user | 
| johnsm           | 
| brian            | 
| guest            | 
| adrianr          | 
+------------------+
6 rows in set (0.01 sec)

How to show current user in MySQL

To get the information on the current user, we use the user() function as shown:

SELECT user();
+-----------------+
| user()          |
+-----------------+
| local@localhost |
+-----------------+
1 row in set (0.00 sec)

Or you use the current_user() function:

SELECT current_user();
+-----------------+
| current_user()  |
+-----------------+
| local@localhost |
+-----------------+
1 row in set (0.00 sec)

Conclusion

In this tutorial, you have learned how to show or list users in MySQL, by querying data from the user table in the MySQL database.


See Also – MySql Create User

mysql show all usersIf you have any additional questions on how to show all users in MySQL, feel free to leave a comment below. You can always get a Managed VPS from us and our fully-managed support will help you with problems related to MySQL, or will explain how to list/show all users in MySql database.

PS. If you liked this post on How to Show All Users in MySQL,  please share it with your friends on the social networks using the buttons on the left or simply leave a reply below. Thanks.