Table of Contents | ||||||
---|---|---|---|---|---|---|
|
[ 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.
...
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 |
|
|
|
|
...
|
|
...
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="devomysql" queue queue.type="LinkedList" queue queue.filename="mysqlq1" queue queue.saveonshutdown="on" action action.resumeRetryCount="-1" Target="DEVO-RELAY="127.0.0.1" PORT="5140" Protocol" Port="PORT" Protocol="tcp" ) } # Send to Devo directly over TLS #ruleset(name="mysql-rules") { # # action( # # type="omfwd" # # template="devomysql" # # queue.type="LinkedList" # # queue.filename="mysqlq1" # # queue.saveonshutdown="on" # # action.resumeRetryCount="-1" # DEVO-RELAY# Target="collector-us.devo.io" # PORT# 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 postrotate compress sharedscripts postrotate test 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 else fi else $MYADMIN $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:
Anchor | ||||
---|---|---|---|---|
|
Field | Type | Source field name | Extra field |
---|---|---|---|
eventdate |
|
| |
environment |
| venv | |
application |
| vapp | |
clon |
| vclon | |
rawMessage |
| rawSource | ✓ |
message |
|
| |
hostchain |
|
| ✓ |
tag |
|
| ✓ |
Anchor | ||||
---|---|---|---|---|
|
Field | Type | Source field name | Extra field |
---|---|---|---|
eventdate |
|
| |
environment |
| venv | |
application |
| vapp | |
clon |
| vclon | |
rawMessage |
| rawSource | ✓ |
message |
|
| |
hostchain |
|
| ✓ |
tag |
|
| ✓ |
Anchor | ||||
---|---|---|---|---|
|
Field | Type | Source field name | Extra field |
---|---|---|---|
eventdate |
|
| |
environment |
| venv | |
application |
| vapp | |
clon |
| vclon | |
rawMessage |
| rawSource | ✓ |
message |
|
| |
hostchain |
|
| ✓ |
tag |
|
| ✓ |