How to List All Users in a MySQL Database

November 18, 2019


The primary function of a database is to store information in a structured way to allow easy access to that information. Having many users with permission to access and work on your MySQL server can become a security concern.

A short set of queries made within the MySQL client can provide you with valuable information to help prevent potential issues. Use the commands outlined in this tutorial to list all user accounts on a MySQL server.

Tutorial on how to list all users in a MySQL database.


  • 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.

Command to log in to MySQL shell.

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.

The output in this example shows a table of four rows:

List of MySQL users.

The list we received has limited value. To retrieve a more detailed overview of the MySQL users and their permissions, we can expand our search parameters and add additional columns.

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.

List of all options to search by.

It is possible to combine several options in a single command to get detailed user information.

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.

List of users in additon to their passwords and hostnames.

Note: The Password field in the mysql.user table has been renamed in MySQL 5.7. The field that lists user passwords is now named authentication_string.

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.


Instead of just listing users, you can modify search results and find user-specific information. A detailed and customizable overview enables you to make an informed decision regarding the security of your MySQL server.

By combining the commands presented in this article, you are now able to structure user information from the mysql.user database with ease.

Was this article helpful?
Vladimir Kaplarevic
Vladimir is a resident Tech Writer at phoenixNAP. He has more than 7 years of experience in implementing e-commerce and online payment solutions with various global IT services providers. His articles aim to instill a passion for innovative technologies in others by providing practical advice and using an engaging writing style.
Next you should read
How To Create New MySQL User and Grant Privileges
September 18, 2019

MySQL is a database application for Linux. It's part of the LAMP (Linux, Apache, MySQL, PHP) stack that...
Read more
How To Show a List of All Databases in MySQL
July 23, 2019

With Structured Query Language (SQL), you can easily access and manage content in all your databases...
Read more
How to Connect to MySQL using PHP
May 6, 2019

To access and add content to a MySQL database, you must first establish a connection between the database and...
Read more
How To Install MySQL on CentOS 7
February 6, 2019

MySQL is a popular free and open source database management application. It forms part of the LAMP stack...
Read more
  • © 2021 Copyright phoenixNAP | Global IT Services. All Rights Reserved.