db.mysql

The logs from the MySQL database server are labeled with the  db.mysql.type.environment.application.clon tag.

MySQL can generate the following log types:

  • General query log (type: out)
  • Error log (type: error)
  • Slow query log (type: slow)
  • Binary log
  • Relay log

Tag structure

The elements environment, application and clone are free, but mandatory. 

  • environment - refers to the environment where the event occurs: development, testing, production, etc. The elements number and name are not assigned by Devo because there may be a log of variation between installations
  • application - refers to the database server name
  • clone - refers to the name/ID of the node that produced the event

The element type is fixed and it identifies the type and format of the event that is being sent. It could take one of the following values: out, error and slow.

For more information on how tags work, check the Introduction to tags article.

General log

This log contains a record of the activity produced in the database such as customers login/logout, SQL consultations that are being executed, etc. It is disabled by default, since it may generate a big volume of logs in very active systems.

How can you enable this log?

  • as a configuration file level by editing /etc/mysql/my.cnf
  • as a parameter in the service start-up with the options --general-log --general-log-file=/var/log/mysql/mysql.log

Enabling the general log at my.cnf

general_log_file        = /var/log/mysql/mysql.log
general_log             = 1
Note that the generation of these logs could affect the system performance in databases with significant activity. 

Error log

This log (disabled by default) contains errors generated by the database during the start/stop and running time.

How can you enable this log?

  • as a configuration file level by editing /etc/mysql/my.cnf
  • as a parameter in the service start-up with the options  --log-error=/var/log/mysql/error.log --log-warnings=2

Enabling the error log at my.cnf

[mysqld_safe]
log_error=/var/log/mysql/error.log
log-warnings=2
 
[mysqld]
log_error=/var/log/mysql/error.log
log-warnings=2

The log-warnings option reports additional information to the error log and it is enabled by default with the value 1. If you need the failed authentications to be written then it must have a value > 1.

Slow query log

This log (disabled by default) reports on the queries whose execution time exceeds a defined time threshold. 

How can you can enable this log ?

  • as configuration file level by editing /etc/mysql/my.cnf
  • as parameter in the service start-up with the options --slow_query_log --slow_query_log_file=/var/log/mysql/mysql-slow.log --long_query_time=2 --log-queries-not-using-indexes

Enabling the query log at my.cnf

log_slow_queries=/var/log/mysql/mysql-slow.log
long_query_time=2 #Umbral en segundos
log-queries-not-using-indexes #Also register queries that do not use indexes

You can also specify the option --log-slow-admin-statements as start-up parameter, allowing the registration of administrative sentences (e.g. ALTER TABLE, OPTIMIZE TABLE, ANALYZE TABLE, REPAIR TABLE, etc) that exceed the established threshold. This parameter is available starting from MySQL version 5.7.1.

Sending to Devo via files

Unix environments

The standard method is to configure MySQL to write its logs to file and rely on another tool (like rsyslog or syslog-ng) to send the events.

/etc/rsyslog.d/45-mysql.conf file

$template mysql,"<%PRI%>%timegenerated% %HOSTNAME% %syslogtag% %msg%"
 
# MySQL error log
$InputFileName /var/log/mysql/error.log
$InputFileTag db.mysql.error.ENV.APP:CLON:
$InputFileStateFile stat-file1-MysqlError
$InputFileSeverity info
$InputFileFacility local7
$InputFilePollInterval 1
$InputFilePersistStateInterval 1
$InputRunFileMonitor
 
# MySQL general log
$InputFileName /var/log/mysql/mysql.log
$InputFileTag db.mysql.out.ENV.APP:CLON:
$InputFileStateFile stat-file1-MysqlOut
$InputFileSeverity info
$InputFileFacility local7
$InputFilePollInterval 1
$InputFilePersistStateInterval 1
$InputRunFileMonitor
 
# MySQL slow log
$InputFileName /var/log/mysql/mysql-slow.log
$InputFileTag db.mysql.slow.ENV.APP:CLON:
$InputFileStateFile stat-file1-MysqlSlow
$InputFileSeverity info
$InputFileFacility local7
$InputFilePollInterval 1
$InputFilePersistStateInterval 1
$InputRunFileMonitor
 
# SSL config for logtrust secure relay
#$DefaultNetstreamDriver gtls # use gtls netstream driver
#$DefaultNetstreamDriverCAFile /etc/rsyslog.d/certs/ca.crt
#$DefaultNetstreamDriverCertFile /etc/rsyslog.d/certs/user.crt
#$DefaultNetstreamDriverKeyFile /etc/rsyslog.d/certs/user.key
#$ActionSendStreamDriverMode 1 # require TLS for the connection
#$ActionSendStreamDriverAuthMode x509/name
#$ActionSendStreamDriverPermittedPeer collector
 
if $syslogtag contains 'db.mysql.' and $syslogfacility-text == 'local7' then @@LOGTRUST-RELAY:PORT;mysql
:syslogtag, contains, "db.mysql." ~
  • If the events are sent to a secure relay, uncomment the configuration file SSL section.
  • Customize the above template as follows:
    • Replace ENV.APP.CLON with the values assigned to your application.
    • Replace LOGTRUST-RELAY and PORT with your Devo relay server and port.
  • Check if the file you are going to process and the directory where it resides can be read by the user running rsyslog. If not, you should give the appropriate permissions (e.g. changing the file group to syslog).

Monitoring file permissions

# chown :syslog /var/log/mysql /var/log/mysql/*.log
# chmod 750 /var/log/mysql
# chmod 640 /var/log/mysql/*.log
  • If you need to configure the files rotation, you can use logrotate. Here is a configuration example:

/etc/logrotate.d/mysql file

/var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log /var/log/mysql/error.log {
    daily
    rotate 7
    missingok
    create 640 mysql syslog
    compress
    sharedscripts
    postrotate
        test -x /usr/bin/mysqladmin || exit 0
 MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf"
        if [ -z "`$MYADMIN ping 2>/dev/null`" ]; then
          if killall -q -s0 -umysql mysqld; then
            exit 1
          fi
        else
          $MYADMIN flush-logs
        fi
    endscript
}
  • Finally, you should restart syslog process:

restarting syslog daemon

/etc/init.d/rsyslog restart

Windows environments

You can send error logs to the system local syslog by using syslog option (available from MySQL version 5.1.20) on the below configuration file or as boot parameter --syslog.

Enabling error log to local syslog

[mysqld_safe]
syslog

You can use the following rsyslog file to tag the logs and send them to Devo:

/etc/rsyslog.d/46-mysql-local.conf

$template mysql-local,"<%PRI%>%timegenerated% %HOSTNAME% db.mysql.error.ENV.APP.CLON:%msg%"
 
# SSL config for logtrust secure relay
#$DefaultNetstreamDriver gtls # use gtls netstream driver
#$DefaultNetstreamDriverCAFile /etc/rsyslog.d/certs/ca.crt
#$DefaultNetstreamDriverCertFile /etc/rsyslog.d/certs/user.crt
#$DefaultNetstreamDriverKeyFile /etc/rsyslog.d/certs/user.key
#$ActionSendStreamDriverMode 1 # require TLS for the connection
#$ActionSendStreamDriverAuthMode x509/name
#$ActionSendStreamDriverPermittedPeer collector
 
if $programname contains 'mysql' then @@LOGTRUST-RELAY:PORT;mysql-local
:programname, contains, "mysql" ~

Have we answered your question?

If not, please contact our technical support team via email by clicking the button below.

CONTACT US