How to Display All Users in MySQL?

This is a common question asked by MySQL beginners. Today, let's explore how to display all users in MySQL. Typically, in MySQL, we use SHOW DATABASES to display all databases and SHOW TABLES to display all tables. So, you might assume the command to display all users is SHOW USERS, right? Nope, that's not the case. Let's dive into how to display all users in MySQL.

Displaying All Users in MySQL

1. Log in to the Database

First, you need to log in to the database using the following command. Note that you must log in as the root user:

## mysql -u root -p

2. Query the User Table

MySQL includes a built-in database called mysql, which stores various data such as user information, permissions, stored procedures, and more. To display all users, you can use a simple query like this:

SELECT User, Host, Password FROM mysql.user;

Here is the example output:

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

If you want to add or remove certain columns from the result, you just need to edit the SQL query. For example, if you only want to display the usernames, you can use:

SELECT User FROM mysql.user;

It's that simple! With this method, you can retrieve all users. Give it a try!

3. Display All Unique Users (No Duplicates)

Those familiar with MySQL know about the DISTINCT keyword, which is used to remove duplicate data. You can use the following command to display all unique MySQL users while ignoring users that only differ by hostname:

SELECT DISTINCT User FROM mysql.user;

The output will look like this:

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

That's it! You now know how to display all users in MySQL effectively.