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