Page Comparison

...

[ Introduction ] [ Valid tags and data tables ] [ Table structure ]

Introduction

The tags beginning with with db.mysql mysql identify log events generated by the MySQL Server. Specifically, the available tags correspond to the server's general query, error, and slow query logs.

This article describes the tag naming structure, a little about the log files, and how to send log events from the MySQL server to Devo.

Tag structure

Valid tags and data tables

The full tag must have at least six levels. The first two are fixed as db.mysql. The third level identifies the log type/format and currently must be one of error error,out, or slow slow.

The fourth, fifth and sixth levels are required and should identify the environment type, server name, and node/node group respectively.

environment - Describes the environment in when the event occurred. For example, development, testing, or production.
application - The name of the MySQL database server.
clon - The name or ID of the node/node group that generated the event

The values of these levels should be guided by the structure we propose because they will be saved in the events when saved in Devo. When you open the resulting data table, these will appear in the environment, application, and clon columns fields.

...

technology

...

brand

...

log type/format

...

environment

...

web application

...

clon

...

db

...

mysql

...

error
out
slow

free but required

...

free but required

...

free but required

Therefore, the valid tags include:These are the valid tags and corresponding data tables that will receive the parsers' data:

Product / Service	Tags	Data tables
MySQL	`db.mysql.error.<env>.<app>.<clon>`	`db.mysql.error`
	`db.mysql.`

...

out.

...

<env>.

...

<app>.

...

<clon>

db.mysql.out

...


`db.mysql.slow.<env>.<app>.<clon>`	`db.mysql.slow`

...

For more information, read more about Devo tags.

MySQL logs

Make sure that your MySQL Server is configured to generate the log events that you want to send to Devo. For example, both the general and slow query logs are disabled by default. Additionally, the error log and the slow query log can be configured to log different kinds of events. You should review the configuration of these logs before setting up the forwarding of events to Devo.

...

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

Code Block

template(
    name    name="mysql"
    type    type="string"
    string    string="<%PRI%>%timegenerated% %HOSTNAME% %syslogtag% %msg%"
)
# MySQL general log
input(
    type    type="imfile"
    File    File="/var/lib/mysql/mysql.log"
    Tag    Tag="db.mysql.out.ENV.APP.CLON:"
    Facility    Facility="user"
    Severity    Severity="info"
    PersistStateInterval    PersistStateInterval="1"
    reopenOnTruncate    reopenOnTruncate="on"
    freshStartTail    freshStartTail="on"
 
    ruleset    ruleset="mysql-rules"
)
 
# MySQL error log
input(
    type    type="imfile"
    File    File="/var/lib/mysql/mysql-error.log"
    Tag    Tag="db.mysql.error.ENV.APP.CLON:"
    Facility    Facility="user"
    Severity    Severity="info"
    PersistStateInterval    PersistStateInterval="1"
    reopenOnTruncate    reopenOnTruncate="on"
    freshStartTail    freshStartTail="on"
 
    ruleset    ruleset="mysql-rules"
)
 
# MySQL slow log
input(
    type    type="imfile"
    File    File="/var/lib/mysql/mysql-slow.log"
    Tag    Tag="db.mysql.slow.ENV.APP.CLON:"
    Facility    Facility="user"
    Severity    Severity="info"
    PersistStateInterval    PersistStateInterval="1"
    reopenOnTruncate    reopenOnTruncate="on"
    freshStartTail    freshStartTail="on"
 
    ruleset    ruleset="mysql-rules"
)
 
ruleset(name="mysql-rules") {
    action    action(
        type        type="omfwd"
        template        template="mysql"
        queue        queue.type="LinkedList"
        queue        queue.filename="mysqlq1"
        queue        queue.saveonshutdown="on"
        action        action.resumeRetryCount="-1"
        Target        Target="DEVO-RELAY"
        Port        Port="PORT"
        Protocol        Protocol="tcp"
        )
}
 
# Send to Devo directly over TLS
#ruleset(name="mysql-rules") {
#   #    action(
#        #        type="omfwd"
#        #        template="mysql"
#       #        queue.type="LinkedList"
#       #        queue.filename="mysqlq1"
#       #        queue.saveonshutdown="on"
#       #        action.resumeRetryCount="-1"
#       #        Target="collector-us.devo.io"
#       #        Port="443"
#       #        Protocol="tcp"
#        #        StreamDriver="gtls"
#       #        StreamDriverMode="1"
#       #        StreamDriverAuthMode="x509/name"
#       #        StreamDriverPermittedPeers="collector-us.devo.io"
##       )
#}

Note the following placeholder values in the sample file above:

...

Code Block

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

When you have made all the configuration changes necessary, don't forget to restart the rsyslog process:

Code Block
/etc/init.d/rsyslog restart

Windows environments

You can use a MySQL Server system variable to write the error events to the Windows event log. See the vendor documentation for the version of MySQL Server you are running.

...

Code Block

$template mysql-local,"<%PRI%>%timegenerated% %HOSTNAME% db.mysql.error.ENV.APP.CLON:%msg%"
   
# SSL config for DEVO Cloud
#$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 @@DEVO-RELAY:PORT;mysql-local
:programname, contains, "mysql" ~

Table structure

These are the fields displayed in these tables:

db.mysql.error
db.mysql.out
db.mysql.slow

Anchor
tag1
tag1
db.mysql.error

Field	Type	Source field name	Extra field
eventdate	`timestamp`
environment	`str`	venv
application	`str`	vapp
clon	`str`	vclon
rawMessage	`str`	rawSource	✓
message	`str`
hostchain	`str`		✓
tag	`str`		✓

Anchor
tag2
tag2
db.mysql.out

Field	Type	Source field name	Extra field
eventdate	`timestamp`
environment	`str`	venv
application	`str`	vapp
clon	`str`	vclon
rawMessage	`str`	rawSource	✓
message	`str`
hostchain	`str`		✓
tag	`str`		✓

Anchor
tag3
tag3
db.mysql.slow

Field	Type	Source field name	Extra field
eventdate	`timestamp`
environment	`str`	venv
application	`str`	vapp
clon	`str`	vclon
rawMessage	`str`	rawSource	✓
message	`str`
hostchain	`str`		✓
tag	`str`		✓

Versions Compared

Old Version 9

New Version Current

Key

Introduction

Tag structure

Valid tags and data tables

MySQL logs

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

Windows environments

Table structure

Anchor
tag1
tag1
db.mysql.error

Anchor
tag2
tag2
db.mysql.out

Anchor
tag3
tag3
db.mysql.slow

Page Comparison

Versions Compared

Old Version 9

New Version Current

Key

Introduction

Tag structure

Valid tags and data tables

MySQL logs

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

Windows environments

Table structure

Anchortag1tag1db.mysql.error

Anchortag2tag2db.mysql.out

Anchortag3tag3db.mysql.slow

Anchor
tag1
tag1
db.mysql.error

Anchor
tag2
tag2
db.mysql.out

Anchor
tag3
tag3
db.mysql.slow