mysql show users | mysql-list-users

This is a common question that most beginner MySQL users ask. We noticed it all over the internet, as well as on our blog. It’s frequently asked because there are other MySQL commands for displaying information about the database, for example: SHOW DATABASES will list all databases, SHOW TABLES will display all of the tables found in your MySQL database, etc. Thus, 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 to do just 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

Showing all users that exist on your local MySQL server is fairly easy – just follow these next couple of steps:

First of all, you need to login as root in MySQL by using the following command (Don’t forget to enter your MySQL root password if you have one set):

## 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             | 111.111.111.1|              |
| guest            | %            |              |
| adrianr          | 10.11.12.13  | RFsgY6aiVg   |
+------------------+--------------+--------------+
7 rows in set (0.01 sec)

Of course, the names in the left column will vary depending on what users exist on your MySQL server. 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 there is to it. Using this method, you will get a list of all users in your MySQL server.

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 team will help you with problems related to MySQL, or will explain how to list/show all users on your MySQL server. They are available 24/7, and can help you with any questions or requests that you may have.

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 below, or simply leave a reply in the comment section. Thanks.