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

3.ログの確認

Errorまたは Slow Query 発生後、システムログでMySQL関連のログを確認