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