[mariadb] 실행된 SQL 쿼리 이력 확인 (ubuntu)

1. 실행중인 쿼리 확인 (show full processlist)

-- engine 상태
show engine innodb status;

-- 현재 접속중인 유저 및 실행중인 쿼리
-- 기본
show full processlist;
-- 상세
select * from INFORMATION_SCHEMA.PROCESSLIST;

-- 해당 Thread 현황
select * from information_schema.INNODB_TRXG;

-- 마지막에 해당 thread에서 실행중(된) 쿼리 조회
SELECT esc.THREAD_ID, t.processlist_id, esc.SQL_TEXT
  FROM performance_schema.events_statements_current esc
  /**********************************************************************************
  processlist 에서 보여주는 mysql 이 만든 thread와
  performance_schema 에서 만든 thread 값이 달라
  threads 테이블을 참조해야 작업을 확인할 수 있다.
  why? performance_schema.threads 는 background thread의 값도 가지고 있기 때문이다.
  **********************************************************************************/
  JOIN performance_schema.threads t ON t.thread_id = esc.thread_id
 WHERE t.processlist_id = 56604
 ;

 

2. 실행했던 모든 쿼리 확인 (임시)(설정 필요)

mariadb 재실행시 설정이 풀린다.

1)  root 권한으로 mysql로 접속
mysql -u root 
mysql -u root -p // 비밀번호 치고 들어가야 할 때  

2) general log에 관한 설정 조회

show variables where Variable_name in ('version', 'log', 'general_log');
show variables like 'general%';

MariaDB [(none)]> show variables like 'general%';
+------------------+-------------------------+
| Variable_name    | Value                   |
+------------------+-------------------------+
| general_log      | OFF                      |
| general_log_file | general.log |
+------------------+-------------------------+

3) 설정

set global general_log = 1;
-- or
set GLOBAL general_log='ON';

3. 실행된 쿼리 이력 확인 + 로그파일 (영구)(설정 필요)

mysql 설정 파일 설정

1) MySQL의 설정 파일을 연다. (운영체제 마다 위치 상이)
ubuntu ex) mysql
/etc/my.cnf

ubuntu ex) mariadb
/etc/mysql/my.cnf

/etc/mysql/mariadb.cnf

2) general_log 와 general_log_file 을 설정하고 저장합니다.

database를 재기동 하면 쿼리의 로그가 기록됩니다.

# version에 따라서 다를 수 있음
# version에 따라 file로 내보내기만 될 수 있음

# maria
[mariadb]
#쿼리 로그가 출력되도록 설정
general_log
# log_output= FILE | TABLE
# file로 내보내기
log_output=FILE
#로그파일 경로 설정
general_log_file=/var/log/mariadb/general/mariadb_general.log

# mysql
[mysqld]
#쿼리 로그가 출력되도록 설정
general_log=ON
# log_output= FILE | TABLE
# file로 내보내기
log_output=FILE
#로그파일 경로 설정
general_log_file=/var/log/mysql/general/mysql_general.log

3) 재기동 (운영체제 마다 위치 상이)

#database 재기동
/etc/init.d/mysqld restart
/etc/init.d/mysql restart
service mysql restart

#권한없음 나오면 sudo

4) (file인 경우) mysql log 권한 설정 (log를 root에서 조회 하는걸 권장하지 않는다.)

왜 안되지.. ㅠㅠ

# 폴더를 만들어 준 후 mysql에게만 권한을 주도록 하자.
chown -R mysql:mysql /var/log/mysql

# 위와 같이 권한을 설정하였을 경우 또는 etc..로 아래와 같은 문제가 생길 수 있다.
-bash: cd: mysql: Permission denied
sudo: cd: command not found

# mysql log 조회시 계정(권한을 준 계정)을 달리 들어가서 접속하면 보인다.
su - [계정명]
su - mysql

# Authentication failure Error
# root에 비밀번호가 설정이 안되었을 경우
# 개인이면 상관없지만 회사라면 꼭 상사한테 물어보고 설정할 것
sudo passwd root

 

참고 사이트

MySQL 로그와 파라미터 설정 정리

[MariaDB] general log 설정하기

[MariaDB] general log 설정하기

performace_schema 를 이용하여 최근에 실행된 쿼리 확인하기

performace_schema 를 이용하여 최근에 실행된 쿼리 확인하기

[MySQL] 쿼리 로그 확인

[MySQL] 쿼리 로그 확인

 

4. 실행 속도 느린 쿼리 확인

MySQL의 설정 파일을 편집 명령어로 열고 slow_query_log 와 slow_query_log_file 을 설정하고 저장합니다.

MySQL을 재기동 하면 설정한 내용이 반영됩니다.

# 0:끄기,1:켜기
vim /etc/my.cnf slow_query_log=1

#로그파일 경로 설정
slow_query_log_file=/home/mysql/mysqld-slow.log
#5초이상 걸리는 쿼리
long_query_time=5

#MySQL 재기동
/etc/init.d/mysqld restart

이 설정으로 쿼리의 실행시간을 확인 할 수 있습니다.

long_query_time의 기본값은 10초입니다.

 

5. 쿼리 확인

파일 경로를 지정해놨으면 file 경로에 가서 보기!

table에 저장하면 해당 테이블(general_log, slow_log....)에서 볼 수 있다.

SELECT * FROM mysql.general_log;
SELECT * FROM mysql.slow_log;

 

6. 참조

-- 둘다 같음 서버 상태
SELECT * FROM information_schema.GLOBAL_STATUS;
show status;
-- maria db에 접속되어 있는 client 정보 리스트
SHOW PROCESSLIST;

-- Aborted_connects : MySQL 서버에 접속이 실패된 수
-- Max_used_connections : 최대로 동시에 접속한 수
-- Threads_connected : 현재 연결된 Thread 수
-- Connections : mysql서버에 연결시도한 수 
show status like '%connect%'; 
-- Aborted_clients : 클라이언트 프로그램이 비 정상적으로 종료된 수
show status like '%clients%';
-- Threads_cached : Threads Cache의 Thread 수
-- Threads_connected : 현재 연결된 Thread 수
-- Threads_created : 접속을 위해 생성된 Thread 수
-- Threads_running : sleeping 되어 있지 않은 Thread 수
show status like '%thread%';



/* 
커넥션을 모니터링 할 때 위에서 확인한 상태 값을 계산하여 튜닝 여부를 판단할 수 있다.
Cache Miss Rate(%) =  Threads_created / Connections * 100
Connection Miss Rate(%) = Aborted_connects / Connections * 100
Connection Usage(%) = Threads_connected / max_connections * 100 

Connection Usage(%)가 100%라면 max_connections 수를 증가시켜야 한다.
Connection 수가 부족할 경우 Too Many Connection Error가 발생한다.

DB서버의 접속이 많은 경우 wait_timeout을 최대한 적게 설정하여 불필요한 연결을 빨리 정리하는 것이 좋다.
그러나 Connection Miss Rate(%)가 1% 이상이면 wait_timeout을 좀 더 길게 설정한다.

MySQL 서버는 외부로부터 접속 요청을 받을 경우 인증을 위해
IP 주소를 호스트네임으로 바꾸는 과정을 수행하여 접속 시에 불필요한 부하가 발생한다.
skip-name-resolve를 설정하고 접속시 IP기반으로 접속을 하게 되면
hostname lookup 과정을 생략하게 되어 좀 더 빠르게 접속할 수 있다.
*/ 

show variables like '%timeout';

 

도커 컨테이너에서 DB를 구성하는 경우

도커 컨테이너에서 데이터베이스를 구성하는 경우 환경마다 다르겠지만 "logrorate" 가 없어서

로그가 분할되지않고 한 파일에 다 기록되는 경우가 있을 수 있다.

이럴경우 나중엔 너무 길어서 읽히지도 않는다;;

컨터이너 안에 "logrorate" 가 설정되어있는지 확인 후 설치하고 설정하자.

 

https://jang8584.tistory.com/275

[MYSQL] general_log 및 logrorate 관리

https://www.manualfactory.net/10547

Ubuntu 16.04 / logrotate 설치하고 설정하기

 

+ 참조 사이트

https://mariadb.com/kb/en/general-query-log/

General Query Log

ponyozzang.tistory.com/170

MySQL Database - 실행한 SQL 쿼리 이력을 확인하는 방법

morphys.tistory.com/entry/mysql%EC%97%90-%EC%9E%85%EB%A0%A5%ED%96%88%EB%8D%98-%EB%AA%85%EB%A0%B9%EC%96%B4history%EB%A5%BC-%EB%B3%B4%EA%B8%B0

mysql에 입력했던 명령어(history)를 보기

 

https://dev.mysql.com/doc/refman/5.6/en/performance-schema-system-variables.html#sysvar_performance_schema

MySQL :: MySQL 5.6 Reference Manual :: 22.15 Performance Schema System Variables

 

error 참조용

https://serverfault.com/questions/351062/general-logging-wont-work-in-mysql/351074

General logging won't work in MySQL

https://askubuntu.com/questions/291666/why-doesnt-sudo-cd-var-named-work

Why doesn't `sudo cd /var/named` work?

https://km0830.tistory.com/5

데이터베이스 timeout 설정하기

https://hongal.tistory.com/14

[DB][MySQL][관리] MySQL의 TimeOut

https://m.blog.naver.com/jevida/220370360735

MySQL/MariaDB Connection 관련 설정 변수