db.mysql

The tags beginning with db.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

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, out, or 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.

Therefore, the valid tags include:

• db.mysql.error.env.app.clon

• db.mysql.out.env.app.clon

• db.mysql.slow.env.app.clon

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.

For full details about the MySQL Server logs and configuration options, see the vendor documentation.

Sending to Devo via files

Unix environments

You can read more about using rsyslog to monitor and send files to a Devo endpoint in the Sending data to Devo section of our documentation. Here we offer a sample rsyslog configuration file that is set up to monitor the error, general query and slow query logs, and forward them to a Devo Relay. 

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

template(
    name="mysql"
    type="string"
    string="<%PRI%>%timegenerated% %HOSTNAME% %syslogtag% %msg%"
)
# MySQL general log
input(
    type="imfile"
    File="/var/lib/mysql/mysql.log"
    Tag="db.mysql.out.ENV.APP.CLON:"
    Facility="user"
    Severity="info"
    PersistStateInterval="1"
    reopenOnTruncate="on"
    freshStartTail="on"

    ruleset="mysql-rules"
)

# MySQL error log
input(
    type="imfile"
    File="/var/lib/mysql/mysql-error.log"
    Tag="db.mysql.error.ENV.APP.CLON:"
    Facility="user"
    Severity="info"
    PersistStateInterval="1"
    reopenOnTruncate="on"
    freshStartTail="on"

    ruleset="mysql-rules"
)

# MySQL slow log
input(
    type="imfile"
    File="/var/lib/mysql/mysql-slow.log"
    Tag="db.mysql.slow.ENV.APP.CLON:"
    Facility="user"
    Severity="info"
    PersistStateInterval="1"
    reopenOnTruncate="on"
    freshStartTail="on"

    ruleset="mysql-rules"
)

ruleset(name="mysql-rules") {
    action(
        type="omfwd"
        template="devo"
        queue.type="LinkedList"
        queue.filename="mysqlq1"
        queue.saveonshutdown="on"
        action.resumeRetryCount="-1"
        DEVO-RELAY="127.0.0.1"
        PORT="5140"
        Protocol="tcp"
    )
}

# Send to Devo directly over TLS
#ruleset(name="mysql-rules") {
#    action(
#        type="omfwd"
#        template="devo"
#        queue.type="LinkedList"
#        queue.filename="mysqlq1"
#        queue.saveonshutdown="on"
#        action.resumeRetryCount="-1"
#        DEVO-RELAY="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:

• Replace ENV.APP.CLON with the values that represent the source environment, server name, and node reference.

• Replace DEVO-RELAY and PORT with the IP address and port of your Devo Relay. 

You can uncomment the SSL section of the file to send the events directly to the Devo Cloud. In this case, you should replace DEVO-RELAY and PORT with the hostname of your Devo domain and port 443. 

Make sure that the file you are sending and the directory where resides can be read by the user running rsyslog. If not, you can assign the necessary permissions by changing the file group to syslog:

chown :syslog /var/log/mysql /var/log/mysql/*.log
chmod 750 /var/log/mysql
chmod 640 /var/log/mysql/*.log

To configure file rotation, you can use the logrotate utility. The following in an example of a mysql.conf might be set up.

/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
}

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

/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.

You can use the rsyslog to tag the logs and send them to Devo. The following configuration file is a sample that needs to be customized to use the complete tag, correct paths to the security certificates (if forwarding to the DevoCloud directly), and the DEVO-RELAY and PORT of the destination.

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

$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" ~