Ubuntu: How to Show MySQL User Accounts via the Terminal

To get a list of user accounts on a MySQL server instance, we need to make a SELECT call to the mysql.user table.

First, fire up MySQL in your terminal and login as an administrator account with:

mysql -u root -p

You will be prompted for your root password before being granted access to the MySQL monitor.

Now to grab a list of available user accounts on the system. Running:

select * from mysql.user;

Will result in quite a big table being attempted to be drawn to the screen. To narrow this down and focus our user listing to just the column information we’re actually interested in, we can run

desc mysql.user;

to give us an indication of what columns are available in the system user table. For the most part, we are only really interested in the user account name and accepted host values, meaning our new statement to list the user accounts via the terminal looks as follows:

select user, host from mysql.user

Nifty.