The way MySQL is structured is that it keeps its own users in a MySQL Table – smart, isn’t it? They are related to what Databases they have access to and what permissions they have on those Databases. In addition to that, they also store information about the User, like password and roles. There are MySQL commands to display the Databases and Tables, explain them, but since the User is an entry in those Tables there is a slightly different way of displaying them.
You need root access to the MySQL database in order to view all users on the server, for this you should initially log in as the root user:
mysql -u root -p # enter password for the root user
As the User Table has many columns in it there are several important ones, like the Host and User. We’ll use a SELECT statement to view some of them for the current entries:
SELECT Host, User FROM mysql.user;
This could list several duplicated entries for the User as it could have several different Hosts, we can just list the unique Users, for this we’ll use DISTINCT with SELECT:
SELECT DISTINCT User from mysql.user;
Experiment with different MySQL commands on our SSD VPS Hosting Plans or get full freedom by managing your own Dedicated Server at a very affordable price!
Useful MySQL Commands: