MysqlHow-ToBeginner · 3 min read

How to List All Users in MySQL Quickly and Easily

To list all users in MySQL, run the query SELECT User, Host FROM mysql.user;. This shows all usernames and their host origins stored in the MySQL system database.

📐

Syntax

The query to list all users in MySQL accesses the mysql.user table, which stores user account information.

SELECT User, Host: selects the username and host columns.
FROM mysql.user: specifies the system table containing user data.

sql

SELECT User, Host FROM mysql.user;

💻

Example

This example shows how to list all MySQL users and their hosts. It helps you see who can connect and from where.

sql

mysql> SELECT User, Host FROM mysql.user;
+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| root             | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| debian-sys-maint | localhost |
+------------------+-----------+

Output

⚠️

Common Pitfalls

Common mistakes include:

Not having sufficient privileges to query mysql.user. You need SELECT permission on this table.
Confusing the User column with the current logged-in user; this query lists all users, not just yours.
Expecting this query to show passwords; passwords are stored hashed and not displayed here.

sql

/* Wrong: Trying to use SHOW USERS (not valid) */
SHOW USERS;

/* Right: Use SELECT on mysql.user */
SELECT User, Host FROM mysql.user;

📊

Quick Reference

Summary tips for listing MySQL users:

Use SELECT User, Host FROM mysql.user; to see all users.
Run the query as a user with admin privileges.
Remember hosts show where users can connect from.

✅

Key Takeaways

Use SELECT User, Host FROM mysql.user; to list all MySQL users and their hosts.

You need proper privileges to query the mysql.user table.

This query shows all users, not just the current one.

Hosts indicate where users are allowed to connect from.

Passwords are not visible in this query for security reasons.