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

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

1. rsyslog conf 설정

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

#variables required for non-syslog log file forwarding – mysql error
#edit on your location
$InputFileName /var/log/mysqld.log

$InputFileTag mysqld-errors
$InputFileStateFile mysqld-errors
$InputFileSeverity error
$InputFileFacility local7
$InputRunFileMonitor

#variables required for non-syslog log file forwarding – mysql slow query
$InputFileName /var/log/mysql-slow.log
$InputFileTag mysql-slow
$InputFileStateFile mysql-slow
$InputFileSeverity notice
$InputFileFacility local7
$InputRunFileMonitor

###########################################
#Add details for any more non-syslog log files here
#Copy the variables above for each file you want to add
###########################################

#Set how often you should poll for new events, default is 10sec
$InputFilePollInterval 10

###### 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/messages
& ~

if $programname == ‘mysql-slow’ then /var/log/messages
& ~

*.* /var/log/messages

2) rsyslog restart
# systemctl restart rsyslog

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_%’;