2 Simple Methods To List Users in MySQL

There are various methods to list users in MySQL, if you are using CyberPanel you can also use PHPMyadmin. In this tutorial we will go through 2 methods to list users in MySQL.

In MySQL, there isn't a show users command, as most people assume. The MySQL command shows information about the database, but there are other commands you can use to see that information. SHOW DATABASES, for example, will display our MySQL Server's databases, while SHOW TABLES will display the tables in the MySQL database you selected.

The SHOW USERS command in MySQL is often assumed to be available by people unfamiliar with the database. It is still possible to see a list of users, even if there isn't a specific command, and to filter them according to your needs. Let's start using the MySQL command line tool to do all this.

Methods that we are going to use:

1. List users through command line
2. List users through PHPMyAdmin

List users in MySQL Through Command line

To list MySQL users through command line you need to SSH into your server, you can read our other article regarding transfer files over ssh in which we've explained how to SSH into your server using Bitvise.

Once you are in your SSH, lets list users in MySQL.

Big note: Before you do anything you need to log into MySQL using

mysql -u root -p<ROOT_PASSWORD>

Replace <ROOT_PASSWORD> with your MySQL root password.

MySQL have a user table that you can use get everything related to users. Lets first see the structure of this table using

desc mysql.user;

Result will be something like

MariaDB [(none)]> desc mysql.user;
+------------------------+-----------------------------------+------+-----+----------+-------+
| Field                  | Type                              | Null | Key | Default  | Extra |
+------------------------+-----------------------------------+------+-----+----------+-------+
| Host                   | char(60)                          | NO   | PRI |          |       |
| User                   | char(80)                          | NO   | PRI |          |       |
| Password               | char(41)                          | NO   |     |          |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N        |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N        |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N        |       |
.........
.........

Now to get users and their host run following command

select user,host from mysql.user;

This will give you list of all users in MySQL along with their host.

MariaDB [(none)]> select user,host from mysql.user;
+--------------------------+-----------------------------------------+
| user                     | host                                    |
+--------------------------+-----------------------------------------+
| savemyde                 | 160.153.129.235                         |
| savemyde_babyessays      | 160.153.129.235                         |
| savemyde_babyesssays     | 160.153.129.235                         |
| savemyde_bestcollege     | 160.153.129.235                         |
.........
........

Show current logged users

Everything in MySQL revolves around users, even to access and operate on database you need to create users and give these users access to databases, now, let see how we can list currently logged in users,

Run

select user,db,command from information_schema.processlist

Result:

MariaDB [(none)]> select user,db,command from information_schema.processlist;
+-------------+------------+---------+
| user        | db         | command |
+-------------+------------+---------+
| cyberpanel  | cyberpanel | Sleep   |
| root        | NULL       | Query   |
| cyberpanel  | cyberpanel | Sleep   |
| system user | NULL       | Daemon  |
| system user | NULL       | Daemon  |
| system user | NULL       | Daemon  |
| system user | NULL       | Daemon  |
| system user | NULL       | Daemon  |
+-------------+------------+---------+
8 rows in set (0.004 sec)

Now you can see that which MySQL user is currently logged in and what the user is trying to do in the command column.

List users in MySQL Through PHPMyAdmin

To list MySQL users through PHPMyadmin, you will need to install CyberPanel. CyberPanel will automatically install PHPMyadmin for you, PHPMyAdmin is graphical user interface to manage MySQL.

Once CyberPanel is installed, select PHPMyAdmin from left-side menu and it will automatically log you into PHPMyAdmin

From top menu select SQL, which will open the query box for you, now paste the query and click Go.

desc mysql.user;

Results of above command

Similarly you can run query to list users

select user,host from mysql.user;

Command to see currently logged in users

select user,db,command from information_schema.processlist

Summary

As you can see that we went through various ways to list MySQL users. Users in MySQL are integral part as they needed to access and operate on databases.

We've also explained how to see currently logged in users and what they are doing, which is important in-case you are trying to debug a slow MySQL server.

Related Content

MySQL Slow Query Logs: A Detail Guide