MySQL user management from the command line

If phpMyAdmin is not installed on your server or if you don’t have access to it but instead have shell access, you can easily manage your MySQL database users from the command line.

MySQL stores the list of users in the mysql.user table. List of all databases are stored in the mysql.db table.

Show list of MySQL users

# Show list of all users
select user from mysql.user;

# Show list of users assigned to a particular database
select user from mysql.db where db='databasename';
Code language: PHP (php)

Show list of databases for a user

# Show a list of databases a particular user can manage
select db from mysql.db where user='dbusername';
Code language: PHP (php)

Delete a user

# Delete an user from all MySQL users. 'dbusername' must exist.
drop user dbusername@localhost;
Code language: PHP (php)