MySQL, MariaDB 의 user와 database 정리확인용 쿼리

*. 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;
댓글
  • No Nickname
    No Comment
  • 권한이 없습니다.
    {{m_row.m_nick}}
    -