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.
