I'm not finding a user by show grants for 'username'@'localhost'' I'm thinking that perhaps the username is slightly different, so I want to do a wildcard search of all the database users. How can I do this?

update: for clarification, as I'm thinking maybe I've not been 100% clear. by wildcard I meant I wanted to be able to search for only part of the users name, or for all users who's name matches pattern. On the system I needed this on there were 5k+ users, and although that's not performance loss huge, I don't want to look through them one at a time either.

2 Answers 2


Thanks to @matthewh's answer I managed to compose a query that would work when searching through a large number of users:

use mysql;
select  User, Host from  user where User like 'user%';

Note: <user> is whatever component of the username you need to search for, you may need a % on both sides if you don't know the start of the username, also <> are merely to note the variable, don't include them in an actual query, unless the username includes < or >.

use mysql;

select Host,User from user;

will give you all the MySQL users on that server.

  • I voted this down one, because it doesn't do what I meant with by wildcard, though I acknowledge that may have been my fault for not being clear enough up front. It might have been interpretted as everything when what I wanted was the equivalent of `find -name 'user*' for a filename on unix. Commented Jan 20, 2011 at 13:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.