Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

use mysql;

select Host,User from user;

will give you all the MySQL users on that server.

share|improve this answer
    
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. –  xenoterracide Jan 20 '11 at 13:05
add comment
up vote 6 down vote accepted

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 >.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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