All commands will be executed inside the MySQL shell as a root user.

 

To access the MySQL shell type the following command and enter your MySQL root user password when prompted:

$ mysql -u root -p

 

If you haven’t set a password for your MySQL root user you can omit the -p switch.

 

Show MySQL Users

MySQL stores information about the users, in a table named user in the mysql database. To get a list of all MySQL user accounts we can use the SELECT statement and retrieve all rows from the mysql.users table:

mysql> SELECT User, Host FROM mysql.user;

 

The output should look similar to below:

 

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | localhost |
| adam             | %         |
| eldo             | %         |
| jassher          | 10.10.0.6 |
| justin           | 10.10.0.9 |
| linda            | localhost |
| mona             | localhost 

| +------------------+-----------+ 8 rows in set (0.00 sec)

8 rows in set (0.00 sec)

 

The command above shows only two columns from the mysql.user table (User and Host), This table contains more than 20 columns such as Password, Select_priv, Update_priv … etc.

You can use the desc mysql.user; statement to display information about each of a table’s columns. Once you know the column name run a query against a selected data.

For example to get a list of all MySQL users accounts including information about the password and whether it is active or expired, you can use the following query:

 

mysql> SELECT User, Host, Password, password_expired FROM mysql.user;

 

 +----------------+-----------+-------------------------------------------+------------------+
| User           | Host      | Password                                  | password_expired |
+----------------+-----------+-------------------------------------------+------------------+
| root           | localhost |                                           | N                |
| adam           | %         | *ADC3B5B27617732CD6320A2DA976258E149A7EC8 | N                |
| eldo           | %         | *9550E004046348198A143A115550E1262209FB6F | N                |
| jassher        | 10.10.0.6 | *F91C86B486B945C083B61A05FF6E197560D187EC | Y                |
| justin         | 10.10.0.9 |                                           | Y                |
| linda          | localhost | *17F2B1E48029294841AD66772BEBB7E6E6A005AF | N                |
| mona           | localhost | *74409C8DB55AC1A6829D801915981C46EDBFC64A | N                |
+----------------+-----------+-------------------------------------------+------------------+

8 rows in set (0.00 sec)

 

This is how to get a list of all MySQL users accounts by retrieving data from the user table in the mysql database.

Was this answer helpful? 1 Users Found This Useful (2 Votes)