Document toolboxDocument toolbox

db.mysql

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

Introduction

The tags beginning with db.mysql identify events generated by the MySQL Server.

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, 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 fields.

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

Product / Service

Tags

Data tables

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.

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="mysql"         queue.type="LinkedList"         queue.filename="mysqlq1"         queue.saveonshutdown="on"         action.resumeRetryCount="-1"         Target="DEVO-RELAY"         Port="PORT"         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:

  • 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:

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

Table structure

These are the fields displayed in these tables:

db.mysql.error

Field

Type

Source field name

Extra field

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

 

✓

db.mysql.out

Field

Type

Source field name

Extra field

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

 

✓

db.mysql.slow

Field

Type

Source field name

Extra field

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

 

✓