The information about the users is stored in the user system table in the mysql database.
A user consists of two parts: the user name and the host the user connected from, so to get the list of users, it is necessary to include both the user and host columns from the user table:
mysql> SELECT User, Host, CONCAT(User, '@', Host) AS Username FROM mysql.user;
You may also run the following command, that checks the information_schema.USER_PRIVILEGES table instead:
shell> mysql -B -N -e "SELECT DISTINCT CONCAT('SHOW GRANTS FOR ', GRANTEE, ';') FROM information_schema.USER_PRIVILEGES" > show-grants.sql
Note: Note that the usage of -N option will skip displaying column names in the output.
To get a list of privileges for a given user, the SHOW GRANTS command can be used, for example to get the privileges for the root user when logged in from localhost use:
SHOW GRANTS FOR 'root'@'localhost';
To get a list of users with grant over certain schema/s, the following query can be used (note that permissions on schemas are maintained after schema is dropped):
SELECT User FROM mysql.db WHERE Db ="[schema]";