MySQL Show Users: A How-To Guide with Examples

A common question when using MySQL is how to show all users on the database.

In this guide, you'll learn how to see a list of users. You'll also be able to see a list of logged-in users as well as the current user.

Let's take a look.

MySQL Show Users

To show a list of all users on the MySQL database, query the mysql.user table:

1SELECT user, host
2FROM mysql.user;

The results are:

Some usernames are repeated, as they have different host values, and possibly different values for other columns.

To find a list of usernames only, or a distinct list of users, use the DISTINCT keyword on the user column.

1SELECT DISTINCT user
2FROM mysql.user;

In both of these examples, there's a root user and two built-in mysql users.

Find Locked and Password Expired Users in MySQL

There are a couple of columns you can check in the user table to see if a user is locked or has their password expired:

1SELECT
2user,
3host,
4account_locked,
5password_expired
6FROM mysql.user;

Here's what you'll see:

Show the Current User

If you need to see the current logged in user, you can use the USER() function or the CURRENT_USER function.

1SELECT
2USER(),
3CURRENT_USER();

I was logged in as root when I ran this command, so that's the user shown.

Show Currently Logged In Users

The mysql.user table shows a list of users in the database.

To see a list of currently logged in users, you can query a different table:

1SELECT id, user, host, db, command, time, state, info
2FROM information_schema.processlist;

A range of information is shown about currently logged in users in MySQL:

We can see that only the root user is logged in. The INFO column shows the recently run query, and the db column shows which database the users are logged in to. The table can be filtered using a WHERE clause as well.

What Else Can You Find Out About MySQL Users?

The mysql.user table has a lot of information about users. To find out what information is available, run the desc command:

1DESC mysql.user;

Conclusion

Using either the mysql.users table or the processlist table will help you get more information about the users in a MySQL database, whether you are looking for all users or currently logged in users.