MySQL List Users – How to List MySQL User Accounts via command line?

By Raju

February 13, 2017

MySQL

You are using the MySQL Database server and created database and users. Now you need to see MySQL users list. How can you view users?  In this tutorial, you will see how to list MySQL user accounts via command line.

Login to MySQL Server

Once you are in Linux / Ubuntu command line enter below command to access MySQL server.

shell>  mysql --user=root mysql

The above command connects you to MySQL server interface with root user. If you have assigned a password to the root account, you must also supply a --password or -p option.

shell> mysql --user=root mysql -p

After connecting to MySQL server successfully, you can list users.

List MySQL Users

Use below SQL query to see list of mysql users.

mysql>  SELECT User FROM mysql.user;

The above command trims of "User" table and lists only user names.

+-------+
| User |
+-------+
| root |
+-------+
| newuser |
+-------+

If you want to list of MySQL user information, including user permission information and all users data. You could try below query.

mysql> select * from mysql.user;.

You could try other variations with below example queries.

mysql> select User, Host from mysql.user;
mysql> select User, Host, Password from mysql.user;
mysql> select DISTINCT User FROM mysql.user;

MySQL Users Information

You can see all the fields in the mysql.user table including description by running this MySQL query.

mysql> desc mysql.user;

If you have any additional questions on how to show all users in MySQL, feel free to ask us through below comment box.

Leave a Reply

Your email address will not be published. Required fields are marked

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Create a website in 3 simple steps

Choose a website template, add features, then customise! - Free Online Website builder.