*. Database 별 권한이 있는 User -> 사용중인 User와 Database

SQL> 

SELECT USER,HOST,Db FROM mysql.db;

*. 사용하는 곳이 없는 User  -> 삭제 예정

(관리용 계정은 제외)

SQL > 

SELECT USER,HOST FROM mysql.user

LEFT JOIN mysql.db USING(USER,HOST)

WHERE mysql.db.db IS NULL

AND mysql.user.Select_priv = 'N';

*. User 의 사용 가능 Db

SELECT USER,HOST,GROUP_CONCAT(Db) AS 'databases' , COUNT(db) AS 'database_number' FROM mysql.user

LEFT JOIN mysql.db USING(USER,HOST)

WHERE mysql.user.Select_priv = 'N'

GROUP BY USER,HOST;

*. Database 기준 User수, 최근 update일

(mysql 특수 Database 는 제외)

SQL > 

SELECT A.SCHEMA_NAME , 

(SELECT COUNT(*) FROM mysql.db B WHERE B.db = A.SCHEMA_NAME) AS user_number ,

(SELECT MAX(UPDATE_TIME) FROM `information_schema`.`TABLES` C WHERE C.TABLE_SCHEMA = A.SCHEMA_NAME) AS last_UPDATE_TIME 

FROM `information_schema`.`SCHEMATA` A

WHERE A.SCHEMA_NAME NOT IN('performance_schema','information_schema','mysql')

ORDER BY user_number DESC,last_UPDATE_TIME DESC;