Mysql DBA 업무 SQL

Mysql DBA 업무 SQL

Mysql DBA 업무를 수행하면서 필요했던 SQL 모음이다.

 

계정 및 권한 관리

계정 리스트 확인 하기

use mysql;
select * from user;

계정 생성

/* 계정 생성 */
CREATE USER create user test3@123.123.123.123 identified by '[패스워드]';
-- 계정 생성하면서 권한 주기
GRANT ALL ON *.* TO test@localhost IDENTIFIED BY "[패스워드]";
GRANT Select, Insert, Delete On test_db.test 'test_user'@'192.168.0.20' IDENTIFIED BY '[패스워드]'
FLUSH privileges;

권한 추가

GRANT ALL ON *.* TO test@localhost ;
FLUSH privileges;

권한 삭제

revoke 각종 권한들 on 디비이름.테이블 from 사용자ID@접속호스트
flush privileges

계정 패스워드 변경

set password for testuser@localhost = password('[변경 패스워드]');

현재 접속된 내 계정 권한 확인

SHOW GRANTS FOR CURRENT_USER;

권한 확인

SHOW GRANTS FOR 'test_user'@'%';

test_A의 역할을 test_B 에서 부여

GRANT test_A@'%' TO test_B@'%' ;

사용자 암호 변경

ALTER USER 'user-name'@'localhost' IDENTIFIED BY 'NEW_USER_PASSWORD';
FLUSH PRIVILEGES;

사용자 암호 변경시 ALTER문이 안되는 경우

UPDATE mysql.user SET authentication_string = PASSWORD('NEW_USER_PASSWORD')
WHERE User = 'user-name' AND Host = 'localhost';
FLUSH PRIVILEGES;

 

 

Lock 관리 쿼리

잠금으로 인해 트랜잭션이 차단된 쿼리

SELECT 
    pl.id
    ,pl.user
    ,pl.state
    ,it.trx_id 
    ,it.trx_mysql_thread_id 
    ,it.trx_query AS query
    ,it.trx_id AS blocking_trx_id
    ,it.trx_mysql_thread_id AS blocking_thread
    ,it.trx_query AS blocking_query
FROM information_schema.processlist AS pl 
INNER JOIN information_schema.innodb_trx AS it
	ON pl.id = it.trx_mysql_thread_id
INNER JOIN information_schema.innodb_lock_waits AS ilw
	ON it.trx_id = ilw.requesting_trx_id 
        AND it.trx_id = ilw.blocking_trx_id
  ;

트랜잭션이 활성 상태인 커넥션에서 실행한 쿼리 내역

SELECT A.PROCESSLIST_ID 
     , C.THREAD_ID 
     , CONCAT(A.PROCESSLIST_USER,'@',A.PROCESSLIST_HOST) AS DB_ACCOUNT
     , C.EVENT_NAME 
     , C.SQL_TEXT 
     , sys.format_time(C.TIMER_WAIT) AS DURATION
     , DATE_SUB(NOW(), INTERVAL (
     							  SELECT VARIABLE_VALUE 
     							    FROM performance_schema.global_status SQ 
     							   WHERE SQ.VARIABLE_NAME ='UPTIME'
     							) -C.TIMER_START *10e-13 SECOND ) AS START_TIME
     , DATE_SUB(NOW(), INTERVAL (
     							  SELECT VARIABLE_VALUE 
     							    FROM performance_schema.global_status SQ 
     							   WHERE SQ.VARIABLE_NAME ='UPTIME'
     							) -C.TIMER_END *10e-13 SECOND ) AS END_TIME						
  FROM performance_schema.threads A
 INNER JOIN performance_schema.events_transactions_current B 
 		 ON B.THREAD_ID=A.THREAD_ID 
 INNER JOIN performance_schema.events_statements_history C
         ON C.NESTING_EVENT_ID = B.EVENT_ID 
 WHERE B.STATE = 'ACTIVE'
   AND C.MYSQL_ERRNO = 0
 ORDER BY A.PROCESSLIST_ID , B.TIMER_START

데이터 락 대기 확인

 SELECT * 
   FROM sys.innodb_lock_waits

메타데이터 락 확인

SELECT *
  FROM performance_schema.metadata_locks

 

 

시스템 관리 쿼리

디폴트 캐릭터 셋 확인

-- 데이터베이스
SELECT 
	default_character_set_name 
FROM information_schema.SCHEMATA
;

-- 테이블
SELECT 
	CCSA.character_set_name 
FROM information_schema.TABLES AS T
INNER JOIN information_schema.COLLATION_CHARACTER_SET_APPLICABILITY AS CCSA
	ON CCSA.collation_name = T.table_collation
WHERE T.table_schema = 'Database_Name'
  AND T.table_name = 'Table_Name';
WHERE schema_name = 'Database_Name';

-- 컬럼
SELECT 
	character_set_name 
FROM information_schema.COLUMNS 
WHERE table_schema = 'Database_Name'
  AND table_name = 'Table_Name'
  AND column_name = 'Column_Name';

id로 끝나는 컬럼인데 인덱스가 설정 안된 테이블 및 컬럼

SELECT 
	t.TABLE_SCHEMA
	,t.TABLE_NAME
	,c.COLUMN_NAME
	,IFNULL(kcu.CONSTRAINT_NAME, 'Not indexed') AS Indexed
FROM information_schema.TABLES as t
INNER JOIN information_schema.COLUMNS as c
	ON c.TABLE_SCHEMA = t.TABLE_SCHEMA
		AND c.TABLE_NAME = t.TABLE_NAME
		AND c.COLUMN_NAME LIKE '%_id'
LEFT JOIN information_schema.KEY_COLUMN_USAGE as kcu
	ON kcu.TABLE_SCHEMA = t.TABLE_SCHEMA
		AND kcu.TABLE_NAME = t.TABLE_NAME
		AND kcu.COLUMN_NAME = c.COLUMN_NAME
		AND kcu.ORDINAL_POSITION = 1
WHERE kcu.TABLE_SCHEMA IS NULL
AND t.TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql','sys');
;

TABLE SIZE 체크

SELECT table_name AS TableName
	 , ROUND(((data_length + index_length) / 1024 / 1024), 2) AS SizeInMB
  FROM information_schema.TABLES
 WHERE table_schema = 'testdb'
--  AND table_name = 'Table_Name'

DATABASE SIZE 체크

SELECT table_schema AS Database_Name
  	 , ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS SizeInMB
  FROM information_schema.tables
 GROUP BY table_schema

PK없는 테이블 확인

select 
	table_schema
	,table_name
from information_schema.columns
where table_schema = 'SCHEMA_NAME'
group by 
	table_schema
	,table_name
having sum(if (column_key in ('PRI', 'UNI'), 1, 0)) = 0

MySQL 총 메모리 사용량

SELECT A.*
  FROM sys.memory_global_total A

미사용 인덱스 확인

SELECT *
  FROM sys.schema_unused_indexes

중복된 인덱스 확인

SELECT *
  FROM sys.schema_redundant_indexes sri

변경이 없는 테이블 목록

SELECT T.TABLE_SCHEMA
     , T.TABLE_NAME
     , T.TABLE_ROWS 
     , TIO.COUNT_READ
     , TIO.COUNT_WRITE
  FROM information_schema.TABLES T 
  JOIN performance_schema.table_io_waits_summary_by_table TIO
    ON TIO.OBJECT_SCHEMA = T.TABLE_SCHEMA AND TIO.OBJECT_NAME = T.TABLE_NAME 
 WHERE T.TABLE_SCHEMA NOT IN ('mysql','performance_schema','sys')
   AND TIO.COUNT_WRITE = 0
 ORDER BY T.TABLE_SCHEMA 
        , T.TABLE_NAME

SERVER에서 사이즈가 큰 데이터베이스 찾기

SELECT
	COUNT(*) AS TotalTableCount
	,table_schema
	,CONCAT(ROUND(SUM(table_rows)/1000000,2),'M') AS TotalRowCount
	,CONCAT(ROUND(SUM(data_length)/(1024*1024*1024),2),'G') AS TotalTableSize
	,CONCAT(ROUND(SUM(index_length)/(1024*1024*1024),2),'G') AS TotalTableIndex
	,CONCAT(ROUND(SUM(data_length+index_length)/(1024*1024*1024),2),'G') TotalSize	
FROM information_schema.TABLES
WHERE table_schema NOT IN('information_schema', 'performance_schema', 'mysql','sys')
GROUP BY table_schema
ORDER BY SUM(data_length+index_length) 
DESC LIMIT 10

최근 업데이트를 언제 했는지 확인

SELECT UPDATE_TIME
FROM information_schema.tables
WHERE TABLE_SCHEMA = 'Database_Name'
   AND TABLE_NAME = 'Table_Name'

 

 

튜닝 관련 쿼리

I/O요청이 많은 테이블 목록 확인

SELECT * 
  FROM sys.io_global_by_file_by_bytes 
 WHERE file LIKE '%ibd'

테이블별 작업량 통게 확인

SELECT TABLE_SCHEMA
     , TABLE_NAME
     , rows_fetched 
     , rows_inserted 
     , rows_updated 
     , rows_deleted 
     , io_read 
     , io_write 
  FROM sys.schema_table_statistics A 
 WHERE A.table_schema  NOT IN ('mysql','performance_schema','sys')

풀 테이블 스캔 쿼리 확인

SELECT A.db 
     , A.query 
     , A.exec_count 
     , sys.format_time(A.total_latency) AS FORMATTED_TOTAL_LATENCY
     , rows_sent_avg 
     , rows_examined_avg 
     , last_seen 
  FROM sys.`x$statements_with_full_table_scans` A
 ORDER BY A.total_latency DESC

자주 실행되는 쿼리 목록 확인

SELECT A.db 
     , A.exec_count 
     , A.query 
  FROM sys.statement_analysis A 
 ORDER BY A.exec_count DESC

실행시간이 긴 쿼리 확인

SELECT A.query 
     , A.exec_count 
     , sys.format_time(A.avg_latency) AS avg_latency
     , rows_sent_avg 
     , rows_examined_avg 
     , last_seen 
  FROM sys.`x$statement_analysis` A 
 ORDER BY A.avg_latency DESC, A.exec_count DESC

정렬작업을 수행한 쿼리 목록 확인

SELECT A.*
  FROM sys.statements_with_sorting A
 ORDER BY A.last_seen DESC

임시테이블을 생성하는 쿼리 목록 확인

SELECT A.*
  FROM sys.statements_with_temp_tables A

오래걸린 쿼리와 트랜잭션 찾기

SELECT trx.trx_id
	,trx.trx_started
	,trx.trx_mysql_thread_id
FROM INFORMATION_SCHEMA.INNODB_TRX AS trx
INNER JOIN INFORMATION_SCHEMA.PROCESSLIST AS pl 
	ON trx.trx_mysql_thread_id = pl.id
WHERE trx.trx_started < CURRENT_TIMESTAMP - INTERVAL 59 SECOND
  AND pl.user <> 'system_user'
  ;
  
   
 SELECT 
	pl.id 'PROCESS ID'
	,trx.trx_started
	,esh.event_name 'EVENT NAME'
	,esh.sql_text 'SQL'
FROM information_schema.innodb_trx AS trx
INNER JOIN information_schema.processlist pl 
	ON trx.trx_mysql_thread_id = pl.id
INNER JOIN performance_schema.threads th 
	ON th.processlist_id = trx.trx_mysql_thread_id
INNER JOIN performance_schema.events_statements_history esh 
	ON esh.thread_id = th.thread_id
WHERE trx.trx_started < CURRENT_TIME - INTERVAL 59 SECOND
  AND pl.user <> 'system_user'
ORDER BY esh.EVENT_ID
;

인덱스 사용량에 대한 통계

SELECT 
    TABLE_NAME
    ,INDEX_NAME
    ,SEQ_IN_INDEX
    ,COLUMN_NAME
    ,CARDINALITY
    ,INDEX_TYPE    
FROM INFORMATION_SCHEMA.STATISTICS 
WHERE table_schema = 'DatabaseName'