Archív značiek: consistency

MySQL consistency check – permissions and non-accessible databases

mysql-grant-syntax-2The following query lists all grants for nonexistent databases:

Grants for nonexistent databases

/* grants in mysql.db for nonexistent databases */
SELECT DISTINCT db
FROM mysql.db AS d
LEFT JOIN information_schema.SCHEMATA AS s ON (d.db = s.SCHEMA_NAME)
WHERE s.SCHEMA_NAME IS NULL;

/* grants cleanup */
DELETE mysql.db
FROM mysql.db 
LEFT JOIN information_schema.SCHEMATA ON (mysql.db.db =  information_schema.SCHEMATA.SCHEMA_NAME)
WHERE information_schema.SCHEMATA.SCHEMA_NAME IS NULL;

Databases withouth grants

/* databases withouth grants */ \
SELECT DISTINCT s.SCHEMA_NAME
FROM information_schema.SCHEMATA AS s 
LEFT JOIN mysql.db AS d ON (d.db = s.SCHEMA_NAME)
WHERE d.db IS NULL;

Queries are not complete, you are welcome to add other queries.