Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have privileges floating around in the mysql.* tables pertaining to users, databases and tables which no longer exist. Is there a simple way to find and remove these orphaned entries?

share|improve this question
up vote 1 down vote accepted

This really isn't the simple way. But you could get some of what you want by composing a set of outer joins that would compare the privilege tables against information_schema tables. After comparing any privilege table to the schema you could then compare the user table against the privilege tables to find users without any privileges.

The challenging part is that most privilege tables allow you to use the % character as a wildcard. So you could be permitting database_foo_% which would map to database_foo_1, database_foo_blah and so on. Given the the wildcard feature, I doubt there is any completely automated setup. Or at least if there is one it would have to be pretty complex.

For example this will look for entries where There is a database(schema) without privileges, or a set of privileges that don't directly match a database.

select SCHEMA_NAME,Db 
     information_schema.SCHEMATA left join 
     mysql.db on 
where Db is null
select SCHEMA_NAME,Db 
     information_schema.SCHEMATA right join 
     mysql.db on 
where SCHEMA_NAME is null;
share|improve this answer

Your Answer


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.