Show All Users in MySQL

This is a common question most beginner MySQL users ask. We notice it all over the internet and on our blog 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 MySQL users and in this tutorial we’ll explain how do that.

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: SHOW USERS – How to do it

The main part of our tutorial – how to show all users in MySQL:

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

## mysql -u root -p

There’s lots of tables in your MySQL database, but right now we’ll only need the User table.
To show all MySQL users from the User table, use this command:

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             ||              |
| guest            | %            |              |
| adrianr          |  | 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.

Get a Fully Managed VPS from RoseHosting with FREE 24/7 Support

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)

See Also – MySql Create User

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

Categories Tutorials
admin :

View Comments (1)