Prerequisites:
- Command-line/terminal window.
- MySQL or MariaDB installed.
- User with sudo or root privileges.
How to Access MySQL as Root
Access the MySQL server as root user by entering the following command in your terminal:
sudo mysql --user=root mysql -p
The -p option is mandatory only if you have a predefined password for your root user. If no password is defined, use the command without the -p option.
The output presented using Ubuntu 18.04 shows that we have gained access to the MySQL client as the root user.
We are now in the position to initiate a MySQL query, and retrieve information from the mysql.user database.
How to Show All MySQL Users
The following command lists usernames that have access to this specific server:
SELECT user FROM mysql.user;
With this command, we have instructed MySQL to create a table. The system retrieves the information from the User column in the mysql.user database.
How to List mysql.user Database Fields
Before expanding the query, let’s list the fields available in the mysql.user database. The following command lists all the available columns:
desc mysql.user;
The options in the Field column represent the information we can request from the mysql.user database.
How to Show MySQL User Info
The following query provides a table with the User, Host, and authentication_string columns:
SELECT User, Host, authentication_string FROM mysql.user;
The query adds two more columns to the User column and provides valuable information such as the user’s password and hostname.
How to List Unique MySQL Users
Another useful option is to remove usernames that appear in multiple rows. To display only unique usernames enter the following command:
SELECT DISTINCT User From mysql.user;
The output removes duplicate rows and shows specific usernames only once.
