반응형
information_schema 활용하기
MySQL 혹은 MariaDB의 매타데이터들을 담고 있는 information_schema DB를 활용하는 방법입니다.
사용하시기 전에 주의 하실점이 있습니다.
※ information_schema 의 정보를 함부러 수정 하시면 안됩니다.
간혹 information_schema DB 내 Table에 insert, update를 하시면 절대 않됩니다.
DB가 실행이 않되거나, 멈출수 있습니다.
※ information_schema 의 정보는 서비스 계정에서 노출되면 위험합니다.
DB의 거의 대부분의 정보를 가지고 있기 때문에 유용하지만, 그만큼 외부로 노출되면 위험합니다.
따라서, 일반 계정에서는 사용 권한을 주지 말아야 합니다.
예를들어, 서비스계정에서 information_schema 조회가 가능하여 불법적으로 SQL Injection 과 같은 방법을 통해 정보가 유출되면 해킹의 위험이 있으니 주의 하셔야 합니다.
일단 information_schema DB에 어떤 테이블들이 존재 하는지 확인해 보지요.
USE information_schema;
SHOW TABLES;
Tables_in_information_schema
---------------------------------------
ALL_PLUGINS
APPLICABLE_ROLES
CHARACTER_SETS
COLLATIONS
COLLATION_CHARACTER_SET_APPLICABILITY
COLUMNS
COLUMN_PRIVILEGES
ENABLED_ROLES
ENGINES
EVENTS
FILES
GLOBAL_STATUS
GLOBAL_VARIABLES
KEY_CACHES
KEY_COLUMN_USAGE
PARAMETERS
PARTITIONS
PLUGINS
PROCESSLIST
PROFILING
REFERENTIAL_CONSTRAINTS
ROUTINES
SCHEMATA
SCHEMA_PRIVILEGES
SESSION_STATUS
SESSION_VARIABLES
....................................................................................................
상당히 많은 테이블들이 존재 하는데 몇가지 유용한 테이블들만 설명합니다.
향후, 시간이 되면 Lock table 들을 찾아 내거나 DB 튜닝시에도 참조 하는 방법들을 설명 할 예정입니다.
-- 유저의 권한설정입니다.
-- 해당테이블을 통해 유저의 권한설정을 변경 할 수도 있습니다.
SELECT * FROM USER_PRIVILEGES;
GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE
------------------------------ ------------- ----------------------- --------------
'root'@'localhost' def SELECT YES
'root'@'localhost' def INSERT YES
'root'@'localhost' def UPDATE YES
....................................................................................................
-- 사용할수 있는 DB Engine.
SELECT * FROM ENGINES;
ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS
------------------ ------- ------------------------------------------------------------------------------------------------ ------------ ------ ------------
CSV YES CSV storage engine NO NO NO
MRG_MyISAM YES Collection of identical MyISAM tables NO NO NO
MyISAM YES MyISAM storage engine NO NO NO
SEQUENCE YES Generated tables filled with sequential values YES NO YES
MEMORY YES Hash based, stored in memory, useful for temporary tables NO NO NO
PERFORMANCE_SCHEMA YES Performance Schema NO NO NO
Aria YES Crash-safe tables with MyISAM heritage NO NO NO
InnoDB DEFAULT Percona-XtraDB, Supports transactions, row-level locking, foreign keys and encryption for tables YES YES YES
....................................................................................................
-- 서버 상태
SELECT * FROM GLOBAL_VARIABLES;
ARIABLE_NAME VARIABLE_VALUE
------------------------------------------------------------ ----------------------------------------
ABORTED_CLIENTS 7
ABORTED_CONNECTS 13496
....................................................................................................
-- 서버 변수값
-- run time중 변경에 주의 합니다.
SELECT * FROM GLOBAL_STATUS;
VARIABLE_NAME VARIABLE_VALUE
------------------------------------------------------ -------------------------------------------------
BINLOG_STMT_CACHE_SIZE 32768
SQL_MODE NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
....................................................................................................
-- 프로세스 리스트
SELECT * FROM PROCESSLIST;
ID USER HOST DB COMMAND TIME STATE INFO TIME_MS STAGE MAX_STAGE PROGRESS MEMORY_USED EXAMINED_ROWS QUERY_ID INFO_BINARY TID
------ ------ --------------- ------------------ ------- ------ -------------------- --------------------------------------- ----------- ------ --------- -------- ----------- ------------- -------- --------------------------------------- --------
46598 root localhost:51662 (NULL) Sleep 7278 (NULL) 7278948.011 0 0 0.000 68632 0 396447 (NULL) 23895
46597 root localhost:51658 information_schema Query 0 Filling schema table select * from PROCESSLIST LIMIT 0, 1000 0.805 0 0 0.000 84680 0 397493 select * from PROCESSLIST LIMIT 0, 1000 23913
....................................................................................................
-- 테이블 리스트
SELECT * FROM TABLES;
ABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT
------------- ------------------ -------------------------------------------------- ----------- ------------------ ------- ---------- ---------- -------------- ----------- ------------------ ------------ --------- -------------- ------------------- ------------------- ------------------- ------------------ -------- ------------------ ---------------------------------------------------
def Study_db account BASE TABLE InnoDB 10 Compact 2 8192 16384 0 0 0 3 2017-12-28 17:25:28 (NULL) (NULL) utf8mb4_unicode_ci (NULL)
def Study_db dept BASE TABLE InnoDB 10 Compact 3 5461 16384 0 0 0 5 2017-12-29 00:56:35 (NULL) (NULL) utf8mb4_unicode_ci (NULL)
def Study_db emp BASE TABLE InnoDB 10 Compact 4 4096 16384 0 0 0 5 2017-12-29 01:08:00 (NULL) (NULL) utf8mb4_unicode_ci (NULL)
def Study_db emp_dept BASE TABLE InnoDB 10 Compact 4 4096 16384 0 0 0 (NULL) 2017-12-29 00:57:53 (NULL) (NULL) utf8mb4_unicode_ci (NULL)
....................................................................................................
-- 컬럼 리스트
SELECT * FROM COLUMNS;
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT
------------- ------------------ ------------------------------------- -------------------------------- ---------------- -------------------- ----------- ---------- ------------------------ ---------------------- ----------------- ------------- ------------------ ------------------ ------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------- --------------------------- ------------------------------- ----------------
def Study_db account id 1 (NULL) NO int (NULL) (NULL) 10 0 (NULL) (NULL) (NULL) int(11) PRI auto_increment select,insert,update,references
def Study_db account name 2 (NULL) NO varchar 50 200 (NULL) (NULL) (NULL) utf8mb4 utf8mb4_unicode_ci varchar(50) select,insert,update,references
....................................................................................................
COLUMNS Table과 TABLES Table을 이용하여 DB Schema를 추출해 보자
-- 전체 DB Schema - Table - Column - (Property)
SELECT
a.table_schema ,
a.table_name,
a.table_rows,
a.auto_increment,
b.column_name,
b.data_type,
b.column_default,
b.ordinal_position,
b.column_key,
b.column_type,
b.extra,
ifnull(character_maximum_length,ifnull(numeric_precision, CASE data_type WHEN 'datetime' THEN 8 WHEN 'timestamp' THEN 4 WHEN 'date' THEN 3 END )) column_length,
b.is_nullable,
b.column_comment
FROM TABLES a, COLUMNS b
WHERE a.table_schema = b.table_schema AND a.table_name = b.table_name
AND a.table_schema NOT IN ('information_schema', 'mysql', 'test', 'lost+found')
GROUP BY a.table_name,b.column_name,data_type,column_type,is_nullable
ORDER BY a.table_schema, a.table_name, ordinal_position;
table_schema table_name table_rows auto_increment column_name data_type column_default ordinal_position column_key column_type extra column_length is_nullable column_comment
------------------ -------------------------------------------------- ---------- -------------- --------------------------- ---------- ------------------- ---------------- ---------- -------------------------------- -------------- ------------- ----------- ----------------
ccnet Binding 0 (NULL) email varchar (NULL) 1 MUL varchar(255) 255 YES
ccnet Binding 0 (NULL) peer_id char (NULL) 2 UNI char(41) 41 YES
ccnet EmailUser 0 2 id int (NULL) 1 PRI int(11) auto_increment 10 NO
ccnet EmailUser 0 2 email varchar (NULL) 2 UNI varchar(255) 255 YES
ccnet EmailUser 0 2 passwd varchar (NULL) 3 varchar(256) 256 YES
ccnet EmailUser 0 2 is_staff tinyint (NULL) 4 tinyint(1) 3 NO
ccnet EmailUser 0 2 is_active tinyint (NULL) 5 tinyint(1) 3 NO
ccnet EmailUser 0 2 ctime bigint (NULL) 6 bigint(20) 19 YES
ccnet EmailUser 0 2 reference_id varchar (NULL) 7 UNI varchar(255) 255 YES
ccnet Group 0 1 group_id int (NULL) 1 PRI int(11) auto_increment 10 NO
ccnet Group 0 1 group_name varchar (NULL) 2 varchar(255) 255 YES
....................................................................................................
statistics Table을 이용하여 Index정보 추출해보자
-- index list
SELECT
table_schema,
table_name,
index_name,
seq_in_index,
column_name,
index_type
FROM statistics
WHERE table_schema NOT IN ('information_schema', 'mysql', 'test', 'lost+found')
GROUP BY table_name, index_name, seq_in_index, column_name
ORDER BY table_schema, table_name, index_name, seq_in_index;
------------ ----------------------------------------- ---------------------------------------------------------------- ------------ ----------------------- ------------
ccnet Binding email 1 email BTREE
ccnet Binding peer_id 1 peer_id BTREE
ccnet EmailUser email 1 email BTREE
ccnet EmailUser PRIMARY 1 id BTREE
ccnet EmailUser reference_id 1 reference_id BTREE
ccnet Group PRIMARY 1 group_id BTREE
ccnet GroupUser PRIMARY 1 group_id BTREE
ccnet GroupUser PRIMARY 2 user_name BTREE
....................................................................................................
이 처럼
information_schema DB를 이용하여 유용한 data를 추출할 수 있다.
반응형
'IT 관련 > Database' 카테고리의 다른 글
| [MySQL] utf8 vs utf8mb4 차이는? (3) | 2017.12.31 |
|---|---|
| MariaDB Galera Cluster- lock문제 (4) | 2017.12.30 |
| MariaDB Galera Cluster - 복구/운영 (1) | 2017.12.30 |
| MariaDB Galera Cluster-설치/셋팅 (1) | 2017.12.30 |
| [MySQL] DB Connection close (0) | 2017.12.30 |
| [MySQL] Join을 이용한 Update, Delete (0) | 2017.12.29 |
| [MySQL]트랜잭션(transaction) 처리#1 (0) | 2017.12.28 |
| [MySQL] 트랜잭션(transaction) 처리 (0) | 2017.12.28 |
댓글