MySQL Error 로그와 Slow-Query 로그 수집

MySQL Error 로그와 Slow-Query 취합을 위한 설정

1. rsyslog conf 설정

1) 설정
vi /etc/rsyslog.d/80-mysql.conf

#variables required for non-syslog log file forwarding – mysql error
#edit on your location

input(type=”imfile”
File=”/var/log/mysqld.log”
Tag=”mysqld-errors”
Severity=”error”
Facility=”local7″)

input(type=”imfile”
File=”/var/log/mysql-slow.log”
Tag=”mysql-slow”
Severity=”notice”
Facility=”local7″)

###### Creates a template for each log file in the Logentries UI
### logic to apply the relevant templates to the different log files

if $programname == ‘mysqld-errors’ then /var/log/plura/ceelog-127.0.0.1.log;CEETemplate
:programname, isequal, “mysqld-errors” ~

if $programname == ‘mysql-slow’ then /var/log/plura/ceelog-127.0.0.1.log;CEETemplate
:programname, isequal, “mysql-slow” ~

2) rsyslog restart
# systemctl restart rsyslog

1-1. PLURA repo 에서 다운로드 받기

# wget https://repo.plura.io/v5/module/rsyslog/80-mysql.conf

# curl https://repo.plura.io/v5/module/rsyslog/80-mysql.conf -o /etc/rsyslog.d/80-mysql.conf

2. MySQL – SLOW QUERY 설정

1) 설정
vi /etc/my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql-slow.log
long_query_time = 3

2) 로그 파일 생성 및 권한 설정
# touch /var/log/mysql-slow.log
# chown mysql.mysql /var/log/mysql-slow.log

3) 권한 확인
# ls -aZ /var/log/mysql*

4) mysql restart
# systemctl restart mysqld

5) 활성화 확인
mysql> show variables like ‘slow_query_%’;